View a markdown version of this page

$setWindowFields - Amazon DocumentDB
Window boundariesSupported window operatorsExample (MongoDB Shell)Code examples

$setWindowFields

New from version 8.0.2.

The $setWindowFields stage in Amazon DocumentDB performs operations on a specified span of documents (a window) in a partition, and returns the results based on the chosen window operator. The stage groups the input documents into partitions, optionally sorts them, and for each document computes one or more output fields over a window of surrounding documents.

Syntax

{
  $setWindowFields: {
    partitionBy: <expression>,
    sortBy: { <sortField1>: <sortOrder>, ... },
    output: {
      <outputField1>: {
        <windowOperator>: <expression>,
        window: {
          documents: [ <lowerBound>, <upperBound> ],
          range: [ <lowerBound>, <upperBound> ],
          unit: <time unit>
        }
      },
      ...
    }
  }
}

Parameters

  • partitionBy: Optional. A single expression used to group the documents into partitions. This can be a field path (for example, "$state") or another expression. If omitted, all input documents belong to a single partition.

  • sortBy: Optional. A document that specifies the field or fields to sort the documents in each partition by, in the form { <field>: 1 } for ascending order or { <field>: -1 } for descending order. A sortBy is required when you use document-based or range-based window bounds, and when you use one of the ranking operators.

  • output: Required. A document that specifies the fields to append to each document. You can specify multiple output fields, but each output field takes exactly one window operator, its argument, and an optional window document that defines the window boundaries: { <field>: { <windowOperator>: <expression>, window: { ... } } }.

Window boundaries

Within each output field you can optionally define the window boundaries with a window document. A window can be defined by document position or by a range of values.

  • documents: Specifies the window as a number of documents before and after the current document, in the form [ <lowerBound>, <upperBound> ]. Each bound is an integer offset (for example, -1 or 2), or one of the keywords "unbounded" and "current". "unbounded" extends the window to the start of the partition when used as the lower bound, or to the end of the partition when used as the upper bound. "current" means the current document. A documents window requires a sortBy unless both bounds are "unbounded".

  • range: Specifies the window as a range of sortBy field values relative to the current document, in the form [ <lowerBound>, <upperBound> ]. Each bound is a number that is added to the sortBy value of the current document, or the "unbounded" keyword. A range window requires exactly one ascending sortBy field and does not accept the "current" keyword.

  • unit: Optional. Used with range to interpret the numeric window bounds as a span of time when the sortBy field holds date values. Amazon DocumentDB supports the following units: "year", "quarter", "month", "week", "day", "hour", "minute", "second", and "millisecond". When unit is specified, the range bounds must be integers.

When the window document is omitted, the default window is documents: ["unbounded", "unbounded"], which spans the entire partition.

Note

When you use a range window over date values, we recommend a fixed-duration unit such as "day" (or smaller) rather than "month", "quarter", or "year", whose calendar-dependent lengths can produce ambiguous window boundaries.

Supported window operators

Amazon DocumentDB supports the following 26 window operators in the output field of the $setWindowFields stage:

  • $sum

  • $avg

  • $count

  • $push

  • $addToSet

  • $stdDevPop

  • $stdDevSamp

  • $covariancePop

  • $covarianceSamp

  • $first

  • $last

  • $firstN

  • $lastN

  • $top

  • $bottom

  • $topN

  • $bottomN

  • $min

  • $minN

  • $max

  • $maxN

  • $median

  • $percentile

  • $rank

  • $denseRank

  • $documentNumber

Note

The $rand operator is not supported within a window operator in the $setWindowFields stage.

Empty windows

When a window contains no documents (for example, a range window with no values in range), the result depends on the operator: $count and $sum return 0; $push, $addToSet, $firstN, $lastN, $minN, $maxN, $topN, and $bottomN return an empty array; $percentile returns an array of null values (one per requested percentile); all other operators return null.

