I have classroom document which contain 'modules', the whole document looks like this:

{
    "_id": "628a7ea21e2a666d7872efbf",
    "name": "Test Class",
    "owner": "60763491b98b9e186ef33137",
    "schoolId": "607dff27c712219af1e65d83",
    "description": "This is a test class.",
    "roster": [],
    "modules": [
        {
            "name": "Test Module 1",
            "id": "62a7082d0bf84c43fdfe95ff",
            "isPublished": false
        },
        {
            "name": "Test Module 2",
            "id": "62a72d6378ce044dca32e1a2",
            "isPublished": false
        }
    ]
}

I also have assignment documents as such:

{
    "classroomId": "628a7ea21e2a666d7872efbf",
    "moduleId": "62a7082d0bf84c43fdfe95ff",
    "name": "Assignment 1",
    "description": "Test description",
    "created": 1655120822055,
    "reading": null,
    "questions": [],
    "isPublished": true,
    "_id": "62a723b6683ffc4b11940c7b"
}

My question is how could do aggregation such that if I want to do a lookup on the assignments for the classroom, I am able to group the assignment documents by moduleId and then add them as a field to the modules array. The final document would like this:

{
    "_id": "628a7ea21e2a666d7872efbf",
    "name": "Test Class",
    "owner": "60763491b98b9e186ef33137",
    "schoolId": "607dff27c712219af1e65d83",
    "description": "This is a test class.",
    "roster": [],
    "modules": [
        {
            "name": "Test Module 1",
            "id": "62a7082d0bf84c43fdfe95ff",
            "isPublished": false,
            "assignments": [
                {
                    "_id": "62a708ab0bf84c43fdfe9600",
                    "classroomId": "628a7ea21e2a666d7872efbf",
                    "moduleId": "62a7082d0bf84c43fdfe95ff",
                    "name": "Assignment 1",
                    "description": "Test description",
                    "created": 1655113899629,
                    "due": null,
                    "settings": null,
                    "reading": null,
                    "questions": [],
                    "isPublished": true
                },
                {
                    "_id": "62a723b6683ffc4b11940c7b",
                    "classroomId": "628a7ea21e2a666d7872efbf",
                    "moduleId": "62a7082d0bf84c43fdfe95ff",
                    "name": "Assignment 1",
                    "description": "Test description",
                    "created": 1655120822055,
                    "due": null,
                    "settings": null,
                    "reading": null,
                    "questions": [],
                    "isPublished": true
                }
            ]

        },
        {
            "name": "Test Module 2",
            "id": "62a72d6378ce044dca32e1a2",
            "isPublished": false,
            "assignments": [

            ]
        }
    ]
}

Right now I just have the base lookup and obviously this just gets me a separate assignments field, without groups.

lookup = [
    {
        $lookup: {
            from: "assignments",
            localField: "modules.id",
            foreignField: "moduleId",
            as: "assignments"
        }
    }
];

CodePudding user response：

1. $lookup

2. $set - Set modules field.

   2.1. $map - Iterate the modules array and returns a new array.

   2.1.1. $mergeObject - Merge current iterate module document with the document with assignments array from the result 2.1.1.1.

   2.1.1.1. $filter - Filter the assignments array by matching moduleId.

3. $unset - Remove assignments field.

db.classroom.aggregate([
  {
    $lookup: {
      from: "assignments",
      localField: "modules.id",
      foreignField: "moduleId",
      as: "assignments"
    }
  },
  {
    $set: {
      modules: {
        $map: {
          input: "$modules",
          as: "module",
          in: {
            $mergeObjects: [
              "$$module",
              {
                "assignments": {
                  $filter: {
                    input: "$assignments",
                    as: "asgn",
                    cond: {
                      $eq: [
                        "$$module.id",
                        "$$asgn.moduleId"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: "assignments"
  }
])

Sample Mongo Playground