On this page本页内容
$lookup Changed in version 5.1.在版本5.1中更改。
Performs a left outer join to a collection in the same database to filter in documents from the "joined" collection for processing. 对同一个数据库中的集合执行左外部联接，以从“联接”集合中筛选文档进行处理。To each input document, the 对于每个输入文档，$lookup stage adds a new array field whose elements are the matching documents from the "joined" collection. $lookup阶段将添加一个新的数组字段，该字段的元素是“联接”集合中的匹配文档。The $lookup stage passes these reshaped documents to the next stage.$lookup阶段将这些重新成形的文档传递到下一阶段。
Starting in MongoDB 5.1, 从MongoDB 5.1开始，$lookup works across sharded collections.$lookup可以跨分片集合工作。
The $lookup stage has the following syntaxes:$lookup阶段具有以下语法：
To perform an equality match between a field from the input documents with a field from the documents of the "joined" collection, the 为了在输入文档中的字段与“联接”集合中的文档中的一个字段之间执行相等匹配，$lookup stage has this syntax:$lookup阶段具有以下语法：
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}
The $lookup takes a document with these fields:$lookup获取具有以下字段的文档：
from |
|
localField |
|
foreignField |
|
as |
|
The operation would correspond to the following pseudo-SQL statement:该操作将对应于以下伪SQL语句：
SELECT *, < output array field> FROM collection WHERE < output array field> IN ( SELECT * FROM < collection to join> WHERE < foreignField> = < collection.localField> );
See these examples:请参阅以下示例：
MongoDB 3.6 adds support for:MongoDB 3.6增加了对以下功能的支持：
In MongoDB, a correlated subquery is a pipeline in a 在MongoDB中，相关子查询是$lookup stage that references document fields from a joined collection. $lookup阶段中的一个管道，它引用来自联接集合的文档字段。An uncorrelated subquery does not reference joined fields.不相关子查询不引用联接字段。
Starting in MongoDB 5.0, for an uncorrelated subquery in a 从MongoDB 5.0开始，对于包含$lookup pipeline stage containing a $sample stage, the $sampleRate operator, or the $rand operator, the subquery is always run again if repeated. $sample阶段、$sampleRate运算符或$rand运算符的$lookup管道阶段中的不相关子查询，如果重复，子查询总是再次运行。Previously, depending on the subquery output size, either the subquery output was cached or the subquery was run again.以前，根据子查询输出大小，缓存子查询输出或再次运行子查询。
MongoDB correlated subqueries are comparable to SQL correlated subqueries, where the inner query references outer query values. MongoDB相关子查询与SQL相关子查询类似，其中内部查询引用外部查询值。An SQL uncorrelated subquery does not reference outer query values.SQL不相关子查询不引用外部查询值。
MongoDB 5.0 also supports concise correlated subqueries.MongoDB 5.0还支持简洁的相关子查询。
To perform correlated and uncorrelated subqueries with two collections, and perform other join conditions besides a single equality match, use this 要对两个集合执行相关和不相关子查询，并执行除单个相等匹配之外的其他联接条件，请使用以下$lookup syntax:$lookup语法：
{
$lookup:
{
from: <joined collection>,
let: { <var_1>: <expression>, …, <var_n>: <expression> },
pipeline: [ <pipeline to run on joined collection> ],
as: <output array field>
}
}
The $lookup stage accepts a document with these fields:$lookup阶段接受具有以下字段的文档：
from |
|
let |
|
pipeline |
|
as |
|
The operation corresponds to this pseudo-SQL statement:该操作对应于以下伪SQL语句：
SELECT *, < output array field> FROM collection WHERE < output array field> IN ( SELECT < documents as determined from the pipeline> FROM < collection to join> WHERE < pipeline> );
See the following examples:请参见以下示例：
New in version 5.0.在版本5.0中新增。
Starting in MongoDB 5.0, you can use a concise syntax for a correlated subquery. 从MongoDB 5.0开始，您可以为相关子查询使用简洁的语法。Correlated subqueries reference document fields from a joined "foreign" collection and the "local" collection on which the 相关子查询引用了连接的“外部”集合和“本地”集合中的文档字段，在该集合上运行了aggregate() method was run.aggregate()方法。
The following new concise syntax removes the requirement for an equality match on the foreign and local fields inside of an 以下新的简明语法消除了对$expr operator:$expr运算符内部的外部和本地字段进行相等匹配的要求：
{
$lookup:
{
from: <foreign collection>,
localField: <field from local collection's documents>,
foreignField: <field from foreign collection's documents>,
let: { <var_1>: <expression>, …, <var_n>: <expression> },
pipeline: [ <pipeline to run> ],
as: <output array field>
}
}
The $lookup accepts a document with these fields:$lookup接受具有以下字段的文档：
from |
|
localField |
|
foreignField |
|
let |
|
pipeline |
|
as |
|
The operation corresponds to this pseudo-SQL statement:该操作对应于以下伪SQL语句：
SELECT *, < output array field> FROM localCollection WHERE < output array field> IN ( SELECT < documents as determined from the pipeline> FROM < foreignCollection> WHERE < foreignCollection.foreignField> = < localCollection.localField> AND < pipeline match condition> );
See this example:请参阅以下示例：
If performing an aggregation that involves multiple views, such as with 如果执行涉及多个视图的聚合，例如使用$lookup or $graphLookup, the views must have the same collation.$lookup或$graphLookup，则这些视图必须具有相同的排序规则。
Changed in version 4.2.在版本4.2中更改。
$out or the $merge stage in the $lookup stage. $lookup阶段中不能包含$out或$merge阶段。pipeline field.pipeline字段中包含任何一个阶段。
{
$lookup:
{
from: <collection to join>,
let: { <var_1>: <expression>, …, <var_n>: <expression> },
pipeline: [ <pipeline to execute on the joined collection> ], // Cannot include $out or $merge
as: <output array field>
}
}
Starting in MongoDB 5.1, you can specify sharded collections in the 从MongoDB 5.1开始，您可以在from parameter of $lookup stages.$lookup阶段的from参数中指定分片集合。
$lookup$lookup执行单个等式联接Create a collection 使用以下文档创建集合orders with these documents:orders：
db.orders.insertMany( [
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
{ "_id" : 3 }
] )
Create another collection 使用以下文档创建另一个集合inventory with these documents:inventory：
db.inventory.insertMany( [
{ "_id" : 1, "sku" : "almonds", "description": "product 1", "instock" : 120 },
{ "_id" : 2, "sku" : "bread", "description": "product 2", "instock" : 80 },
{ "_id" : 3, "sku" : "cashews", "description": "product 3", "instock" : 60 },
{ "_id" : 4, "sku" : "pecans", "description": "product 4", "instock" : 70 },
{ "_id" : 5, "sku": null, "description": "Incomplete" },
{ "_id" : 6 }
] )
The following aggregation operation on the orders collection joins the documents from orders with the documents from the inventory collection using the fields item from the orders collection and the sku field from the inventory collection:orders集合上的以下聚合操作使用orders集合中的字段item和inventory集合中的sku字段，将orders中的文档与inventory集合的文档连接起来：
db.orders.aggregate( [
{
$lookup:
{
from: "inventory",
localField: "item",
foreignField: "sku",
as: "inventory_docs"
}
}
] )
The operation returns these documents:操作将返回以下文档：
{
"_id" : 1,
"item" : "almonds",
"price" : 12,
"quantity" : 2,
"inventory_docs" : [
{ "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 }
]
}
{
"_id" : 2,
"item" : "pecans",
"price" : 20,
"quantity" : 1,
"inventory_docs" : [
{ "_id" : 4, "sku" : "pecans", "description" : "product 4", "instock" : 70 }
]
}
{
"_id" : 3,
"inventory_docs" : [
{ "_id" : 5, "sku" : null, "description" : "Incomplete" },
{ "_id" : 6 }
]
}
The operation corresponds to this pseudo-SQL statement:该操作对应于以下伪SQL语句：
SELECT *, inventory_docs FROM orders WHERE inventory_docs IN ( SELECT * FROM inventory WHERE sku = orders.item );
$lookup with an Array$lookupIf the 如果localField is an array, you can match the array elements against a scalar foreignField without an $unwind stage.localField是数组，则可以将数组元素与标量foreignField匹配，而无需$unwind阶段。
For example, create an example collection 例如，使用以下文档创建示例集合classes with these documents:classes：
db.classes.insertMany( [
{ _id: 1, title: "Reading is ...", enrollmentlist: [ "giraffe2", "pandabear", "artie" ], days: ["M", "W", "F"] },
{ _id: 2, title: "But Writing ...", enrollmentlist: [ "giraffe1", "artie" ], days: ["T", "F"] }
] )
Create another collection 使用以下文档创建其他集合members with these documents:members：
db.members.insertMany( [
{ _id: 1, name: "artie", joined: new Date("2016-05-01"), status: "A" },
{ _id: 2, name: "giraffe", joined: new Date("2017-05-01"), status: "D" },
{ _id: 3, name: "giraffe1", joined: new Date("2017-10-01"), status: "A" },
{ _id: 4, name: "panda", joined: new Date("2018-10-11"), status: "A" },
{ _id: 5, name: "pandabear", joined: new Date("2018-12-01"), status: "A" },
{ _id: 6, name: "giraffe2", joined: new Date("2018-12-01"), status: "D" }
] )
The following aggregation operation joins documents in the 以下聚合操作将classes collection with the members collection, matching on the members field to the name field:classes集合中的文档与members集合连接起来，将members字段与name字段匹配：
db.classes.aggregate( [
{
$lookup:
{
from: "members",
localField: "enrollmentlist",
foreignField: "name",
as: "enrollee_info"
}
}
] )
The operation returns the following:该操作返回以下内容：
{
"_id" : 1,
"title" : "Reading is ...",
"enrollmentlist" : [ "giraffe2", "pandabear", "artie" ],
"days" : [ "M", "W", "F" ],
"enrollee_info" : [
{ "_id" : 1, "name" : "artie", "joined" : ISODate("2016-05-01T00:00:00Z"), "status" : "A" },
{ "_id" : 5, "name" : "pandabear", "joined" : ISODate("2018-12-01T00:00:00Z"), "status" : "A" },
{ "_id" : 6, "name" : "giraffe2", "joined" : ISODate("2018-12-01T00:00:00Z"), "status" : "D" }
]
}
{
"_id" : 2,
"title" : "But Writing ...",
"enrollmentlist" : [ "giraffe1", "artie" ],
"days" : [ "T", "F" ],
"enrollee_info" : [
{ "_id" : 1, "name" : "artie", "joined" : ISODate("2016-05-01T00:00:00Z"), "status" : "A" },
{ "_id" : 3, "name" : "giraffe1", "joined" : ISODate("2017-10-01T00:00:00Z"), "status" : "A" }
]
}
$lookup with $mergeObjects$lookup与$mergeObjects一起使用The $mergeObjects operator combines multiple documents into a single document.$mergeObjects运算符将多个文档合并为一个文档。
Create a collection 使用以下文档创建集合orders with these documents:orders：
db.orders.insertMany( [
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }
] )
Create another collection 使用以下文档创建另一个集合items with these documents:items：
db.items.insertMany( [
{ "_id" : 1, "item" : "almonds", description: "almond clusters", "instock" : 120 },
{ "_id" : 2, "item" : "bread", description: "raisin and nut bread", "instock" : 80 },
{ "_id" : 3, "item" : "pecans", description: "candied pecans", "instock" : 60 }
] )
The following operation first uses the 以下操作首先使用$lookup stage to join the two collections by the item fields and then uses $mergeObjects in the $replaceRoot to merge the joined documents from items and orders:$lookup阶段通过item字段连接两个集合，然后使用$replaceRoot中的$mergeObjects合并来自items和orders的连接文档：
db.orders.aggregate( [
{
$lookup: {
from: "items",
localField: "item",
// field in the orders collection
foreignField: "item", // field in the items collection
as: "fromItems"
}
},
{
$replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$fromItems", 0 ] }, "$$ROOT" ] } }
},
{ $project: { fromItems: 0 } }
] )
The operation returns these documents:操作将返回以下文档：
{
_id: 1,
item: 'almonds',
description: 'almond clusters',
instock: 120,
price: 12,
quantity: 2
},
{
_id: 2,
item: 'pecans',
description: 'candied pecans',
instock: 60,
price: 20,
quantity: 1
}
$lookup$lookup执行多个联接和相关子查询Changed in version 3.6.在版本3.6中更改。
MongoDB 3.6 adds support for executing a pipeline on a joined collection and allows multiple join conditions.MongoDB 3.6增加了对在连接的集合上执行管道的支持，并允许多个连接条件。
A join condition can reference a field in the local collection on which the 联接条件可以引用本地集合中运行了aggregate() method was run and reference a field in the joined collection. aggregate()方法的字段，并引用联接集合中的字段。This allows a correlated subquery between the two collections.这允许在两个集合之间进行相关子查询。
MongoDB 5.0 also supports concise correlated subqueries.MongoDB 5.0还支持简洁的相关子查询。
Create a collection 使用以下文档创建集合orders with these documents:orders：
db.orders.insertMany( [
{ "_id" : 1, "item" : "almonds", "price" : 12, "ordered" : 2 },
{ "_id" : 2, "item" : "pecans", "price" : 20, "ordered" : 1 },
{ "_id" : 3, "item" : "cookies", "price" : 10, "ordered" : 60 }
] )
Create another collection 使用以下文档创建另一个集合warehouses with these documents:warehouses：
db.warehouses.insertMany( [
{ "_id" : 1, "stock_item" : "almonds", warehouse: "A", "instock" : 120 },
{ "_id" : 2, "stock_item" : "pecans", warehouse: "A", "instock" : 80 },
{ "_id" : 3, "stock_item" : "almonds", warehouse: "B", "instock" : 60 },
{ "_id" : 4, "stock_item" : "cookies", warehouse: "B", "instock" : 40 },
{ "_id" : 5, "stock_item" : "cookies", warehouse: "A", "instock" : 80 }
] )
The following example:以下示例：
orders.item and warehouse.stock_item fields.orders.item和warehouse.stock_item字段上使用关联子查询和联接。db.orders.aggregate( [
{
$lookup:
{
from: "warehouses",
let: { order_item: "$item", order_qty: "$ordered" },
pipeline: [
{ $match:
{ $expr:
{ $and:
[
{ $eq: [ "$stock_item", "$$order_item" ] },
{ $gte: [ "$instock", "$$order_qty" ] }
]
}
}
},
{ $project: { stock_item: 0, _id: 0 } }
],
as: "stockdata"
}
}
] )
The operation returns these documents:操作将返回以下文档：
{
_id: 1,
item: 'almonds',
price: 12,
ordered: 2,
stockdata: [
{ warehouse: 'A', instock: 120 },
{ warehouse: 'B', instock: 60 }
]
},
{
_id: 2,
item: 'pecans',
price: 20,
ordered: 1,
stockdata: [ { warehouse: 'A', instock: 80 } ]
},
{
_id: 3,
item: 'cookies',
price: 10,
ordered: 60,
stockdata: [ { warehouse: 'A', instock: 80 } ]
}
The operation corresponds to this pseudo-SQL statement:该操作对应于以下伪SQL语句：
SELECT *, stockdata FROM orders WHERE stockdata IN ( SELECT warehouse, instock FROM warehouses WHERE stock_item = orders.item AND instock >= orders.ordered );
Starting in MongoDB 5.0, the 从MongoDB 5.0开始，放置在$eq, $lt, $lte, $gt, and $gte comparison operators placed in an $expr operator can use an index on the from collection referenced in a $lookup stage. $expr运算符中的$eq、$lt、$lte、$gt和$gte比较运算符可以在$lookup阶段引用的from集合上使用索引。Limitations:限制：
For example, if the index 例如，如果{ stock_item: 1, instock: 1 } exists on the warehouses collection:warehouses集合上存在索引{ stock_item: 1, instock: 1 }：
warehouses.stock_item field uses the index.warehouses.stock_item字段上的相等匹配使用索引。warehouses.instock field also uses the indexed field in the compound index.warehouses.instock字段查询的范围部分也使用复合索引中的索引字段。$lookup$lookup执行不相关的子查询An aggregation pipeline 聚合管道$lookup stage can execute a pipeline on the joined collection, which allows uncorrelated subqueries. An uncorrelated subquery does not reference the joined document fields.$lookup阶段可以在连接的集合上执行管道，这允许不相关的子查询。不相关的子查询不引用联接的文档字段。
Starting in MongoDB 5.0, for an uncorrelated subquery in a 从MongoDB 5.0开始，对于包含$lookup pipeline stage containing a $sample stage, the $sampleRate operator, or the $rand operator, the subquery is always run again if repeated. $sample阶段、$sampleRate运算符或$rand运算符的$lookup管道阶段中的不相关子查询，如果重复，子查询总是再次运行。Previously, depending on the subquery output size, either the subquery output was cached or the subquery was run again.以前，根据子查询输出大小，缓存子查询输出或再次运行子查询。
Create a collection 使用以下文档创建集合absences with these documents:absences：
db.absences.insertMany( [
{ "_id" : 1, "student" : "Ann Aardvark", sickdays: [ new Date ("2018-05-01"),new Date ("2018-08-23") ] },
{ "_id" : 2, "student" : "Zoe Zebra", sickdays: [ new Date ("2018-02-01"),new Date ("2018-05-23") ] },
] )
Create another collection 使用以下文档创建另一个集合holidays with these documents:holidays：
db.holidays.insertMany( [
{ "_id" : 1, year: 2018, name: "New Years", date: new Date("2018-01-01") },
{ "_id" : 2, year: 2018, name: "Pi Day", date: new Date("2018-03-14") },
{ "_id" : 3, year: 2018, name: "Ice Cream Day", date: new Date("2018-07-15") },
{ "_id" : 4, year: 2017, name: "New Years", date: new Date("2017-01-01") },
{ "_id" : 5, year: 2017, name: "Ice Cream Day", date: new Date("2017-07-16") }
] )
The following operation joins the 以下操作将absences collection with 2018 holiday information from the holidays collection:absences集合与holidays集合中的2018假日信息结合起来：
db.absences.aggregate( [
{
$lookup:
{
from: "holidays",
pipeline: [
{ $match: { year: 2018 } },
{ $project: { _id: 0, date: { name: "$name", date: "$date" } } },
{ $replaceRoot: { newRoot: "$date" } }
],
as: "holidays"
}
}
] )
The operation returns the following:该操作返回以下内容：
{
_id: 1,
student: 'Ann Aardvark',
sickdays: [
ISODate("2018-05-01T00:00:00.000Z"),
ISODate("2018-08-23T00:00:00.000Z")
],
holidays: [
{ name: 'New Years', date: ISODate("2018-01-01T00:00:00.000Z") },
{ name: 'Pi Day', date: ISODate("2018-03-14T00:00:00.000Z") },
{ name: 'Ice Cream Day', date: ISODate("2018-07-15T00:00:00.000Z")
}
]
},
{
_id: 2,
student: 'Zoe Zebra',
sickdays: [
ISODate("2018-02-01T00:00:00.000Z"),
ISODate("2018-05-23T00:00:00.000Z")
],
holidays: [
{ name: 'New Years', date: ISODate("2018-01-01T00:00:00.000Z") },
{ name: 'Pi Day', date: ISODate("2018-03-14T00:00:00.000Z") },
{ name: 'Ice Cream Day', date: ISODate("2018-07-15T00:00:00.000Z")
}
]
}
The operation corresponds to this pseudo-SQL statement:该操作对应于以下伪SQL语句：
SELECT *, holidays FROM absences WHERE holidays IN ( SELECT name, date FROM holidays WHERE year = 2018 );
$lookup$lookup执行简明的相关子查询New in version 5.0.在版本5.0中新增。
Starting in MongoDB 5.0, an aggregation pipeline 从MongoDB 5.0开始，聚合管道$lookup stage supports a concise correlated subquery syntax that improves joins between collections. $lookup阶段支持简洁的相关子查询语法，以改进集合之间的连接。The new concise syntax removes the requirement for an equality match on the foreign and local fields inside of an 新的简明语法消除了$expr operator in a $match stage.$match阶段中$expr运算符内部的外部和本地字段的相等匹配要求。
Create a collection 创建一个集合restaurants:restaurants：
db.restaurants.insertMany( [
{
_id: 1,
name: "American Steak House",
food: [ "filet", "sirloin" ],
beverages: [ "beer", "wine" ]
},
{
_id: 2,
name: "Honest John Pizza",
food: [ "cheese pizza", "pepperoni pizza" ],
beverages: [ "soda" ]
}
] )
Create another collection 使用食物和可选饮料订单创建另一个集合orders with food and optional drink orders:orders：
db.orders.insertMany( [
{
_id: 1,
item: "filet",
restaurant_name: "American Steak House"
},
{
_id: 2,
item: "cheese pizza",
restaurant_name: "Honest John Pizza",
drink: "lemonade"
},
{
_id: 3,
item: "cheese pizza",
restaurant_name: "Honest John Pizza",
drink: "soda"
}
] )
The following example:以下示例：
orders and restaurants collections by matching the orders.restaurant_name localField with the restaurants.name foreignField. orders.restaurant_name 本地字段与restaurants.name 外部字段匹配，加入orders和restaurants集合。pipeline is run.pipeline之前执行匹配。$in array match between the orders.drink and restaurants.beverages fields that are accessed using $$orders_drink and $beverages respectively.orders.drink和restaurants.beverages字段之间执行$in数组匹配，这两个字段分别使用$$orders_drink和$beverages访问。db.orders.aggregate( [
{
$lookup: {
from: "restaurants",
localField: "restaurant_name",
foreignField: "name",
let: { orders_drink: "$drink" },
pipeline: [ {
$match: {
$expr: { $in: [ "$$orders_drink", "$beverages" ] }
}
} ],
as: "matches"
}
}
] )
There is a match for the soda value in the orders.drink and restaurants.beverages fields. orders.drink和restaurants.beverages字段中有一个与soda值匹配的值。This output shows the 此输出显示matches array and contains all joined fields from the restaurants collection for the match:matches数组，并包含匹配的restaurants集合中的所有连接字段：
{
"_id" : 1, "item" : "filet",
"restaurant_name" : "American Steak House",
"matches" : [ ]
}
{
"_id" : 2, "item" : "cheese pizza",
"restaurant_name" : "Honest John Pizza",
"drink" : "lemonade",
"matches" : [ ]
}
{
"_id" : 3, "item" : "cheese pizza",
"restaurant_name" : "Honest John Pizza",
"drink" : "soda",
"matches" : [ {
"_id" : 2, "name" : "Honest John Pizza",
"food" : [ "cheese pizza", "pepperoni pizza" ],
"beverages" : [ "soda" ]
} ]
}
Before the introduction of concise correlated subqueries, you had to use an 在引入简明关联子查询之前，您必须在$eq equality match between the local field and the joined field in the $expr operator in the pipeline$lookup stage as shown in Perform Multiple Joins and a Correlated Subquery with $lookup.pipeline$lookup阶段的$expr运算符中的本地字段和联接字段之间使用$eq相等匹配，如执行多个联接和具有$lookout的关联子查询中所示。
This example uses the older verbose syntax from MongoDB versions before 5.0 and returns the same results as the previous concise example:此示例使用5.0之前的MongoDB版本中较旧的详细语法，并返回与前一个简明示例相同的结果：
db.orders.aggregate( [
{
$lookup: {
from: "restaurants",
let: { orders_restaurant_name: "$restaurant_name",
orders_drink: "$drink" },
pipeline: [ {
$match: {
$expr: {
$and: [
{ $eq: [ "$$orders_restaurant_name", "$name" ] },
{ $in: [ "$$orders_drink", "$beverages" ] }
]
}
}
} ],
as: "matches"
}
}
] )
The previous examples correspond to this pseudo-SQL statement:前面的示例对应于此伪SQL语句：
SELECT *, matches FROM orders WHERE matches IN ( SELECT * FROM restaurants WHERE restaurants.name = orders.restaurant_name AND restaurants.beverages = orders.drink );