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 }
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With