Join between two collections in MongoDB

$lookup command used to perform join between two collections. This is a new feature of MongoDB version 3.2

$lookup performs the left outer join between two collections in the same database. For each input document, $lookup add new array fields whose element matches the joined collection.

Syntax:

{    $lookup:      {        from: <collection to join>,        localField: <field from the input documents>,        foreignField: <field from the documents of the "from" collection>,        as: <output array field>      } }

$lookup takes a document with the following fields

from: Its takes collection name in the same database to perform joined.

localField: It takes the document in $lookup to perform equally match with foreign field from the documents which are the field of collection where we are performing aggregation.

foreighFiled: It takes the field of “from” the collection.

as: This is new array field contains the matching documents from the “from” collection.

Example: Suppose we have a collection of the employee.

Collection EMP

{_id:1,Name:"Manish",dep:"HR"} {_id:2,Name:"Raj",dep:"Salesforce"} {_id:3,Name:"Dilip",dep:"Database"} {_id:4,Name:"Vipul",dep:"UI"} {_id:5,Name:"Ashish",dep:"Syste Admin"} {_id:7,Name:"Pankaj"}

Collection DEP

{_id:10,depName:"HR",Manager:"Mar1"} {_id:11,depName:"Salesforce",Manager:"Mar2"} {_id:12,depName:"Database",Manager:"Mar3"} {_id:13,depName:"UI",Manager:"Mar4"} {_id:14,depName:"Syste Admin",Manager:"Mar5"}

We have two collections EMP and DEP and we want to know about employees and their managers run the following query.

db.EMP.aggregate([     {       $lookup:         {           from: "DEP",           localField: "dep",           foreignField: "depName",           as: "EMp_Manager"         }    } ])

Result: 

{ "_id" : 1, "Name" : "Manish", "dep" : "HR", "EMp_Manager" : [ { "_id" : 10, "depName" : "HR", "Manager" : "Mar1" } ] }  { "_id" : 2, "Name" : "Raj", "dep" : "Salesforce", "EMp_Manager" : [ { "_id" : 11, "depName" : "Salesforce", "Manager" : "Mar2" } ] } { "_id" : 3, "Name" : "Dilip", "dep" : "Database", "EMp_Manager" : [ { "_id" : 12, "depName" : "Database", "Manager" : "Mar3" } ] } { "_id" : 4, "Name" : "Vipul", "dep" : "UI", "EMp_Manager" : [ { "_id" : 13, "depName" : "UI", "Manager" : "Mar4" } ] } { "_id" : 5, "Name" : "Ashish", "dep" : "Syste Admin", "EMp_Manager" : [ { "_id" : 14, "depName" : "Syste Admin", "Manager" : "Mar5" } ] } { "_id" : 7, "Name" : "Pankaj", "EMp_Manager" : [] }