$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" : [] }
|
No comments:
Post a Comment
Please do not enter any spam link in the comment box.