MongoDB - Calculate $sum of values in array containing array elements

Question

I have an aggregation query producing an array containing array elements. How can I $sum the values and produce output similar to the following:

  { "additionalReminders": 7 }

This is an example of the data structure. Notice some of the array elements are empty and should be ignored or counted as 0:

  {
    "additionalReminders": [
      [
        "1",
        "1"
      ],
      [],
      [],
      [
        "1"
      ],
      [],
      [
        "1"
      ],
      [],
      [
        "1"
      ],
      [],
      [
        "1"
      ],
      [],
      [],
      [
        "1"
      ]
    ]
  }

Mongo Playground: https://mongoplayground.net/p/BjSU_oYC8KG

codemonkey · Accepted Answer · 2021-02-21 19:55:01Z

1

Here is what you're looking for:

db.collection.aggregate([
  {
    "$unwind": "$additionalReminders"
  },
  {
    "$unwind": "$additionalReminders"
  },
  {
    $group: {
      _id: null,
      "additionalReminders": {
        $sum: {
          "$toInt": "$additionalReminders"
        }
      }
    }
  },
  {
    $project: {
      _id: false,
      additionalReminders: true
    }
  }
])

Playground: https://mongoplayground.net/p/JInbHAmc_yV

The idea is to unwind your field a couple of times so as to get this output:

...
{
    "_id": ObjectId("5a934e000102030405000000"),
    "additionalReminders": "1"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "additionalReminders": "1"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "additionalReminders": "1"
  },
...

Then it's just a simple matter of summing up the $additionalReminders field, but not before we cast it to an Int.

edited Feb 21, 2021 at 19:55

answered Feb 21, 2021 at 19:49

codemonkey

7,9655 gold badges28 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

MongoDB - Calculate $sum of values in array containing array elements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related