How to get best 5 results in $group method in mongodb?

November 20, 2022

On production server I use mongodb 4.4

I have a query that works well

db.step_tournaments_results.aggregate([
     { "$match": { "tournament_id": "6377f2f96174982ef89c48d2" } },
         { "$sort": { "total_points": -1, "time_spent": 1 } },

     {
         $group: {
             _id: "$club_name", 
            'total_points': { $sum: "$total_points"}, 
            'time_spent': { $sum: "$time_spent"}
         },
     },
])

But the problem is in $group operator, because it sums all the points of every group for total_points, but I need only best 5 of every group. How to achieve that?

>Solution :

Query

like your query, match and sort
on group instead of sum, gather all members inside one array
(i collected the $ROOT but you can collect only the 2 fields you need inside a {}, if the documents have many fields)
take the first 5 of them
take the 2 sums you need from the first 5
remove the temp fields

*with mongodb 6, you can do this in the group, without need to collect th members in an array, in mongodb 5 you can also do those with window-fields without group, but for mongodb 4.4 i think this is a way to do it

aggregate(
[{"$match": {"tournament_id": {"$eq": "6377f2f96174982ef89c48d2"}}},
 {"$sort": {"total_points": -1, "time_spent": 1}},
 {"$group": {"_id": "$club_name", "group-members": {"$push": "$$ROOT"}}},
 {"$set": 
   {"first-five": {"$slice": ["$group-members", 5]},
    "group-members": "$$REMOVE"}},
 {"$set": 
   {"total_points": {"$sum": "$first-five.total_points"},
    "time_spent": {"$sum": "$first-five.time_spent"},
    "first-five": "$$REMOVE"}}])