Docs Home → Develop Applications → MongoDB Manual
$setWindowFields (aggregation)![](/docs/v5.2/assets/link.svg)
Definition![](/docs/v5.2/assets/link.svg)
New in version 5.0.
Performs operations on a specified span of documents in a collection, known as a window, and returns the results based on the chosen window operator.
For example, you can use the $setWindowFields
stage to
output the:
Difference in sales between two documents in a collection.
Sales rankings.
Cumulative sales totals.
Analysis of complex time series information without exporting the data to an external database.
Syntax![](/docs/v5.2/assets/link.svg)
The $setWindowFields
stage syntax:
{ $setWindowFields: { partitionBy: <expression>, sortBy: { <sort field 1>: <sort order>, <sort field 2>: <sort order>, ..., <sort field n>: <sort order> }, output: { <output field 1>: { <window operator>: <window operator parameters>, window: { documents: [ <lower boundary>, <upper boundary> ], range: [ <lower boundary>, <upper boundary> ], unit: <time unit> } }, <output field 2>: { ... }, ... <output field n>: { ... } } } }
The $setWindowFields
stage takes a document with these
fields:
Field | Necessity | Description |
---|---|---|
Optional | Specifies an expression to group
the documents. In the | |
Required for some operators (see
Restrictions) | Specifies the field(s) to sort the documents by in the partition.
Uses the same syntax as the | |
Required | Specifies the field(s) to append to the documents in the output
returned by the A field can contain dots to
specify embedded document fields and array fields. The semantics
for the embedded document dotted notation in the
| |
Optional | ||
Optional | A window where the lower and upper boundaries are specified relative to the position of the current document read from the collection. The window boundaries are specified using a two element array containing a lower and upper limit string or integer. Use:
| |
Optional | A window where the lower and upper boundaries are defined using a range of values based on the sortBy field in the current document. The window boundaries are specified using a two element array containing a lower and upper limit string or number. Use:
| |
Optional |
Tip
See also:
Behavior![](/docs/v5.2/assets/link.svg)
The $setWindowFields
stage appends new fields to existing
documents. You can include one or more $setWindowFields
stages in an aggregation operation.
Window Operators![](/docs/v5.2/assets/link.svg)
These operators can be used with the $setWindowFields
stage:
Accumulator operators:
$addToSet
,$avg
,$bottom
,$bottomN
,$count
,$covariancePop
,$covarianceSamp
,$derivative
,$expMovingAvg
,$firstN
,$integral
,$lastN
,$max
,$maxN
,$min
,$minN
,$push
,$stdDevSamp
,$stdDevPop
,$sum
,$top
,$topN
.
Gap filling operators:
$locf
.
Rank operators:
$denseRank
,$documentNumber
, and$rank
.
Restrictions![](/docs/v5.2/assets/link.svg)
Restrictions for the $setWindowFields
stage:
Starting in MongoDB 5.1 (and 5.0.4), the
$setWindowFields
stage cannot be used:Within transactions.
With
"snapshot"
read concern.
sortBy is required for:
Time range windows require all sortBy values to be dates.
Range and time range windows can only contain one sortBy field and the sort must be ascending.
You cannot specify both a documents window and a range window.
These operators use an implicit window and return an error if you specify a window option:
For range windows, only numbers in the specified range are included in the window. Missing, undefined, and
null
values are excluded.For time range windows:
Only date and time types are included in the window.
Numeric boundary values must be integers. For example, you can use 2 hours as a boundary but you cannot use 1.5 hours.
For empty windows or windows with incompatible values (for example, using
$sum
on strings), the returned value depends on the operator:
Examples![](/docs/v5.2/assets/link.svg)
Create a cakeSales
collection that contains cake sales in the states
of California (CA
) and Washington (WA
):
db.cakeSales.insertMany( [ { _id: 0, type: "chocolate", orderDate: new Date("2020-05-18T14:10:30Z"), state: "CA", price: 13, quantity: 120 }, { _id: 1, type: "chocolate", orderDate: new Date("2021-03-20T11:30:05Z"), state: "WA", price: 14, quantity: 140 }, { _id: 2, type: "vanilla", orderDate: new Date("2021-01-11T06:31:15Z"), state: "CA", price: 12, quantity: 145 }, { _id: 3, type: "vanilla", orderDate: new Date("2020-02-08T13:13:23Z"), state: "WA", price: 13, quantity: 104 }, { _id: 4, type: "strawberry", orderDate: new Date("2019-05-18T16:09:01Z"), state: "CA", price: 41, quantity: 162 }, { _id: 5, type: "strawberry", orderDate: new Date("2019-01-08T06:12:03Z"), state: "WA", price: 43, quantity: 134 } ] )
The following examples use the cakeSales
collection.
Documents Window Examples![](/docs/v5.2/assets/link.svg)
Use Documents Window to Obtain Cumulative Quantity for Each State![](/docs/v5.2/assets/link.svg)
This example uses a documents window
in $setWindowFields
to output the cumulative cake sales
quantity
for each state
:
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: "$state", sortBy: { orderDate: 1 }, output: { cumulativeQuantityForState: { $sum: "$quantity", window: { documents: [ "unbounded", "current" ] } } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { orderDate: 1 }
sorts the documents in each partition byorderDate
in ascending order (1
), so the earliestorderDate
is first.
output
:Sets the
cumulativeQuantityForState
field to the cumulativequantity
for eachstate
, which increases by successive additions to the previous value in the partition.Calculates the cumulative
quantity
using the$sum
operator run in a documents window.The window contains documents between an
unbounded
lower limit and thecurrent
document. This means$sum
returns the cumulativequantity
for the documents between the beginning of the partition and the current document.
In this example output, the cumulative quantity
for CA
and
WA
is shown in the cumulativeQuantityForState
field:
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "cumulativeQuantityForState" : 162 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "cumulativeQuantityForState" : 282 } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "cumulativeQuantityForState" : 427 } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "cumulativeQuantityForState" : 134 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "cumulativeQuantityForState" : 238 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "cumulativeQuantityForState" : 378 }
Use Documents Window to Obtain Cumulative Quantity for Each Year![](/docs/v5.2/assets/link.svg)
This example uses a documents window
in $setWindowFields
to output the cumulative cake sales
quantity
for each $year
in orderDate
:
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: { $year: "$orderDate" }, sortBy: { orderDate: 1 }, output: { cumulativeQuantityForYear: { $sum: "$quantity", window: { documents: [ "unbounded", "current" ] } } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection by$year
inorderDate
. There are are partitions for2019
,2020
, and2021
.sortBy: { orderDate: 1 }
sorts the documents in each partition byorderDate
in ascending order (1
), so the earliestorderDate
is first.output
:Sets the
cumulativeQuantityForYear
field to the cumulativequantity
for each year, which increases by successive additions to the previous value in the partition.Calculates the cumulative
quantity
using the$sum
operator run in a documents window.The window contains documents between an
unbounded
lower limit and thecurrent
document. This means$sum
returns the cumulativequantity
for the documents between the beginning of the partition and the current document.
In this example output, the cumulative quantity
for each year is
shown in the cumulativeQuantityForYear
field:
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "cumulativeQuantityForYear" : 134 } { "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "cumulativeQuantityForYear" : 296 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "cumulativeQuantityForYear" : 104 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "cumulativeQuantityForYear" : 224 } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "cumulativeQuantityForYear" : 145 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "cumulativeQuantityForYear" : 285 }
Use Documents Window to Obtain Moving Average Quantity for Each Year![](/docs/v5.2/assets/link.svg)
This example uses a documents window
in $setWindowFields
to output the moving average
for the cake sales quantity
:
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: { $year: "$orderDate" }, sortBy: { orderDate: 1 }, output: { averageQuantity: { $avg: "$quantity", window: { documents: [ -1, 0 ] } } } } } ] )
In the example:
partitionBy: "$orderDate"
partitions the documents in the collection by$year
inorderDate
. There are are partitions for2019
,2020
, and2021
.sortBy: { orderDate: 1 }
sorts the documents in each partition byorderDate
in ascending order (1
), so the earliestorderDate
is first.output
:Sets the
averageQuantity
field to the moving averagequantity
for each year.Calculates the moving average
quantity
using the$avg
operator run in a documents window.The window contains documents between
-1
and0
. This means$avg
returns the moving averagequantity
between the document before the current document (-1
) and the current document (0
) in the partition.
In this example output, the moving average quantity
is shown in the
averageQuantity
field:
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "averageQuantity" : 134 } { "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "averageQuantity" : 148 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "averageQuantity" : 104 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "averageQuantity" : 112 } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "averageQuantity" : 145 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "averageQuantity" : 142.5 }
Use Documents Window to Obtain Cumulative and Maximum Quantity for Each Year![](/docs/v5.2/assets/link.svg)
This example uses a documents window
in $setWindowFields
to output the cumulative and maximum
cake sales quantity
values for each $year
in
orderDate
:
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: { $year: "$orderDate" }, sortBy: { orderDate: 1 }, output: { cumulativeQuantityForYear: { $sum: "$quantity", window: { documents: [ "unbounded", "current" ] } }, maximumQuantityForYear: { $max: "$quantity", window: { documents: [ "unbounded", "unbounded" ] } } } } } ] )
In the example:
partitionBy: "$orderDate"
partitions the documents in the collection by$year
inorderDate
. There are are partitions for2019
,2020
, and2021
.sortBy: { orderDate: 1 }
sorts the documents in each partition byorderDate
in ascending order (1
), so the earliestorderDate
is first.output
:Sets the
cumulativeQuantityForYear
field to the cumulativequantity
for each year.Calculates the cumulative
quantity
using the$sum
operator run in a documents window.The window contains documents between an
unbounded
lower limit and thecurrent
document. This means$sum
returns the cumulative quantity for the documents between the beginning of the partition and the current document.Sets the
maximumQuantityForYear
field to the maximumquantity
for each year.Calculates the maximum
quantity
of all the documents using the$max
operator run in a documents window.The window contains documents between an
unbounded
lower andupper
limit. This means$max
returns the maximum quantity for the documents in the partition.
In this example output, the cumulative quantity
is shown in the
cumulativeQuantityForYear
field and the maximum quantity
is
shown in the maximumQuantityForYear
field:
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "cumulativeQuantityForYear" : 134, "maximumQuantityForYear" : 162 } { "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "cumulativeQuantityForYear" : 296, "maximumQuantityForYear" : 162 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "cumulativeQuantityForYear" : 104, "maximumQuantityForYear" : 120 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "cumulativeQuantityForYear" : 224, "maximumQuantityForYear" : 120 } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "cumulativeQuantityForYear" : 145, "maximumQuantityForYear" : 145 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "cumulativeQuantityForYear" : 285, "maximumQuantityForYear" : 145 }
Range Window Example![](/docs/v5.2/assets/link.svg)
This example uses a range window in
$setWindowFields
to return the sum of the quantity
values of cakes sold for orders within plus or minus 10 dollars of the
current document's price
value:
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: "$state", sortBy: { price: 1 }, output: { quantityFromSimilarOrders: { $sum: "$quantity", window: { range: [ -10, 10 ] } } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { price: 1 }
sorts the documents in each partition byprice
in ascending order (1
), so the lowestprice
is first.output
sets thequantityFromSimilarOrders
field to the sum of thequantity
values from the documents in a range window.
In this example output, the sum of the quantity
values for documents
in the window is shown in the quantityFromSimilarOrders
field:
{ "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "quantityFromSimilarOrders" : 265 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "quantityFromSimilarOrders" : 265 } { "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "quantityFromSimilarOrders" : 162 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "quantityFromSimilarOrders" : 244 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "quantityFromSimilarOrders" : 244 } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "quantityFromSimilarOrders" : 134 }
Time Range Window Examples![](/docs/v5.2/assets/link.svg)
Use a Time Range Window with a Positive Upper Bound![](/docs/v5.2/assets/link.svg)
The following example uses a window with
a positive upper bound time range unit in
$setWindowFields
. The pipeline outputs an array of
orderDate
values for each state
that match the specified time
range.
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: "$state", sortBy: { orderDate: 1 }, output: { recentOrders: { $push: "$orderDate", window: { range: [ "unbounded", 10 ], unit: "month" } } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { orderDate: 1 }
sorts the documents in each partition byorderDate
in ascending order (1
), so the earliestorderDate
is first.output
:
The window contains documents between an
unbounded
lower limit and an upper limit set to10
(10 months after the current document'sorderDate
value) using a time range unit.$push
returns the array oforderDate
values for the documents between the beginning of the partition and the documents withorderDate
values inclusively in a range of the current document'sorderDate
value plus10
months.
In this example output, the array of orderDate
values for CA
and
WA
is shown in the recentOrders
field:
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "recentOrders" : [ ISODate("2019-05-18T16:09:01Z") ] } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "recentOrders" : [ ISODate("2019-05-18T16:09:01Z"), ISODate("2020-05-18T14:10:30Z"), ISODate("2021-01-11T06:31:15Z") ] } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "recentOrders" : [ ISODate("2019-05-18T16:09:01Z"), ISODate("2020-05-18T14:10:30Z"), ISODate("2021-01-11T06:31:15Z") ] } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "recentOrders" : [ ISODate("2019-01-08T06:12:03Z") ] } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "recentOrders" : [ ISODate("2019-01-08T06:12:03Z"), ISODate("2020-02-08T13:13:23Z") ] } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "recentOrders" : [ ISODate("2019-01-08T06:12:03Z"), ISODate("2020-02-08T13:13:23Z"), ISODate("2021-03-20T11:30:05Z") ] }
Use a Time Range Window with a Negative Upper Bound![](/docs/v5.2/assets/link.svg)
The following example uses a window with
a negative upper bound time range unit in
$setWindowFields
. The pipeline outputs an array of
orderDate
values for each state
that match the specified time
range.
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: "$state", sortBy: { orderDate: 1 }, output: { recentOrders: { $push: "$orderDate", window: { range: [ "unbounded", -10 ], unit: "month" } } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { orderDate: 1 }
sorts the documents in each partition byorderDate
in ascending order (1
), so the earliestorderDate
is first.output
:
The window contains documents between an
unbounded
lower limit and an upper limit set to-10
(10 months before the current document'sorderDate
value) using a time range unit.$push
returns the array oforderDate
values for the documents between the beginning of the partition and the documents withorderDate
values inclusively in a range of the current document'sorderDate
value minus10
months.
In this example output, the array of orderDate
values for CA
and
WA
is shown in the recentOrders
field:
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "recentOrders" : [ ] } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "recentOrders" : [ ISODate("2019-05-18T16:09:01Z") ] } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "recentOrders" : [ ISODate("2019-05-18T16:09:01Z") ] } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "recentOrders" : [ ] } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "recentOrders" : [ ISODate("2019-01-08T06:12:03Z") ] } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "recentOrders" : [ ISODate("2019-01-08T06:12:03Z"), ISODate("2020-02-08T13:13:23Z") ] }
Tip
See also:
For an additional example about IOT Power Consumption, see the Practical MongoDB Aggregations e-book.