MongoDB sum of all array size in document

Question

On MongoDB I have a collection like this: -

{
  org: "org",
  departments: [
    {
       name: "abc",
       employees: [
         { name: "number1" },
         { name: "number2" },
         { name: "number3" },
       ]
    },
    {
       name: "mno",
       employees: [
         { name: "number4" },
         { name: "number5" }
       ]
    },
    {
       name: "xyz",
       employees: [
         { name: "number6" },
         { name: "number7" }
       ]
    }
  ]
},{
  org: "xyz"....
}

How to find total employees in "org" by using aggregate? expect result { org: "org", total_employees: 7 }, { org: "xyz", total_employees: x },....

mickl · Accepted Answer · 2019-02-14 08:32:23Z

4

You can use $size to get length of inner arrays and $reduce to sum those lengths:

db.collection.aggregate([
    {
        $project: {
        total_employees: {
            $reduce: {
                input: "$departments",
                initialValue: 0,
                in: { $add: [ "$$value", { $size: "$$this.employees" } ]  }
            }
        }
        }
    }
])

Mongo playground

answered Feb 14, 2019 at 8:32

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

Comments

Ashh · Accepted Answer · 2019-02-14 08:38:07Z

3

You can find the $size(length) of the array by looping over departments array using $map aggregation and then can $sum together all the array values

db.collection.aggregate([
  { "$project": { 
    "total_employees": {
      "$sum": {
        "$map": { 
          "input": "$departments",
          "in": { "$size": "$$this.employees" }
        }
      }
    }
  }}
])

Output

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "total_employees": 7
  }
]

edited Feb 14, 2019 at 8:38

answered Feb 14, 2019 at 8:32

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Collectives™ on Stack Overflow

MongoDB sum of all array size in document

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