Mongo Aggregate Function to sum variables in collection that match query

Admin 1
Viewed 137

I have the following dataset, which contains marks for a student's test. However, each mark is stored in a separate object for each question of the test, like this:

  {
    school: A,
    question: 1,
    student: 1,
    test: Summer Test,
    mark: 0,
  },
  {
    school: A,
    question: 2,
    student: 1,
    test: Summer Test,
    mark: 1,
  },
  {
    school: A,
    question: 3,
    student: 1,
    test: Summer Test,
    mark: 2,
  },

How can I use a mongo aggregate to calculate how many marks a student got for a particular test by adding up all the individual marks? Then group by school? So the result would look like this:

**school A**    
{
   Summer Test {
      students: {student 1 = 3 marks; student 2 = 0 marks; student 3 = 2 marks}
   }
   Winter Test {
      students: {student 1 = 2 marks; student 2 = 1 marks; student 3 = 3 marks}
   }
}

And so on for each school? Could anyone give me some advice on how to go about this? Many Thanks

aggregation-framework mongodb javascript
2 Answers
db.collection.aggregate([
  {
    //first group by school and test (so we get students outcome per test and school)
    $group: {
      _id: {
        school: "$school",
        test: "$test"
      },
      students: {
        $push: {
          question: "$question",
          student: "$student",
          mark: "$mark"
        }
      }
    }
  },
  //group again, so we can get tests (summer/winter) grouped by school this time
  {
    $group: {
      _id: "$_id.school",
      tests: {
        $push: {
          test: "$_id.test",
          students: "$students"
        }
      }
    }
  }
])
user_1257400
  • $group by school, test and student and sum total mark
  • $group by school and test and construct the array of students in key-value pair
  • $arrayToObject convert above students array to object
  • $group the only by school and construct the array of tests in key-value pair
  • $project to show required fields and convert tests array to object
db.collection.aggregate([
  {
    $group: {
      _id: {
        school: "$school",
        test: "$test",
        student: "$student"
      },
      mark: { $sum: "$mark" }
    }
  },
  {
    $group: {
      _id: {
        school: "$_id.school",
        test: "$_id.test"
      },
      students: {
        $push: {
          k: { $toString: "$_id.student" },
          v: "$mark"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.school",
      tests: {
        $push: {
          k: "$_id.test",
          v: { $arrayToObject: "$students" }
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      school: "$_id",
      tests: { $arrayToObject: "$tests" }
    }
  }
])

Playground

user_89871280
Related