I am looking to do the following. With a MongoDB collection name department with the following structure:

{
  "_id":99,
  "name":"Erick Kalewe",
  "faculty":"Zazio",
  "lecturers":[
    {
      "lecturerID":31,
      "name":"Granny Kinton",
      "email":"[email protected]",
      "imparts":[
        {
          "groupID":70,
          "codCourse":99
        }
      ]
    },
    {
      "lecturerID":36,
      "name":"Michale Dahmel",
      "email":"[email protected]",
      "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":"[email protected]",
      "imparts":[
        {
          "groupID":70,
          "codCourse":99
        }
      ]
    },
    {
      "lecturerID":36,
      "name":"Michale Dahmel",
      "email":"[email protected]",
      "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 end objective is to get a report with the number of students taught by a professor from a department.

If you could help me I would be forever grateful since I have tried everything I have found. Thank you in advance!

CodePudding user response：

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)
• the $set is to fix the structure, and flatten the impacts that came from join as arrays (concat this arrays to 1)
• then its lecturers that i need to be grouped and pushed (for rest arguments i keep the $first)

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": "group",
    "localField": "lecturers.imparts.groupID",
    "foreignField": "_id",
    "as": "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"}}},
 {"$set": 
   {"lecturers": 
     {"$mergeObjects": 
       ["$lecturers",
         {"imparts": 
           {"$reduce": 
             {"input": "$imparts",
              "initialValue": [],
              "in": {"$concatArrays": ["$$value", "$$this"]}}}}]},
    "imparts": "$$REMOVE"}},
 {"$group": 
   {"_id": "$_id._id",
    "name": {"$first": "$name"},
    "faculty": {"$first": "$faculty"},
    "lectures": {"$push": "$lecturers"}}}])

CodePudding user response：

You can try aggregation framework,

• $lookup with group collection pass lecturers.imparts.groupID as localField and pass _id as foreignField
• $addFields to merge group data with imports and remove group fields because it is not needed
• $map to iterate loop of lecturers array
• $mergeObjects to merge current object of lecturers and updated object of imports
• $map to iterate loop of imports array
• $mergeObjects to merge current object of imports and found result from group
• $filter to iterate loop of group array and find the group by groupID
• $arrayElemAt to get first element from above filtered result

db.department.aggregate([
  {
    $lookup: {
      from: "group",
      localField: "lecturers.imparts.groupID",
      foreignField: "_id",
      as: "group"
    }
  },
  {
    $addFields: {
      lecturers: {
        $map: {
          input: "$lecturers",
          in: {
            $mergeObjects: [
              "$$this",
              {
                imparts: {
                  $map: {
                    input: "$$this.imparts",
                    as: "i",
                    in: {
                      $mergeObjects: [
                        "$$i",
                        {
                          $arrayElemAt: [
                            {
                              $filter: {
                                input: "$group",
                                cond: { $eq: ["$$this._id", "$$i.groupID"] }
                              }
                            },
                            0
                          ]
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      },
      group: "$$REMOVE"
    }
  }
])

Playground