MongoDB - Sum the field in an array

Question

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
}

Wernfried Domscheit · Accepted Answer · 2023-01-11 08:17:36Z

2

There is a shorter and most likely faster solution:

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

answered Jan 11, 2023 at 8:17

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

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

Comments

Yong Shun · Accepted Answer · 2023-01-11 07:44:01Z

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

answered Jan 11, 2023 at 7:44

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Collectives™ on Stack Overflow

MongoDB - Sum the field in an array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related