Question 117644 in Mongodb Question posted in
The official documentation can be found here.

MongoDB lookup (join) with field in double nested array

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 objective is to get a report with the number of students taught by a professor from a department.

Tags:

3

Answers


  1. 0

    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"}}}}])
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    Now that we understand the question (according to your other question), an answer can be:

    1. Add each department document a set of all its relevant groups.
    2. $lookup only the student ids for each group to create a groups array.
    3. Insert the relevant groups data to each lecturer.
    4. Calculate maxImpartsStudents which is the number of unique students per lecturer from all of its groups
    5. $reduce the lecturers array to include only the lecturer with highest maxImpartsStudents.
    6. Format the answer
    db.department.aggregate([
  {
    $addFields: {
      groups: {
        $setIntersection: [
          {
            $reduce: {
              input: "$lecturers.imparts.groupID",
              initialValue: [],
              in: {$concatArrays: ["$$value", "$$this"]}
            }
          }
        ]
      }
    }
  },
  {
    $lookup: {
      from: "group",
      let: {groupIDs: "$groups"},
      pipeline: [
        {$match: {$expr: {$in: ["$_id", "$$groupIDs"]}}},
        {
          $project: {
            students: {
              $reduce: {
                input: "$students",
                initialValue: [],
                in: {$concatArrays: ["$$value", ["$$this.studentID"]]}
              }
            }
          }
        }
      ],
      as: "groups"
    }
  },
  {
    $project: {
      name: 1,
      lecturers: {
        $map: {
          input: "$lecturers",
          in: {
            $mergeObjects: [
              {lecturerID: "$$this.lecturerID"},
              {groups: {
                  $map: {
                    input: "$$this.imparts",
                    in: {
                      $arrayElemAt: [
                        "$groups",
                        {$indexOfArray: ["$groups._id", "$$this.groupID"]}
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      name: 1,
      lecturers: {
        $map: {
          input: "$lecturers",
          as: "item",
          in: {
            $mergeObjects: [
              {
                maxImpartsStudents: {
                  $size: {
                    $reduce: {
                      input: "$$item.groups",
                      initialValue: [],
                      in: {$setUnion: ["$$value", "$$this.students"]}
                    }
                  }
                }
              },
              {lecturerID: "$$item.lecturerID"}
            ]
          }
        }
      }
    }
  },
  {
    $set: {
      lecturers: {
        $reduce: {
          input: "$lecturers",
          initialValue: {
            "maxImpartsStudents": 0
          },
          in: {
            $cond: [
              {$gte: ["$$this.maxImpartsStudents", "$$value.maxImpartsStudents"]},
              "$$this", "$$value"
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      lecturerID: "$lecturers.lecturerID",
      maxImpartsStudents: "$lecturers.maxImpartsStudents",
      departmentName: "$name"
    }
  }
])

    Which is much better than combining the solutions from both questions.

    See how it works on the playground example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search