Example (MongoDB Shell)

The following example partitions the documents by region, sorts each partition by day, and computes a running total of revenue from the start of the partition through the current document.

Create sample documents

db.sales.insertMany([
  { _id: 1, region: "east", day: 1, revenue: 100 },
  { _id: 2, region: "east", day: 2, revenue: 150 },
  { _id: 3, region: "east", day: 3, revenue: 120 },
  { _id: 4, region: "west", day: 1, revenue: 200 },
  { _id: 5, region: "west", day: 2, revenue: 180 }
]);

Query example

db.sales.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$region",
      sortBy: { day: 1 },
      output: {
        runningRevenue: {
          $sum: "$revenue",
          window: { documents: ["unbounded", "current"] }
        }
      }
    }
  }
]);

Output

[
  { "_id": 1, "region": "east", "day": 1, "revenue": 100, "runningRevenue": 100 },
  { "_id": 2, "region": "east", "day": 2, "revenue": 150, "runningRevenue": 250 },
  { "_id": 3, "region": "east", "day": 3, "revenue": 120, "runningRevenue": 370 },
  { "_id": 4, "region": "west", "day": 1, "revenue": 200, "runningRevenue": 200 },
  { "_id": 5, "region": "west", "day": 2, "revenue": 180, "runningRevenue": 380 }
]

Each document is augmented with runningRevenue, the cumulative sum of revenue within its partition up to and including the current document.

Code examples

To view a code example for using the $setWindowFields stage, choose the tab for the language that you want to use:

Node.js
const { MongoClient } = require('mongodb');

async function example() {
  const client = new MongoClient('mongodb://<username>:<password>@<cluster-endpoint>:27017/?tls=true&tlsCAFile=global-bundle.pem&replicaSet=rs0&readPreference=secondaryPreferred&retryWrites=false');

  try {
    await client.connect();
    const db = client.db('test');
    const sales = db.collection('sales');

    await sales.insertMany([
      { _id: 1, region: "east", day: 1, revenue: 100 },
      { _id: 2, region: "east", day: 2, revenue: 150 },
      { _id: 3, region: "east", day: 3, revenue: 120 },
      { _id: 4, region: "west", day: 1, revenue: 200 },
      { _id: 5, region: "west", day: 2, revenue: 180 }
    ]);

    const result = await sales.aggregate([
      {
        $setWindowFields: {
          partitionBy: "$region",
          sortBy: { day: 1 },
          output: {
            runningRevenue: {
              $sum: "$revenue",
              window: { documents: ["unbounded", "current"] }
            }
          }
        }
      }
    ]).toArray();

    console.log(result);
  } finally {
    await client.close();
  }
}

example();
Python
from pymongo import MongoClient

def example():
    client = MongoClient('mongodb://<username>:<password>@<cluster-endpoint>:27017/?tls=true&tlsCAFile=global-bundle.pem&replicaSet=rs0&readPreference=secondaryPreferred&retryWrites=false')

    try:
        db = client['test']
        sales = db['sales']

        sales.insert_many([
            { '_id': 1, 'region': 'east', 'day': 1, 'revenue': 100 },
            { '_id': 2, 'region': 'east', 'day': 2, 'revenue': 150 },
            { '_id': 3, 'region': 'east', 'day': 3, 'revenue': 120 },
            { '_id': 4, 'region': 'west', 'day': 1, 'revenue': 200 },
            { '_id': 5, 'region': 'west', 'day': 2, 'revenue': 180 }
        ])

        result = list(sales.aggregate([
            {
                '$setWindowFields': {
                    'partitionBy': '$region',
                    'sortBy': { 'day': 1 },
                    'output': {
                        'runningRevenue': {
                            '$sum': '$revenue',
                            'window': { 'documents': ['unbounded', 'current'] }
                        }
                    }
                }
            }
        ]))

        print(result)
    finally:
        client.close()

example()