How to limit documents in $group in mongodb aggregation pipeline?

schub Published at Dev

schub

Assume having these dummy documents in a mongo db:

{
  "_id" : ObjectId("58f24f38e5fe51fa52e3a90c"),
  "ref" : "r1",
  "ts" : 1492275000121
}
{
  "_id" : ObjectId("58f24f54e5fe51fa52e3a90d"),
  "ref" : "r1",
  "ts" : 1492275028031
}
{
  "_id" : ObjectId("58f24f5ce5fe51fa52e3a90e"),
  "ref" : "r2",
  "ts" : 1492275036560
}
{
  "_id" : ObjectId("58f24f62e5fe51fa52e3a90f"),
  "ref" : "r3",
  "ts" : 1492275042696
}
{
  "_id" : ObjectId("58f24f64e5fe51fa52e3a910"),
  "ref" : "r2",
  "ts" : 1492275044864
}
{
  "_id" : ObjectId("58f24f69e5fe51fa52e3a911"),
  "ref" : "r1",
  "ts" : 1492275049360
}
{
  "_id" : ObjectId("58f24f6be5fe51fa52e3a912"),
  "ref" : "r3",
  "ts" : 1492275051880
}
{
  "_id" : ObjectId("58f24f6ee5fe51fa52e3a913"),
  "ref" : "r3",
  "ts" : 1492275054512
}
{
  "_id" : ObjectId("58f24f70e5fe51fa52e3a914"),
  "ref" : "r2",
  "ts" : 1492275056344
}

What I want to achieve is to get a list of documents, one for each ref which has the latest timestamp (ts).

Something like:

get all documents where ref is in ["r1", "r2"]
group these documents by ref
for each group return the latest document according to ts

I expect:

[
    {
        "ref":"r1",
        "ts": 1492275049360
    },{
        "ref":"r2",
        "ts": 1492275056344
    }
]

Can I do this with a single db request? I looked into the aggregate function but couldn't find a way to get the latest document of a group.

s7vr

You can use $sort by ts desc followed by $first in $group.

db.collection.aggregate(
   [
     { $match: { ref: { $in: ["r1", "r2"] } } },
     { $sort: { ts: -1 } },
     { $group: { _id: "$ref", ts: { $first: "$ts" } } }
   ]
)

Collected from the Internet

Please contact [email protected] to delete if infringement.