Get the last item in a subarray of a embedded document in MongoDb

Question

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"}}}]}

You can use the same aggregation with match and filter. Since you want the last element of the array, you can use the $slice aggregation operator. — prasad_
– prasad_, Commented Mar 11, 2020 at 3:06
Thank you for your answer. The problem is when I mix them, it doesn't return results. For separate they work, but together I got 0 documents, and the document I am looking for I inserted to get the expected result, I mean it does contain "Poliza.DatosPoliza.IdPoliza" : "BB-0730480328111" — powerPixie
– powerPixie, Commented Mar 11, 2020 at 7:16
Can you post the sample document you are using to test as JSON? — prasad_
– prasad_, Commented Mar 11, 2020 at 7:46

prasad_ · Accepted Answer · 2020-03-11 08:54:53Z

1

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"
                        }
                ]
        }
}

answered Mar 11, 2020 at 8:54

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

prasad_ Over a year ago

The "_id" : ObjectId("5e68a36f74da2d695db7957c") looks different as I had saved with a different value for the document I tested.

Collectives™ on Stack Overflow

Get the last item in a subarray of a embedded document in MongoDb

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related