Sample input:

{
  "students":[
    {
        "name" : "John",
        "semesters":[
            {
                "semester": "fall",
                "grades": [
                    {"EXAM_1" : 25},
                    {"EXAM_2" : 45},
                    {"EXAM_3" : 22}
                ]
            },
            {
                "semester": "winter",
                "grades": [
                    {"EXAM_1" : 85},
                    {"EXAM_2" : 32},
                    {"EXAM_3" : 17}
                ]
            }
        ]
    },{
        "name" : "Abraham",
        "semesters":[
            {
                "semester": "fall",
                "grades": [
                    {"EXAM_1" : 5},
                    {"EXAM_2" : 91},
                    {"EXAM_3" : 51}
                ]
            },
            {
                "semester": "winter",
                "grades": [
                    {"EXAM_1" : 55},
                    {"EXAM_2" : 62},
                    {"EXAM_3" : 17}
                ]
            }
        ]
    },{
        "name" : "Zach",
        "semesters":[
            {
                "semester": "spring",
                "grades": [
                    {"EXAM_1" : 18},
                    {"EXAM_2" : 19},
                    {"EXAM_3" : 26}
                ]
            },
            {
                "semester": "winter",
                "grades": [
                    {"EXAM_1" : 100},
                    {"EXAM_2" : 94},
                    {"EXAM_3" : 45}
                ]
            }
        ]
    }
  ]
}

So this is what I have so far

data = await db.userstats.aggregate([
    {   "$unwind": "$students.semesters" },
    {   "$unwind": "$students.semesters.fall" },
    {   "$unwind": f"$students.semesters.fall.grades" },
    {
        {   "$sum": [
                {"$match" : { "$students.semesters.fall.grades" : "EXAM_3" } },
                {"$multiply": [2, {"$match" : { "$students.semesters.fall.grades" : "EXAM_1" } }]}
            ] 
        }
    },
    {
        "$project": {
            "name" : "$name",
            "character" : "$students.semesters.fall",
            "exam_name" : "$students.semesters.fall.grades",
            "exam_value" : "2*exam 1   exam 3"
        }
    },
    { "$sort": { "exam_value": -1 }},
    { '$limit' : 30 }
    ]).to_list(length=None) 
print(data)

I've been trying to implement a calculation performed on exam grades for each student in a data sample and comparing it to other students. I am stuck on how to properly perform the calculation. The basic rundown is that I need the output to be sorted calculations of

2*exam 1 exam3.

I understand that $sum cannot be used in the pipeline stage, but I am unaware of how to use the $match command within the $sum operator.

Sample output:

{name: John, calculated_exam_grade: 202, 'semester':'winter'},
{name: Abraham, calculated_exam_grade: 101, 'semester':'fall'}, 
{name: John, calculated_exam_grade: 95, 'semester':'fall'},
etc...

CodePudding user response：

Based on the expected result provided, the query is almost similar to the link I posted in the comment.

1. $unwind - Deconstruct students array.

2. $unwind - Deconstruct student.semesters array.

3. $project - Decorate output documents with the calculation for the calculated_exam_grade field.

4. $sort

5. $limit

db.collection.aggregate([
  {
    "$unwind": "$students"
  },
  {
    "$unwind": "$students.semesters"
  },
  {
    "$project": {
      _id: 0,
      "name": "$students.name",
      "semester": "$students.semesters.semester",
      "calculated_exam_grade": {
        $sum: [
          {
            "$multiply": [
              2,
              {
                $sum: [
                  "$students.semesters.grades.EXAM_1"
                ]
              }
            ]
          },
          {
            $sum: [
              "$students.semesters.grades.EXAM_3"
            ]
          }
        ]
      }
    }
  },
  {
    "$sort": {
      "calculated_exam_grade": -1
    }
  },
  {
    "$limit": 30
  }
])

Sample Mongo Playground