The Most Awaited Feature “SQL Joins” is Now Available in MongoDB 3.2

SQL Joins are used to combine documents/rows from 2 or more tables based upon common field present in them.

MongoDB 3.2 launched the most awaited feature “Joins” which is supported in SQL database however was not present in the earlier version of MongoDB. This feature will change the way you design your database schema and application using MongoDB.

Now you can run left-outer equi-joins query using $lookup operator in the MongoDB Aggregation Framework. With the help of Joins you can improve your application’s performance by reducing the number of queries on MongoDB.

What is a Left Outer Equi-Join?

Left Outer Equi-Join will have all data from left collection and only matching data from right collection.

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

The $lookup operator has the following syntax:

[sourcecode language=”css”]
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}

[/sourcecode]

Let me explain join with an example.

Orders collection:

[sourcecode language=”css”]
{
"_id" : 2.0,
"items" : [
3.0,
4.0
]
}

[/sourcecode]

Products collection:

[sourcecode language=”css”]
{
"_id" : 3.0,
"item_name" : "Desi Ghee",
"item_price" : 500.0,
"quantity" : "2 kg"
}

{
"_id" : 4.0,
"item_name" : "Namkeen",
"item_price" : 50.0,
"quantity" : "1 pkt"
}

[/sourcecode]

$lookup can be used in conjunction with other pipeline operators in aggregation framework to make a lookup into cross collection ( i,e left outer joins).

Below Query will first unwind the items presents in order collection and make a join with products collection by checking items equals to _id .

[sourcecode language=”css”]
db.orders.aggregate([
{
$unwind: "$items"
},
{
$lookup:
{
from: "products",
localField: "items",
foreignField: "_id",
as: "embeddedData"
}
}
])

[/sourcecode]

The output of above MongoDB query is:

[sourcecode language=”css”]
/* 1 */
{
"_id" : 2.0,
"items" : 3.0,
"products" : [
{
"_id" : 3.0,
"item_name" : "Desi Ghee",
"item_price" : 500.0,
"quantity" : "2 kg"
}
]
}

/* 2 */
{
"_id" : 2.0,
"items" : 4.0,
"products" : [
{
"_id" : 4.0,
"item_name" : "Namkeen",
"item_price" : 50.0,
"quantity" : "1 pkt"
}
]
}

[/sourcecode]

I hope this is helpful.

For more details check – https://docs.mongodb.com/v3.2/reference/operator/aggregation/lookup/