How can I get all the sum of fields in an array in mongoose? From the db i want to sum up all the amounts in 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" } }
    }
  }
])