How do you filter or reshape the joined side of a $lookup using let and a sub-pipeline?
answer
- The equality form only expresses one condition
- You need a channel from the outer document
- Two dollar signs, not one
- Plain $match compares it as a literal string
- $expr is what makes the variable resolve
basics
~20 sUse the pipeline form: let binds fields of the input document to variables, and the sub-pipeline runs against the foreign collection with those variables available as $$name. Compare them inside a $match with $expr, then $project or $limit to trim what comes back.
solid answer
~40 sThe equality form can only express one equality and always returns whole documents. The pipeline form fixes both. You declare `let: { cid: "$customerId" }` to bind input-document values into variables, then write a `pipeline` array that runs against the `from` collection. Inside it, the variables are referenced as `$$cid`, and because they are aggregation expressions rather than literals, any `$match` that uses them must be wrapped in `$expr`. That unlocks correlated conditions the equality form cannot express: range joins, multi-field joins, and joins with extra filters. It also lets you `$project` away fields or `$sort` plus `$limit` inside the join to fetch only the newest few related documents per parent. From MongoDB 5.0 you can combine `localField`/`foreignField` with `let`/`pipeline`, letting the indexed equality do the heavy matching while the sub-pipeline adds filtering.
code
javascript · 13 linesdb.customers.aggregate([
{ $lookup: {
from: "orders",
let: { cid: "$_id" },
pipeline: [
{ $match: { $expr: { $eq: [ "$customerId", "$$cid" ] } } },
{ $sort: { createdAt: -1 } },
{ $limit: 3 },
{ $project: { total: 1, createdAt: 1 } }
],
as: "recentOrders"
} }
])go deeper
Know that $lookup has a second form taking let and pipeline, and that it exists because the four-field form can only join on one equality.
Be able to write one from memory: let binds outer values, $$name reads them, and $match must be wrapped in $expr for the variable to resolve rather than be compared as a literal.
Show that you think about cost — the sub-pipeline runs per input document, so its match needs index support, the left side should be filtered first, and projecting inside the join keeps documents small.
Frame it as a query-surface decision: how much joining logic belongs in the database versus the service, and what the operational ceiling is before a correlated sub-pipeline should become materialized or remodelled data.
## Why the equality form runs out `$lookup`'s equality form joins on exactly one field pair and returns whole foreign documents. Real joins often need more: match on two fields, match on a range, restrict the joined documents to the active ones, or keep only the three most recent per parent. The *pipeline form* covers all of that. ## Anatomy ```js { $lookup: { from: "products", let: { pid: "$productId", qty: "$quantity" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: [ "$_id", "$$pid" ] }, { $gte: [ "$stock", "$$qty" ] } ] } } }, { $project: { name: 1, price: 1 } } ], as: "product" } } ``` Three pieces matter: - **`let`** declares variables. Each value is an aggregation expression evaluated against the *input* document, so `"$productId"` reads that field from the left side. - **`pipeline`** is an ordinary aggregation pipeline whose input is the `from` collection. It sees no fields of the input document — the only channel is `let`. - **`$$name`** is how you read a `let` variable inside the sub-pipeline. Single `$` still means "a field of the foreign document". ## Why $expr is mandatory A plain `$match: { _id: "$$pid" }` does not do what you want: in query-language position, `"$$pid"` is compared as a *literal string*, not resolved as a variable. `$expr` switches `$match` into aggregation-expression evaluation, where `$$pid` resolves and `$eq` compares two expressions. Forgetting `$expr` is the single most common bug in pipeline-form lookups, and it fails silently — every array comes back empty. ## What it buys you **Non-equality and multi-field joins.** `$gte`, `$lt`, `$in`, and boolean combinations are all available, so "join the price row whose valid-from/valid-to window contains this order's date" becomes expressible. **Payload trimming.** A `$project` (or `$unset`) inside the sub-pipeline means only the fields you need cross into the joined array. On a wide foreign collection this is the difference between a pipeline that fits comfortably and one that pushes at the 16 MB document limit. **Top-N per parent.** `$sort` plus `$limit` inside the sub-pipeline gives "the five most recent events for each user" in a single stage. The equality form cannot do this at all — it would pull every event and force you to slice afterwards. **Pre-aggregation.** The sub-pipeline can `$group` or `$count`, so the joined array can be a single summary document rather than a list. "How many open tickets does each customer have" costs one small array element per customer instead of the tickets themselves. ## Combining both forms From MongoDB 5.0 onward, `localField` and `foreignField` may be specified *together with* `let` and `pipeline`. This concise correlated form is usually the best of both: the equality condition is expressed in the form the engine can drive from an index on the foreign field, and the sub-pipeline handles the extra filtering and projection. On older versions you had to choose one shape or the other, and expressing the equality via `$expr` was the only option in the pipeline form. ## Indexing and cost The sub-pipeline runs once per input document, so its `$match` must be index-supported or the join degrades into a scan of the foreign collection per document. An `$expr` equality on an indexed field can use that index, but complex expression predicates often cannot. If you cannot get the equality into `localField`/`foreignField`, at least make sure the leading condition of the sub-pipeline's `$match` is a simple, indexed equality. As with any join, put a `$match` on the left side *before* the `$lookup` so the sub-pipeline runs fewer times. ## Restrictions worth knowing The sub-pipeline is a real pipeline, but not an unrestricted one: output stages like `$out` and `$merge` have no meaning inside a join and are not allowed. And the variables in `let` are read-only snapshots of the input document — there is no way to write back into the left side from inside the sub-pipeline.
- Why does { $match: { _id: "$$pid" } } inside a $lookup sub-pipeline return nothing?Because in query-language position `"$$pid"` is treated as a literal string value, not resolved as a variable — MongoDB looks for documents whose `_id` literally equals that text. Wrapping it as `{ $match: { $expr: { $eq: [ "$_id", "$$pid" ] } } }` switches the stage to aggregation-expression evaluation, where the variable resolves and the comparison works. The failure is silent: every joined array simply comes back empty.
- How would you return only each customer's three most recent orders with a single $lookup?Use the pipeline form with `let: { cid: "$_id" }` and a sub-pipeline of `$match` on `$expr` equality, then `$sort: { createdAt: -1 }`, then `$limit: 3`, then a `$project` for the fields you need. The `as` array then holds at most three trimmed documents per customer. The equality form cannot express this — it would fetch every order and require slicing afterwards.
- When should you still prefer the equality form over the pipeline form?When the join really is one indexed equality and you want the whole foreign document. The equality form is shorter, harder to get wrong, and its match is trivially index-driven. Reach for the pipeline form when you need extra predicates, non-equality conditions, projection to shrink the payload, or per-parent sorting and limiting.
saying these in an interview costs you the question
- Writes $match with a variable but omits $expr
- Uses single $ to reference a let variable
- Thinks the sub-pipeline can read outer document fields directly
- Assumes any $expr predicate still uses an index
- Believes the sub-pipeline runs once for the whole collection