20. Window Functions with $setWindowFields

$group usually reduces input documents to one result per group. $setWindowFields calculates over related documents while retaining individual records in the output.

Use partitionBy to define independent groups, sortBy to define order, and a window to define the records included in the calculation. Window functions support running totals, rankings, moving averages, and other analytics. Add a final $sort when the returned output order matters, and use a unique tie-breaker when timestamps can match.

Use deterministic ordering

Include a unique tie-breaker such as _id when the calculation needs a deterministic sequence.

Code Example
db.orders.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$customerId",
      sortBy: { createdAt: 1, _id: 1 },
      output: {
        runningOrderCount: {
          $sum: 1,
          window: { documents: ["unbounded", "current"] }
        }
      }
    }
  },
  { $sort: { customerId: 1, createdAt: 1, _id: 1 } }
]);
Key Takeaways
  • $group reduces documents to summaries.
  • $setWindowFields can retain individual records.
  • Partition and sort order define window semantics.
  • Sort final output explicitly when needed.
Knowledge Check

What is a key difference between $group and $setWindowFields?