How to sum a bunch of numeric fields in an array of objects in MongoDB?

Question

Below is my stage output after I did a $lookup in an aggregate query:

"projects": [
        {
            "_id": "609d1a10b588c73aeee9aac9",
            "projectType": "public",
            "status": "completed",
            "amt_paid": 100,
            "revisionStatus": false,
            "commission": 20,
            "engineer_submission": "true",
            "revenueOnRevision": 10
        },
        {
            "_id": "609d3754b588c73aeee9aae8",
            "projectType": "public",
            "status": "open",
            "amt_paid": 50,
            "totalBids": 0,
            "revisionStatus": false,
            "commission": 30,
            "engineer_submission": "false",
            "revenueOnRevision": 100
        }
    ]

Desired Output:

   "projects": [
        {
            "_id": "609d1a10b588c73aeee9aac9",
            "projectType": "public",
            "status": "completed",
            "amt_paid": 100,
            "revisionStatus": false,
            "commission": 20,
            "engineer_submission": "true",
            "revenueOnRevision": 10
        },
        {
            "_id": "609d3754b588c73aeee9aae8",
            "projectType": "public",
            "status": "open",
            "amt_paid": 50,
            "totalBids": 0,
            "revisionStatus": false,
            "commission": 30,
            "engineer_submission": "false",
            "revenueOnRevision": 100
        }
    ],
    totalAmt : 310

the field totalAmt gets its value by adding (amt_paid + revenueOnRevision + commission) of every element in the array and summing it all up.

Tried doing:

$set : {
  $sum : {
       $add : ['$projects.amt_paid', '$projects.commission', '$projects.revenueOnRevision']  
       }
     }

I don't understand why the above stage wont work. Would appreciate if someone could point out my mistake or show me the correct way to solve this. Thank you.

turivishal · Accepted Answer · 2021-05-13 19:13:32Z

1

I don't understand why the above stage wont work.

When we select embedded field of array projects.amt_paid, it will result array of elements means numbers as per your sample data, so $add wont sum nested array of numbers directly,

First you need to sum array of numbers using $sum and then use $add/$sum to sum,

  {
    $set: {
      totalAmt: {
        $add: [
          { $sum: "$projects.amt_paid" },
          { $sum: "$projects.commission" },
          { $sum: "$projects.revenueOnRevision" }
        ]
      }
    }
  }

Playground

There are different ways to do this kind of operation, you can use $concatArrays to concat all three array fields and then $sum

  {
    $set: {
      totalAmt: {
        $sum: {
          $concatArrays: [
            "$projects.amt_paid",
            "$projects.commission",
            "$projects.revenueOnRevision"
          ]
        }
      }
    }
  }

Playground

edited May 13, 2021 at 19:13

answered May 13, 2021 at 19:01

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sushmit Over a year ago

thanks a lot turivishal. I understood why it does not work.

Collectives™ on Stack Overflow

How to sum a bunch of numeric fields in an array of objects in MongoDB?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related