AWS documentdb: DocumentDB $lookup correlated subquery docs (let/pipeline)
Summary
Adds documentation for the $lookup aggregation stage's correlated subquery support (new in 8.0.2), including the `let` and `pipeline` parameters, concise localField/foreignField syntax, supported/unsupported pipeline stages, restrictions, and Node.js/Python code examples.
Security assessment
The change is purely functional feature documentation for the $lookup aggregation stage (correlated subqueries via let/pipeline, supported stages, and code samples). It contains no mention of vulnerabilities, CVEs, authentication, encryption, IAM, or hardening guidance; the only constraint noted ($out/$merge exclusion) is a functional limitation, not a security control.
Evidence
+ * `pipeline`: Optional if `localField` and `foreignField` are specified. An aggregation pipeline to run on the `from` collection. The pipeline returns documents from the `from` collection. To return all documents, specify an empty pipeline `[]`. The pipeline cannot include the `$out` or `$merge` stages. See Join conditions and correlated subqueries (MongoDB Shell).
Diff
diff --git a/documentdb/latest/devguide/lookup.md b/documentdb/latest/devguide/lookup.md index e83b747c0..e02cd8c09 100644 --- a//documentdb/latest/devguide/lookup.md +++ b//documentdb/latest/devguide/lookup.md @@ -7 +7 @@ -Example (MongoDB Shell)Code examples +Example (MongoDB Shell)Join conditions and correlated subqueries (MongoDB Shell)Correlated subqueries using concise syntax (MongoDB Shell)Supported stages in correlated subqueriesCode examplesCorrelated subquery code examples @@ -22,0 +23,4 @@ The `$lookup` aggregation stage in Amazon DocumentDB allows you to perform a lef + * `pipeline`: Optional if `localField` and `foreignField` are specified. An aggregation pipeline to run on the `from` collection. The pipeline returns documents from the `from` collection. To return all documents, specify an empty pipeline `[]`. The pipeline cannot include the `$out` or `$merge` stages. See Join conditions and correlated subqueries (MongoDB Shell). + + * `let`: Optional. A document that defines variables to use in the `pipeline` stages. Use the variable expressions to access the fields from the input documents that are joined to the `pipeline`. To reference a variable in the pipeline stages, use the `"$$<variable>"` syntax. See Join conditions and correlated subqueries (MongoDB Shell). + @@ -78,0 +83,173 @@ The following example demonstrates a simple `$lookup` operation that joins data +## Join conditions and correlated subqueries (MongoDB Shell) + +New from version 8.0.2. + +In addition to a single equality match, `$lookup` in Amazon DocumentDB can run an aggregation pipeline on the joined collection. This supports uncorrelated subqueries, which run the same subquery for every input document, and correlated subqueries, which use variables defined from each input document's fields to return results correlated to that document. + +**Syntax** + +To run a correlated subquery, define variables from the input document's fields with `let`, and reference them in the `pipeline` stages: + + + { + $lookup: { + from: <collection to join>, + let: { <var_1>: <expression>, ..., <var_n>: <expression> }, + pipeline: [ <pipeline to run on the joined collection> ], + as: <output array field> + } + } + +**Parameters** + + * `let`: A document that defines variables from the fields of the input documents. To reference a variable in the pipeline stages, use the `"$$<variable>"` syntax. The variables can be accessed by the stages in the `pipeline`, including additional `$lookup` stages nested in the `pipeline`. A `$match` stage requires the use of the `$expr` operator to access the variables. Omit `let` to run an uncorrelated subquery. + + * `pipeline`: The aggregation pipeline to run on the `from` collection. The pipeline cannot access fields of the input documents directly; define variables with `let` and reference the variables instead. To return all documents, specify an empty pipeline `[]`. + + + + +The `$eq`, `$gt`, `$gte`, `$lt`, and `$lte` comparison operators placed in an `$expr` operator inside a `$match` stage can use an index on the `from` collection when they compare a field of the joined collection with a `let` variable or a constant. + +**Create sample documents** + + + db.customers.insertMany([ + { _id: 1, name: "Alice", min_total: 60 }, + { _id: 2, name: "Bob", min_total: 50 }, + { _id: 3, name: "Charlie", min_total: 20 } + ]); + + db.orders.insertMany([ + { _id: 1, customer_id: 1, total: 50 }, + { _id: 2, customer_id: 1, total: 100 }, + { _id: 3, customer_id: 2, total: 75 } + ]); + +**Query example** + +The following example joins each customer with their orders, and keeps only the orders whose `total` is greater than or equal to that customer's own `min_total` value. The join condition and the per-customer filter both reference `let` variables inside `$expr`: + + + db.customers.aggregate([ + { + $lookup: { + from: "orders", + let: { customer_id: "$_id", min_total: "$min_total" }, + pipeline: [ + { + $match: { + $expr: { + $and: [ + { $eq: [ "$customer_id", "$$customer_id" ] }, + { $gte: [ "$total", "$$min_total" ] } + ] + } + } + }, + { $project: { _id: 0, total: 1 } } + ], + as: "qualifying_orders" + } + } + ]); + +**Output** + + + [ + { _id: 1, name: 'Alice', min_total: 60, qualifying_orders: [ { total: 100 } ] }, + { _id: 2, name: 'Bob', min_total: 50, qualifying_orders: [ { total: 75 } ] }, + { _id: 3, name: 'Charlie', min_total: 20, qualifying_orders: [] } + ] + +For each customer, the subquery is evaluated with that customer's `_id` and `min_total` values, so each document receives its own correlated result. + +## Correlated subqueries using concise syntax (MongoDB Shell) + +New from version 8.0.2. + +Amazon DocumentDB also supports the concise correlated subquery syntax, which combines the equality match on `localField` and `foreignField` with a `pipeline`. The concise syntax removes the requirement to express the equality match inside an `$expr` operator in a `$match` 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> + } + } + +**Query example** + +The following example returns the same results as the previous correlated subquery example. The equality match between `_id` and `customer_id` is expressed with `localField` and `foreignField` instead of an `$expr` condition: + + + db.customers.aggregate([ + { + $lookup: { + from: "orders", + localField: "_id", + foreignField: "customer_id", + let: { min_total: "$min_total" }, + pipeline: [ + { $match: { $expr: { $gte: [ "$total", "$$min_total" ] } } }, + { $project: { _id: 0, total: 1 } } + ], + as: "qualifying_orders" + } + } + ]); + +**Output** + + + [ + { _id: 1, name: 'Alice', min_total: 60, qualifying_orders: [ { total: 100 } ] }, + { _id: 2, name: 'Bob', min_total: 50, qualifying_orders: [ { total: 75 } ] }, + { _id: 3, name: 'Charlie', min_total: 20, qualifying_orders: [] } + ] + +## Supported stages in correlated subqueries + +When a `$lookup` pipeline uses `let`, the following stages can reference the `"$$<variable>"` variables: + + * `$match` (using the `$expr` operator) + + * `$project` + + * `$addFields` + + * `$set` + + * `$group` + + * `$bucket` + + * `$bucketAuto` + + * `$sortByCount` + + * `$lookup` (nested) + + * `$graphLookup` + + + + +Stages that do not evaluate variable expressions, such as `$sort`, `$skip`, `$limit`, `$count`, `$sample`, `$unwind`, and `$unset`, can also be used in the pipeline but cannot reference the variables. + +The following stages are not supported in a `$lookup` pipeline that uses `let`: `$facet`, `$redact`, `$replaceRoot`, `$replaceWith`, `$geoNear`, `$setWindowFields`, `$fill`, and `$match` with `$text`. + +**Restrictions** + + * The `pipeline` cannot include the `$out` or `$merge` stages. + + + + @@ -145,0 +323,101 @@ Python +## Correlated subquery code examples + +To view a code example for using the `$lookup` stage with a correlated subquery (using `let` and `pipeline`), choose the tab for the language that you want to use: + +Node.js + + + + const { MongoClient } = require('mongodb'); + + async function example() { + const client = await MongoClient.connect('mongodb://<username>:<password>@<cluster-endpoint>:27017/?tls=true&tlsCAFile=global-bundle.pem&replicaSet=rs0&readPreference=secondaryPreferred&retryWrites=false'); +