Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 70 ยท Aggregation pipeline

Window Functions and Advanced Aggregation

Learn MongoDB window functions with $setWindowFields: running totals, moving averages, ranking, plus $densify and $fill, compared with SQL windows.

$group collapses many documents into one. Sometimes you want the opposite: keep every document and add a value computed from its neighbors. A running balance on each transaction, a rank within a department, a seven-day moving average next to each day's sales. SQL calls these window functions, and MongoDB has had them since version 5.0 in the $setWindowFields stage.

This lesson explains the stage, its partitionBy, sortBy, output and window parts, and then two companions for time series with gaps, $densify and $fill. The shell code is real MongoDB and read-only. The TryIts run a JavaScript stand-in that implements running totals, moving averages and ranks over arrays of objects. If you know SQL windows (Window Functions, Window Frames), the ideas carry over almost one to one.

Group versus window

$group Many documents in, one per group out.
$setWindowFields Every document stays, with a new field.

Windows add information. They never remove documents.

1 / 2

The shape of $setWindowFields

Here is a running total of revenue per region, ordered by date:

JavaScript
db.sales.aggregate([
  { $setWindowFields: {
      partitionBy: "$region",
      sortBy: { date: 1 },
      output: {
        runningTotal: {
          $sum: "$amount",
          window: { documents: ["unbounded", "current"] }
        }
      }
  } }
])

Read it from the top. partitionBy splits the stream into independent groups (here, one per region); leave it out to treat all documents as one partition. sortBy orders documents inside each partition, and many window operators need it. In output, each key becomes a new field, and its value is an accumulator ($sum, $avg, $min, $max, $push and others) plus a window that says which documents to look at.

The window can be defined two ways:

  • documents: [from, to] counts documents relative to the current one. "unbounded" means the partition edge, "current" is the current document, and numbers are offsets such as -2 (two before) and 0. ["unbounded", "current"] gives a running total; [-2, 0] gives the current document and the two before it.
  • range: [from, to] works on the value of the sortBy field instead of positions, for example "all rows whose date is within seven days before this one" using a unit such as "day".

If you omit window, the accumulator sees the whole partition, which lets you attach a partition total to every document (useful for computing each row's share).

JS Running totals and a moving average

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice how the running total restarts for south: the partition is the boundary. The moving average on the first rows of each partition uses fewer than three values, because the window cannot reach before the partition starts. SQL behaves the same way with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

Ranking and positional operators

Some operators are window functions in the strict sense: they have no input field and no window, and they depend only on sortBy.

JavaScript
db.scores.aggregate([
  { $setWindowFields: {
      partitionBy: "$class",
      sortBy: { points: -1 },
      output: {
        rank:       { $rank: {} },         // ties share a rank, then a gap follows
        denseRank:  { $denseRank: {} },    // ties share a rank, no gaps
        position:   { $documentNumber: {} } // 1, 2, 3, ... no ties
      }
  } },
  { $match: { rank: { $lte: 3 } } }  // the top three per class
])

This is the MongoDB answer to "top N per group", which a plain $group makes awkward. The $match after the stage filters on the new field. Other useful operators include $shift (read a value from a neighboring document, for example the previous day's price), $derivative and $integral (rates and areas over a window), $expMovingAvg, and $first/$last over a window.

The second stand-in computes $rank, $denseRank and $documentNumber with ties, so you can see the difference.

JS rank, denseRank and documentNumber

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Raj and Mia tie on 85, so both are rank 2 and the next student is rank 4 with $rank, but dense rank 3 with $denseRank. $documentNumber always breaks ties arbitrarily (in the stand-in, by name; in MongoDB the order of tied documents is not guaranteed unless sortBy includes a tiebreaker, so add _id).

$densify and $fill

Time series data often has holes: a sensor misses a day, a shop has no sales on Sunday. Window math over data with holes quietly gives wrong answers, so MongoDB has two stages to repair it.

$densify (MongoDB 5.1 and later) inserts the missing documents so a field has no gaps:

JavaScript
{ $densify: {
    field: "date",
    partitionByFields: ["sensor"],
    range: { step: 1, unit: "day", bounds: "full" }
} }

The new documents have only the date and partition fields. $fill (5.3 and later) then fills in the empty values, either by carrying the last known value forward (locf, "last observation carried forward"), by linear interpolation, or with a constant:

JavaScript
{ $fill: {
    sortBy: { date: 1 },
    partitionByFields: ["sensor"],
    output: {
      temperature: { method: "locf" },
      alerts: { value: 0 }
    }
} }

Together they turn a ragged series into a complete one before you run a moving average. They are most at home in time-series workloads, see Project: Event Logging with Bucketing and TTL.

Windows versus SQL

The ideas map directly:

SQL MongoDB
PARTITION BY region partitionBy: "$region"
ORDER BY date sortBy: { date: 1 }
SUM(x) OVER (...) $sum with window in output
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW window: { documents: [-2, "current"] }
RANK(), DENSE_RANK(), ROW_NUMBER() $rank, $denseRank, $documentNumber
LAG(x) $shift with by: -1

One difference: MongoDB puts a window per output field, so each field can use a different window within the same stage, whereas SQL writes a WINDOW clause or repeats OVER. For heavy windowed reports the $setWindowFields stage works in memory per partition and sorts its input, so apply a $match first and consider an index that matches the partition and sort fields (Aggregation Performance and Explain).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does $setWindowFields differ from $group?

    A window computes values from neighboring documents but leaves the documents in place, while $group collapses each group into a single document.
  2. 2. What does window: { documents: ["unbounded", "current"] } produce with $sum?

    The window runs from the first document of the partition to the current one, so each row sees the total so far.
  3. 3. Scores 90, 85, 85, 70 are ranked in descending order. What are the $rank values?

    $rank gives tied documents the same rank and then skips ahead, so after two documents at rank 2 the next is rank 4. $denseRank would give 1, 2, 2, 3.
  4. 4. Which stage fills calendar gaps with new documents so a series has no missing days?

    $densify creates the missing documents. $fill then supplies values for their empty fields, for example by carrying the last value forward.

Summary

  • $setWindowFields (MongoDB 5.0+) adds computed fields while keeping every document; partitionBy, sortBy, output and window define the calculation.
  • window.documents uses positions (["unbounded", "current"] for running totals, [-2, 0] for a three-row average); window.range uses values of the sort field.
  • $rank, $denseRank and $documentNumber give top-N-per-group queries; $shift reads neighboring rows.
  • $densify (5.1+) inserts missing points and $fill (5.3+) fills their values with locf, linear or a constant.
  • The concepts map one to one onto SQL window functions.

Next, make pipelines fast with Aggregation Performance and Explain.