MongoDB calculate sum of $unwind document -

MongoDB calculate sum of $unwind document -

lets have 2 reports documents embeded line_items document:

reports embeded line_items { _id: "1", week_number: "1", line_items: [ { cash: "5", miscellaneous: "10" }, { cash: "20", miscellaneous: "0" } ] }, { _id: "2", week_number: "2", line_items: [ { cash: "100", miscellaneous: "0" }, { cash: "10", miscellaneous: "0" } ] }

what need perform set of additions on each line_item (in case cash + miscellaneous) , have grand total set on reports query 'gross' field. end next result:

desired result { _id: "1", week_number: "1", gross: "35" },{ _id: "2", week_number: "2", gross: "110" }

i have tried next query no avail:

db.reports.aggregate([{$unwind: "$line_items"},{$group: {_id : "$_id", gross: {$sum : {$add: ["$cash", "$miscellaneous"]}}}}]);

you can't sum strings, you'll first need alter info type of cash , miscellaneous fields in docs numeric type.

but 1 time that, can sum them including line_items. prefix on fields in aggregate command:

class="lang-javascript prettyprint-override">db.reports.aggregate([ {$unwind: "$line_items"}, {$group: { _id : "$_id", gross: {$sum : {$add: ["$line_items.cash", "$line_items.miscellaneous"]}} }} ]);

output:

class="lang-javascript prettyprint-override">{ "result" : [ { "_id" : "2", "gross" : 110 }, { "_id" : "1", "gross" : 35 } ], "ok" : 1 }

mongodb mongodb-query