Join AtlasMart referenced data with indexed and correlated $lookup pipelines while measuring foreign-side work, fan-out, and tenant-safe join predicates.
$lookup Joins, Correlated Pipelines, Indexability, and When References Become Expensive
Batch heterogeneous writes safely, interpret partial success, compare ordered and unordered execution, and use modern cross-namespace bulk APIs without assuming all-or-nothing behavior.
Learning objectives
Explain $lookup as a left outer join that appends matching foreign documents as an array.
Build correlated lookup pipelines with let variables and $expr without confusing field-to-field expressions with indexable field-to-constant comparisons.
Measure foreign-side index support and recognize when references turn one request into expensive repeated joins.
Prevent tenant leakage by carrying tenant identity into the join predicate, not by filtering joined results later.
Choose denormalization or precomputation when repeated lookups dominate the workload.
This lesson pins MongoDB Community Server
8.3.8 with
mongodb/mongodb-community-server:8.3.8-ubuntu2204-slim
and mongosh 2.10.0. The server is a disposable
standalone published only on loopback
127.0.0.1:27057. Authentication and TLS are
disabled only for this isolated lab. Feature Compatibility
Version (FCV) and allowDiskUseByDefault are
observed but never changed. Default read/write concern and
primary read preference apply. Atlas, Search, KMS, Enterprise
Advanced, and paid services are not required. Runtime output
shown as “expected” is documentation-derived because this
generation environment has no Docker/mongod/mongosh runtime.
The exact optimizer tree, execution counters, spill fields, and stage-specific explain shape can vary with patch version, FCV, indexes, data distribution, and topology. The lesson therefore names the invariant to verify—matched documents, traversal set/depth, facet counts, window values, indexes used, disk-use evidence, and target collection state—instead of requiring byte-for-byte explain output.
1. AtlasMart problem: “can this line be fulfilled?”
Chapter 06 deliberately used references where inventory has an
independent lifecycle from orders. That makes the model honest,
but it means a checkout read may need information from two
collections. MongoDB $lookup performs a left outer
join: every input document continues through the pipeline and a
new array contains matching documents from the foreign
collection. A correlated lookup uses values from the
current input document to constrain the foreign pipeline.
| Term | Meaning in this lesson |
|---|---|
| $lookup | Aggregation stage that joins each input document to matching documents from another collection in the same database. |
| foreign collection |
The collection named by from; here,
inventory.
|
| let variable |
A value captured from the current input document and
exposed to the foreign pipeline as $$name.
|
| $expr |
Allows aggregation expressions—such as comparisons to a
let variable—inside a $match.
|
| indexable comparison | A comparison the foreign-side planner can satisfy using an index; current documentation restricts lookup-pipeline index use to specific field/constant comparisons and excludes multikey, partial, and sparse indexes for these comparisons. |
| fan-out | How many foreign documents one input document joins to; high fan-out increases memory, bytes, and downstream cardinality. |
2. Build a tenant-safe fixture and supporting index
docker rm -f atlasmart-mongo-ch09-l1 2>/dev/null || truedocker volume rm atlasmart-mongo-ch09-l1-data 2>/dev/null || truedocker run -d --name atlasmart-mongo-ch09-l1 \ -p 127.0.0.1:27057:27017 \ -v atlasmart-mongo-ch09-l1-data:/data/db \ mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimmongosh "mongodb://127.0.0.1:27057/atlasmart?directConnection=true" --quiet --eval \'printjson({server:db.version(),hello:db.hello().isWritablePrimary}); printjson(db.getSiblingDB("admin").runCommand({getParameter:1,featureCompatibilityVersion:1,allowDiskUseByDefault:1}))' const lines=db.order_lines_ch09_l1;const inv=db.inventory_ch09_l1;lines.drop(); inv.drop();lines.insertMany([ {_id:"l1",tenantId:"tenant-a",orderId:"o901",sku:"USB-C-1",requestedQty:2}, {_id:"l2",tenantId:"tenant-a",orderId:"o901",sku:"BOOK-1",requestedQty:3}, {_id:"l3",tenantId:"tenant-a",orderId:"o902",sku:"USB-C-1",requestedQty:8}, {_id:"l4",tenantId:"tenant-b",orderId:"o903",sku:"USB-C-1",requestedQty:1}]);inv.insertMany([ {_id:"i1",tenantId:"tenant-a",warehouseId:"w1",sku:"USB-C-1",availableQty:5}, {_id:"i2",tenantId:"tenant-a",warehouseId:"w2",sku:"USB-C-1",availableQty:12}, {_id:"i3",tenantId:"tenant-a",warehouseId:"w1",sku:"BOOK-1",availableQty:2}, {_id:"i4",tenantId:"tenant-a",warehouseId:"w2",sku:"BOOK-1",availableQty:7}, {_id:"i5",tenantId:"tenant-b",warehouseId:"w9",sku:"USB-C-1",availableQty:50}]);inv.createIndex({tenantId:1,sku:1,availableQty:1});printjson({lines:lines.countDocuments({}),inventory:inv.countDocuments({}),indexes:inv.getIndexes()});
The logical identity of stock is not just sku.
AtlasMart is multi-tenant, so the join predicate includes
tenantId. Joining only on SKU would expose
tenant-b inventory to tenant-a even though the final
application might later hide it.
3. Correlated pipeline: equality plus quantity threshold
The foreign pipeline compares each inventory document to the
current line’s tenant, SKU, and requested quantity. For each
outer document, the let values are constants for
that inner execution. MongoDB can use eligible indexes for
$eq/$lt/$lte/$gt/$gte comparisons when the
documented lookup-index rules are satisfied. Do not assume every
$expr is indexable: a foreign field compared to
another foreign field is different from a foreign field compared
to a constant-valued let operand.
const p=[ {$match:{tenantId:"tenant-a"}}, {$lookup:{ from:"inventory_ch09_l1", let:{t:"$tenantId",s:"$sku",need:"$requestedQty"}, pipeline:[ {$match:{$expr:{$and:[ {$eq:["$tenantId","$$t"]}, {$eq:["$sku","$$s"]}, {$gte:["$availableQty","$$need"]} ]}}}, {$project:{_id:0,warehouseId:1,availableQty:1}}, {$sort:{availableQty:-1,warehouseId:1}} ], as:"fulfillableBy" }}, {$project:{_id:0,orderId:1,sku:1,requestedQty:1,fulfillableBy:1}}];printjson(lines.aggregate(p).toArray());o901 / USB-C-1 / need 2 -> w2:12, w1:5o901 / BOOK-1 / need 3 -> w2:7o902 / USB-C-1 / need 8 -> w2:12No tenant-b warehouse appears in tenant-a results.4. Explain foreign-side work instead of assuming the index
Collection metadata proves an index exists; it does not prove
the lookup used it. Run
explain("executionStats") and inspect the
lookup-stage metrics and nested plan evidence. Exact field
placement varies, so the helper walks the explain document
looking for indexesUsed, keys examined, or
documents examined.
const e=lines.explain("executionStats").aggregate([ {$match:{tenantId:"tenant-a"}}, {$lookup:{from:"inventory_ch09_l1",let:{t:"$tenantId",s:"$sku",need:"$requestedQty"},pipeline:[ {$match:{$expr:{$and:[{$eq:["$tenantId","$$t"]},{$eq:["$sku","$$s"]},{$gte:["$availableQty","$$need"]}]}}} ],as:"fulfillableBy"}}]);function walk(x,path="root"){ if(!x||typeof x!=="object") return; if(x.indexesUsed||x.totalKeysExamined!==undefined||x.totalDocsExamined!==undefined) printjson({path,indexesUsed:x.indexesUsed,keys:x.totalKeysExamined,docs:x.totalDocsExamined}); for(const [k,v] of Object.entries(x)) walk(v,`${path}.${k}`);}walk(e);Evidence of the compound index and bounded key/document examination supports the claim that the foreign match is indexed for this fixture. It does not prove acceptable production latency under larger outer cardinality, different parameter distributions, cold cache, sharding, or concurrency.
5. Controlled failure: remove the foreign index
A simple equality lookup without a supporting foreign index is a classic “works in development” failure. The following deliberately drops the index only inside the disposable lab, captures explain evidence, and recreates it. Compare foreign documents examined and lookup metrics before and after rather than relying on wall-clock time from five rows.
inv.dropIndex({tenantId:1,sku:1,availableQty:1});print("foreign indexes after drop",inv.getIndexes().map(x=>x.name));printjson(lines.explain("executionStats").aggregate([ {$match:{tenantId:"tenant-a"}}, {$lookup:{from:"inventory_ch09_l1",localField:"sku",foreignField:"sku",as:"stock"}}]));inv.createIndex({tenantId:1,sku:1,availableQty:1});If thousands of outer documents each probe an unindexed or high-fan-out foreign collection, the pipeline can multiply CPU, storage reads, memory, and network work. Fixes include earlier selective filtering, a supporting foreign index, reducing the foreign result shape, caching/precomputing a stable subset, or revisiting Chapter 06’s embedding decision when the joined fact truly belongs to the aggregate.
6. Indexability boundaries that change design decisions
| Case | Practical consequence |
|---|---|
Simple localField/foreignField equality
|
Use a supporting index on the foreign field; without it, performance can degrade sharply. |
Correlated $expr comparison |
Current lookup rules allow index use for certain comparison operators when the foreign field is compared to a constant-valued operand. |
| let resolves to missing/empty | The documented comparison does not use an index for that case; sanitize or model missing join keys deliberately. |
| Foreign multikey/partial/sparse index for correlated comparison |
Current lookup rules do not use those index types for the
documented $expr comparison path.
|
| Huge outer input | Even an indexed foreign probe repeated many times can dominate latency; reduce outer cardinality before the join when semantics allow. |
| Large joined array | Project only needed foreign fields and bound fan-out; otherwise a later unwind/group amplifies the join cost. |
7. Verification, cleanup, and production judgment
Verification checklist
- Exactly four order-line and five inventory documents are seeded.
- The tenant-a lookup never returns tenant-b inventory.
-
The threshold join returns only warehouses whose
availableQtymeets each line’s request. - Explain is inspected for actual foreign index use rather than inferred from index existence.
- The controlled index drop occurs only in the disposable collection and the index is recreated.
Production judgment. $lookup is
appropriate when references are semantically correct and the
join is selective, indexed, bounded, and observable. It
guarantees neither snapshot-like business consistency across
independently changing collections nor lower latency than
embedding. On replicas or shards, topology and read preference
can change where read work occurs; starting with MongoDB 5.1, a
sharded foreign collection is supported, but distributed fan-out
still costs network and merge work. Treat tenant predicates as
authorization-adjacent invariants, record examined/returned
ratios and joined-array cardinality, test cold-cache and skewed
keys, and keep a schema rollback path if denormalization is
later introduced.
The next lesson moves from a one-hop relationship to recursive traversal, where bounding depth and tenant scope become even more important.
docker rm -f atlasmart-mongo-ch09-l1docker volume rm atlasmart-mongo-ch09-l1-dataCheck your understanding
- Why is joining only on SKU unsafe in AtlasMart?
- What does a let variable represent inside a correlated $lookup pipeline?
- Why is index existence insufficient evidence of lookup performance?
- What workload signal suggests a referenced relationship may need denormalization or precomputation?
- Does an indexed $lookup guarantee a consistent snapshot of two independently changing collections?
Review the answers
Because SKU is not the complete multi-tenant identity; another tenant can have the same SKU and must not be joined into tenant-a data.
A value captured from the current outer document and supplied to the foreign pipeline; for that inner execution it can act as a constant operand under the documented indexability rules.
The planner might choose another path, a let value may be missing, or the relevant comparison may not be indexable. Explain execution evidence is needed.
Large outer cardinality, repeated joins, high foreign fan-out, and a joined fact that is read with the aggregate far more often than it changes.
No. Join indexing affects access cost; cross-document consistency semantics still depend on the surrounding operation, topology, and data-change timing.
Authoritative references
- MongoDB 8.3 release notes — Current 8.3 behavior and version-sensitive aggregation changes; re-check before reproducing.
- MongoDB aggregation pipeline — Ordered-stage execution model used throughout the chapter.
- Aggregation pipeline limits — Memory, disk-spill, stage-count, and 16 MiB output-document constraints.
- mongosh release notes — mongosh version used for the chapter commands.
- $lookup stage — Join semantics, correlated subqueries, indexability limitations, sharded foreign collections, and performance considerations.
- $expr predicate — Expression comparisons and lookup-specific index limitations.
- Explain command — Execution-statistics evidence and explain-output caveats.