Docs Menu

Docs HomeDevelop ApplicationsMongoDB Manual

$locf (aggregation)

On this page

  • Definition
  • Syntax
  • Behavior
  • Examples
$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.

The $locf expression has this syntax:

{ $locf: <expression> }

For more information on expressions, see Expression Operators.

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.

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

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.

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

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

In the example:

  • sortBy: { time: 1 } sorts the documents by the time field in ascending order, from earliest to latest.

  • output specifies:

    • linearFillPrice as a target field to be filled.

      • { $linearFill: "$price" } is the value for the linearFillPrice field. $linearFill fills missing price values using linear interpolation based on the surrounding price values in the sequence.

    • locfPrice as a target field to be filled.

      • { $locf: "$price" } is the value for the locfPrice field. locf stands for last observation carried forward. $locf fills missing price values with the value from the previous document in the sequence.

Example output:

[
{
_id: ObjectId("620ad555394d47411658b5ef"),
time: ISODate("2021-03-08T09:00:00.000Z"),
price: 500,
linearFillPrice: 500,
locfPrice: 500
},
{
_id: ObjectId("620ad555394d47411658b5f0"),
time: ISODate("2021-03-08T10:00:00.000Z"),
linearFillPrice: 507.5,
locfPrice: 500
},
{
_id: ObjectId("620ad555394d47411658b5f1"),
time: ISODate("2021-03-08T11:00:00.000Z"),
price: 515,
linearFillPrice: 515,
locfPrice: 515
},
{
_id: ObjectId("620ad555394d47411658b5f2"),
time: ISODate("2021-03-08T12:00:00.000Z"),
linearFillPrice: 505,
locfPrice: 515
},
{
_id: ObjectId("620ad555394d47411658b5f3"),
time: ISODate("2021-03-08T13:00:00.000Z"),
linearFillPrice: 495,
locfPrice: 515
},
{
_id: ObjectId("620ad555394d47411658b5f4"),
time: ISODate("2021-03-08T14:00:00.000Z"),
price: 485,
linearFillPrice: 485,
locfPrice: 485
}
]
← $ln (aggregation)