MongoDb: grouping then sorting and taking last document

Question

I have a collection of documents. The documents each have a rev , quote and various other properties. I need to group by the quote number then select the single document in the group with latest rev. example 4 documents each with quote 12345 and rev A-D I need the "D" document and all the other docs with their latest revs sent to client. I found a similar question here but cant make it work for me.

query

db.myCollection.aggregate([
{ "$group": {
   "_id": "$quote",
   "rev": { "$first":"$rev"}
}
])

collection

 {
  "_id" : ObjectId("58b6054c17c2cd53cf4176fe"),
  "rev" : "A",
  "quote" : "12345",
},
{
  "_id" : ObjectId("58b6085617c2cd53cf417710"),
  "rev" : "B",
  "quote" : "12345",
},
{
  "_id" : ObjectId("58b6da4617c2cd53cf417728"),
  "rev" : "C",
  "quote" : "12345",
},
{
  "_id" : ObjectId("58b6e5c317c2cd53cf417744"),
  "rev" : "D",
  "quote" : "12345",
},
{
  "_id" : ObjectId("58b6f44917c2cd53cf41776e"),
  "rev" : "A",
  "quote" : "54321",
},
{
  "_id" : ObjectId("58b6ffcb17c2cd53cf417788"),
  "rev" : "B",
  "quote" : "54321",
}

Have you tried adding the sort like the other answer mentioned ? Something like `db.myCollection.aggregate([{"$sort":{"quote":1, "rev":1 }}, { "$group": { "_id": "$quote", "rev": { "$last":"$rev"} } ])` — s7vr, Oct 10 '17 at 17:36
@Veeram that did return data but it was only the _id and rev property. How do I get the entire document? — James Morris, Oct 10 '17 at 17:57
Try `db.myCollection.aggregate([{"$sort":{"quote":1, "rev":1 }}, { "$group": { "_id": "$quote", "data": { "$last":"$$ROOT"} } ])` — s7vr, Oct 10 '17 at 18:02

score 0 · Accepted Answer · answered Oct 10 '17 at 18:16

0

Thanks to @Veeram for this solution.

db.myCollection.aggregate([{"$sort":{"quote":1, "rev":1 }}, { "$group": { "_id": "$quote", "data": { "$last":"$$ROOT"} } ])

answered Oct 10 '17 at 18:16

James Morris

353
5
20

MongoDb: grouping then sorting and taking last document

1 Answers1