To join three tables (or collections) in MongoDB, you can use the $lookup aggregation stage multiple times within an aggregation pipeline. This allows you to perform multiple left outer joins. Here’s an example scenario and how you can implement it.
Example Scenario
Let's assume we have three collections: orders, customers, and products.
orders Collection:
[
{ "_id": 1, "order_id": "ORD001", "customer_id": 1, "product_id": 101, "amount": 500 },
{ "_id": 2, "order_id": "ORD002", "customer_id": 2, "product_id": 102, "amount": 200 },
{ "_id": 3, "order_id": "ORD003", "customer_id": 1, "product_id": 103, "amount": 300 },
{ "_id": 4, "order_id": "ORD004", "customer_id": 3, "product_id": 101, "amount": 700 }
]
customers Collection:
[
{ "_id": 1, "name": "John Doe", "email": "john@example.com" },
{ "_id": 2, "name": "Jane Smith", "email": "jane@example.com" },
{ "_id": 3, "name": "Mike Johnson", "email": "mike@example.com" }
]
products Collection:
[
{ "_id": 101, "product_name": "Laptop", "price": 1000 },
{ "_id": 102, "product_name": "Phone", "price": 500 },
{ "_id": 103, "product_name": "Tablet", "price": 300 }
]
Joining the Collections
We want to create a report that includes orders along with the customer details and product details.
Aggregation Pipeline with Multiple $lookup Stages
Here’s how to use multiple $lookup stages to join the three collections:
db.orders.aggregate([
{
$lookup: {
from: "customers",
localField: "customer_id",
foreignField: "_id",
as: "customer_info"
}
},
{
$unwind: "$customer_info"
},
{
$lookup: {
from: "products",
localField: "product_id",
foreignField: "_id",
as: "product_info"
}
},
{
$unwind: "$product_info"
},
{
$project: {
order_id: 1,
amount: 1,
"customer_info.name": 1,
"customer_info.email": 1,
"product_info.product_name": 1,
"product_info.price": 1
}
}
])
Explanation:
- First
$lookup: Joins the orders collection with the customers collection using the customer_id field. - First
$unwind: Deconstructs the resulting customer_info array so that each document contains a single customer object. - Second
$lookup: Joins the resulting documents with the products collection using the product_id field. - Second
$unwind: Deconstructs the resulting product_info array so that each document contains a single product object. $project: Specifies the fields to include in the final output, creating a concise and readable result.
Result:
The result of the above aggregation will be:
[
{
"_id": 1,
"order_id": "ORD001",
"amount": 500,
"customer_info": {
"name": "John Doe",
"email": "john@example.com"
},
"product_info": {
"product_name": "Laptop",
"price": 1000
}
},
{
"_id": 3,
"order_id": "ORD003",
"amount": 300,
"customer_info": {
"name": "John Doe",
"email": "john@example.com"
},
"product_info": {
"product_name": "Tablet",
"price": 300
}
},
{
"_id": 2,
"order_id": "ORD002",
"amount": 200,
"customer_info": {
"name": "Jane Smith",
"email": "jane@example.com"
},
"product_info": {
"product_name": "Phone",
"price": 500
}
},
{
"_id": 4,
"order_id": "ORD004",
"amount": 700,
"customer_info": {
"name": "Mike Johnson",
"email": "mike@example.com"
},
"product_info": {
"product_name": "Laptop",
"price": 1000
}
}
]
In this result:
- Each order document now includes the customer details (
customer_info) and the product details (product_info). - The
project stage ensures that only the relevant fields are included in the output.
Additional Tips:
- Complex Joins: For more complex joins, you can use additional aggregation stages like
$group, $match, or nested $lookup stages. - Optimizing Performance: Ensure that the fields used in joins are indexed to improve performance.
- Pipeline Customization: Customize the pipeline stages to fit specific requirements, such as filtering, sorting, or additional calculations.
By using multiple $lookup stages in the aggregation pipeline, you can effectively join multiple collections and create comprehensive reports or views in MongoDB.
