How can I get all the sum of fields in an array in Mongoose? I want to sum up all the amounts in the payments array.

DB:

[
  {
    "_id": 0,
    "name": "shoe",
    "payments": [
      {
        "type": "a",
        "amount": 10
      },
      {
        "type": "b",
        "amount": 15
      },
      {
        "type": "a",
        "amount": 15
      },
      
    ]
  },
  {
    "_id": 0,
    "name": "shirt",
    "payments": [
      {
        "type": "a",
        "amount": 5
      },
      {
        "type": "b",
        "amount": 20
      },
      
    ]
  }
]

Expected result:

{
  "amountSum": 65
}

CodePudding user response：

1. $group - Group all documents.

   1.1. $sum - Sum the value returned from 1.1.1 for the amountSum field.

   1.1.1. $reduce - As payments is an array of objects, sum all the amount for the elements and transform the result from the array to number.

db.collection.aggregate([
  {
    $group: {
      _id: null,
      amountSum: {
        $sum: {
          $reduce: {
            input: "$payments",
            initialValue: 0,
            in: {
              $sum: [
                "$$value",
                "$$this.amount"
              ]
            }
          }
        }
      }
    }
  }
])

Demo @ Mongo Playground

CodePudding user response：

There is a shorter and most likely faster solution:

db.collection.aggregate([
  {
    $group: {
      _id: null,
      amountSum: { $sum: { $sum: "$payments.amount" } }
    }
  }
])