$LOOKUP ( Similar to LEFT OUTER JOIN in RDBMS) in MongoDB

In RDBMS, we have different type of joins to join the tables and get the required data from joined tables. As MongoDB is a NO Sql database, MongoDB will not support those type of joins. But we can implement LEFT OUTER JOIN using $lookup function in MongoDB. This function is there in MongoDB 3.2 version. To join two collections we need to have a common field in both the collections. $lookup works with aggregate function only.

Syntax:
db.collection1.aggregate([
 {
    $lookup:
      {
         from:"collection2",
         localField:"common field name from collection1",
         foreignField:"common field name from collection2",
         as:"Alias name for collection2"
     }
 }
])


Here in above syntax,
collection1 -- Collection name which is acts like parent table.
collection2 -- Collection name which is acts like child table and this will be joined with collection1.
"common field name from collection1" -- Join column from collection1.
"common field name from collection2" -- Join column from collection2.
"Alias name for collection2" -- Alias name for collection in output and this acts as an array in result.

 Consider the below example:

Collection1 (named department):



Collection2 (named employee):




In first collection "department", we have "dept No" and In second collection employee, we have "Dept No". Now, am joining these two collections using common field "Dept No".


I want to map all the employee details to corresponding department numbers in the result set. To do this, I need to pass the below command.

db.department.aggregate([
{
  $lookup:
   {
      from:"employee",
      localField:"dept No",
      foreignField:"Dept No",
      as:"Emp_Details"
   }
 }
])

Output of the above command:


 If we want filter the records before join, then we need to use $match function. Let's assume that, we want to get the department records and corresponding employee details where department number is 101. To do this, we need to use below command.


db.department.aggregate([
 {
    $match:
     {
        "dept No":101
     }
 },
{
  $lookup:
   {
      from:"employee",
      localField:"dept No",
      foreignField:"Dept No",
      as:"Emp_Details"
   }
 }
])

Result set of the above command:





If You want to restrict the number of fields to be displayed in result set, you can use $project function along with the $lookup and $match.

In the below query, I am restricting the number of fields to 4(department number and name,employee name and seat number).

db.department.aggregate([
{
    $match:{"dept No":101}
},
{
    $lookup:
        {
            from:"employee",
            localField:"dept No",
            foreignField:"Dept No",
            as:"Emp_Details"
        }
},
{
    $project:
        {
            "Dept Name":1,
            "dept No":1,
            "Emp_Details":{"Emp Name":1,"Emp Seat No":1}
        }
}
])

Hope, This has given you a brief idea about join in MongoDB.