MongoDB aggregation: array of objects sum with group by

Question

Having the sample data below, I'm trying to get a total count of students and the combined topScore of each subject of each section and floor:

[{
  "section": "east",
  "floor": "1st",
  "classrom": 100,
  "tests": [
  {
    "subject": "math",
    "students": 30,
    "topScore": 90
  },
  {
    "subject": "english",
    "students": 40,
    "topScore": 80
  }]
},
{
  "section": "east",
  "floor": "1st",
  "classrom": 150,
  "tests": [
  {
    "subject": "math",
    "students": 35,
    "topScore": 85
  },
  {
    "subject": "english",
    "students": 45,
    "topScore": 70
  }]
}]

Desired result:

[{
  "section": "east",
  "floor": "1st",
  "classroms": [100, 150],
  "tests": [
  {
    "subject": "math",
    "totalStudents": 65,
    "combinedTopScores": 175
  },
  {
    "subject": "english",
    "totalStudents": 85,
    "combinedTopScores": 150
  }]
}]

What I have so far is:

db.collection.aggregate([{
  "$group": {
    "_id": {
      "section": "$section",
      "floor": "$floor"
    },
    "classrooms": { "$push": "$classroom" },
    "tests": { "$push": "$tests" }
  }
}])

Which gives me:

{
  "_id":
  {
    "section": "east",
    "floor": "1st"
  },
  "classrooms": [100, 150],
  "tests": [
    [{
      "subject": "math",
      "students": 30,
      "topScore": 90
    },
    {
      "subject": "english",
      "students": 40,
      "topScore": 80
    }],
    [{
      "subject": "math",
      "students": 35,
      "topScore": 85
    },
    {
      "subject": "english",
      "students": 45,
      "topScore": 70
    }]
  ]
}

So I'm having a hard time figuring out the $sum of the tests array. Specially because it has to be grouped by subject.

Can anybody point me a direction? Is that even possible?

Thanks!

mickl · Accepted Answer · 2018-09-05 05:26:08Z

You need to $unwind tests array to be able to group by section+floor+subject. Then you can calculate totals and perform second $group stage just by section + floor. Since classroms will be an array of arrays and might contain duplicates you can use $reduce with $setUnion to flatten those arrays and remove duplicated values. Try:

db.collection.aggregate([
    { $unwind: "$tests" },
    {
        $group: {
            _id: {
                section: "$section",
                floor: "$floor",
                subject: "$tests.subject"
            },
            totalStudents: { $sum: "$tests.students" },
            combinedTopScores: { $sum: "$tests.topScore" },
            classroms: { $push: "$classrom" }
        }
    },
    {
        $group: {
            _id: { section: "$_id.section", floor: "$_id.floor" },
            classroms: { $push: "$classroms" },
            tests: {
                $push: {
                    subject: "$_id.subject",
                    totalStudents: "$totalStudents",
                    combinedTopScores: "$combinedTopScores"
                }
            }
        }
    },
    {
        $project: {
            section: "$_id.section",
            floor: "$_id.floor",
            classroms : {
                $reduce: {
                    input: "$classroms",
                    initialValue: [],
                    in: { $setUnion: [ "$$this", "$$value" ] }
                }
            },
            tests: 1
        }
    }
])

Collectives™ on Stack Overflow

MongoDB aggregation: array of objects sum with group by

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