Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mongodb sum query returning zero [duplicate]

I have a collection balance with records

{  
 "_id" : "uezyuLx4jjfvcqMXN",   
  "amount" : "10",   
"createdBy" : "AGuT6zQtSojvozbbg"   
}
{  
 "_id" : "dCC8GrNdEjym3ryua",   
"amount" : "10",  
"createdBy" : "AGuT6zQtSojvozbbg"  
}

I'm trying to calculate sum of amounts:

db.balance.aggregate([{$group:{_id:"$createdBy",total:{ $sum: "$amount"}}}])

Expected output:

{createdBy:AGuT6zQtSojvozbbg , total:20}

Actual output:

{ "_id" : "AGuT6zQtSojvozbbg", "total" : 0 }
like image 631
ketan kulkarni Avatar asked Mar 17 '17 08:03

ketan kulkarni


1 Answers

You have string values in amount field. From documentation of $sum:

Calculates and returns the sum of numeric values. $sum ignores non-numeric values.

And returning zero is an expected behavior:

If all operands are non-numeric, $sum returns 0.

You should convert field value from string to number:

db.balance.find({amount: {$type:"string"}}).forEach(function(doc) { 
    var amountInt = new NumberInt(doc.amount);
    db.balance.update({_id: doc._id}, {$set: {amount: amountInt}});
});

After that your query will work fine. The only thing you will need to add is projection stage to rename _id field to createdBy.

like image 57
Sergey Berezovskiy Avatar answered Oct 24 '22 06:10

Sergey Berezovskiy