$lookup (aggregation stage)（聚合阶段）

Perform a Single Equality Join with $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集合的字段项和inventory集合的sku字段将订单中的文档与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
);

For more information, see Equality Match Performance Considerations.有关详细信息，请参阅平等匹配性能考虑因素。

Use $lookup with an Array将$lookup与数组一起使用

If 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", foreign: new Date("2016-05-01"), status: "A" },
   { _id: 2, name: "giraffe", foreign: new Date("2017-05-01"), status: "D" },
   { _id: 3, name: "giraffe1", foreign: new Date("2017-10-01"), status: "A" },
   { _id: 4, name: "panda", foreign: new Date("2018-10-11"), status: "A" },
   { _id: 5, name: "pandabear", foreign: new Date("2018-12-01"), status: "A" },
   { _id: 6, name: "giraffe2", foreign: new Date("2018-12-01"), status: "D" }
] )

The following aggregation operation joins documents in the classes collection with the members collection, matching on the enrollmentlist field to the name field:以下聚合操作将classes集合中的文档与members集合连接起来，在enrollmentlist（注册列表）字段上与名称字段匹配：

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", foreign: ISODate("2016-05-01T00:00:00Z"), status: "A" },
      { _id: 5, name: "pandabear", foreign: ISODate("2018-12-01T00:00:00Z"), status: "A" },
      { _id: 6, name: "giraffe2", foreign: 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", foreign: ISODate("2016-05-01T00:00:00Z"), status: "A" },
      { _id: 3, name: "giraffe1", foreign: ISODate("2017-10-01T00:00:00Z"), status: "A" }
   ]
}

Use $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 foreign 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
}

Use Multiple Join Conditions and a Correlated Subquery使用多个连接条件和相关子查询

Pipelines can execute on a foreign collection and include multiple join conditions. 管道可以在外部集合上执行，并包含多个连接条件。The $expr operator enables more complex join conditions including conjunctions and non-equality matches.$expr运算符支持更复杂的连接条件，包括连词和非等式匹配。

A join condition can reference a field in the local collection on which the aggregate() method was run and reference a field in the foreign collection. This allows a correlated subquery between the two collections.连接条件可以引用本地集合中运行aggregate()方法的字段，也可以引用外部集合中的字段。这允许在两个集合之间进行相关子查询。

MongoDB 5.0 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:以下示例：

• Uses a correlated subquery with a join on the orders.item and warehouse.stock_item fields.在orders.item和warehouse.stock_item字段上使用具有联接的相关子查询。
• Ensures the quantity of the item in stock can fulfill the ordered quantity.确保inventory商品的数量能够满足订购数量。

