How to get count of all nested arrays in mongo?

Question

For a given sample data as follows:

{
    name: "School 1",
    classes: [
        {
            name: "Class 1",
            students: [
                {
                    name: "Student 1"
                },
                {
                    name: "Student 2"
                }
            ]
        },
        {
            name: "Class 2",
            students: [
                {
                    name: "Student 3"
                },
                {
                    name: "Student 4"
                },
                {
                    name: "Student 5"
                }
            ]
        },
    ]
},
{
    name: "School 2",
    classes: [
        {
            name: "Class 3",
            students: [
                {
                    name: "Student 6"
                }
            ]
        },
        {
            name: "Class 4",
            students: [
                {
                    name: "Student 7"
                },
                {
                    name: "Student 8"
                }
        },
        {
            name: "Class 5",
            students: [
                {
                    name: "Student 9"
                }
            ]
        }
    ]
}

I need to get the count of classes within each school, and count of students within each class. So a projected output would look like this:

    {
    name: "School 1",
    classesCount: 2,
    classes: [
        {
            name: "Class 1",
            studentsCount: 2
        },
        {
            name: "Class 2",
            studentsCount: 3
        }
    ]
},
{
    name: "School 2",
    classesCount: 3,
    classes: [
        {
            name: "Class 3",
            studentsCount: 1
        },
        {
            name: "Class 4",
            studentsCount: 2
        },
        {
            name: "Class 5",
            studentsCount: 1
        }
    ]
}

I was able to use the following query to get the number of classes per school, but couldn't get further count of students per class:

db.getCollection('Schools').aggregate([
{
  $project: {
      name: 1, //name of school
     classesCount: { $cond: { if: { $isArray: "$classes" }, then: { $size: "$classes" }, else: "NA"} },
     classes: ??
  }
}
])

What should the query look like for this result? The query should also handle null or 0 elements for any class and student.

Answer 1

You have to use $map operator to apply to each class, so you can get the size of sudents for this class.

Here's the query :

db.collection.aggregate([
  {
    $project: {
      classes: {
        $map: {
          input: "$classes",
          as: "classes",
          in: {
            name: "$$classes.name",
            studentsCount: {
              $size: "$$classes.students"
            }
          }
        }
      }
    }
  }
])

You can test the query here.