CODEWITHSUNDEEP
mongodbaggregation-framework
JayJohnsonDesigns
JayJohnsonDesigns

Reputation: 33

Why are these MongoDB sums not adding up?

Lets say I have 2 documents:

{ 
    "_id" : "8tvgUTKt2U", 
    "answers" : [
        [
            NumberInt(-1)
        ]
    ]
},
{ 
    "_id" : "BDTESUEIlm", 
    "answers" : [
        [
            NumberInt(1)
        ], 
        [
            NumberInt(-1)
        ]
    ]
}

I would like to total the values of the array items. But when I $unwind 'answers' and $sum them in $group pipeline I get this:

{ 
    "_id" : "BDTESUEIlm", 
    "answerTotal" : NumberInt(0)
}
{ 
    "_id" : "8tvgUTKt2U", 
    "answerTotal" : NumberInt(0)
}

What am I doing wrong?

Upvotes: 1

Views: 100

Answers (1)

chridam
chridam

Reputation: 103475

It’s because when you $unwind a two dimensional array once you end up with just an array and $sum gives correct results when applied to numerical values in $group pipeline stage otherwise it will default to 0 if all operands are non-numeric.

To remedy this, you can use the $sum in a $project pipeline without the need to $unwind as $sum traverses into the array to operate on the numerical elements of the array to return a single value if the expression resolves to an array.

Consider running the following pipeline to get the correct results:

db.collection.aggregate([
    { '$project': {
        'answerTotal': {
            '$sum': {
                '$map': {
                    'input': '$answers',
                    'as': 'ans',
                    'in': { '$sum': '$$ans' }
                } 
            }
        }
    } }
])

Upvotes: 1

Related Questions