$setWindowFields

$setWindowFields

Window Functions with setWindowFields

$group destroys the rows it summarizes; window functions do not. $setWindowFields, added in MongoDB 5.0 1,815 , computes a value for each document from a window of its neighbors and attaches it to that document, so running totals, rankings and moving averages stop needing client-side code or a self-join. It takes partitionBy (like SQL's PARTITION BY), sortBy (the order inside each partition, mandatory for any order-sensitive operator), and output.

A running total, the previous row's value, and a rank

db.orders.aggregate([
  { $match: { customerId: 'c1' } },
  { $set: { total: { $sum: { $map: { input: '$items', as: 'i',
                             in: { $multiply: ['$$i.qty', '$$i.price'] } } } } } },
  { $setWindowFields: {
      partitionBy: '$customerId', sortBy: { placedAt: 1 },
      output: { running: { $sum: '$total', window: { documents: ['unbounded', 'current'] } },
                prev: { $shift: { output: '$total', by: -1 } },
                rank: { $rank: {} } } } },
  { $project: { total: 1, running: 1, prev: 1, rank: 1 } }
])

Output

[
  { _id: 'o-1001', total: 150, running: 150, prev: null, rank: 1 },
  { _id: 'o-1003', total: 300, running: 450, prev: 150, rank: 2 },
  { _id: 'o-1009', total: 655, running: 1105, prev: 300, rank: 3 }
]

The window document makes running cumulative: documents: ['unbounded', 'current'] means "from the first document of the partition to this one". Change it to [-1, 1] for a three-row moving average, or use range to measure bounds in the values of the sortBy field rather than in row counts — range: [-7, 0], unit: 'day' gives a true seven-day trailing window even when some days have no orders. With no window the operator sees the whole partition, which is how you compute each row's share of its group's total in one stage.

Most $group accumulators work here, plus operators that need an ordering: $rank, $denseRank and $documentNumber differ in how they treat ties; $derivative and $integral suit time series; $linearFill and $locf fill gaps left by missing measurements.

MENU