Docs Menu

$shift (aggregation)

On this page

  • Definition
  • Behavior
  • Examples

New in version 5.0.

$shift

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 integer with a numeric document position relative to the current document in the output.

For example:

  • 1 specifies the document position after the current document.
  • -1 specifies the document position before the current document.
  • -2 specifies the document position that is two positions before the current document.

Specifies an optional default expression to evaluate if the document position is outside of the implicit $setWindowFields stage window. The implicit window contains all the documents in the partition.

The default expression must evaluate to a constant value.

If you do not specify a default expression, $shift returns null for documents whose positions are outside of the implicit $setWindowFields stage window.

$shift returns an error if you specify a window in the $setWindowFields stage.

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.

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 by state. There are partitions for CA and WA.
  • sortBy: { quantity: -1 } sorts the documents in each partition by quantity in descending order (-1), so the highest quantity is first.
  • output after sortBy:

    • Sets the shiftQuantityForState field to the quantity value from the documents in each state.
    • Uses $shift to return the quantity value from the document that follows the current document in the output.

      • The document position is specified using the $shift by integer set to 1.
      • For documents outside of the implicit window, $shift returns "Not available", which is specified using the default expression.

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" }

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 by state. There are partitions for CA and WA.
  • sortBy: { quantity: -1 } sorts the documents in each partition by quantity in descending order (-1), so the highest quantity is first.
  • output after sortBy:

    • Sets the shiftQuantityForState field to the quantity value from the documents in each state.
    • Uses $shift to return the quantity value from the document before the current document in the output.

      • The document position is specified using the $shift by integer set to -1.
      • For documents outside of the implicit window, $shift returns "Not available", which is specified using the default expression.

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 }
Give Feedback
© 2021 MongoDB, Inc.

About

  • Careers
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2021 MongoDB, Inc.