mongodb, $sum on array of objects field

3,935 views

Skip to first unread message

Valeriu Tonu

unread,

Feb 17, 2018, 5:59:52 PM2/17/18

to mongodb-user

I am new to mongodb and may be I am missing something. But having a lot of samples in internet, still having problems to get a total on one field which is part of array of objects. Here is what I am doing:

db.collection.insertMany([
{ "id" : "6002010011500", "balance" : [ { "type" : "PR", "amount" : "1000" 
}, { "type" : "IN", "amount" : "300" } ] },
{"id" :  "5001010001005", "balance": [ { "type" : "PR", "amount" : "-3000" 
}, { "type" : "IN", "amount" : "-600" } ] }
])

trying to get total amount in different ways:

db.collection.aggregate([
{$group:{_id:null, TotalBalance:{$sum:"$balance.amount"}}}
])

getting the balance 0 instead of -2300

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

same things with $unwind:

db.collection.aggregate([
{$unwind:"$balance"},
{$group:{_id:null, TotalBalance:{$sum:"$balance.amount"}}}
])

what I am doing wrong?

Thanks

Wan Bachtiar

unread,

Feb 18, 2018, 11:50:59 PM2/18/18

to mongodb-user

what I am doing wrong?

Hi Valeriu,

Your aggregation pipeline with $unwind is actually correct, and should provides the result you desired.
However, the problem is with the documents; the field type of amount is non-numeric (string). See also $sum operator Non-Numeric or Non-Existent Fields. Try changing those values to numerical values. i.e. "amount": 1000

I am new to mongodb and may be I am missing something.

I would recommend to enrol in a free online course at MongoDB University to learn more about MongoDB, especially M121: the MongoDB Aggregation Framework.