CODEWITHSUNDEEP
mongodbmongodb-query
jfortunato
jfortunato

Reputation: 12077

MongoDB calculate sum of $unwind document

Lets say I have 2 reports documents with an embeded line_items document:

Reports with 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 I need to do is perform a set of additions on each line_item (in this case cash + miscellaneous) and have the grand total set on the reports query as a 'gross' field. I would like to end up with the following result:

Desired result

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

I have tried the following query to no avail:

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

Upvotes: 1

Views: 1676

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 312035

You can't sum strings, so you'll first need to change the data type of the cash and miscellaneous fields in your docs to a numeric type.

But once you do that, you can sum them by including the line_items. prefix on those fields in your aggregate command:

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

Output:

{
    "result" : [ 
        {
            "_id" : "2",
            "gross" : 110
        }, 
        {
            "_id" : "1",
            "gross" : 35
        }
    ],
    "ok" : 1
}

Upvotes: 3

Related Questions