Aggregation: $lookup and Advanced Stages

Introduction

When data lives in separate collections, find alone cannot join them—you either query twice in application code or use $lookup in the aggregation pipeline. This chapter joins users, posts, and comments, then introduces $facet, $bucket, $merge, and $out for multi-report and materialized views.

Prerequisites

Seed Referenced Collections

$lookup — Left Outer Join

Join posts with users on authorId:

Code explanation:

  • from — other collection name (same database)
  • as — array of matched docs (0 or 1 for FK-style)
  • $unwind — flatten author array to object fields

SQL analogy: LEFT JOIN users ON posts.author_id = users._id.

Nested $lookup — Posts with Comments

For large comment threads, paginate comments in a separate query instead of loading all in one aggregation.

$lookup with Pipeline (Correlated Subquery)

MongoDB 3.6+ pipeline form filters joined collection:

Implements subset pattern — Common Modeling Patterns.

$facet — Multiple Pipelines in One Pass

Dashboard stats without three round trips:

Result shape:

javascript
{
  byCustomer: [ ... ],
  overall: [ { revenue: 80, count: 2 } ],
  byDay: [ ... ]
}

$bucket and $bucketAuto

Histogram-style grouping:

$bucketAuto lets MongoDB choose boundaries from sample size.

$out and $merge — Write Results

Materialize aggregation output to a collection:

javascript
// Replace collection contents (permissions required)
db.orders.aggregate([
  { $match: { status: "paid" } },
  {
    $group: {
      _id: "$customer",
      revenue: { $sum: { $toDouble: "$amount" } }
    }
  },
  { $out: "customer_revenue" }
])

$merge upserts into existing collection (MongoDB 4.2+):

Use for nightly rollup jobs feeding dashboards — Event analytics project.

$replaceRoot and $addFields

Promote nested document to root:

$addFields adds computed fields without dropping others (alias $set stage).

Performance Notes

TipReason
$match firstLess data in pipeline
Index foreignFieldFaster $lookup
Limit before $lookupFewer join rows
Avoid $lookup on huge collections without filterMemory and time

Post-Chapter Checklist

  • $lookup joined posts → users
  • Pipeline $lookup with $limit on comments
  • $facet returned multiple report sections
  • You know $out vs $merge use cases

FAQ

$lookup vs SQL JOIN?

Similar left outer join. MongoDB has no inner join stage—filter with $match after lookup.

Cross-database $lookup?

Not in same aggregation—use $lookup only within one database; federated queries need app layer or $unionWith (same DB).

$graphLookup?

Recursive join for trees (categories, org chart)—advanced; not covered here.

Sharded $lookup?

Both collections must shard on compatible keys or performance suffers — chapter 15.

Still use two queries?

For simple post + author, two findOne calls may be clearer than aggregation—profile both.