MongoDb Group BY MAX date and get field from this document

user_3665897 0
Viewed 425

Look my problen hehe. I don't know how I can do it. There are a lot of assetState 1..n I would like do aggregation for get last asset state group by asset.

Mongo collection : assetState

[
{
    "lsd" : {
     "$id" : ObjectId("lucas")
    },
    "stateDate" : ISODate("2018-09-10T16:26:44.501Z"),
    "assetId" : ObjectId("5b96b7645f2b3c0101520s60")
},
{
    "lsd" : {
     "$id" : ObjectId("denner")
    },
    "stateDate" : ISODate("2018-09-10T17:26:44.501Z"),
    "assetId" : ObjectId("5b96b7645f2b3c0101520s60")
},
{
    "lsd" : {
     "$id" : ObjectId("denner")
    },
    "stateDate" : ISODate("2018-09-10T18:26:44.501Z"),
    "assetId" : ObjectId("5b96b7645f2a8c0001530f61")
},
{
    "lsd" : {
      "$id" : ObjectId("lermen")
        }
    },
    "stateDate" : ISODate("2018-09-10T20:26:44.501Z"),
    "assetId" : ObjectId("5b96b7645f2a8c0001530f61")
},
{
    "lsd" : {
      "$id" : ObjectId("floripa")
    },
    "stateDate" : ISODate("2018-09-10T19:26:44.501Z"),
    "assetId" : ObjectId("5b96b7645f2a8c0001530f61")
}
]

I would like get max "stateDate", so I need get LSD from same row(document).

Expected result:

{
    "lsd" : {
      "$id" : ObjectId("lermen")
    },
    "stateDate" : ISODate("2018-09-10T20:26:44.501Z")
}

I tried to do:

db.getCollection('assetState').aggregate([
{
        $group: {
            "_id": {"assetId": "$assetId"},
            "stateDate": {
                "$max": "$stateDate"
            },
            "lsd":  {$last: "$lsd"} // I tried change $max to $min and $last it din't work :(

        }
]);

Result:

{
    "lsd" : {
      "$id" : ObjectId("floripa")
    },
    "stateDate" : ISODate("2018-09-10T20:26:44.501Z")
}

Many Thanks

aggregation-framework mongodb-query mongodb
4 Answers

Try this query 

db.getCollection('assetState').aggregate([
{$sort:{"stateDate":-1}},
]).limit(1)
user_106901380

you can $sort descending before $group and gets the first item of each group with $arrayElemAt

db.getCollection('assetState').aggregate([
{ $sort: { stateDate: -1 } },
{ $group: { _id: { "assetId" : "$assetId" }, 
    states: { $push: "$$ROOT" }
    }
},
{ $project: { "last_asset": { $arrayElemAt: [ "$states", 0 ] }, _id:0  } },
])

Result:

/* 1 */
{
    "last_asset" : {
        "_id" : ObjectId("5db2b34fa1b70230bba9c4d9"),
        "lsd" : "denner",
        "stateDate" : ISODate("2018-09-10T17:26:44.501Z"),
        "assetId" : "5b96b7645f2b3c0101520s60"
    }
}

/* 2 */
{
    "last_asset" : {
        "_id" : ObjectId("5db2b34fa1b70230bba9c4db"),
        "lsd" : "lermen",
        "stateDate" : ISODate("2018-09-10T20:26:44.501Z"),
        "assetId" : "5b96b7645f2a8c0001530f61"
    }
}
user_85001100
user_64643300

I have this for the group by phase:

{
 _id: {"sourceOfEvent":"$sourceOfEvent","vehicleId":"$vehicleId"},
  maxTimeOfEvent: { $max: "$timeOfEvent" }
}

So group by on 2 fields and I use the max to get the max of the timeOfEvent (which is a date)

user_3904360
Related