How to get lastest record each group in mongodb?

user_7462493 0
Viewed 42

I have a list message with format below:

 [
   {
     "_id": 1,
     "groupID": 1,
     "content": "content 1",
     "createAt": 1
   },
 
   {
     "_id": 2,
     "groupID": 1,
     "content": "content 2",
     "createAt": 2
   },
   {
     "_id": 3,
     "groupID": 2,
     "content": "content 3",
     "createAt": 3
   },
   {
     "_id": 4,
     "groupID": 2,
     "content": "content 4",
     "createAt": 4
   },
   {
     "_id": 5,
     "groupID": 2,
     "content": "content 5",
     "createAt": 5
   },
   {
     "_id": 6,
     "groupID": 2,
     "content": "content 6",
     "createAt": 6
   }
 ]

How to get the last message (compare with 'createAt') with each group?

Expected result:

{
   "_id": 2,
   "groupID": 1,
   "content": "content 2",
   "createAt": 2
}

{
   "_id": 6,
   "groupID": 2,
   "content": "content 6",
   "createAt": 6
}
mongoose aggregation-framework mongodb-query mongodb
1 Answers

You can $sort + $group and use $$ROOT to preserve the last document entirely. Then you need $replaceRoot to promote last document to a root level:

db.collection.aggregate([
    { $sort: { createAt: 1 } },
    {
        $group: {
            _id: "$groupID",
            last: { $last: "$$ROOT" }
        }
    },
    {
        $replaceRoot: { newRoot: "$last" }
    }
])

Mongo Playground

user_62389770
Related