Query varying multi-level relations in mongodb

Question

Simulated example. Two mongodb collections, departments and employees:

Departments

{ _id: '101' }
{ _id: '102', parent: '101' }
{ _id: '103', parent: '101' }
{ _id: '104', parent: '103' }

Employees

{ _id: '201', department: '101' }
{ _id: '202', department: '102' }
{ _id: '203', department: '102' }
{ _id: '204', department: '103' }
{ _id: '205', department: '104' }

How can I query a list of all employees in a department, or any sub department (by the department.parent reference property)?

So for the above example, querying for:

department 101 should give employee documents 201, 202, 203, 204, 205
department 102 should give employee documents 202, 203, 204, 205
department 103 should give employee documents 204, 205

If it is not possible to make a query for "any number" of levels in the department tree, one that gives the results for "up to N" levels is fully acceptable.

It would be hard to do so with your current data structure(doable but not very efficient). I suggest you add children field into department to simulate a tree structure. That would make you life so much easier. Refer to https://docs.mongodb.com/manual/tutorial/model-tree-structures/ — spiritwalker, Sep 15 '17 at 07:02

Clement Amarnath · Answer 1 · 2017-09-15T10:59:09.227

$lookup in MongoDb performs a left outer join to an unsharded collection in the same database.

In case if we have one level of relationship between our collections then we can $lookup to perform an aggregation on the collections.

Assuming we have one level of relationship(say one department to an Employee) in the given sample collection then the aggregate query is

db.department.aggregate([
    {
      $lookup:
        {
          from: "employees",
          localField: "_id",
          foreignField: "department",
          as: "result"
        }
   }
])

_id from the Department collection is only taken for aggregation and the parent department relationship is not taken for aggregation

and the result on the sample collection given above is

{
        "_id" : "101",
        "result" : [
                {
                        "_id" : "201",
                        "department" : "101"
                }
        ]
}
{
        "_id" : "102",
        "parent" : "101",
        "result" : [
                {
                        "_id" : "202",
                        "department" : "102"
                },
                {
                        "_id" : "203",
                        "department" : "102"
                }
        ]
}
{
        "_id" : "103",
        "parent" : "101",
        "result" : [
                {
                        "_id" : "204",
                        "department" : "103"
                }
        ]
}
{
        "_id" : "104",
        "parent" : "103",
        "result" : [
                {
                        "_id" : "205",
                        "department" : "104"
                }
        ]
}

Please note that MongoDB is a NoSQL database and it is a good practice to design our collection schema in such a way that there is no or very less relationship between collections.

Query varying multi-level relations in mongodb

1 Answers1