Field aggregation and selecting a document with the maximum value of another field as a collection

Question

Using a common structure, the best way to get documents with the maximum field value for each group, therefore, using the collection below, I would like to have the functionality to return one document for each group_id that has the most recent date. The second list shows the desired result.

group_id date 1 11/1/12 1 11/2/12 1 11/3/12 2 11/1/12 3 11/2/12 3 11/3/12 

DESIRED RESULT

 group_id date 1 11/3/12 2 11/1/12 3 11/3/12 

Answer 1

You can use the $max grouping function in the Aggregate structure to find the last document for each group_id . You will need additional requests to obtain complete documents based on grouped criteria.

 var results = new Array(); db.groups.aggregate( // Find documents with latest date for each group_id { $group: { _id: '$group_id', date: { $max: '$date' }, }}, // Rename _id to group_id, so can use as find criteria { $project: { _id: 0, group_id:'$_id', date: '$date' }} ).result.forEach(function(match) { // Find matching documents per group and push onto results array results.push(db.groups.findOne(match)); }); 

Examples of results:

 { "_id" : ObjectId("5096cfb8c24a6fd1a8b68551"), "group_id" : 1, "date" : ISODate("2012-11-03T00:00:00Z"), "foo" : "bar" } { "_id" : ObjectId("5096cfccc24a6fd1a8b68552"), "group_id" : 2, "date" : ISODate("2012-11-01T00:00:00Z"), "foo" : "baz" } { "_id" : ObjectId("5096cfddc24a6fd1a8b68553"), "group_id" : 3, "date" : ISODate("2012-11-03T00:00:00Z"), "foo" : "bat" }