$lookup (aggregation)

Equality Match with a Single Join Condition具有单一联接条件的相等匹配

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获取具有以下字段的文档：

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:请参阅以下示例：

• Perform a Single Equality Join with $lookup使用$lookup执行单一相等联接
• Use $lookup with an Array将$lookup与数组一起使用
• Use $lookup with $mergeObjects将$lookup与$mergeObjects一起使用

Join Conditions and Subqueries on a Joined Collection已联接集合上的联接条件和子查询

MongoDB supports:MongoDB支持：

• Executing a pipeline on a joined collection.对已联接的集合执行管道。
• Multiple join conditions.多个联接条件。
• Correlated and uncorrelated subqueries.相关和不相关的子查询。

In MongoDB, a correlated subquery is a pipeline in a $lookup stage that references document fields from a joined collection. 在MongoDB中，关联子查询是$lookup阶段中的一个pipeline，它引用联接集合中的文档字段。An uncorrelated subquery does not reference joined fields.不相关的子查询不引用联接字段。

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阶段接受具有以下字段的文档：

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:请参阅以下示例：

• Perform Multiple Joins and a Correlated Subquery with $lookup使用$lookup执行多个联接和关联子查询
• Perform an Uncorrelated Subquery with $lookup使用$lookup执行不相关子查询

Correlated Subqueries Using Concise Syntax使用简明语法的关联子查询

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.关联子查询引用已联接的“foreign”集合和运行aggregate()方法的“local”集合中的文档字段。

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接受具有以下字段的文档：

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:请参见此示例：

• Perform a Concise Correlated Subquery with $lookup使用$lookup执行简明关联子查询

Views and Collation视图和排序

If performing an aggregation that involves multiple views, such as with $lookup or $graphLookup, the views must have the same collation.如果执行涉及多个视图的聚合，例如使用$lookup或$graphLookup，则这些视图必须具有相同的排序规则。

Restrictions限制

You cannot include the $out or the $merge stage in the $lookup stage. 在$lookup阶段中不能包含$out或$merge阶段。That is, when specifying a pipeline for the joined collection, you cannot include either stage in the 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>
   }
}

Atlas Search SupportAtlas搜索支持

Starting in MongoDB 6.0, you can specify the Atlas Search $search or $searchMeta stage in the $lookup pipeline to search collections on the Atlas cluster. The $search or the $searchMeta stage must be the first stage inside the $lookup pipeline.从MongoDB 6.0开始，您可以在$lookup管道中指定Atlas SearchAtlas Search $search或$searchMeta阶段来搜索Atlas集群上的集合。$search或$searchMeta阶段必须是$lookup管道内的第一个阶段。

For example, when you Join Conditions and Subqueries on a Joined Collection or run Correlated Subqueries Using Concise Syntax, you can specify $search or $searchMeta inside the pipeline as shown below:例如，当您在已联接集合上的联接条件和子查询或使用简明语法运行关联子查询时，您可以在管道内指定$search或$searchMeta，如下所示：

[{
  "$lookup": {
    "from": <joined collection>,
    localField: <field from the input documents>,
    foreignField: <field from the documents of the "from" collection>,
    "as": <output array field>,
    "pipeline": [{
      "$search": {
        "<operator>": {
          <operator-specification>
        }
      },
      ...
    }]
  }
}]

[{
  "$lookup": {
    "from": <joined collection>,
    localField: <field from the input documents>,
    foreignField: <field from the documents of the "from" collection>,
    "as": <output array field>,
    "pipeline": [{
      "$searchMeta": {
        "<collector>": {
          <collector-specification>
        }
      },
      ...
    }]
  }
}]

To see an example of $lookup with $search, see the Atlas Search tutorial Run an Atlas Search $search Query Using $lookup.要查看$lookup与$search的示例，请参阅Atlas search教程使用$lookup运行Atlas search$search查询。

Sharded Collections分片集合

Starting in MongoDB 5.1, you can specify sharded collections in the from parameter of $lookup stages.从MongoDB 5.1开始，您可以在$lookup阶段的from参数中指定分片集合。

Slot-Based Query Execution Engine基于插槽的查询执行引擎

Starting in version 6.0, MongoDB can use the slot-based execution query engine to execute $lookup stages if all preceding stages in the pipeline can also be executed by the slot-based execution engine and none of the following conditions are true:从6.0版本开始，MongoDB可以使用基于插槽的执行查询引擎来执行$lookup阶段，如果管道中的所有前面的阶段也可以由基于插槽的运行引擎来执行，并且以下条件都不成立：

• The $lookup operation executes a pipeline on a joined collection. $lookup操作在联接的集合上执行管道。To see an example of this kind of operation, see Join Conditions and Subqueries on a Joined Collection.要查看此类操作的示例，请参阅已联接集合上的联接条件和子查询。
• The $lookup's localField or foreignField specify numeric components. For example: { localField: "restaurant.0.review" }.$lookup的localField或foreignField指定数字组件。例如：{ localField: "restaurant.0.review" }。
• The from field of any $lookup in the pipeline specifies a view or sharded collection.管道中任何$lookup的from字段都指定了一个视图或分片集合。

For more information, see $lookup Optimization.有关详细信息，请参阅$lookup优化。

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集合中的字段item和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
);

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", 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 enrollmentlist field to the name field:以下聚合操作将classes集合中的文档与members集合连接起来，在enrollmentlist字段与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" }
   ]
}

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 joined documents from items and orders:以下操作首先使用$lookup阶段按item字段联接两个集合，然后使用$replaceRoot中的$mergeObjects合并来自item和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
}

Perform Multiple Joins and a Correlated Subquery with $lookup使用$lookup执行多个联接和关联子查询

Pipelines can execute on a joined collection and include multiple join conditions.管道可以在连接的集合上执行，并包括多个连接条件。

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 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.确保库存商品的数量能够满足订单数量。

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

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集合使用索引。限制：

• Multikey indexes are not used.不使用多键索引。
• Indexes are not used for comparisons where the operand is an array or the operand type is undefined.如果操作数是数组或操作数类型未定义，则不将索引用于比较。
• Indexes are not used for comparisons with more than one field path operand.索引不用于与多个字段路径操作数进行比较。

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 joined collection, which allows uncorrelated subqueries. 聚合管道$lookup阶段可以在联接的集合上执行管道，这允许不相关的子查询。An uncorrelated subquery does not reference the joined document fields.不相关的子查询不引用联接的文档字段。

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

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. 通过将orders.restaurant_name localField与restaurants.name foreignField匹配来连接orders和restaurants集合。The match is performed before the pipeline is run.匹配在pipeline运行之前执行。
• Performs an $in array match between the orders.drink and restaurants.beverages fields that are accessed using $$orders_drink and $beverages respectively.在分别使用$$orders_drink和$beverages访问的orders.drink和restaurants.beverages字段之间执行$in数组匹配。

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.在引入简洁的关联子查询之前，您必须在管道$lookup阶段的$expr运算符中的本地字段和联接字段之间使用$eq相等匹配，如使用$lookup执行多个联接和关联子查询中所示。

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