mongo aggregation - accumulate for different groups of values of a field

Question

If I have Player documents of the form

{name: String, score: Int}

and I have Group documents, where groups represent lists of players

{groupName: String, players: [ObjectID]}

Players can belong to multiple groups.

I want to do an aggregation of Player documents, grouped by Group (ex. get the sums of players' scores for each group, with one aggregation pipeline).

Options I'm aware of:

=> give Player docs back pointers to Group documents they are a associated with, then $group by GroupID. But I prefer to not have to modify the Player collection. (perhaps there is a way to "add in" GroupIDs to the docs during the pipeline?)

=> make a separate call for each group and use a $match stage to filter to just players in the current group being queried for. But I prefer to make a single, simple call.

How can I achieve such an aggregation? Does MongoDB Aggregation have something that works for this?

(By the way, it's not a problem to have the groups and players, mapped to each other, in memory where the call is being made. So options involving passing lists of players as arguments are indeed fair game.)

Answer 1

You could use the $lookup operator for this. Consider running the following aggregation pipeline:

Mongo shell:

db.group.aggregate([
    { "$unwind": "$players" },
    {
        "$lookup": {
            "from": "players",
            "localField": "players",
            "foreignField": "_id",
            "as": "players_join"
        }
    },
    { "$unwind": "$players_join" },
    {
        "$group": {
            "_id": {
                "groupName": "$groupName",
                "name": "$players_join.name"
            },
            "totalScore": { "$sum": "$players_join.score" }
        }
    }
])

You can add support for aggregation by getting the aggregation framework package which wraps up some Mongo methods for you. Just meteor add meteorhacks:aggregate and you should be in business. This will add an aggregate method to your collections in Meteor.

Answer 2

Additionnaly to @Chridam answer, to avoid confusing the collections/fields/unwind aliases, I suggest this :

db.Group.aggregate( [
  {$unwind:"$players"}, 
  {$lookup: {
    from:"Player",
    localField:"players",
    foreignField:"_id",
    as:"P" }
  },
  {$unwind:"$P"},
  {"$group":{ 
    total: {$sum:"$P.score"}, 
    _id:"$_id"
  }}  
])