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?