MongoDB / Intermediate: aggregation and application contracts / Lesson 8
$lookup, $unwind and report cardinality
Join document collections without losing optional parents or inflating totals. Explain matched arrays, array expansion and bounded lookup design.
On this page
Lesson goals
- Predict lookup arrays and unwind output counts.
- Choose existence, detail or preaggregated lookup results.
A join stage creates a matched array
A simple $lookup adds an array of matching foreign documents to each local document. It is a left-outer-style operation, not a guarantee of exactly one match. If the foreign key is not unique, several matches can appear. Missing fields and null matching have documented behavior that should be tested when keys are optional. Treat the output array as a change in shape, not an ordinary scalar customer object. A model that repeatedly joins unbounded children may deserve reconsidered document boundaries or summaries.
State a concrete foreign-key fixture
Assume customers contains {_id:7,name:"Lina"} and booking 101 contains customerId 7. The lookup attaches a one-element customer array; an unmatched booking receives an empty array. This pipeline projects only the booking identifier and matched customer names, making the array shape visible. It needs the stated synthetic collections and was not executed. Use an index supporting the foreign lookup key for a representative workload, but do not claim a specific execution strategy merely because this tiny fixture has one match.
db.getSiblingDB('quay_lab').bookings.aggregate([
{ $match: { _id: 101 } },
{ $lookup: { from: 'customers', localField: 'customerId', foreignField: '_id', as: 'customer' } },
{ $project: { _id: 1, 'customer.name': 1 } }
]);{ _id: 101, customer: [ { name: "Lina" } ] }The result is a matched array. It is not flattened automatically.
Expansion changes the report grain
$unwind emits a document for each array element, repeating surrounding parent facts. By default, missing, null and empty-array inputs are not preserved as ordinary detail rows; preserveNullAndEmptyArrays can keep parents when the report requires them. A booking with two lines becomes two output documents. If a later lookup adds three payments and unwinds them too, six combinations can result. Parent-level totals then repeat, so sum only after defining the measureโs correct grain and any independent preaggregation.
Keep an empty parent intentionally
The literal fixture has booking 1 with two line quantities and booking 2 with an empty array. With preservation enabled, the expected output contains three documents: two expanded line records for booking 1 and one preserved booking 2 without a line quantity. Counting every output document would therefore count one row for the empty booking, not zero child elements. This distinction mirrors the general problem of counting preserved outer rows and should be explicit in report validation.
db.getSiblingDB('quay_lab').aggregate([
{ $documents: [ { _id: 1, lines: [{ qty: 2 }, { qty: 3 }] }, { _id: 2, lines: [] } ] },
{ $unwind: { path: '$lines', preserveNullAndEmptyArrays: true } },
{ $project: { _id: 1, qty: '$lines.qty' } },
{ $sort: { _id: 1, qty: 1 } }
]);{ _id: 1, qty: 2 }
{ _id: 1, qty: 3 }
{ _id: 2 }The last document preserves the parent without inventing a child quantity of zero.
Bound foreign work and preserve access scope
A pipeline form of $lookup can express more precise matching and projections, with let variables referenced through supported expression rules. Index eligibility depends on the actual comparison and version, so inspect explain rather than assuming every expression is indexed. Include tenant scope on both sides when the data model requires it. MongoDB 5.1 added support for a sharded from collection in $lookup; that availability does not make a cross-shard join inexpensive. Choose existence checks or foreign summaries when full detail arrays are unnecessary.
Common errors and fixes
Totals multiply after two unwinds
- Why it happens
- Independent child arrays formed a product of combinations.
- How to fix it
- Aggregate each child measure to the intended parent grain first.
- Prevent it next time
- Predict output counts with two-by-three fixtures.
Parents without children disappear
- Why it happens
- Default unwind behavior discarded empty or missing arrays.
- How to fix it
- Use preservation only when the report requires those parents.
- Prevent it next time
- Test empty, missing, null and populated arrays separately.
An application treats lookup output as one object
- Why it happens
- The matched array contract was ignored.
- How to fix it
- Handle zero, one and multiple matches deliberately.
- Prevent it next time
- Enforce foreign uniqueness where the model actually requires one match.
Check your understanding
1. A booking has two lines and three payments. What output count can unwinding both produce, and why are sums dangerous?
Show worked solution
It can produce six combinations. Every line is repeated three times and every payment twice, so direct sums inflate both measures. Aggregate line and payment amounts independently to one summary per booking before combining them, or design a report that intentionally keeps detail without summing repeated parent facts. DISTINCT on amounts is not a safe correction because equal amounts can be legitimate separate events.
2. A left-style lookup finds no customer, then the pipeline unwinds customer without options. Is the booking preserved?
Show worked solution
The lookup first creates an empty array, but default unwind drops that document. If the report needs unmatched bookings, enable preservation and define how missing customer fields are presented. If customers are mandatory, investigate integrity rather than merely hiding the absence. The correct choice follows the report and data contract, not the name left-style alone.
Key takeaways
- Lookup arrays and unwind multiplicity determine report meaning.
- Preservation and preaggregation are deliberate correctness choices.