CODEWITHSUNDEEP
mongodbaggregate
powerPixie
powerPixie

Reputation: 708

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

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

Answers (1)

prasad_
prasad_

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

Related Questions