/ /

$lookup（聚合阶段）

定义

$lookup

在版本5.1中进行了更改。

Performs a left outer join to a collection in the same database to filter in documents from the foreign collection for processing. The $lookup stage adds a new array field to each input document. The new array field contains the matching documents from the foreign collection. The $lookup stage passes these reshaped documents to the next stage.

从 MongoDB 5.1 开始，可以将 $lookup 与分片集合一起使用。

要组合来自两个不同集合的元素，请使用 $unionWith 管道阶段。

重要

$lookup 与任何其他查询性能一样，性能取决于操作类型以及外部字段是否已建立索引。大型集合上未索引或相关的子查询 $lookup 操作可能会降低查询性能。为了减少对 $lookup 的依赖，请考虑使用嵌入式数据模型将相关数据存储在单个集合中。

For details on $lookup performance by operation type, see Performance Considerations.

兼容性

可以使用 $lookup 查找托管在以下环境中的部署：

MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud

MongoDB Enterprise：基于订阅、自我管理的 MongoDB 版本
MongoDB Community：源代码可用、免费使用且可自行管理的 MongoDB 版本

语法

The $lookup stage syntax:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       let: { <var_1>: <expression>, …, <var_n>: <expression> },
       pipeline: [ <pipeline to run> ],
       as: <output array field>
     }
}

The $lookup accepts a document with these fields:

字段	必要性	说明
from	必需	在同一个数据库中指定待联接到本地集合的外部集合。在某些边缘情况下，可以使用 `pipeline` 替换 `from`，并将 `$documents` 作为第一阶段。有关示例，请参阅在 $lookup 阶段中使用 $documents 阶段。从 MongoDB 5.1 开始，`from` 集合可以分片。
localField	如果指定了 `pipeline`，则为可选	Specifies the field from the documents input to the `$lookup` stage. `$lookup` performs an equality match on the `localField` to the `foreignField` from the documents of the `from` collection. If an input document does not contain the `localField`, the `$lookup` treats the field as having a value of `null` for matching purposes.
foreignField	如果指定了 `pipeline`，则为可选	指定外部文档的 `foreignField` 对本地文档的 `localField` 执行等值匹配。 If a foreign document does not contain a `foreignField` value, the `$lookup` uses a `null` value for the match.
let	Optional	指定各个管道阶段中使用的变量。使用变量表达式访问本地集合文档中的字段，这些文档输入到 `pipeline`。 To reference variables in pipeline stages, use the `"$$<variable>"` syntax. The let variables can be accessed by the stages in the pipeline, including additional `$lookup` stages nested in the `pipeline`. `$match` 阶段需要使用 `$expr` 操作符来访问变量。`$expr` 操作符允许在 `$match` 语法中使用聚合表达式。 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: 索引只能用于字段和常量之间的比较，因此 `let` 操作数必须解析为常量。示例，`$a` 和常量值之间的比较可以使用索引，但 `$a` 和 `$b` 之间的比较不能使用索引。当 `let` 操作数解析为空值或缺失值时，索引不用于比较。不使用多键、部分或稀疏索引。 Other (non-`$match`) stages in the pipeline do not require an `$expr` operator to access the variables.
管道	如果指定了 `localField` 和 `foreignField`，则为可选	指定在外部集合上运行的 `pipeline`。`pipeline` 返回外部集合的文档。如要返回所有文档，请指定一个空的 `pipeline: []`。 The `pipeline` cannot include the `$out` or `$merge` stages. Starting in v6.0, the `pipeline` can contain the Atlas Search `$search` stage as the first stage inside the pipeline. To learn more, see Atlas Search Support. The `pipeline` cannot access fields from input documents. Instead, define variables for the document fields using the let option and then reference the variables in the `pipeline` stages. To reference variables in pipeline stages, use the `"$$<variable>"` syntax. The let variables can be accessed by the stages in the pipeline, including additional `$lookup` stages nested in the `pipeline`. `$match` 阶段需要使用 `$expr` 操作符来访问变量。`$expr` 操作符允许在 `$match` 语法中使用聚合表达式。 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: 索引只能用于字段和常量之间的比较，因此 `let` 操作数必须解析为常量。示例，`$a` 和常量值之间的比较可以使用索引，但 `$a` 和 `$b` 之间的比较不能使用索引。当 `let` 操作数解析为空值或缺失值时，索引不用于比较。不使用多键、部分或稀疏索引。 Other (non-`$match`) stages in the pipeline do not require an `$expr` operator to access the variables.
作为	必需	指定要添加到输入文档中的新数组字段的名称。新数组字段包含来自 `from` 集合的匹配文档。如果输入文档中已存在指定的名称，现有字段将被重写。

