Mongo group documents by id and get the latest timestamp document

Question

Suppose we have the following set of documents stored in mongodb:

{ "fooId" : "1", "status" : "A", "timestamp" : ISODate("2016-01-01T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "1", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "1", "status" : "C", "timestamp" : ISODate("2016-01-03T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "2", "status" : "A", "timestamp" : ISODate("2016-01-01T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "2", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "3", "status" : "A", "timestamp" : ISODate("2016-01-01T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "3", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "3", "status" : "C", "timestamp" : ISODate("2016-01-03T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "3", "status" : "D", "timestamp" : ISODate("2016-01-04T00:00:00.000Z") "otherInfo" : "BAR", ... } 

I would like to get the latest status for each fooId based on timestamp. Therefore, my return will look like this:

 { "fooId" : "1", "status" : "C", "timestamp" : ISODate("2016-01-03T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "2", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00.000Z") "otherInfo" : "BAR", ... } { "fooId" : "3", "status" : "D", "timestamp" : ISODate("2016-01-04T00:00:00.000Z") "otherInfo" : "BAR", ... } 

I tried to do this using aggregation using the group operator, but the part that I'm interested in is an easy way to return the entire document from the aggregation so that it looks the same as if I used a search query? It seems you should specify all the fields when you group, and this does not look extensible if the documents may have optional fields for them that may not be known to me. The current request that I have is as follows:

 db.collectionName.aggregate( [ { $sort: { timestamp: 1 } }, { $group: { _id: "$fooId", timestamp: { $last: "$timestamp" }, status: { "$last": "$status" }, otherInfo: { "$last": "$otherInfo" }, } } ] ) 

Answer 1

If you are doing and aggregating, you need to do the same SQL, which means pointing the aggregation operation to a column, the only option you have is to use the $$ROOT statement

 db.test.aggregate( [ { $sort: { timestamp: 1 } }, { $group: { _id: "$fooId", timestamp: { $last: "$$ROOT" } } } ] ); 

But that will slightly change the result.

 { "_id" : "1", "timestamp" : { "_id" : ObjectId("570e6be3e81c8b195818e7fa"), "fooId" : "1", "status" : "A", "timestamp" :ISODate("2016-01-01T00:00:00Z"), "otherInfo" : "BAR" } } 

If you want to return the original document format, you probably need the $ project step after that

Answer 2

You can use the $$ROOT system variable with $last to return the last document.

 db.collectionName.aggregate([ { "$sort": { "timestamp": 1 } }, { "$group": { "_id": "$fooId", "last_doc": { "$last": "$$ROOT" } }} ]) 

Of course, this will be the last document for each group as a field value.

 { "_id" : "2", "doc" : { "_id" : ObjectId("570e6df92f5bb4fcc8bb177e"), "fooId" : "2", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00Z") } } 

---

If this conclusion does not suit you, the best option would be to add another $group step to the pipeline when you simply return an array of these documents using $push .

 db.collectionName.aggregate([ { "$sort": { "timestamp": 1 } }, { "$group": { "_id": "$fooId", "last_doc": { "$last": "$$ROOT" } }}, { "$group": { "_id": null, "result": { "$push": "$last_doc" } }} ]) 

Answer 3

Although there is no direct way to return the original documents, and I do not see any value, try the aggregation request:

 db.collection.aggregate([ {$sort: {fooId:1, timestamp: -1}}, {$group:{_id:"$fooId", doc:{$first:"$$ROOT"}}}, {$project:{_id:0, doc:["$doc"]}} ]).forEach(function(item){ printjson(item.doc[0]); }); 

This request will emit:

 { "_id" : ObjectId("570e76d5e94e6584078f02c4"), "fooId" : "2", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00.000+0000"), "otherInfo" : "BAR" } { "_id" : ObjectId("570e76d5e94e6584078f02c8"), "fooId" : "3", "status" : "D", "timestamp" : ISODate("2016-01-04T00:00:00.000+0000"), "otherInfo" : "BAR" } { "_id" : ObjectId("570e76d5e94e6584078f02c2"), "fooId" : "1", "status" : "C", "timestamp" : ISODate("2016-01-03T00:00:00.000+0000"), "otherInfo" : "BAR" }