Window Functions With $setWindowFields
Learners will compute running totals, rank, and moving averages over ordered partitions using the $setWindowFields stage.
What Are Window Functions?
Window functions compute values over a set of documents related to the current document—a 'window'—without collapsing them into a single group like $group does. They originated in SQL (SQL:2003) and were added to MongoDB in version 5.0 through the $setWindowFields pipeline stage. Common use cases include running totals, moving averages, rank, and cumulative metrics.
The $setWindowFields Stage Structure
The $setWindowFields stage has three main configuration keys: partitionBy defines how to divide documents into independent windows (like GROUP BY in SQL), sortBy orders documents within each partition, and output specifies the new fields to compute along with their window operator and window bounds.
db.dailySales.aggregate([
{
$setWindowFields: {
partitionBy: '$region', // separate window per region
sortBy: { saleDate: 1 }, // order by date within each region
output: {
runningTotal: {
$sum: '$amount',
window: { documents: ['unbounded', 'current'] }
}
}
}
}
])All lessons in this course
- $sum, $avg, $min, $max: Numeric Aggregation
- $push and $addToSet: Building Arrays in Groups
- $first, $last, and $top/$bottom Accumulators
- Window Functions With $setWindowFields