CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
nonagon
nonagon

Reputation: 3473

mongo aggregation field x with max y

I have a collection of documents, e.g.:

{_id: 1, kind: 'cow', nickname: 'bess', weight: 145}
{_id: 2, kind: 'cow', nickname: 'babs', weight: 130}
{_id: 3, kind: 'horse', nickname: 'boris', weight: 140}
{_id: 4, kind: 'horse', nickname: 'gnoris', weight: 110}

I'd like to group them by the 'kind' field, and then return the nickname of the animal with the largest weight in each group, the max weight in the group, and the number of animals in the group, thus returning:

{'kind': 'cow', 'nickname': 'bess', 'max_weight': 145, 'count': 2}
{'kind': 'horse', 'nickname': 'boris', 'max_weight': 140, 'count': 2}

I can see how to return the max weight and count for each group with the following mongo aggregation:

db.aggregate([
    {'$group': {'_id': '$kind',
                'max_weight': {'$max': '$weight'},
                'count': {'$sum': 1}}}
])

Is there a way to have this aggregation return the corresponding nickname for the heaviest animal in each group?

Upvotes: 5

Views: 1067

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50406

Use $sort instead of $max to return a whole document and reference with $first:

db.collection.aggregate([
    { "$sort": { "weight": -1 } },
    { "$group": {
         "_id": "$kind", 
         "max_weight": { "$first": "$weight" },
         "nickname": { "$first": "$nickname" },
         "count": { "$sum": 1 }
    }}
])

That makes sure the document values on the "grouping boundary" is returned by the arguments you give to $first. Since $sort is in descending order of "weight".

Upvotes: 8

Related Questions