Learning MongoDB - Advanced Aggregation: Lookups, Facets & Window Functions

#Introduction

In episode 9 you built the foundation of the aggregation pipeline. But real applications need more than just group and sum: combining data across collections, computing rankings and summaries in a single query, and creating price distribution histograms.

Episode 10 answers these with advanced stages: $lookup for joining collections, $facet for many parallel sub-pipelines, $bucket for histograms, and $setWindowFields for SQL-style window functions. Let's get started.

#$lookup: Joining Collections

#Basic Lookup

$lookup is the SQL LEFT JOIN analog: it takes a field from documents in the main collection, matches it against a field in another collection, and attaches the results as an array to the main document.

db.orders.aggregate([
  { $lookup: {
      from: "users",
      localField: "userId",
      foreignField: "_id",
      as: "user"
  } }
])

Read it as: from the users collection, match userId in the order document with _id in users, then store the result in the user field (always an array, even for a single document). Every order now carries the complete user data — without two separate queries. Because as is always an array, tidy it up with $unwind if you need a flat shape.

#Correlated Subquery Lookup

Basic lookup can only match with simple equality. For more complex conditions — like joining based on two fields or with additional filters — use the pipeline syntax in let + pipeline:

db.orders.aggregate([
  { $lookup: {
      from: "items",
      let: { orderId: "$_id" },
      pipeline: [
        { $match: {
            $expr: { $eq: ["$orderId", "$$orderId"] },
            qty: { $gte: 2 }
        } }
      ],
      as: "bulkItems"
  } }
])

The let syntax defines a local variable ($$orderId) used inside the sub-pipeline — giving you full control over the join conditions.

#$facet: Multi-Pipeline in One Query

$facet runs several sub-pipelines in parallel in a single pass over the data — the results are collected into one document. This is revolutionary for dashboard pages that need several different summaries from the same data, without repeated queries:

db.orders.aggregate([
  { $match: { status: "paid" } },
  { $facet: {
      "totalRevenue": [
        { $group: { _id: null, total: { $sum: "$total" } } }
      ],
      "byCategory": [
        { $group: { _id: "$category", count: { $sum: 1 } } },
        { $sort: { count: -1 } }
      ],
      "recentOrders": [
        { $sort: { createdAt: -1 } },
        { $limit: 5 },
        { $project: { _id: 1, total: 1, status: 1 } }
      ]
  } }
])

The result is a single document with three keys: totalRevenue, byCategory, and recentOrders. Imagine how many separate queries are saved — and the consistency is perfect because all facets read the same snapshot.

#$bucket and $bucketAuto

$bucket groups data into ranges (buckets) you define. Perfect for creating a price histogram:

db.products.aggregate([
  { $bucket: {
      groupBy: "$price",
      boundaries: [0, 100000, 250000, 500000, 1000000],
      default: "lainnya",
      output: {
        count: { $sum: 1 },
        totalValue: { $sum: "$price" }
      }
  } }
])

The boundaries [0, 100000, 250000, 500000, 1000000] form four buckets: 0–100k, 100k–250k, 250k–500k, 500k–1M. Products outside the range fall into the default bucket.

Meanwhile, $bucketAuto divides data into a desired number of buckets with automatically calculated boundaries for even distribution:

db.products.aggregate([
  { $bucketAuto: {
      groupBy: "$price",
      buckets: 5,
      output: {
        count: { $sum: 1 },
        average: { $avg: "$price" }
      }
  } }
])

MongoDB determines each bucket's boundaries itself so the distribution is even — useful when the natural range of the data isn't yet known.

#$setWindowFields: Window Functions

$setWindowFields brings SQL-style window function capabilities (ROW_NUMBER, SUM ... OVER, moving average) into MongoDB. It computes values based on a window — the frame of documents around the current document — without grouping all the data.

#Ranking with $rank and $denseRank

db.products.aggregate([
  { $setWindowFields: {
      partitionBy: "$category",
      sortBy: { soldCount: -1 },
      output: {
        rank: { $rank: {} },
        denseRank: { $denseRank: {} }
      }
  } },
  { $match: { rank: { $lte: 3 } } },
  { $project: { name: 1, category: 1, soldCount: 1, rank: 1 } }
])

partitionBy splits the data per category, sortBy determines the order, then $rank assigns a sequence number. The difference between $rank and $denseRank: $rank skips after tied values (1, 2, 2, 4), while $denseRank doesn't (1, 2, 2, 3). The result is the top three best-selling products per category.

#Running Total and Moving Average

Window functions can also compute cumulative sums and moving averages — the heart of time-series analysis:

db.sales.aggregate([
  { $sort: { date: 1 } },
  { $setWindowFields: {
      sortBy: { date: 1 },
      output: {
        runningTotal: {
          $sum: "$amount",
          window: { documents: ["unbounded", "current"] }
        },
        movingAvg7: {
          $avg: "$amount",
          window: { documents: [-6, 0] }
        }
      }
  } }
])

• runningTotal sums from the first document (unbounded) up to the current one (current); movingAvg7 averages the last 7 days to smooth out daily fluctuations.

Combining $facet and $setWindowFields produces enterprise-level reports in a single query: one facet for aggregate summaries, another for details with rankings and running totals. All facets share the same snapshot, so the results are guaranteed consistent.

One thing to watch out for: an unindexed $lookup is very expensive — it becomes a nested loop on the server. Make sure the foreignField has an index, and avoid giant $lookups on every request; for read-heavy workloads, consider the snapshot/denormalization approach from episode 7. Use $lookup as a complement, not the backbone of your design.

#Conclusion

In episode 10 you pushed aggregation to a professional level: basic $lookup and correlated subqueries to join collections in the style of a LEFT JOIN, $facet to run several parallel sub-pipelines in one query — like a complete dashboard in a single call, $bucket and $bucketAuto for histograms with manual or automatic boundaries, and $setWindowFields for window functions: ranking with $rank/$denseRank, running totals, and moving averages.

Key takeaways:

• $lookup joins collections; always make sure the foreignField is indexed.
• The $lookup pipeline syntax gives flexible join conditions.
• $facet combines many summaries in one consistent query.
• $bucket for manual histograms; $bucketAuto for automatic distribution.
• $setWindowFields provides rankings, running totals, and moving averages.

In the next episode, episode 11, we talk about finding data by keyword: Full-Text Search & Atlas Search. You'll create text indexes and use the $text query with relevance scoring, then get to know the Lucene-based MongoDB Atlas Search, which brings fuzzy search, autocomplete, and faceted search. See you in episode 11!