Grouping objects inside an array - MongoDB Aggregation

Question

I am using a training grades database from MongoDB. It is structured as follows.

    "_id": {
        "$oid": "56d5f7eb604eb380b0d8d8fa"
    },
    "class_id": {
        "$numberDouble": "173"
    },
    "scores": [
        {
            "score": {
                "$numberDouble": "19.81430597438296"
            },
            "type": "exam"
        },
        {
            "score": {
                "$numberDouble": "16.851404299968642"
            },
            "type": "quiz"
        },
        {
            "score": {
                "$numberDouble": "60.108751761488186"
            },
            "type": "homework"
        },
        {
            "score": {
                "$numberDouble": "22.886167083915776"
            },
            "type": "homework"
        }
    ],
    "student_id": {
        "$numberDouble": "4"
    }
}

I am trying to run aggregation which returns all documents grouped first by class_id and then by student_id with all homework scores like the following.

{
   class_id: 3,
   all_scores: [
      {
         student_id: 110, 
         scores : [
            {
               type: "homework", 
               score: 89.98
            },
            {
               type: "homework", 
               score: 90.98
            },
         ]
      },
      {
         student_id:190, 
         scores : [
            {
               type: "homework", 
               score: 18.98
            },
            {
               type: "homework", 
               score: 99.98
            },
         ]
      },
   ]
}
   

I am running the following aggregation function.

[
  {
    '$unwind': {
      'path': '$scores'
    }
  }, {
    '$match': {
      'scores.type': 'homework'
    }
  }, {
    '$group': {
      '_id': '$class_id', 
      'scores': {
        '$push': {
          'type': '$scores.type', 
          'score': '$scores.score', 
          'student_id': '$student_id'
        }
      }
    }
  }
]
   

But it is returning the following result:

{
   _id: 3, 
   scores: [
      {
         "type": "homework",
         "score": 89.98, 
         "student_id": 110
      }, 
      {
         "type": "homework",
         "score": 90.98, 
         "student_id": 110
      }, 
      {
         "type": "homework",
         "score": 18.98, 
         "student_id": 190
      }, 
      {
         "type": "homework",
         "score": 99.98, 
         "student_id": 190
      },
   ]
}

If even if there are multiple objects in the scores array, it is not combining them with the student_id group and shows them separate. I am not sure of what I should add to the aggregation. Any help would be appreciated!

Answer 1

Try With this Aggregate Query,

[
  {
    '$unwind': {
      'path': '$scores'
    }
  }, {
    '$match': {
      'scores.type': 'homework'
    }
  }, {
    '$group': {
      '_id': {class_id:'$class_id',
              student_id:'$student_id'}, 
      'scores': {
        '$push': {
          'type': '$scores.type', 
          'score': '$scores.score'
        }
      }
    }
  }
]

Answer 2

Mongo Playground Link

I think this is the precise format you wanted.

The aggregation pipeline:

[
  {
    "$unwind": {
      "path": "$scores"
    }
  },
  {
    "$match": {
      "scores.type": "homework"
    }
  },
  {
    "$group": {
      "_id": {
        "class_id": "$class_id",
        "student_id": "$student_id"
      },
      "scores": {
        "$push": {
          "type": "$scores.type",
          "score": "$scores.score"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.class_id",
      all_scores: {
        $push: {
          "student_id": "$_id.student_id",
          scores: "$scores"
        }
      }
    }
  },
  {
    "$project": {
      _id: 0,
      class_id: "$_id",
      all_scores: "$all_scores"
    }
  }
]

The first two stages of the pipeline I guess are simply to filter out the non-homework documents.

To perform a "nested grouping" of sorts, where not only does the data have an outer grouping over class_id but an inner grouping in the scores over student_id, first we group the data in the first $group stage over both those fields, much like described here.

The scores array in each document here will be the same as the arrays we need in each inner grouping (over student_id), so, now we can just group by the class_name (in the _id object after the result of the first group stage) and add the student_id along with the scores in each object to push in the all_scores array. Then the final $project stage is pretty trivial, just to get it in the format that we want.