MongoDB lookup (join) with field in double nested array

Question

With a MongoDB collection name department with the following structure:

{
  "_id":99,
  "name":"Erick Kalewe",
  "faculty":"Zazio",
  "lecturers":[
    {
      "lecturerID":31,
      "name":"Granny Kinton",
      "email":"gkintonu@answers.com",
      "imparts":[
        {
          "groupID":70,
          "codCourse":99
        }
      ]
    },
    {
      "lecturerID":36,
      "name":"Michale Dahmel",
      "email":"mdahmelz@artisteer.com",
      "imparts":[
        {
          "groupID":100,
          "codCourse":60
        }
      ]
    }
  ]
}

and another collection group with this structure:

{
  "_id":100,
  "codCourse":11,
  "language":"Romanian",
  "max_students":196,
  "students":[
    {
      "studentID":1
    }
  ],
  "classes":[
    {
      "date":datetime.datetime(2022, 5, 10, 4, 24, 19),
      "cod_classroom":100
    }
  ]
}

join them to get the following:

{
  "_id":99,
  "name":"Erick Kalewe",
  "faculty":"Zazio",
  "lecturers":[
    {
      "lecturerID":31,
      "name":"Granny Kinton",
      "email":"gkintonu@answers.com",
      "imparts":[
        {
          "groupID":70,
          "codCourse":99
        }
      ]
    },
    {
      "lecturerID":36,
      "name":"Michale Dahmel",
      "email":"mdahmelz@artisteer.com",
      "imparts":[
        {
          "_id":100,
          "codCourse":11,
          "language":"Romanian",
          "max_students":196,
          "students":[
            {
              "studentID":1
            }
          ],
          "classes":[
            {
              "date":datetime.datetime(2022, 5, 10, 4, 24, 19),
              "cod_classroom":100
            }
          ]
        }
      ]
    }
  ]
}

The objective is to get a report with the number of students taught by a professor from a department.

Answer 1

Query

• unwind, do the join, and re-group back
• its kinda big query because you want to join in nested field, and this means 2 unwind and 2 groupings to restore the structure
  (i think in general joining fields shouldn't go deep inside)
• unwind both arrays
• do the lookup on groupID
• and now construct back the document as 2 level nested
• first its impacts that need to be grouped and pushed (for rest argument i keep the $first) we sum also the students based on the comment
• then its lecturers that i need to be grouped and pushed (for rest arguments i keep the $first) we take the lecture with the max students in the department (mongodb can compare documents also)

Playmongo (you can put your mouse at the end of each stage to see in/out of that stage)

department.aggregate(
[{"$unwind": "$lecturers"}, {"$unwind": "$lecturers.imparts"},
 {"$lookup": 
   {"from": "coll",
    "localField": "lecturers.imparts.groupID",
    "foreignField": "_id",
    "as": "lecturers.imparts"}},
 {"$set": {"lecturers.imparts": {"$first": "$lecturers.imparts"}}},
 {"$group": 
   {"_id": {"_id": "$_id", "lecturersID": "$lecturers.lecturerID"},
    "name": {"$first": "$name"},
    "faculty": {"$first": "$faculty"},
    "lecturers": 
     {"$first": 
       {"lecturerID": "$lecturers.lecturerID",
        "name": "$lecturers.name",
        "email": "$lecturers.email"}},
    "imparts": {"$push": "$lecturers.imparts"},
    "lecture_max_students": 
     {"$sum": "$lecturers.imparts.max_students"}}},
 {"$set": 
   {"lecturers": 
     {"$mergeObjects": 
       ["$lecturers", {"imparts": "$imparts"},
         {"lecture_max_students": "$lecture_max_students"}]},
    "imparts": "$$REMOVE","lecture_max_students": "$$REMOVE"}},
 {"$group": 
   {"_id": "$_id._id",
    "name": {"$first": "$name"},
    "faculty": {"$first": "$faculty"},
    "lectures": {"$push": "$lecturers"},
    "dept-max-lecturer": 
     {"$max": {"max-students": "$lecturers.lecture_max_students",
               "lecturerID": "$lecturers.lecturerID"}}}}])