db.orders.aggregate( [
   {
      $lookup:
         {
           from : "warehouses",
           localField : "item",
           foreignField : "stock_item",
           let : { order_qty: "$ordered" },
           pipeline : [
              { $match :
                 { $expr :
                      { $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
);

The $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. Limitations:放置在$expr运算符中的$eq、$lt、$lte、$gt和$gte比较运算符可以在$lookup阶段引用的from集合上使用索引。限制：

• Indexes can only be used for comparisons between fields and constants, so the let operand must resolve to a constant.索引只能用于字段和常量之间的比较，因此let操作数必须解析为常量。

  For example, a comparison between $a and a constant value can use an index, but a comparison between $a and $b cannot.例如，$a和常量值之间的比较可以使用索引，但$a和$b之间的比较不能。

• Indexes are not used for comparisons where the let operand resolves to an empty or missing value.当let操作数解析为空值或缺失值时，索引不用于比较。
• Multikey indexes are not used.不使用多键索引。

For example, if the index { stock_item: 1, instock: 1 } exists on the warehouses collection:例如，如果warehouses（仓库）集合上存在索引{ stock_item: 1, instock: 1 }：

• The equality match on the warehouses.stock_item field uses the index.warehouses.stock_item字段上的相等匹配使用索引。
• The range part of the query on the warehouses.instock field also uses the indexed field in the compound index.warehouses.instock字段查询的范围部分也使用复合索引中的索引字段。

Perform an Uncorrelated Subquery with $lookup使用$lookup执行不相关的子查询

An aggregation pipeline $lookup stage can execute a pipeline on the foreign collection, which allows uncorrelated subqueries. An uncorrelated subquery does not reference the local document fields.聚合管道$lookup阶段可以在外部集合上执行管道，这允许不相关的子查询。不相关的子查询不引用本地文档字段。

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
);

For more information, see Uncorrelated Subquery Performance Considerations.有关更多信息，请参阅不相关的子查询性能考虑因素。

Perform a Concise Correlated Subquery with $lookup使用$lookup执行简洁的相关子查询

Starting in MongoDB 5.0, an aggregation pipeline $lookup stage supports a concise correlated subquery syntax that improves joins between collections. 从MongoDB 5.0开始，聚合管道$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:以下示例：

• Joins the orders and restaurants collections by matching the orders.restaurant_name localField with the restaurants.name foreignField. The match is performed before the pipeline is run.通过将orders.restaurant_name localField与restaurants.name foreignField匹配来合并orders和restaurants集合。匹配在管道运行之前执行。
• Performs an $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. This output shows the matches array and contains all foreign fields from the restaurants collection for the match:在orders.drink和restaurants.beverages字段中，soda（苏打水）的值是匹配的。此输出显示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" ]
   } ]
}

This example uses the older verbose syntax from MongoDB versions before 5.0 and returns the same results as the previous concise example:此示例使用MongoDB 5.0之前版本的旧详细语法，并返回与前面简洁示例相同的结果：

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
);

For more information, see Correlated Subquery Performance Considerations.有关更多信息，请参阅相关子查询性能考虑因素。

Namespaces in Subpipelines子管道中的命名空间

Starting in MongoDB 8.0, namespaces in subpipelines within $lookup and $unionWith are validated to ensure the correct use of from and coll fields:从MongoDB 8.0开始，$lookup和$unionWith子管道中的命名空间经过验证，以确保from和coll字段的正确使用：

• For $lookup, omit the from field if you use a subpipeline with a stage which doesn't require a specified collection. For example, a $documents stage.对于$lookup，如果您使用的子管道具有不需要指定集合的阶段，请省略from字段。例如，$documents阶段。
• Similarly, for $unionWith, omit the coll field.同样，对于$unionWith，省略coll字段。

Unchanged behavior:行为不变：

• For a $lookup that starts with a stage for a collection, for example a $match or $collStats subpipeline, you must include the from field and specify the collection.对于以集合的阶段开始的$lookup，例如$match或$collStats子管道，您必须包含from字段并指定集合。
• Similarly, for $unionWith, include the coll field and specify the collection.同样，对于$unionWith，包含coll字段并指定集合。

The following scenario shows an example.以下场景显示了一个示例。

Create a collection cakeFlavors:创建一个集合cakeFlavors（蛋糕口味）：

db.cakeFlavors.insertMany( [
   { _id: 1, flavor: "chocolate" },
   { _id: 2, flavor: "strawberry" },
   { _id: 3, flavor: "cherry" }
] )

Starting in MongoDB 8.0, the following example returns an error because it contains an invalid from field:从MongoDB 8.0开始，以下示例返回错误，因为它包含无效的from字段：

db.cakeFlavors.aggregate( [ {
   $lookup: {
      from: "cakeFlavors",
      pipeline: [ { $documents: [ {} ] } ],
      as: "test"
   }
} ] )

In MongoDB versions before 8.0, the previous example runs.在MongoDB 8.0之前的版本中，前面的示例运行。

For an example with a valid from field, see Perform a Single Equality Join with $lookup.有关有效from字段的示例，请参阅使用$lookup执行单个相等连接。