Reputation: 708
I am struggle with a query to get the last item in a subarray of a embedded document.
My document structure is similiar to this one:
> id : ObjectId("5e2984660f596903a9733622")
Poliza :OBJECT
_id
Poliza
DatosPoliza
IdPoliza
Siniestro:OBJECT
_id
Siniestro
SituacionesSiniestro
0. fechaSituacion
situacionSiniestro
1. fechaSituacion
situacionSiniestro
I need to match the following key value in Poliza(filter with):
{$match:{"Poliza.DatosPoliza.IdPoliza" : "BB-0730480328111"}
And at the same time get the last item in an array in Siniestro. I've got it with the following query, in a new collection I've created only with Siniestro object structure, just to make sure that the aggregation works:
db.sim.aggregate([{$unwind:"$Siniestro.SituacionesSiniestro"},{$sort:{"Siniestro.SituacionesSiniestro.fechaSituacion":-1}},{$limit:1},{$group:{_id:"$_id","situaciones":{$push:"$Siniestro.SituacionesSiniestro"}}},{$project:{"Siniestro.SituacionesSiniestro":"$situaciones"}}])
My point is, the queries work isolated from each other. But I need to execute it in the same collection, filtering with the match and using the second large query to get the last item in Siniestro.SituacionesSiniestro.fechaSituacion.
Can you help me?
Document Model
{"_id":{"$oid":"5e2984660f596903a9733622"},
"Poliza":{"DuracionPoliza":"RE",
"DatosSuspensiones":{"Suspension":{"NumeroOrden":{"$numberInt":"1"},
"Motivo":"ER",
"FechaSuspension":"2013-01-01",
"FechaReactivacion":"2013-01-01"}},
"DatosAnulacion":{"MotivoAnulacion":"","FechaAnulacion":"","DetalleAnulacion":""},
"Fechas":{"FechaEfectoInicial":{"$date":{"$numberLong":"1095897600000"}},
"FechaSituacion":"2019-08-20","FechaEmision":"2004-09-23",
"FechaVencimiento":"2020-09-23","FechaEfectoActual":{"$date":{"$numberLong":"1569196800000"}}},
"DatosRiesgos":{"Riesgo":
{"FechaFin":"2020-09-23",
"FechaInicio":"2019-09-23",
"NumeroOrden":{"$numberInt":"1"},
"DescripcionRiesgo":"CL VER HOJAS ENEXAS 4 1 08024 BARCELONA",
"RiesgoHogar":{"SituacionRiesgo":{"NombreVia":"VER HOJAS ENEXAS","Poblacion":"BARCELONA","CodigoPostal":{"$numberInt":"8024"},"ClaseVia":"CL"," ":{"$numberInt":"8"},"OtrosDatosVia":" "},
"ClaseInmueble":"UU","UsoInmueble":"HA"},
"DatosCoberturas":{"Cobertura":[{"FechaFin":"2020-09-23","IdCobertura":"073010000","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"1"},
"DescripcionCobertura":"RESPONSABILIDAD CIVIL","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073013000","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"2"},
"DescripcionCobertura":"ASISTENCIA JURIDICA","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073016001","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"3"},
"DescripcionCobertura":"INCENDIO CONTINENTE","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073016002","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"4"},
"DescripcionCobertura":"AGUA CONTINENTE","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073016003","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"5"},
"DescripcionCobertura":"ROTURAS CONTINENTE","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073016004","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"6"},
"DescripcionCobertura":"ROBO CONTINENTE","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073016006","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"7"},
"DescripcionCobertura":"DANOS ESTETICOS CONTINENTE","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073016226","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"8"},
"DescripcionCobertura":"DA�OS ELECTRICOS CONTINENTE","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073016253","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"9"},
"DescripcionCobertura":"ATMOSFERICOS CONTINENTE","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073018019","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"10"},
"DescripcionCobertura":"CONSORCIO DA�OS","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073018262","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"11"},
"DescripcionCobertura":"CONSORCIO PERDIDA BENEFICIOS","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073311000","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"12"},
"DescripcionCobertura":"ASISTENCIA URGENTE","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073312000","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"13"},
"DescripcionCobertura":"ASIST REPARACIONES Y BRICOLAJE","CapitalAsegurado":{"$numberInt":"0"}},{"FechaFin":"2020-09-23","IdCobertura":"073313000","FechaInicio":"2019-09-23","NumeroOrden":{"$numberInt":"14"},
"DescripcionCobertura":"ASISTENCIA FAMILIAR","CapitalAsegurado":{"$numberInt":"0"}}]}}},
"DatosPoliza":{"DatosMediador":{"ClaseMediador":"CO","IdMediador":{"CodigoInterno":{"$numberInt":"5266433"}},"NombreMediador":"INTEGRACI�N PLATAFORMA DE CORREDORES, S.L."},
"NumeroSuplemento":{"$numberInt":"16"},
"CodigoEntidad":{"CodigoDGS":"C0058","CodigoInterno":"C0058"},
"IdPoliza":"BB-0730480328111",
"DatosRamo":{"DescripcionModalidad":"B00-HOGAR FAMILIAR","RamoDGS":{"$numberInt":"2151"},"RamoEntidad":{"$numberInt":"73"},"ModalidadRamo":"073B00","DescripcionRamo":"RAMO COMBINADO HOGAR"}},
"OrigenesContratacion":{"OrigenContratacion":{"CodigoCentro":{"$numberInt":"9999999"},"NumeroOrden":{"$numberInt":"1"},"DescripcionClase":"OTROS","ClaseContratacion":"OT"}},
"IdProceso":{"$numberInt":"13121"},
"DatosImportes":{"Importes":{"PrimaTotal":{"$numberDouble":"603.18"},"DatosCargos":{"Cargo":[{"Importe":{"$numberDouble":"20.29"},"NumeroOrden":{"$numberInt":"1"},"ClaseCargo":"CO","DescripcionCargo":"Consorcio"},{"Importe":".83","NumeroOrden":{"$numberInt":"2"},"ClaseCargo":"CL","DescripcionCargo":"WWW"},{"Importe":{"$numberDouble":"32.94"},"NumeroOrden":{"$numberInt":"3"},"ClaseCargo":"IP","DescripcionCargo":"IPS"}]},"DatosMoneda":{"Moneda":"EUR","FechaCambio":"1900-01-01","TipoCambio":{"$numberInt":"0"}},"PrimaNeta":{"$numberDouble":"549.12"}}},
"Version":"5.0","numRegistro":{"$numberInt":"1"},
"IdLote":"1009991",
"FraccionPago":"AN",
"Tomador":{"PersonaFisica":{"IdPersona":"11111111T","Nombre":"VKHZXPQX","FechaNacimiento":"1966-03-04","Idioma":"es","Apellido2":"FZVXKXDZZ","Apellido1":"PZVZZ","TipoIdentificacion":"NI","EstadoCivil":"CA","Domicilio":{"NombreVia":"MOISES DE MOISES","Poblacion":"LEON","CodigoPostal":{"$numberInt":"24006"},"ClaseVia":"CL","CALLE":{"$numberInt":"24"},"Pais":"ESP","OtrosDatosVia":"38 6C"},"Sexo":"HO"}},
"ClasePoliza":"NP","SituacionPoliza":"EV"},
"Siniestro":[{"_id":{"$oid":"5e662302b6cf1f3607cdd78b"},
"SituacionesSiniestro":[{"fechaSituacion":{"$date":{"$numberLong":"1583017200000"}},"situacionSiniestro":"AP"},
{"fechaSituacion":{"$date":{"$numberLong":"1583276400000"}},"situacionSiniestro":"PERITO"}],
"IdSiniestroEntidad":"M0835/2020",
"Convenios":[{"Convenio":"AS","numeroOrden":{"$numberDouble":"1"}}],
"DanosSiniestro":[{"DescripcioDano":"pintura","ValorDano":{"$numberDouble":"3000"},"NumeroOrden":{"$numberDouble":"1"},"EstadoDano":"PERITADO"}],
"Reserva":[{"DescripcioReversa":"mano de obra","ImporteReserva":{"$numberDouble":"180"}},{"DescripcioReversa":"material","ImporteReserva":{"$numberDouble":"42"}}],
"DescripcioSiniestro":"Accidente","FechaDeclaracion":{"$date":{"$numberLong":"1551398400000"}},
"FechaOcurrencia":{"$date":{"$numberLong":"1551398400000"}}}]}
Upvotes: 0
Views: 202
Reputation: 14317
The following aggregation query gets the last element of the SituacionesSiniestro
sub-array (within the array Siniestro
):
db.collection.aggregate( [
{ $match: { "Poliza.DatosPoliza.IdPoliza" : "BB-0730480328111" } },
{ $unwind: "$Siniestro" },
{ $project: { "Siniestro.SituacionesSiniestro": { $slice: [ "$Siniestro.SituacionesSiniestro", -1 ] } } },
] )
The output:
{
"_id" : ObjectId("5e68a36f74da2d695db7957c"),
"Siniestro" : {
"SituacionesSiniestro" : [
{
"fechaSituacion" : {
"$date" : {
"$numberLong" : "1583276400000"
}
},
"situacionSiniestro" : "PERITO"
}
]
}
}
Upvotes: 1