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

user_13363286 0
Viewed 36

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.

aggregation-framework mongodb
1 Answers

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

user_89871280
Related