Advanced Aggregation
Multiple $lookup
Two joins in one pipeline, and why each lookup should see as few documents as possible.
Each $lookup is a separate join on whatever documents remain. Two lookups is fine. Two lookups on the whole collection is how a report times out. Cut the input first. Do not unwind both arrays unless you want a cartesian product.
db.users.aggregate([
{ $match: { name: "Rahul" } },
{ $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } },
{ $lookup: { from: "reviews", localField: "_id", foreignField: "userId", as: "reviews" } },
{
$project: {
_id: 0,
name: 1,
orderCount: { $size: "$orders" },
reviewCount: { $size: "$reviews" }
}
}
]){ name: "Rahul", orderCount: 4, reviewCount: 2 }Unwind orders and reviews and Rahul becomes 4 × 2 = 8 documents. $size (or a pipeline $lookup that $counts) when you only need a number. Order of lookups does not combine them; the second join does not see the first's foreign collection. $facet (next) is how you run independent pipelines on the same input — not a substitute for two lookups that both need the parent _id.
Interview question
What goes wrong with two $lookup stages on the same pipeline?
Each runs per remaining document, so an unfiltered collection pays twice. Unwinding both joined arrays multiplies rows — a cartesian product. Prefer $size when I only need counts, and always shrink the input before the first join.