In SQL Database, there are many ways to create the join between two or more tables like
Left Join
Right Join
Inner Join
Full Join
Self Join
But MongoDB is a NoSQL Database so It has one method to join between two or more collection through lookup().
Syntax:-
db.collectionName.aggregate([
{ $lookup:
{
from: 'otherCollection',
localField: 'collection_fieldname',
foreignField: 'otherCollection_fieldname',
as: 'collectionDetails'
}
}
]).toArray();
Suppose, You have two collections First is employees collection which has 3 documents and every document has 5 key fields like _id, name, age, department and salary.
[
{
"_id" : ObjectId("5f26e736deec6e20ea057831"),
"name" : "John",
"age" : 35,
"department" : "department A",
"salary" : 200000
},
{
"_id" : ObjectId("5f26e9dedeec6e20ea057833"),
"name" : "Tony",
"age" : 31,
"department" : "department B",
"salary" : 40000
},
{
"_id" : ObjectId("5f26e9dedeec6e20ea057835"),
"name" : "Andrew",
"age" : 33,
"department" : "department C",
"salary" : 20000
}
]
2) Second collection is department. which has 4 key fields like _id, name, floor and facility.
[
{
"_id" : ObjectId("5f27bfaddeec6e20ea057836"),
"name" : "department A",
"floor" : 5,
"facility" : "standard"
},
{
"_id" : ObjectId("5f27bfaddeec6e20ea057837"),
"name" : "department B",
"floor" : 4,
"facility" : "good"
},
{
"_id" : ObjectId("5f27bfaddeec6e20ea057838"),
"name" : "department C",
"floor" : 3,
"facility" : "normal"
}
]
Now you want to get the employees collection document with department details
db.employees.aggregate([
{ $lookup:
{
from: 'department',
localField: 'department',
foreignField: 'name',
as: 'departmentDetails'
}
}
]).toArray();
Output:-
{
“_id” : ObjectId(“5f26e736deec6e20ea057831”),
“name” : “John”,
“age” : 35,
“department” : “department A”,
“salary” : 200000,
“departmentDetails” : [
{
“_id” : ObjectId(“5f27bfaddeec6e20ea057836”),
“name” : “department A”,
“floor” : 5,
“facility” : “standard”
}
]
},
{
“_id” : ObjectId(“5f26e9dedeec6e20ea057833”),
“name” : “Tony”,
“age” : 31,
“department” : “department B”,
“salary” : 40000,
“departmentDetails” : [
{
“_id” : ObjectId(“5f27bfaddeec6e20ea057837”),
“name” : “department B”,
“floor” : 4,
“facility” : “good”
}
]
},
{
“_id” : ObjectId(“5f26e9dedeec6e20ea057835”),
“name” : “Andrew”,
“age” : 33,
“department” : “department C”,
“salary” : 20000,
“departmentDetails” : [
{
“_id” : ObjectId(“5f27bfaddeec6e20ea057838”),
“name” : “department C”,
“floor” : 3,
“facility” : “normal”
}
]
}
]