In SQL join is widely used for finding related data from one table w.r.t, to other.
In mongo joins can be performed by using lookup functionality.
To demonstrate lookup i will create two simple collection one for student & other for course.student collection hold courseId which is kind of foreign key pointing to record in course collection.
I had populated student & course collection with some dummy data.
For
db.student.find()
Output:
{
"_id" : ObjectId("5b703776b7542ef044bc3f9f"),
"id" : 1.0,
"name" : "sangram",
"courseId" : 1.0
}
{
"_id" : ObjectId("5b703781b7542ef044bc3fa0"),
"id" : 2.0,
"name" : "sagar",
"courseId" : 1.0
}
{
"_id" : ObjectId("5b70378ab7542ef044bc3fa1"),
"id" : 3.0,
"name" : "sachin",
"courseId" : 2.0
}
{
"_id" : ObjectId("5b703793b7542ef044bc3fa2"),
"id" : 4.0,
"name" : "sachin",
"courseId" : 3.0
}
and
db.course.find()
Output:
{
"_id" : ObjectId("5b70379db7542ef044bc3fa3"),
"id" : 4.0,
"name" : "c"
}
{
"_id" : ObjectId("5b7037a7b7542ef044bc3fa4"),
"id" : 1.0,
"name" : "cpp"
}
{
"_id" : ObjectId("5b7037afb7542ef044bc3fa5"),
"id" : 2.0,
"name" : "VB 6.0"
}
{
"_id" : ObjectId("5b7037bdb7542ef044bc3fa6"),
"id" : 3.0,
"name" : "V C#"
}
we can join on course id as follows
db.student.aggregate([
{
$lookup:
{
from: "course",
localField: "courseId",
foreignField: "id",
as: "student_courses"
}
}
])
Output:
{
"_id" : ObjectId("5b703776b7542ef044bc3f9f"),
"id" : 1.0,
"name" : "sangram",
"courseId" : 1.0,
"student_courses" : [
{
"_id" : ObjectId("5b7037a7b7542ef044bc3fa4"),
"id" : 1.0,
"name" : "cpp"
}
]
}
{
"_id" : ObjectId("5b703781b7542ef044bc3fa0"),
"id" : 2.0,
"name" : "sagar",
"courseId" : 1.0,
"student_courses" : [
{
"_id" : ObjectId("5b7037a7b7542ef044bc3fa4"),
"id" : 1.0,
"name" : "cpp"
}
]
}
{
"_id" : ObjectId("5b70378ab7542ef044bc3fa1"),
"id" : 3.0,
"name" : "sachin",
"courseId" : 2.0,
"student_courses" : [
{
"_id" : ObjectId("5b7037afb7542ef044bc3fa5"),
"id" : 2.0,
"name" : "VB 6.0"
}
]
}
{
"_id" : ObjectId("5b703793b7542ef044bc3fa2"),
"id" : 4.0,
"name" : "sachin",
"courseId" : 3.0,
"student_courses" : [
{
"_id" : ObjectId("5b7037bdb7542ef044bc3fa6"),
"id" : 3.0,
"name" : "V C#"
}
]
}
Here output shows student collection data along with referenced course record.
In mongo joins can be performed by using lookup functionality.
To demonstrate lookup i will create two simple collection one for student & other for course.student collection hold courseId which is kind of foreign key pointing to record in course collection.
I had populated student & course collection with some dummy data.
For
db.student.find()
Output:
{
"_id" : ObjectId("5b703776b7542ef044bc3f9f"),
"id" : 1.0,
"name" : "sangram",
"courseId" : 1.0
}
{
"_id" : ObjectId("5b703781b7542ef044bc3fa0"),
"id" : 2.0,
"name" : "sagar",
"courseId" : 1.0
}
{
"_id" : ObjectId("5b70378ab7542ef044bc3fa1"),
"id" : 3.0,
"name" : "sachin",
"courseId" : 2.0
}
{
"_id" : ObjectId("5b703793b7542ef044bc3fa2"),
"id" : 4.0,
"name" : "sachin",
"courseId" : 3.0
}
and
db.course.find()
Output:
{
"_id" : ObjectId("5b70379db7542ef044bc3fa3"),
"id" : 4.0,
"name" : "c"
}
{
"_id" : ObjectId("5b7037a7b7542ef044bc3fa4"),
"id" : 1.0,
"name" : "cpp"
}
{
"_id" : ObjectId("5b7037afb7542ef044bc3fa5"),
"id" : 2.0,
"name" : "VB 6.0"
}
{
"_id" : ObjectId("5b7037bdb7542ef044bc3fa6"),
"id" : 3.0,
"name" : "V C#"
}
we can join on course id as follows
db.student.aggregate([
{
$lookup:
{
from: "course",
localField: "courseId",
foreignField: "id",
as: "student_courses"
}
}
])
Output:
{
"_id" : ObjectId("5b703776b7542ef044bc3f9f"),
"id" : 1.0,
"name" : "sangram",
"courseId" : 1.0,
"student_courses" : [
{
"_id" : ObjectId("5b7037a7b7542ef044bc3fa4"),
"id" : 1.0,
"name" : "cpp"
}
]
}
{
"_id" : ObjectId("5b703781b7542ef044bc3fa0"),
"id" : 2.0,
"name" : "sagar",
"courseId" : 1.0,
"student_courses" : [
{
"_id" : ObjectId("5b7037a7b7542ef044bc3fa4"),
"id" : 1.0,
"name" : "cpp"
}
]
}
{
"_id" : ObjectId("5b70378ab7542ef044bc3fa1"),
"id" : 3.0,
"name" : "sachin",
"courseId" : 2.0,
"student_courses" : [
{
"_id" : ObjectId("5b7037afb7542ef044bc3fa5"),
"id" : 2.0,
"name" : "VB 6.0"
}
]
}
{
"_id" : ObjectId("5b703793b7542ef044bc3fa2"),
"id" : 4.0,
"name" : "sachin",
"courseId" : 3.0,
"student_courses" : [
{
"_id" : ObjectId("5b7037bdb7542ef044bc3fa6"),
"id" : 3.0,
"name" : "V C#"
}
]
}
Here output shows student collection data along with referenced course record.