setWindowFields
$setWindowFields#
Use $setWindowFields to perform operations and output calculations based on a span of documents you define called a window, using a window operator.
For example, you can use $setWindowFields for to output the following calculations:
- Moving average over particular period of time relative to the timestamp in current readings
- Difference in a metric between two readings
- Ranking
Syntax#
{ "$setWindowFields": { "partitionBy": "<expression>", "sortBy": { "<sort field 1>": "<sort order>", "<sort field 2>": "<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>": { } } }}| Field | Description | Required |
|---|---|---|
"partitionBy" | Enter an expression or field to partition the documents by in order to perform the window computation on each partition separately. | Optional |
"sortBy" | Enter one or more fields to sort the documents by in each partition. Follow the $sort stage syntax. | Optional |
"output" | Create an object that defines the output you want for your window operation. | Required |
"<output-field>" | Name a custom field to contain the computed output data for the window. | Required |
"<window-operator>" | Enter the window operator you want to use on the documents within the window. For more information on window operators, see [INSERT LINK]. | Required |
"window" | Define an object that determines the window in which the window operator computes. To cover document range, use the "documents" parameter in your "window" object; to cover a range based on the current document's sortBy value, use the "range" parameter; to cover a range of time from the current document, use the "range", and "unit" parameters. If you do not use the window field, the operation performs on all documents in the partition. | Optional |
"documents" | Defines the window from the current document. Enter a two-item array that defines the lower and upper limits for the window in relation to the current document. A value of 0 or "current" is the current document, a negative number defines the number of documents previous to the current document, and a positive number defines the number of documents after the current document. Enter "unbounded" for the first or last document in the partition, depending on its position in the array. | Optional |
"range" | Defines the window based on a numerical range from the current document's sortBy value. Enter a two-item array that defines the lower and upper limits for the window. A value of 0 or "current" is the current document's sortBy value, a negative number defines the lower limit of the sortBy value from the current document's sortBy value, and a positive number defines the upper limit of the sortBy value from the current document's sortBy value. Enter "unbounded" for the lowest or highest sortBy value in the partition, depending on its position in the array. | Optional |
"unit" | To cover set units of time in your window, such as minutes or years, set a unit value so that the numerical values in your range array represent one of the following units of time: year, quarter, month, week, day, hour, minute, second, or millisecond. For example, if you set unit: "month", then your range of [-5, 2] creates a window from five months before the current document to two months after the current document. | Optional |
Window Operators#
You can use the following operators with the $setWindowFields stage:
- Accumulator operators:
$addToSet: Add items to a field's array in a document$avg: Computes the window's average value of a given field across multiple documents$count: Counts the number of documents in a collection or the number of documents that match a specific condition within a window$derivative: Computes the deriverative of a specific field or expression for documents in a window$expMovingAvg: Computes the exponential moving average of a specific field or expression for documents in a window$max: Computes the window's maximum value$min: Computes the minimum value within a window$push: Collect values from a given field into an array within a window$stdDevPop: Computes the population standard deviation within a window$sum: Computes the sum of a given field or expression within a window
- Order operators:
$first: Gets the first value of a given field or expression within a window$last: Gets the last value of a given field or expression within a window$shift: Shifts the values of a specified field or expression within a defined window
- Rank operators:
$denseRank: Assigns a unique rank to each distinct value within a window with no rank gaps. If there is a tied rank, for example, two entries tied at rank 2, the following entry is ranked 3.$documentNumber: Assigns a sequential number to each document within a defined window.$rank: Assigns a sequential number to each document within a window. In comparison to $denseRank, if there is a tied rank, for example, two entries tied at rank 2, the following entry is ranked 4.
Examples#
Document range sample request#
The following example uses $setWindowFields to output the computed maximum and minimum values and their timestamps from the start of the partition to the current document. The documents are partitioned by sensor type.
[ { "$match": { "$and": [ { "sensor_type": { "$exists": true } } ] } }, { "$setWindowFields": { "partitionBy": "$sensor_type", "sortBy": { "_ts": 1 }, "output": { "maxTillCurrent": { "$max": "$val", "window": { "documents": [ "unbounded", "current" ] } }, "minTillCurrent": { "$min": "$val", "window": { "documents": [ "unbounded", "current" ] } } } } }, { "$limit": 5 }]Sample Response#
{ "_list": [ { "val": 65, "maxTillCurrent": 65, "_tsMetadata": { "_sourceId": "7939bdd1-de6e-4247-93f7-9e0c9c09537a" }, "_id": "645bca63f8b8a87c0f075cea", "sensor_type": "humidity", "_ts": "Wed May 10 22:16:26 IST 2023", "minTillCurrent": 65 }, { "val": 98, "maxTillCurrent": 98, "_tsMetadata": { "_sourceId": "5649bde1-ae4b-3257-53f7-8e0b9c095345" }, "sensor_type": "voc", "_id": "645bca63f8b8a87c0f075cec", "_ts": "Wed May 10 22:16:27 IST 2023", "minTillCurrent": 98 }, { "val": 69, "maxTillCurrent": 69, "_tsMetadata": { "_sourceId": "37e51a11-ac60-424f-8f51-4a9e4bcad0fd" }, "sensor_type": "humidity", "_id": "645bca63f8b8a87c0f075ce9", "_ts": "Wed May 10 22:16:26 IST 2023", "minTillCurrent": 65 }, { "val": 87, "maxTillCurrent": 98, "_tsMetadata": { "_sourceId": "b2fca2ab-60f1-4a9f-b131-2050f953f3c7" }, "sensor_type": "voc", "_id": "645bca63f8b8a87c0f075ceb", "_ts": "Wed May 10 22:16:27 IST 2023", "minTillCurrent": 87 }, { "val": 55, "maxTillCurrent": 69, "_tsMetadata": { "_sourceId": "91ed60bb-860e-45d0-bffd-2bb46a5aff7e" }, "sensor_type": "humidity", "_id": "645bca78f8b8a87c0f075cf1", "_ts": "Wed May 10 22:16:48 IST 2023", "minTillCurrent": 55 } ]}Sample Request - Time Range#
The following example uses $setWindowFields to compute the moving average temperature trend each minute relative to the current timestamp. The documents are partitioned by sensor type.
[ { "$match": { "$and": [ { "sensor_type": { "$exists": true } } ] } }, { "$setWindowFields": { "partitionBy": "$sensor_type", "sortBy": { "_ts": 1 }, "output": { "movigAvg": { "$avg": "$temp", "window": { "range": [ -30, 30 ], "unit": "second" } } } } }]Sample Response#
{ "_list": [ { "temp": 12, "_tsMetadata": { "_sourceId": "7939bdd1-de6e-4247-93f7-9e0c9c09537a" }, "sensor_type": "temp_sens1", "movigAvg": 14, "_id": "645bca63f8b8a87c0f075cea", "_ts": "Wed May 10 22:16:26 IST 2023" }, { "temp": 16, "_tsMetadata": { "_sourceId": "7939bdd1-de6e-4247-93f7-9e0c9c09537a" }, "sensor_type": "temp_sens1", "movigAvg": 14, "_id": "645bca63f8b8a87c0f075cec", "_ts": "Wed May 10 22:16:27 IST 2023" }, { "temp": 11, "_tsMetadata": { "_sourceId": "37e51a11-ac60-424f-8f51-4a9e4bcad0fd" }, "sensor_type": "temp_sens2", "movigAvg": 13.5, "_id": "645bca63f8b8a87c0f075ce9", "_ts": "Wed May 10 22:16:26 IST 2023" }, { "temp": 16, "_tsMetadata": { "_sourceId": "b2fca2ab-60f1-4a9f-b131-2050f953f3c7" }, "sensor_type": "temp_sens2", "movigAvg": 13.66667, "_id": "645bca63f8b8a87c0f075ceb", "_ts": "Wed May 10 22:16:50 IST 2023" }, { "temp": 14, "_tsMetadata": { "_sourceId": "029677c0-f7a2-11ed-b67e-0242ac120002" }, "sensor_type": "temp_sens2", "movigAvg": 15, "_id": "645bca63f8b8a87c0f075ceb", "_ts": "Wed May 10 22:17:10 IST 2023" } ]}