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.

Answer 1

$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.