Mongo aggregation field x with max y
I have a set of documents, for example:
{_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 would like to group them by the "kind" field, and then return the nickname of the animal with the highest weight in each group, maximum 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 maximum weight and count for each group with the following aggregation of mangoes:
db.aggregate([
{'$group': {'_id': '$kind',
'max_weight': {'$max': '$weight'},
'count': {'$sum': 1}}}
])
Is there a way for this aggregation to return the appropriate nickname for the heaviest animal in each group?
source to share
Use instead to return the entire document and link with : $sort
$max
$first
db.collection.aggregate([
{ "$sort": { "weight": -1 } },
{ "$group": {
"_id": "$kind",
"max_weight": { "$first": "$weight" },
"nickname": { "$first": "$nickname" },
"count": { "$sum": 1 }
}}
])
This ensures that document values at the "grouping boundary" are returned by the arguments you pass $first
. Since it $sort
is in descending order of "weight".
source to share