How I can group objects inside an array by object's specific property in mongo db-CodePudding

In the structure of the Marks model, I have courseCode (String), courseTitle (String), studentMarks (Array of studentId and marks of that course ).

The data stored in the marks collection:

[
    {
        "_id": "634 ...",
        "courseCode": "cse1201",
        "courseTitle": "Structured Programming ",
        "studentsMarks": [
            {
                "id": "ce17001",
                "marks": 52,
                "_id": "634a9be567a1f07b ... "
            },
            {
                "id": "ce17002",
                "marks": 50,
                "_id": "634a9be567a1f07be ... "
            }
        ],
        "type": "theory"
    },

{
        "_id": "634a9be567a1f07be0 ...",
        "courseCode": "cse1202",
        "courseTitle": "Data Structer",
        "studentsMarks": [
            {
                "id": "ce17001",
                "marks": 45,
                "_id": "634 ..."
            },
            {
                "id": "ce17002",
                "marks": 61,
                "_id": "634 ... "
            }
        ],
        "type": "theory"
    }
]

I want to group student's marks by their unique student id. (ce17001, ce17002). For each students, there will be an array of marks, which will hold all the marks of taken courses of a particular student.

Expected output :

[
    {
        "id": "ce17001",
        "marksArray": [
            {
                "courseCode": "cse1201",
                "courseTitle": "Structured Programming",
                "marks": 52
            },
            {
                "courseCode": "cse1202",
                "courseTitle": "Data structer",
                "marks": 45
            }
        ]
    },
    {
        "id": "ce17002",
        "marksArray": [
             {
                  "courseCode": "cse1201",
                  "courseTitle": "Structured Programming",
                  "marks": 50
             },
             {
                  "courseCode": "cse1202",
                  "courseTitle": "Data structer",
                  "marks": 61
              } 
        ]
    }
]

CodePudding user response：

One option is to use $unwind and $group:

db.collection.aggregate([
  {$unwind: "$studentsMarks"},
  {$group: {
      _id: "$studentsMarks.id",
      marksArray: {
        $push: {
          courseCode: "$courseCode",
          courseTitle: "$courseTitle",
          marks: "$studentsMarks.marks"
        }
      }
    }
  }
])

See how it works on the playground example