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":"[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.

turivishal · Accepted Answer · 2022-06-10 17:04:49Z

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

Takis · Accepted Answer · 2022-06-11 15:33:56Z

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"}}}}])

edited Jun 11, 2022 at 15:33

answered Jun 10, 2022 at 16:26

Takis

8,7552 gold badges17 silver badges28 bronze badges

6 Comments

Cristina Aranda Over a year ago

Thank you so much! It works perfectly! One more question: having that is there anyway to obtain the lecturer with the maximum number of students for each department? So I would have to count the number of students a lecturer imparts class to and the find the maximum across all lecturers from the department. It would be ideal if it could be done within the same "code sentence as the one above but if it not possible it is great too. I am quite new to mongodb and this way over my capacitites

Takis Over a year ago

i updated it to do both, i think its ok

Endrju Over a year ago

Wow and people call SQL difficult. This is unreadable, with random dollar signs in most (but not all?) places and curly braces that try to imitate LISP.

Takis Over a year ago

@Endrju if you click on the link, on the right side you see the same in cMQL a query language that i made to avoid the JSON, and it looks like Clojure, MongoDB with JSON allows us to create query builders, see cmql.org what i did, query builders are made in many language so dont let JSON drive you away, it can be hidden.

Endrju Over a year ago

Thanks for the response, this cMQL just basically replaced dollars with colons. I think I'll migrate my stuff back to relational db and forget about this thing.

|

nimrod serok · Accepted Answer · 2022-06-11 13:52:07Z

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

Add each department document a set of all its relevant groups.
$lookup only the student ids for each group to create a groups array.
Insert the relevant groups data to each lecturer.
Calculate maxImpartsStudents which is the number of unique students per lecturer from all of its groups
$reduce the lecturers array to include only the lecturer with highest maxImpartsStudents.
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

Collectives™ on Stack Overflow

MongoDB lookup (join) with field in double nested array

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related