$setWindowFields (aggregation)
Definition
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
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:
See Range Window Example. | |
Optional |
Behavior
The $setWindowFields
stage appends new fields to existing
documents. You can include one or more $setWindowFields
stages in an aggregation operation.
Window Operators
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
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:
- Range windows require all sortBy values to be numbers.
- 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
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
Use Documents Window to Obtain Cumulative Quantity for Each State
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.
- Sets the
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
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.
- Sets the
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
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.
- Sets the
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
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.
- Sets the
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
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
Use a Time Range Window with a Positive Upper Bound
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
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") ] }
For an additional example about IOT Power Consumption, see the Practical MongoDB Aggregations e-book.