Aggregating by a field and selecting the document with the max value of another field as a collection

Question

Using the aggregate framework, what is the best way to get documents with a maximum value of a field per grouping so using the collection below I would like to have functionality to return one document for each group_id having the latest date. The second listing 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 Aggregation Framework to find the latest document for each group_id. You will need additional queries to retrieve the full documents based on the 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));
});

Example 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"
}