0

I have this sample data,

{
  _id: ObjectId("50b59cd75bed76f46522c34e"),
  student_id: 0,
  class_id: 2,
  scores: [
    { type: 'exam', score: 57.92947112575566 },
    { type: 'quiz', score: 21.24542588206755 },
    { type: 'homework', score: 68.1956781058743 },
    { type: 'homework', score: 67.95019716560351 },
    { type: 'homework', score: 18.81037253352722 }
  ]
}

I want to aggregate this data to,

{
  _id: ObjectId("50b59cd75bed76f46522c34e"),
  student_id: 0,
  class_id: 2,
  scores: [
    { type: 'exam', score: 57.92947112575566 },
    { type: 'quiz', score: 21.24542588206755 },
    { type: 'homework', score: 51.6520826017 }
  ]
}

Where score from the same type will be calculated the average by sum all the score and divided by total number of same types.

How do I do this with aggregate in MongoDB? Thank You.

Aurell Layalia
  • 11
  • 2

1 Answers1

0
db.<collection_name>.aggregate([
{$unwind: "$scores" },
{$group: {      _id: {"type":"$scores.type", "id":"$_id"},
                id:{$first:"$_id"}, 
                student_id:{$first:"$student_id"}, 
                class_id:{$first:"$class_id"}, 
                score:{$avg: "$scores.score"} 
                }},
{$group: { 
                _id: "$id",
                student_id:{$first:"$student_id"}, 
                class_id:{$first:"$class_id"}, 
                scores:{$push:{score: "$score",type: "$_id.type"} }
                }}
])

The above query is implemented in 3 steps
1: unwind: During this stage a separate document will be created for each element of the scores array.
2. group : During the 1st group aggregation stage the documents will be grouped by type of score and _id of document and then avg is calculated.
3. group : During the 2nd group aggregation stage the documents will be grouped by the orginal document id and using $push we will create an array for score

josephjacobmorris
  • 317
  • 11