Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
APPLIES TO:
MongoDB vCore
The $lookup
stage in the Aggregation Framework is used to perform left outer joins with other collections. It allows you to combine documents from different collections based on a specified condition. This operator is useful for enriching documents with related data from other collections without having to perform multiple queries.
The syntax for the $lookup
stage is as follows:
{
$lookup: {
from: <collection to join>,
localField: <field from input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}
Description | |
---|---|
from |
The name of the collection to join with. |
localField |
The field from the input documents that are matched with the foreignField . |
foreignField |
The field from the documents in the from collection that are matched with the localField . |
as |
The name of the new array field to add to the input documents. This array contains the matched documents from the from collection. |
Let's say we have a ratings
collection with two documents.
{
"_id": "7954bd5c-9ac2-4c10-bb7a-2b79bd0963c5",
"rating": 5
}
{
"_id": "fecca713-35b6-44fb-898d-85232c62db2f",
"rating": 3
}
Let's understand the usage with the following sample json in stores collection.
{
"_id": "7954bd5c-9ac2-4c10-bb7a-2b79bd0963c5",
"name": "Lakeshore Retail | DJ Equipment Stop - Port Cecile",
"location": {
"lat": 60.1441,
"lon": -141.5012
},
"staff": {
"totalStaff": {
"fullTime": 2,
"partTime": 0
}
},
"sales": {
"salesByCategory": [
{
"categoryName": "DJ Headphones",
"totalSales": 35921
}
],
"fullSales": 3700
},
"promotionEvents": [
{
"eventName": "Bargain Blitz Days",
"promotionalDates": {
"startDate": {
"Year": 2024,
"Month": 3,
"Day": 11
},
"endDate": {
"Year": 2024,
"Month": 2,
"Day": 18
}
},
"discounts": [
{
"categoryName": "DJ Turntables",
"discountPercentage": 18
},
{
"categoryName": "DJ Mixers",
"discountPercentage": 15
}
]
}
],
"tag": [
"#ShopLocal",
"#SeasonalSale",
"#FreeShipping",
"#MembershipDeals"
]
}
We want to join the ratings
collection with the stores
collection to list promotion events related to each store having a 5 rating.
db.ratings.aggregate([
// filter based on rating in ratings collection
{
$match: {
"rating": 5
}
},
// find related documents in stores collection
{
$lookup: {
from: "stores",
localField: "_id",
foreignField: "_id",
as: "storeEvents"
}
},
// deconstruct array to output a document for each element of the array
{
$unwind: "$storeEvents"
},
// Include only _id and name fields in the output
{ $project: { _id: 1, "storeEvents.name": 1 } }
])
This query would return the following document.
[
{
"_id": "7954bd5c-9ac2-4c10-bb7a-2b79bd0963c5",
"storeEvents": { "name": "Lakeshore Retail | DJ Equipment Stop - Port Cecile" }
}
]
- let isn't supported as part of pipeline
- Review options for Migrating from MongoDB to Azure Cosmos DB for MongoDB (vCore)
- Read more about Feature compatibility with MongoDB