Joins in MongoDB ($lookup)

Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing.

Joins in MongoDB is introduced in MongoDB 3.2 with $lookup aggregate. Compared to SQL server it is Equivalent to Left outer Equi join. Such that repeated data can be avoided. Update seems to be simple.

The $lookup stage does an equality match between a field from the input documents with a field from the documents of the “joined” collection.
To each input document, the $lookup stage adds a new array field whose elements are the matching documents from the “joined” collection.
The $lookup stage passes these reshaped documents to the next stage.

The $lookup stage has the following 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>
}
}
Let’s see an illustration below.

Address Data :
=============
db.address.insert({
“address”:[{
“city”:”New York Phase#1″,
“country”:”USA”,
“near”:”Gulmohar Hotels”,
“flat#”:”A515″}],
“zip”:”101″
})

db.address.insert({
“address”:[{
“city”:”New York Phase#2″,
“country”:”USA”,
“near”:”Rosewood Hotels”,
“flat#”:”A513″}],
“zip”:”102″
})

db.address.insert({
“address”:[{
“city”:”New York Phase#3″,
“country”:”USA”,
“near”:”Berlyn Hotels”,
“flat#”:”A511″}],
“zip”:”103″
})

db.address.find().pretty()

Student Data :
=============

db.students.insert({ student_batch_no:1, name:”Steve”, interested_in :[“MongoDB”,”Neo4j”,”BigTable”,”DynamoDB”] , “pin”: “101” })
db.students.insert({ student_batch_no:1, name:”John”, interested_in :[“MongoDB”,”Neo4j”,”Cassandra”,”CouchBase”] , “pin”: “102” })
db.students.insert({ student_batch_no:1, name:”Jim”, interested_in :[“Cassandra”,”CouchBase”,”BigTable”,”DynamoDB”] , “pin”: “103” })

db.students.find().pretty()

Join/lookup query :
db.students.aggregate([
{$match : {student_batch_no:1}},
{$lookup : {
from: “address”,
localField : “pin”,
foreignField: “zip”,
as : “student_address”
}}]).pretty()

=—————————-
mongodb > use mydb
switched to db mydb
mongodb >
mongodb >
mongodb > db.students.insert({ student_batch_no:1, name:”Steve”, interested_in :[“MongoDB”,”Neo4j”,”BigTable”,”DynamoDB”] , “pin”: “101” })
WriteResult({ “nInserted” : 1 })
mongodb >
mongodb > db.students.insert({ student_batch_no:1, name:”John”, interested_in :[“MongoDB”,”Neo4j”,”Cassandra”,”CouchBase”] , “pin”: “102” })
WriteResult({ “nInserted” : 1 })
mongodb >
mongodb > db.students.insert({ student_batch_no:1, name:”Jim”, interested_in :[“Cassandra”,”CouchBase”,”BigTable”,”DynamoDB”] , “pin”: “103” })
WriteResult({ “nInserted” : 1 })
mongodb >
mongodb > db.students.find().pretty()
{
“_id” : ObjectId(“577b90c5ac09d4e504c08831”),
“student_batch_no” : 1,
“name” : “Steve”,
“interested_in” : [
“MongoDB”,
“Neo4j”,
“BigTable”,
“DynamoDB”
],
“pin” : “101”
}
{
“_id” : ObjectId(“577b90c5ac09d4e504c08832”),
“student_batch_no” : 1,
“name” : “John”,
“interested_in” : [
“MongoDB”,
“Neo4j”,
“Cassandra”,
“CouchBase”
],
“pin” : “102”
}
{
“_id” : ObjectId(“577b90c5ac09d4e504c08833”),
“student_batch_no” : 1,
“name” : “Jim”,
“interested_in” : [
“Cassandra”,
“CouchBase”,
“BigTable”,
“DynamoDB”
],
“pin” : “103”
}
mongodb >
mongodb >
mongodb > db.address.insert({
… “address”:[{
… “city”:”New York Phase#1″,
… “country”:”USA”,
… “near”:”Gulmohar Hotels”,
… “flat#”:”A515″}],
… “zip”:”101″
… })
WriteResult({ “nInserted” : 1 })
mongodb >
mongodb > db.address.insert({
… “address”:[{
… “city”:”New York Phase#2″,
… “country”:”USA”,
… “near”:”Rosewood Hotels”,
… “flat#”:”A513″}],
… “zip”:”102″
… })
WriteResult({ “nInserted” : 1 })
mongodb >
mongodb > db.address.insert({
… “address”:[{
… “city”:”New York Phase#3″,
… “country”:”USA”,
… “near”:”Berlyn Hotels”,
… “flat#”:”A511″}],
… “zip”:”103″
… })
WriteResult({ “nInserted” : 1 })
mongodb >
mongodb > db.address.find().pretty()
{
“_id” : ObjectId(“577b90d3ac09d4e504c08834”),
“address” : [
{
“city” : “New York Phase#1”,
“country” : “USA”,
“near” : “Gulmohar Hotels”,
“flat#” : “A515”
}
],
“zip” : “101”
}
{
“_id” : ObjectId(“577b90d9ac09d4e504c08835”),
“address” : [
{
“city” : “New York Phase#2”,
“country” : “USA”,
“near” : “Rosewood Hotels”,
“flat#” : “A513”
}
],
“zip” : “102”
}
{
“_id” : ObjectId(“577b90e0ac09d4e504c08836”),
“address” : [
{
“city” : “New York Phase#3”,
“country” : “USA”,
“near” : “Berlyn Hotels”,
“flat#” : “A511”
}
],
“zip” : “103”
}
mongodb >
mongodb >
mongodb >
mongodb > db.students.aggregate([
… {$match : {student_batch_no:1}},
… {$lookup : {
… from: “address”,
… localField : “pin”,
… foreignField: “zip”,
… as : “student_address”
… }}]).pretty()
{
“_id” : ObjectId(“577b90c5ac09d4e504c08831”),
“student_batch_no” : 1,
“name” : “Steve”,
“interested_in” : [
“MongoDB”,
“Neo4j”,
“BigTable”,
“DynamoDB”
],
“pin” : “101”,
“student_address” : [
{
“_id” : ObjectId(“577b90d3ac09d4e504c08834”),
“address” : [
{
“city” : “New York Phase#1”,
“country” : “USA”,
“near” : “Gulmohar Hotels”,
“flat#” : “A515”
}
],
“zip” : “101”
}
]
}
{
“_id” : ObjectId(“577b90c5ac09d4e504c08832”),
“student_batch_no” : 1,
“name” : “John”,
“interested_in” : [
“MongoDB”,
“Neo4j”,
“Cassandra”,
“CouchBase”
],
“pin” : “102”,
“student_address” : [
{
“_id” : ObjectId(“577b90d9ac09d4e504c08835”),
“address” : [
{
“city” : “New York Phase#2”,
“country” : “USA”,
“near” : “Rosewood Hotels”,
“flat#” : “A513”
}
],
“zip” : “102”
}
]
}
{
“_id” : ObjectId(“577b90c5ac09d4e504c08833”),
“student_batch_no” : 1,
“name” : “Jim”,
“interested_in” : [
“Cassandra”,
“CouchBase”,
“BigTable”,
“DynamoDB”
],
“pin” : “103”,
“student_address” : [
{
“_id” : ObjectId(“577b90e0ac09d4e504c08836”),
“address” : [
{
“city” : “New York Phase#3”,
“country” : “USA”,
“near” : “Berlyn Hotels”,
“flat#” : “A511”
}
],
“zip” : “103”
}
]
}
mongodb >
mongodb >
mongodb >