Back to DocumentationMQL Documentation

$shift

Shifts values within a partition and returns the shifted value, useful for comparing values from adjacent documents in sorted partitions.

Syntax

{ $shift: { output: <expression>, by: <number>, default: <expression> } }

Parameters

outputobjectrequired

Field or expression whose value will be shifted

bynumberrequired

Number of positions to shift (positive for forward, negative for backward)

defaultobject

Default value to return if shift operation goes out of bounds

Examples

Shifting sales data

Calculate previous sales value for each document in sorted partition

Query:

db.collection.aggregate([{ $setWindowFields: { partitionBy: "$sales.salesByCategory.categoryName", sortBy: { "sales.salesByCategory.totalSales": 1 }, output: { previousSales: { $shift: { output: "$sales.salesByCategory.totalSales", by: -1, default: null } } } } }])

Output:

Documents with previous sales values shifted by one position backward

Shifting promotional event dates

Get previous promotional event start date for comparison

Query:

db.promotionEvents.aggregate([{ $setWindowFields: { partitionBy: null, sortBy: { "promotionalDates.startDate": 1 }, output: { previousStartDate: { $shift: { output: "$promotionalDates.startDate", by: -1, default: null } } } } }])

Output:

Promotion events with previous event start dates for temporal analysis

Related