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!

Would this result help: mongoplayground.net/p/3PRZB8lXMoB It's not exactly in the format you wanted but it's close. — Anuj Pancholi
– Anuj Pancholi, Commented Sep 19, 2020 at 6:38
So I have gotten that result already by using a much simpler query. I am trying to get it exactly like described in the question. But good suggestion tho! — Neel Patel
– Neel Patel, Commented Sep 19, 2020 at 6:44
Please see my answer, I'll edit it if explanation is required. — Anuj Pancholi
– Anuj Pancholi, Commented Sep 19, 2020 at 7:54

Anuj Pancholi · Accepted Answer · 2020-09-19 08:35:50Z

1

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.

edited Sep 19, 2020 at 8:35

answered Sep 19, 2020 at 7:44

Anuj Pancholi

1,22311 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Neel Patel Over a year ago

So I ran that but now each class id only contains one student object. It should contain multiple.

Anuj Pancholi Over a year ago

You sure? If you check my link, it has multiple students in one document.

Neel Patel Over a year ago

Oh My God! That worked like a charm! Thank you alot brother!

Anuj Pancholi Over a year ago

Happy to help, bro :)

Prakash Harvani · Accepted Answer · 2020-09-19 11:26:40Z

0

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'
        }
      }
    }
  }
]

edited Sep 19, 2020 at 11:26

answered Sep 19, 2020 at 6:28

Prakash Harvani

1,0418 silver badges18 bronze badges

3 Comments

Neel Patel Over a year ago

But that would group it by student_id first. I want to group it by class_id first.

Prakash Harvani Over a year ago

Please check Again I was updated my answer as you want.

Neel Patel Over a year ago

Your solution does work but only partially. While it does group class_id and student_id together, it does not group student_id separately. I need to have one document for each class and within each class an array of all students. And within each student in that class, an array of their scores of type specified.

Collectives™ on Stack Overflow

Grouping objects inside an array - MongoDB Aggregation

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related