This is a new feature introduced MongoDB version 3.2
$lookup performs the left outer join between two collections in the same database. For each input document, $lookup adds a new array field 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 join.
localField: It takes the document in the $lookup to perform equally match with the foreign field from the documents which is a field of collection where we are performing aggregation.
foreighFiled: It takes the field of “from” collections.
as: This is a 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" : [] }
|