涉及单个条件联接的等值匹配

To perform an equality match between a field from the input documents with a field from the documents of the foreign collection, the $lookup stage has this syntax:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       pipeline: [ <pipeline to run> ],
       as: <output array field>
     }
}

注意

在此示例中，pipeline 是可选的，在本地和外部相等阶段之后运行。

该操作对应于如下伪 SQL 语句：

SELECT *, (
   SELECT ARRAY_AGG(*)
   FROM <collection to join>
   WHERE <foreignField> = <collection.localField>
) AS <output array field>
FROM collection;

注意

此页面上的 SQL 语句用于与 MongoDB 聚合管道语法进行比较。SQL 语句无法运行。

有关 MongoDB 示例，请参阅以下页面：

执行单一等值联接 $lookup
结合数组使用 $lookup
将 $lookup 与 $mergeObjects 结合使用

外部集合上的联接条件和子查询

MongoDB 支持：

在外部集合上执行管道。
多个联接条件。
关联和不关联子查询。

In MongoDB, an uncorrelated subquery means that every input document returns the same result. A correlated subquery is a pipeline in a $lookup stage that uses the local or input collection's fields to return results correlated to each incoming document.

注意

Starting in MongoDB 5.0, for an uncorrelated subquery in a $lookup pipeline stage containing a $sample stage, the $sampleRate operator, or the $rand operator, the subquery is always run again if repeated. Previously, depending on the subquery output size, either the subquery output was cached or the subquery was run again.

MongoDB 相关子查询与 SQL 相关子查询类似，其中内部查询引用外部查询值。SQL 不相关子查询不引用外部查询值。

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:
      {
         from: <foreign collection>,
         let: { <var_1>: <expression>, …, <var_n>: <expression> },
         pipeline: [ <pipeline to run on foreign collection> ],
         as: <output array field>
      }
}

该操作对应于如下伪 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>
);

请参阅以下示例：

使用多个连接条件和关联子查询
执行非关联子查询 $lookup

采用简洁语法的关联子查询

版本 5.0 中的新增功能。

从 MongoDB 5.0 开始，您可以对关联子查询使用简洁的语法。关联子查询引用了外部集合和运行 aggregate() 方法的“本地”集合中的文档字段。

下面这个新的简洁语法删除了对于 $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>
      }
}

该操作对应于如下伪 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>
);

请参阅如下示例：

执行简洁关联子查询 $lookup

行为

视图和排序规则

If performing an aggregation that involves multiple views, such as with $lookup or $graphLookup, the views must have the same collation.

限制

You cannot include the $out or the $merge stage in the $lookup stage. That is, when specifying a pipeline for the foreign collection, you cannot include either stage in the pipeline field.

{
   $lookup:
   {
      from: <collection to join>,
      let: { <var_1>: <expression>, …, <var_n>: <expression> },
      pipeline: [ <pipeline to execute on the foreign collection> ],  // Cannot include $out or $merge
      as: <output array field>
   }
}

Atlas Search 支持

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.

For example, when you Join Conditions and Subqueries on a Foreign Collection or run Correlated Subqueries Using Concise Syntax, you can specify $search or $searchMeta inside the pipeline as shown below:

