Advanced Aggregation
Handling Missing Relationships
Left joins return empty arrays. Preserve, drop, or default — and what that does to totals.
A missing relationship is not an error. $lookup still emits the parent, with []. What you do next decides whether this is a left join, an inner join, or an anti-join (parents that matched nothing).
Keep or count
- Leave the array — $size is 0
- Users with no orders still listed
- Pune report includes new signups
Drop or invert
- $unwind without preserve — inner join
- $match { orders: { $size: 0 } } — anti-join
- Re-engagement: never ordered
db.users.aggregate([
{ $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } },
{ $match: { orders: { $size: 0 } } },
{ $project: { _id: 0, name: 1, email: 1, city: 1 } }
]){ orders: [] } is the same match. $unwind: { path: "$orders", preserveNullAndEmptyArrays: true } keeps the user and sets orders to null — then you can $set a default in the expressions module. Totals: $sum: "$amount" after a lookup without unwind is wrong (amount is not on the user). $sum over the array is $sum: "$orders.amount" in a $group/$project expression — or unwind, then sum, and know you multiplied rows.
Interview question
How do you find documents with no related rows after a $lookup?
Match on the joined array being empty: { orders: { $size: 0 } } or { orders: [] }. That is the anti-join. Unwind without preserveNullAndEmptyArrays does the opposite — it drops those documents.
Practice