You are viewing an offline version of MongoDB documentation. Some page features might be unavailable. To view the latest version of the page or use interactive features, visit the live page.

Database Manual

Aggregation Operations

Reference

Operators

$locf (aggregation)

Definition

$locf

New in version 5.2.

Last observation carried forward. Sets values for null and missing fields in a window to the last non-null value for the field.

$locf is only available in the $setWindowFields stage.

Syntax

The $locf expression has this syntax:

{ $locf: <expression> }

For more information on expressions, see Expression Operators.

Behavior

If a field being filled contains both null and non-null values, $locf sets the null and missing values to the field's last known non-null value according to the sort order specified in $setWindowFields.

null and missing field values that appear before non-null values in the sort order remain null.

If a field being filled contains only null or missing values in a partition, $locf sets the field value to null for that partition.

Comparison of `$fill` and `$locf`

To fill missing field values based on the last observed value in a sequence, you can use:

The $fill stage with { method: "locf" }.
When you use the $fill stage, the field you specify in the output is the same field used as the source data. See Fill Missing Field Values Based on the Last Observed Value.
The $locf operator inside of a $setWindowFields stage.
When you use the $locf operator, you can set values for a different field than the field used as the source data. See Use Multiple Fill Methods in a Single Stage.

Examples

The examples on this page use a stock collection that contains tracks a single company's stock price at hourly intervals:

db.stock.insertMany( [
   {
      time: ISODate("2021-03-08T09:00:00.000Z"),
      price: 500
   },
   {
      time: ISODate("2021-03-08T10:00:00.000Z"),
   },
   {
      time: ISODate("2021-03-08T11:00:00.000Z"),
      price: 515
   },
   {
      time: ISODate("2021-03-08T12:00:00.000Z")
   },
   {
      time: ISODate("2021-03-08T13:00:00.000Z")
   },
   {
      time: ISODate("2021-03-08T14:00:00.000Z"),
      price: 485
   }
] )

The price field is missing for some of the documents in the collection.

Fill Missing Values with the Last Observed Value

The following example uses the $locf operator to set missing fields to the value from the last-observed non-null value:

db.stock.aggregate( [
   {
      $setWindowFields: {
        sortBy: { time: 1 },
        output: {
           price: { $locf: "$price" }
        }
      }
    }
] )

In the example:

sortBy: { time: 1 } sorts the documents in each partition by time in ascending order (1), so the earliest time is first.
For documents where the price field is missing, the $locf operator sets the price to the last-observed value in the sequence.

Example output:

[
  {
    _id: ObjectId("62169b65394d47411658b5f5"),
    time: ISODate("2021-03-08T09:00:00.000Z"),
    price: 500
  },
  {
    _id: ObjectId("62169b65394d47411658b5f6"),
    time: ISODate("2021-03-08T10:00:00.000Z"),
    price: 500
  },
  {
    _id: ObjectId("62169b65394d47411658b5f7"),
    time: ISODate("2021-03-08T11:00:00.000Z"),
    price: 515
  },
  {
    _id: ObjectId("62169b65394d47411658b5f8"),
    time: ISODate("2021-03-08T12:00:00.000Z"),
    price: 515
  },
  {
    _id: ObjectId("62169b65394d47411658b5f9"),
    time: ISODate("2021-03-08T13:00:00.000Z"),
    price: 515
  },
  {
    _id: ObjectId("62169b65394d47411658b5fa"),
    time: ISODate("2021-03-08T14:00:00.000Z"),
    price: 485
  }
]

Use Multiple Fill Methods in a Single Stage

When you use the $setWindowFields stage to fill missing values, you can set values for a different field than the field you fill from. As a result, you can use multiple fill methods in a single $setWindowFields stage and output the results in distinct fields.

The following pipeline populates missing price fields using linear interpolation and the last-observation-carried-forward method:

db.stock.aggregate( [
   {
      $setWindowFields:
         {
            sortBy: { time: 1 },
            output:
               {
                  linearFillPrice: { $linearFill: "$price" },
                  locfPrice: { $locf: "$price" }
               }
         }
   }
] )