[{
  "$lookup": {
    "from": <foreign 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": <foreign 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.

分片集合

Starting in MongoDB 5.1, you can specify sharded collections in the from parameter of $lookup stages.

当以分片集合为目标时，您无法在事务中使用 $lookup 阶段。

请参阅：基于插槽的查询执行引擎

注意

从版本 7.0.17 开始，对于补丁版本的 7.0，默认不再启用基于插槽的查询执行引擎。如果您希望查询使用基于插槽的查询执行引擎，请升级到版本 8.0，默认下处于启用状态。

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:

The $lookup operation executes a pipeline on a foreign collection. To see an example of this kind of operation, see Join Conditions and Subqueries on a Foreign Collection.
$lookup 的 localField 或 foreignField 指定数字成分。例如：{ localField: "restaurant.0.review" }。
管道中任何 $lookup 的 from 字段指定视图或分片集合。

有关更多信息，请参阅 $lookup 优化。

性能考虑因素

$lookup 性能取决于执行的操作类型。请参阅下表了解不同 $lookup 操作的性能注意事项。

`$lookup` 操作	性能考虑因素
涉及单个联接的等值匹配	`$lookup` 当外部集合在 `foreignField` 上包含索引时，使用单个联接执行等值匹配的操作通常具有较好的性能。重要提示：如果 `foreignField` 上的支持索引不存在，使用单个联接执行等值匹配的 `$lookup` 操作可能具有较差的性能。
关联子查询非	`$lookup` 在内部管道可以引用外部集合的索引时，包含不相关子查询的操作具有较好的性能。 MongoDB 只需要在缓存查询之前运行一次 `$lookup` 子查询，因为源和外部集合之间没有关系。子查询不基于源集合中的任何值。此行为可提高后续执行 `$lookup` 操作的性能。
关联子查询	`$lookup` 当外部集合包含针对 `foreignField` 的索引时，包含相关子查询的操作性能更佳。如果管道将大量文档传递给 `$lookup` 查询，以下策略可能会提高性能：减少 MongoDB 传递给 `$lookup` 查询的文档数量。例如，在 `$match` 阶段设定更严格的筛选器。重新考虑数据的模式，以确保它对于使用案例来说是最佳的。

有关一般性能策略，请参阅索引策略和查询优化。

示例

本页上的示例使用来自 sample_mflix 数据集的数据。有关如何将此数据集加载到您的自管理 MongoDB 部署中的详细信息，请参阅加载示例数据集。如果您对示例数据库进行了任何修改，您可能需要删除并重新创建数据库，才能运行此页面上的示例。

执行单一等值联接 `$lookup`

以下聚合操作首先将 movies集合筛选为 runtime 大于 1000 的电影，然后在 _id 和 movie_id 字段上与 comments集合连接：

db.movies.aggregate( [
   { $match: { runtime: { $gt: 1000 } } },
   {
      $lookup: {
         from: "comments",
         localField: "_id",
         foreignField: "movie_id",
         as: "movie_comments"
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         "movie_comments.name": 1,
         "movie_comments.text": 1,
         "movie_comments.date": 1
      }
   }
] )

[
  {
    title: 'Centennial',
    year: 1978,
    movie_comments: [
      {
        name: 'Ellaria Sand',
        text: 'Excepturi nam nam eum possimus aspernatur autem. Quis nulla optio praesentium ut distinctio explicabo.',
        date: ISODate('1995-08-18T03:01:50.000Z')
      }
    ]
  },
  { title: 'Baseball', year: 1994, movie_comments: [] }
]

该操作对应于如下伪 SQL 语句：

SELECT *, movie_comments
FROM movies
WHERE movie_comments IN (
   SELECT *
   FROM comments
   WHERE movie_id = movies._id
);

有关更多信息，请参阅等值匹配性能注意事项。

结合数组使用 `$lookup`

如果 localField 是数组，则可以在没有 $unwind 阶段的情况下将数组元素与标量 foreignField 进行匹配。

以下聚合操作将 movies集合与 users集合连接起来，将 movies 中的 cast数组字段与 users 中的标量 name字段进行匹配：

db.movies.aggregate( [
   {
      $match: {
         title: {
            $in: [ "Roger & Me", "The Sum of Us",
               "Centennial" ]
         }
      }
   },
   {
      $lookup: {
         from: "users",
         localField: "cast",
         foreignField: "name",
         as: "cast_users"
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         cast: 1,
         "cast_users.name": 1,
         "cast_users.email": 1
      }
   },
   { $sort: { year: 1 } }
] )

[
  {
    cast: [
      'Raymond Burr',
      'Barbara Carrera',
      'Richard Chamberlain',
      'Robert Conrad'
    ],
    title: 'Centennial',
    year: 1978,
    cast_users: []
  },
  {
    cast: [
      'Michael Moore',
      'Roger B. Smith',
      'Rhonda Britton',
      'Fred Ross'
    ],
    title: 'Roger & Me',
    year: 1989,
    cast_users: [ { name: 'Michael Moore', email: 'michael_moore@fakegmail.com' } ]
  },
  {
    cast: [
      'Jack Thompson',
      'Russell Crowe',
      'John Polson',
      'Deborah Kennedy'
    ],
    title: 'The Sum of Us',
    year: 1994,
    cast_users: [
      {
        name: 'Deborah Kennedy',
        email: 'deborah_kennedy@fakegmail.com'
      }
    ]
  }
]

将 `$lookup` 与 `$mergeObjects` 结合使用

$mergeObjects 操作符将多个文档合并成一个文档。

The following operation uses $lookup to join the movies collection with the comments collection, then uses $mergeObjects in $replaceRoot to merge the first comment document with the movie document:

db.movies.aggregate( [
   { $match: { runtime: { $gt: 1000 } } },
   {
      $lookup: {
         from: "comments",
         localField: "_id",
         foreignField: "movie_id",
         as: "movie_comments"
      }
   },
   {
      $replaceRoot: {
         newRoot: {
            $mergeObjects: [
               { $arrayElemAt: [ "$movie_comments", 0 ] },
               "$$ROOT"
            ]
         }
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         genres: 1,
         name: 1,
         email: 1,
         text: 1,
         date: 1
      }
   }
] )

[
  {
    name: 'Ellaria Sand',
    email: 'indira_varma@gameofthron.es',
    text: 'Excepturi nam nam eum possimus aspernatur autem. Quis nulla optio praesentium ut distinctio explicabo.',
    date: ISODate('1995-08-18T03:01:50.000Z'),
    genres: [ 'Action', 'Adventure', 'Drama' ],
    title: 'Centennial',
    year: 1978
  },
  {
    genres: [ 'Documentary', 'History', 'Sport' ],
    title: 'Baseball',
    year: 1994
  }
]

使用多个连接条件和关联子查询

管道可以在外部集合上执行并包含多个连接条件。$expr 操作符可以实现更复杂的连接条件，包括搭配和非等值匹配。

联接条件可以引用运行了 aggregate() 方法的本地集合中的字段，并引用外部集合中的字段。这样即可在两个集合之间执行一个关联子查询。

MongoDB 5.0 支持简洁关联子查询。

如下示例：

使用 _id 和 movie_id 字段连接 movies 和 comments 集合。
筛选评论以仅包含在电影发布年份之后发布的评论。

db.movies.aggregate( [
   {
      $match: {
         title: {
            $in: [ "Class Action", "Kafka", "Corpse Bride" ]
         }
      }
   },
   {
      $lookup: {
         from: "comments",
         localField: "_id",
         foreignField: "movie_id",
         let: { movie_year: "$year" },
         pipeline: [
            {
               $match: {
                  $expr: {
                     $gt: [
                        { $year: "$date" }, "$$movie_year"
                     ]
                  }
               }
            },
            { $project: { _id: 0, name: 1, date: 1 } }
         ],
         as: "post_release_comments"
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         post_release_comments: 1
      }
   }
] )

[
  {
    year: 1991,
    title: 'Class Action',
    post_release_comments: [
      { name: 'Khal Drogo', date: ISODate('2016-12-06T07:17:03.000Z') }
    ]
  },
  {
    year: 1991,
    title: 'Kafka',
    post_release_comments: [
      { name: 'Khal Drogo', date: ISODate('1998-05-10T03:10:20.000Z') }
    ]
  },
  { year: 2005, title: 'Corpse Bride', post_release_comments: [] }
]

该操作对应于如下伪 SQL 语句：

SELECT *, post_release_comments
FROM movies
WHERE post_release_comments IN (
   SELECT name, date
   FROM comments
   WHERE movie_id = movies._id
   AND YEAR(date) > movies.year
);

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:

索引只能用于字段和常量之间的比较，因此 let 操作数必须解析为常量。

示例，$a 和常量值之间的比较可以使用索引，但 $a 和 $b 之间的比较不能使用索引。
当 let 操作数解析为空值或缺失值时，索引不用于比较。
不使用多键、部分或稀疏索引。

例如，如果索引 { movie_id: 1 } 存在于 comments 集合上：

comments.movie_id 字段上的等值匹配使用索引。

提示

执行非关联子查询 `$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.

注意

以下操作将 users集合与 movies集合中运行时间大于 1000 分钟的电影连接起来：

db.users.aggregate( [
   {
      $match: {
         email: { $in: [
            "mark_addy@gameofthron.es",
            "lena_headey@gameofthron.es"
         ] }
      }
   },
   {
      $lookup: {
         from: "movies",
         pipeline: [
            { $match: { runtime: { $gt: 1000 } } },
            { $project: { _id: 0, title: 1, year: 1 } }
         ],
         as: "long_movies"
      }
   },
   {
      $project: {
         _id: 0, name: 1, email: 1, long_movies: 1
      }
   }
] )

[
  {
    name: 'Robert Baratheon',
    email: 'mark_addy@gameofthron.es',
    long_movies: [
      { title: 'Centennial', year: 1978 },
      { title: 'Baseball', year: 1994 }
    ]
  },
  {
    name: 'Cersei Lannister',
    email: 'lena_headey@gameofthron.es',
    long_movies: [
      { title: 'Centennial', year: 1978 },
      { title: 'Baseball', year: 1994 }
    ]
  }
]

该操作对应于如下伪 SQL 语句：

SELECT *, long_movies
FROM users
WHERE long_movies IN (
   SELECT title, year
   FROM movies
   WHERE runtime > 1000
);

For more information, see Uncorrelated Subquery Performance Considerations.

执行简洁关联子查询 `$lookup`

版本 5.0 中的新增功能。

Starting in MongoDB 5.0, an aggregation pipeline $lookup stage supports a concise correlated subquery syntax that improves joins between collections. 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.

如下示例：

Joins the movies and comments collections by matching the localField _id with the foreignField movie_id. The match is performed before the pipeline is run.
筛选评论，仅包含在电影发布年份之后发布的评论，可分别使用 $$movie_year 和 $date 进行访问。

db.movies.aggregate( [
   {
      $match: {
         title: { $in: [
            "I Don't Kiss",
            "Lucky Luke",
            "Mississippi Masala"
         ] }
      }
   },
   {
      $lookup: {
         from: "comments",
         localField: "_id",
         foreignField: "movie_id",
         let: { movie_year: "$year" },
         pipeline: [
            {
               $match: {
                  $expr: {
                     $gt: [
                        { $year: "$date" }, "$$movie_year"
                     ]
                  }
               }
            },
            { $project: { _id: 0, name: 1, date: 1 } }
         ],
         as: "post_release_comments"
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         post_release_comments: 1
      }
   }
] )

[
  {
    title: "I Don't Kiss",
    year: 1991,
    post_release_comments: [
      {
        name: 'Brandon Hardy',
        date: ISODate('2016-09-18T11:11:34.000Z')
      }
    ]
  },
  {
    title: 'Lucky Luke',
    year: 1991,
    post_release_comments: [
      {
        name: 'Kelsey Smith',
        date: ISODate('2010-01-13T17:55:01.000Z')
      }
    ]
  },
  {
    title: 'Mississippi Masala',
    year: 1991,
    post_release_comments: [
      {
        name: 'Phillip Collins',
        date: ISODate('2010-05-13T08:04:22.000Z')
      }
    ]
  }
]

此示例使用 5.0 之前的 MongoDB 版本中较旧的详细事务语法，并返回与上一个简洁示例相同的结果：

db.movies.aggregate( [
   {
      $match: {
         title: { $in: [
            "I Don't Kiss",
            "Lucky Luke",
            "Mississippi Masala"
         ] }
      }
   },
   {
      $lookup: {
         from: "comments",
         let: { movie_id: "$_id", movie_year: "$year" },
         pipeline: [
            {
               $match: {
                  $expr: {
                     $and: [
                        { $eq: [ "$movie_id", "$$movie_id" ] },
                        { $gt: [
                           { $year: "$date" }, "$$movie_year"
                        ] }
                     ]
                  }
               }
            },
            { $project: { _id: 0, name: 1, date: 1 } }
         ],
         as: "post_release_comments"
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         post_release_comments: 1
      }
   }
] )

[
  {
    title: "I Don't Kiss",
    year: 1991,
    post_release_comments: [
      {
        name: 'Brandon Hardy',
        date: ISODate('2016-09-18T11:11:34.000Z')
      }
    ]
  },
  {
    title: 'Lucky Luke',
    year: 1991,
    post_release_comments: [
      {
        name: 'Kelsey Smith',
        date: ISODate('2010-01-13T17:55:01.000Z')
      }
    ]
  },
  {
    title: 'Mississippi Masala',
    year: 1991,
    post_release_comments: [
      {
        name: 'Phillip Collins',
        date: ISODate('2010-05-13T08:04:22.000Z')
      }
    ]
  }
]

前面的示例对应于如下伪 SQL 语句：

SELECT *, post_release_comments
FROM movies
WHERE post_release_comments IN (
   SELECT *
   FROM comments
   WHERE comments.movie_id = movies._id
   AND YEAR(comments.date) > movies.year
);

For more information, see Correlated Subquery Performance Considerations.

本页上的C#示例使用Atlas示例数据集中的 sample_mflix数据库。要学习；了解如何创建免费的MongoDB Atlas 群集并加载示例数据集，请参阅MongoDB .NET/ C#驱动程序文档中的入门。

以下 Movie 类对 sample_mflix.movies 集合中的文档进行建模：

[BsonIgnoreExtraElements]
public class Movie
{
    [BsonId]
    public ObjectId Id { get; set; }
    [BsonElement("title")]
    public string Title { get; set; } = null!;
    [BsonElement("year")]
    public int? Year { get; set; }
    [BsonElement("runtime")]
    public int? Runtime { get; set; }
    [BsonElement("rated")]
    public string? Rated { get; set; }
    [BsonElement("metacritic")]
    public int Metacritic { get; set; }
    [BsonElement("plot")]
    public string? Plot { get; set; }
    [BsonElement("type")]
    public string? Type { get; set; }
    [BsonElement("cast")]
    public string[]? Cast { get; set; }
    [BsonElement("directors")]
    public string[]? Directors { get; set; }
    [BsonElement("writers")]
    public string[]? Writers { get; set; }
    [BsonElement("imdb")]
    public ImdbData? Imdb { get; set; }
}

以下 Comment 类对 sample_mflix.comments 集合中的文档进行建模：

[BsonIgnoreExtraElements]
public class Comment
{
    [BsonId]
    public ObjectId Id { get; set; }
    [BsonElement("movie_id")]
    public ObjectId MovieId { get; set; }
    [BsonElement("text")]
    public string Text { get; set; } = null!;
}

以下 LookupResult 类存储 $lookup 阶段的输出：

[BsonIgnoreExtraElements]
public class LookupResult
{
    [BsonId]
    public ObjectId Id { get; set; }
    [BsonElement("title")]
    public string Title { get; set; } = null!;
    public List<Comment> Comments { get; set; } = [];
}

要使用MongoDB .NET/C# 驱动程序将 $lookup 阶段添加到聚合管道，请对 PipelineDefinition 对象调用 UnionWith() 方法。

以下示例创建了一个管道阶段，用于在 movies 和 comments 集合之间执行左外连接。该代码将每个 Movie文档中的 Id字段连接到 Comment 文档中的 MovieId字段。每部电影的评论都存储在每个 LookupResult文档中名为 Comments 的字段中。

var commentCollection = _client
    .GetDatabase("sample_mflix")
    .GetCollection<Comment>("comments");
var pipeline = new EmptyPipelineDefinition<Movie>()
    .Lookup<Movie, Movie, Comment, LookupResult>(
        foreignCollection: commentCollection,
        localField: m => m.Id,
        foreignField: c => c.MovieId,
        @as: r => r.Comments);

{
  "_id" : ObjectId("..."),
  "title" : "A Corner in Wheat",
  "Comments" : [
    {
      "_id" : ObjectId("..."),
      "movie_id" : ObjectId("..."),
      "text" : "..."
    },
    "..."
  ]
}

本页上的 Node.js 示例使用 Atlas 示例数据集中的 sample_mflix数据库。要学习如何创建免费的MongoDB Atlas 集群并加载示例数据集，请参阅MongoDB Node.js驱动程序文档中的入门。

要使用MongoDB Node.js驱动程序将 $lookup 阶段添加到聚合管道，请在管道对象中使用 $lookup操作符。

以下示例创建了一个管道阶段，该阶段在 movies 和 comments 集合之间执行左外连接。该代码将每个 movie 文档中的 _id 字段连接到 comment 文档中的 movie_id 字段。comments 字段存储每个 movie 文档中每部电影的评论。然后，示例运行聚合管道：

const pipeline = [
  {
    $lookup: {
      from: "comments",
      localField: "_id",
      foreignField: "movie_id",
      as: "comments"
    }
  }
];
const cursor = collection.aggregate(pipeline);
return cursor;

$lookup（聚合阶段）

定义

兼容性

语法

涉及单个条件联接的等值匹配

外部集合上的联接条件和子查询

采用简洁语法的关联子查询

行为

视图和排序规则

限制

Atlas Search 支持

分片集合

请参阅：基于插槽的查询执行引擎

性能考虑因素

示例

执行单一等值联接 $lookup

结合数组使用 $lookup

将 $lookup 与 $mergeObjects 结合使用

使用多个连接条件和关联子查询

执行非关联子查询 $lookup

执行简洁关联子查询 $lookup

执行单一等值联接 `$lookup`

结合数组使用 `$lookup`

将 `$lookup` 与 `$mergeObjects` 结合使用

执行非关联子查询 `$lookup`

执行简洁关联子查询 `$lookup`