In a 'hospital' collection, there are docs for every department in the hospital. Each document has details like department_name, doctors and nurses assigned for this department. The document structure looks like below:

{
    "department": "emergency_care",
    "doctors": ["d1", "d2", "d3"],
    "nurses": ["n1", "n2", "n3", "n4", "n5"]
},
{
    "department": "child_health",
    "doctors": ["d4", "d5"],
    "nurses": ["n6"]
},
{
    "department": "first_aid_room",
    "nurses": ["n7", "n8"]
}

How to calculate the total number of persons in the hospital? , which is sum of all the doctors and nurses in all department.

Expected output: 13

CodePudding user response：

You can try this query:

• First group all (using _id: null) to get the total number of doctor and nurses.
• Also here use a $cond to $sum 0 if the value is not an array and otherwise the array size.
• And then use a $project stage to output the sum of these two values.

db.collection.aggregate([
  {
    "$group": {
      "_id": null,
      "totalDoctors": {
        "$sum": {
          "$cond": {
            "if": {"$isArray": ["$doctors"]},
            "then": {"$size": "$doctors"},
            "else": 0
          }
        }
      },
      "totalNurses": {
        "$sum": {
          "$cond": {
            "if": {"$isArray": ["$nurses"]},
            "then": {"$size": "$nurses"},
            "else": 0
          }
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "nPersons": {
        "$add": ["$totalDoctors","$totalNurses"
        ]
      }
    }
  }
])

Example here