The $lookup operator in MongoDB is a powerful tool for performing join-like operations between documents from two collections. It allows us to perform join operations which are defined by documents from one collection with data from another collection based on a specified matching condition.
In this article, We will learn about MongoDB Aggregation $lookup along with multiple examples in detail.
Understanding the $lookup Operator
- The $lookup operator in MongoDB allows us to perform a left outer join between documents from two collections.
- This means we can perform the query of documents from one collection with data from another collection based on a specified matching condition.
- It’s useful for combining data from different collections for analysis or reporting purposes.
- The operator can handle cases where there are no matching documents in the foreign collection, providing flexibility in data retrieval.
Syntax:
The basic syntax of the $lookup operator is as follows:
{
$lookup: {
from: <foreignCollection>,
localField: <fieldInInputDocument>,
foreignField: <fieldInForeignDocument>,
as: <outputArrayField>
}
}
Explanation:
- from: The name of the foreign collection to join with.
- localField: The field from the input documents that will be used for matching.
- foreignField: The field from the foreign collection that will be used for matching.
- as: The name of the output array field that will contain the joined documents.
Let’s set up an Environment:
To understand MongoDB Aggregation $lookup we need a collection and some documents on which we will perform various operations and queries. Here we will consider a collection called orders and customers which contains various information shown as below:
Collection: orders
[
{
"_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"order_number": "ORD001",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"name": "John Doe",
"email": "john@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"order_number": "ORD002",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"name": "John Doe",
"email": "john@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a870"),
"order_number": "ORD003",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"name": "Alice Smith",
"email": "alice@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a871"),
"order_number": "ORD004",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"name": "Bob Johnson",
"email": "bob@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a872"),
"order_number": "ORD005",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"name": "Bob Johnson",
"email": "bob@example.com"
}
]
}
]
Collection: customers
[
{
"_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"name": "John Doe",
"email": "john@example.com"
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"name": "Alice Smith",
"email": "alice@example.com"
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"name": "Bob Johnson",
"email": "bob@example.com"
}
]
Example of MongoDB Aggregation $lookup
Example 1
db.orders.aggregate([
{
$lookup: {
from: "customers",
localField: "customer_id",
foreignField: "_id",
as: "customer_details"
}
}
])
Output:
[
{
"_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"order_number": "ORD001",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"name": "John Doe",
"email": "john@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"order_number": "ORD002",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"name": "John Doe",
"email": "john@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a870"),
"order_number": "ORD003",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"name": "Alice Smith",
"email": "alice@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a871"),
"order_number": "ORD004",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"name": "Bob Johnson",
"email": "bob@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a872"),
"order_number": "ORD005",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"name": "Bob Johnson",
"email": "bob@example.com"
}
]
}
]
Example 2: Handling Unmatched Documents
By default, the $lookup operator includes an array field (as) in the output documents, even if no matches are found in the foreign collection. This array field will be empty ([]) for unmatched documents.
Example:
Continuing from the previous example, suppose there are orders with customer_id values that do not exist in the customers collection. The $lookup operator will still include these orders in the output, with an empty customer_details array for unmatched documents.
db.orders.aggregate([
{
$lookup: {
from: "customers",
localField: "customer_id",
foreignField: "_id",
as: "customer_details"
}
},
{
$addFields: {
customer_details: {
$cond: {
if: { $eq: [{ $size: "$customer_details" }, 0] },
then: [],
else: "$customer_details"
}
}
}
}
])
Output:
[
{
"_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"order_number": "ORD001",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"name": "John Doe",
"email": "john@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"order_number": "ORD002",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"name": "John Doe",
"email": "john@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a870"),
"order_number": "ORD003",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"name": "Alice Smith",
"email": "alice@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a871"),
"order_number": "ORD004",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"name": "Bob Johnson",
"email": "bob@example.com"
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a872"),
"order_number": "ORD005",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"name": "Bob Johnson",
"email": "bob@example.com"
}
]
}
]
Example 3: Multiple $lookup Stages
We can include multiple $lookup stages in an aggregation pipeline to perform multiple join operations with different foreign collections.
Example:
Suppose we want to enrich orders documents with details from both customers and products collections. We can include multiple $lookup stages to achieve this:
db.orders.aggregate([
{
$lookup: {
from: "customers",
localField: "customer_id",
foreignField: "_id",
as: "customer_details"
}
},
{
$lookup: {
from: "products",
localField: "product_id",
foreignField: "_id",
as: "product_details"
}
}
])
Output:
[
{
"_id": ObjectId("60f9d7ac345b7c9df348a86e"),
"order_number": "ORD001",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"name": "John Doe",
"email": "john@example.com"
}
],
"product_id": ObjectId("..."),
"product_details": [
{
"_id": ObjectId("..."),
"name": "Product Name",
"price": 100
}
]
},
{
"_id": ObjectId("60f9d7ac345b7c9df348a86f"),
"order_number": "ORD002",
"customer_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"customer_details": [
{
"_id": ObjectId("60f9d7ac345b7c9df348a86d"),
"name": "John Doe",
"email": "john@example.com"
}
],
"product_id": ObjectId("..."),
"product_details": [
{
"_id": ObjectId("..."),
"name": "Product Name",
"price": 100
}
]
},
...
]
In this example, we first join orders with customers and then join the resulting documents with products.
Conclusion
Overall, The $lookup operator in MongoDB’s aggregation framework is a powerful tool for performing join-like operations between collections. By using $lookup, you can perform the query of documents with related data from other collections, enabling more complex data analysis and processing. Whether performing basic one-to-one joins or using pipelines for advanced transformations, the $lookup operator provides flexibility and versatility in MongoDB aggregation.