Harry Harry - 2 months ago 16
Node.js Question

Mongo - Sum from values in several ref object arrays

I have two Mongoose schema/models. One for a

Team
of players and one for the
Players
themselves.

Team:

// Other less relevant stuff
goalkeepers: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'Player'
}],
defenders: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'Player'
}],
midfielders: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'Player'
}],
attackers: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'Player'
}],
points: Number


Player:

// Other less relevant stuff
points: Number


The issues is that I need the
Team
points to be the sum of all the points of the
Players
in each position array.

The area I am currently looking into is MongoDB aggregate functions and in particular
$sum
but none of the examples include
ref
objects. The objects would need to be populated first as well so that the points values can be read.

I feel like someone must have come across this problem before but my searches were in vain unfortunately.

Answer

You could use the $lookup operator to do a join on the ref field. But first you'd need create a field that has all the players _id, the best operator to do that would be the $setUnion operator which takes two or more arrays and returns an array containing the elements that appear in any input array. This is ideal within a $project pipeline stage where you can combine all the array fields with the player refs into one array which you can then denormalise, in preparation for the $lookup join.

Consider the following aggregation pipeline:

Team.aggregate([
    { 
        "$project": {
            "players": {
                "$setUnion": [
                    "$goalkeepers", 
                    "$defenders", 
                    "$midfielders", 
                    "$attackers"
                ]
            }
        }       
    },
    { "$unwind": "$players" },
    { 
        "$lookup": {
            "from": "players",
            "localField": "players",
            "foreignField": "_id",
            "as": "resultingArray"
        },
    { "$unwind": "$resultingArray" },
    {
        "$group": {
            "_id": "$_id",
            "total": { "$sum": "$resultingArray.points" } 
        }
    }
], function(err, result){
    console.log(result);
});
Comments