Docs Home → Develop Applications → MongoDB Manual
$shift (aggregation)![](/docs/v5.2/assets/link.svg)
On this page
Definition![](/docs/v5.2/assets/link.svg)
New in version 5.0.
Returns the value from an expression
applied to a document in a specified position relative to the current
document in the $setWindowFields
stage partition.
The $setWindowFields
stage sortBy field value determines the document order. For
more information on how MongoDB compares fields with different types,
see BSON comparison order.
$shift
is only available in the $setWindowFields
stage.
$shift
syntax:
{ $shift: { output: <output expression>, by: <integer>, default: <default expression> } }
$shift
takes a document with these fields:
Field | Description |
---|---|
Specifies an expression to evaluate and return in the output. | |
Specifies an For example:
| |
Specifies an optional default expression to evaluate if the document position
is outside of the implicit The default expression must evaluate to a constant value. If you do not specify a default
expression, |
Behavior![](/docs/v5.2/assets/link.svg)
$shift
returns an error if you specify a window in the $setWindowFields
stage.
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 cakeSales
collection is used in the following examples.
Shift Using a Positive Integer![](/docs/v5.2/assets/link.svg)
This example uses $shift
in the $setWindowFields
stage to output the quantity
of the cake sales from each document
following the current document for each state
:
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: "$state", sortBy: { quantity: -1 }, output: { shiftQuantityForState: { $shift: { output: "$quantity", by: 1, default: "Not available" } } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { quantity: -1 }
sorts the documents in each partition byquantity
in descending order (-1
), so the highestquantity
is first.
output
aftersortBy
:Sets the
shiftQuantityForState
field to thequantity
value from the documents in eachstate
.Uses
$shift
to return thequantity
value from the document that follows the current document in the output.
In this example output, the shifted quantity
value is shown in the
shiftQuantityForState
field for each returned document:
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "shiftQuantityForState" : 145 } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "shiftQuantityForState" : 120 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "shiftQuantityForState" : "Not available" } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "shiftQuantityForState" : 134 } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "shiftQuantityForState" : 104 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "shiftQuantityForState" : "Not available" }
Shift Using a Negative Integer![](/docs/v5.2/assets/link.svg)
This example uses $shift
in the $setWindowFields
stage to output the quantity
of the cake sales from each document
before the current document for each state
:
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: "$state", sortBy: { quantity: -1 }, output: { shiftQuantityForState: { $shift: { output: "$quantity", by: -1, default: "Not available" } } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { quantity: -1 }
sorts the documents in each partition byquantity
in descending order (-1
), so the highestquantity
is first.
output
aftersortBy
:Sets the
shiftQuantityForState
field to thequantity
value from the documents in eachstate
.Uses
$shift
to return thequantity
value from the document before the current document in the output.
In this example output, the shifted quantity
value is shown in the
shiftQuantityForState
field for each returned document:
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "shiftQuantityForState" : "Not available" } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "shiftQuantityForState" : 162 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "shiftQuantityForState" : 145 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "shiftQuantityForState" : "Not available" } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "shiftQuantityForState" : 140 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "shiftQuantityForState" : 134 }