$last (expression operator)

Defining Document Order

• To define the document order for $last in a $setWindowFields, set the sortBy field.

• To define the document order for $last in other pipeline stages, add a preceding $sort stage.

Array Operator

If the expression resolves to an array:

• For a group of documents, as with the $group and $setWindowFields stages, $last returns the entire array from the last document. It does not traverse array elements.

• For an individual document, as with the $addFields stage, $last returns the last element of the array.

Missing Values

Documents in a group may be missing fields or may have fields with missing values.

• If there are no documents from the prior pipeline stage, the $group stage returns nothing.

• If the field that the $last accumulator is processing is missing, $last returns null.

• When used with $setWindowFields, $last returns null for empty windows. For example, when you have a { documents: [ -1, -1] } documents window on the first document of a partition.

For more information, see the Missing Data example later in this topic.

Use in $group Stage

Consider a sales collection with the following documents:

db.sales.insertMany( [
   { _id : 1, "item" : "abc", "date" : ISODate("2014-01-01T08:00:00Z"), "price" : 10, "quantity" : 2 },
   { _id : 2, "item" : "jkl", "date" : ISODate("2014-02-03T09:00:00Z"), "price" : 20, "quantity" : 1 },
   { _id : 3, "item" : "xyz", "date" : ISODate("2014-02-03T09:05:00Z"), "price" : 5, "quantity" : 5 },
   { _id : 4, "item" : "abc", "date" : ISODate("2014-02-15T08:00:00Z"), "price" : 10, "quantity" : 10 },
   { _id : 5, "item" : "xyz", "date" : ISODate("2014-02-15T09:05:00Z"), "price" : 5, "quantity" : 10 },
   { _id : 6, "item" : "xyz", "date" : ISODate("2014-02-15T12:05:10Z"), "price" : 5, "quantity" : 5 },
   { _id : 7, "item" : "xyz", "date" : ISODate("2014-02-15T14:12:12Z"), "price" : 5, "quantity" : 10 }
] )

The following operation first sorts the documents by item and date, and then in the following $group stage, groups the now sorted documents by the item field and uses the $last accumulator to compute the last sales date for each item:

db.sales.aggregate(
   [
     { $sort: { item: 1, date: 1 } },
     {
       $group:
         {
           _id: "$item",
           lastSalesDate: { $last: "$date" }
         }
     }
   ]
)

The operation returns the following results:

{ "_id" : "xyz", "lastSalesDate" : ISODate("2014-02-15T14:12:12Z") }
{ "_id" : "jkl", "lastSalesDate" : ISODate("2014-02-03T09:00:00Z") }
{ "_id" : "abc", "lastSalesDate" : ISODate("2014-02-15T08:00:00Z") }

Use in $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 }
] )

This example uses $last in the $setWindowFields stage to output the last cake sales order type for each state:

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { orderDate: 1 },
         output: {
            lastOrderTypeForState: {
               $last: "$type",
               window: {
                  documents: [ "current", "unbounded" ]
               }
            }
         }
      }
   }
] )

In the example:

• partitionBy: "$state" partitions the documents in the collection by state. There are partitions for CA and WA.

• sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.

• output sets the lastOrderTypeForState field to the last order type from the documents window.

  The window contains documents between the current lower limit, which is the current document in the output, and the unbounded upper limit. This means $last returns the last order type for the documents between the current document and the end of the partition.

In this output, the last order type value for CA and WA is shown in the lastOrderTypeForState field:

{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"),
  "state" : "CA", "price" : 41, "quantity" : 162, "lastOrderTypeForState" : "vanilla" }
{ "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"),
  "state" : "CA", "price" : 13, "quantity" : 120, "lastOrderTypeForState" : "vanilla" }
{ "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"),
  "state" : "CA", "price" : 12, "quantity" : 145, "lastOrderTypeForState" : "vanilla" }
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"),
  "state" : "WA", "price" : 43, "quantity" : 134, "lastOrderTypeForState" : "chocolate" }
{ "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"),
  "state" : "WA", "price" : 13, "quantity" : 104, "lastOrderTypeForState" : "chocolate" }
{ "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"),
  "state" : "WA", "price" : 14, "quantity" : 140, "lastOrderTypeForState" : "chocolate" }