Join Array in MongoDB

Since version 3.2 you can use the $lookup to perform the equivalent of left outer joins in MongoDB. While you should try to design your schema to avoid joins as much as you can, the $lookup nevertheless is a very powerful feature. Be careful though not to abuse it. It does have limitations, for instance you can only join on collections that are in the same unsharded database and you can do only equality matches.

That being said, if you have an array of values and you want to do a $lookup for each of those values you will have to $unwind the array first, then perform the $lookup, finally $group the data again – although the last step may be optional depending on your application.

Following the employee example from the Join Query in MongoDB article, you could use the aggregate query below to retrieve the subordinates of all the managers by matching each of the “subordinates” array values against the corresponding “_id” values in the same employees collection.

Assume your employees collection contains the following data:

{ 
    "_id" : NumberInt(5), 
    "lastName" : "Smith", 
    "firstName" : "John", 
    "department" : {
        "depName" : "HR"
    }, 
    "manId" : NumberInt(4)
}
{ 
    "_id" : NumberInt(3), 
    "lastName" : "Duck", 
    "firstName" : "Donald", 
    "department" : {
        "depName" : "IT"
    }, 
    "manId" : NumberInt(1)
}
{ 
    "_id" : NumberInt(2), 
    "lastName" : "Mouse", 
    "firstName" : "Minnie", 
    "department" : {
        "depName" : "IT"
    }, 
    "manId" : NumberInt(1)
}
{ 
    "_id" : NumberInt(1), 
    "lastName" : "Mouse", 
    "firstName" : "Mickey", 
    "department" : {
        "depName" : "IT"
    }, 
    "subordinates" : [
        NumberInt(2), 
        NumberInt(3)
    ]
}
{ 
    "_id" : NumberInt(4), 
    "lastName" : "Smith", 
    "firstName" : "Jane", 
    "department" : {
        "depName" : "HR"
    }, 
    "subordinates" : [
        NumberInt(5)
    ]
}

The query below will return the full employee details for each of the subordinates in the manager’s document. While theoretically you only need the $unwind and $lookup stages in your aggregation pipeline, the $project and $group stages following the $lookup really just massage the data to look nicer for your application.

db.employees.aggregate([
{$unwind:{
  path:"$subordinates",
  preserveNullAndEmptyArrays:true
}},
{$lookup:{
  from:"employees",
  localField:"subordinates",
  foreignField:"_id",
  as:"subordinates"
}},
{$project:{
  _id:true,
  lastName:true,
  firstName:true,
  department:true,
  manId:true,
  subordinates: {$arrayElemAt:["$subordinates", 0]}
}},
{$group:{
  _id: {
    _id:"$_id",
    lastName:"$lastName",
    firstName:"$firstName",
    department:"$department",
    manId:"$manId"
  },
  subordinates:{$addToSet:{
    _id:"$subordinates._id",
    lastName:"$subordinates.lastName",
    firstName:"$subordinates.firstName",
    department:"$subordinates.department",
    manId:"$subordinates.manId",
  }}
}},
{$project:{
  _id:"$_id._id",
  lastName:"$_id.lastName",
  firstName:"$_id.firstName",
  department:"$_id.department",
  manId:"$_id.manId",
  subordinates:true
}}
]);

The resulting output looks like this:

{ 
    "_id" : NumberInt(4), 
    "subordinates" : [
        {
            "_id" : NumberInt(5), 
            "lastName" : "Smith", 
            "firstName" : "John", 
            "department" : {
                "depName" : "HR"
            }, 
            "manId" : NumberInt(4)
        }
    ], 
    "lastName" : "Smith", 
    "firstName" : "Jane", 
    "department" : {
        "depName" : "HR"
    }
}
{ 
    "_id" : NumberInt(1), 
    "subordinates" : [
        {
            "_id" : NumberInt(3), 
            "lastName" : "Duck", 
            "firstName" : "Donald", 
            "department" : {
                "depName" : "IT"
            }, 
            "manId" : NumberInt(1)
        }, 
        {
            "_id" : NumberInt(2), 
            "lastName" : "Mouse", 
            "firstName" : "Minnie", 
            "department" : {
                "depName" : "IT"
            }, 
            "manId" : NumberInt(1)
        }
    ], 
    "lastName" : "Mouse", 
    "firstName" : "Mickey", 
    "department" : {
        "depName" : "IT"
    }
}
{ 
    "_id" : NumberInt(2), 
    "subordinates" : [
        {

        }
    ], 
    "lastName" : "Mouse", 
    "firstName" : "Minnie", 
    "department" : {
        "depName" : "IT"
    }, 
    "manId" : NumberInt(1)
}
{ 
    "_id" : NumberInt(3), 
    "subordinates" : [
        {

        }
    ], 
    "lastName" : "Duck", 
    "firstName" : "Donald", 
    "department" : {
        "depName" : "IT"
    }, 
    "manId" : NumberInt(1)
}
{ 
    "_id" : NumberInt(5), 
    "subordinates" : [
        {

        }
    ], 
    "lastName" : "Smith", 
    "firstName" : "John", 
    "department" : {
        "depName" : "HR"
    }, 
    "manId" : NumberInt(4)
}

Leave a Reply

Your email address will not be published. Required fields are marked *

*