MongoDB Sum-Query does not sum

user_12189142 0
Viewed 239

I have the following documents:

{ "_id" : ObjectId("5d9db4462034bf17454d7d33"), "name" : "Product1", "cost_oneoff" : "1", "cost_monthly" : "1", "margin_oneoff" : "1", "margin_monthly" : "1", "price_oneoff" : "1", "price_monthly" : "1" }
{ "_id" : ObjectId("5d9dc2f2d8e17309b46f9b03"), "name" : "Product2", "cost_oneoff" : "0", "cost_monthly" : "1", "margin_oneoff" : "0,5", "margin_monthly" : "0,5", "price_oneoff" : "0", "price_monthly" : "2" }

I want the sum of e.g. cost monthly with the following statement:

{ "_id" : null, "total" : 0 }

Can someone help me? 

db.service_items.aggregate([
    { $match: {$or: [{"_id": ObjectId("5d9db4462034bf17454d7d33")},{"_id": ObjectId("5d9dc2f2d8e17309b46f9b03")}]}},
    { $group: 
        {_id: null,
        total: {
            $sum: "$cost_monthly"
            }
        }
    }
])

Result:

{ "_id" : null, "total" : 0 }

The desired answer is 2

mongodb
2 Answers

The $sum operator only works on an integer. According to the docs it ignores non-numeric values. You seem to have them stored as a string. Change cost_monthly to an integer and you should get the desired result:

"cost_monthly" : 1

You can check it out here.

user_90408660

Like @silencedogood said, The $sum operator only works on an integer. We need to convert the string to a numeric value using $toInt operator.

The following is an example:

db.service_items.aggregate([
    { 
        $match: {
            $or: [
                {
                    "_id": ObjectId("5d9db4462034bf17454d7d33")
                },
                {
                    "_id": ObjectId("5d9dc2f2d8e17309b46f9b03")
                }
            ]
        }
    },
    { 
        $group: {
            "_id": null,
            "total": {
                 $sum: {
                    $toInt: "$cost_monthly"
                 }
            }
        }
    }
])

Note: The $toInt is introduced in Mongo v4.0

user_118579810
Related