$locf (aggregation)
On this page
Definition
$locfNew in version 5.2.
Last observation carried forward. Sets values for
nulland missing fields in a window to the last non-null value for the field.$locfis only available in the$setWindowFieldsstage.
Syntax
The $locf expression has this syntax:
{ $locf: <expression> }
For more information on expressions, see Expressions.
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.
Example
Create a stock collection that contains data for two different
company stocks trading in the financial market. The collection contains
the following properties of the stocks, recorded at hourly intervals:
Field | Type | Description |
|---|---|---|
time | Date | Time when the stock price and volume were observed. |
company | String | Name of the company. |
price | Decimal | Price of the stock at the time observed. |
volume | Integer | Number of shares traded over one day. |
Not all documents in the dataset contain all of the previous fields.
db.stock.insertMany( [ { time: "2021-03-08T09:00:00.000Z", company: "CompanyA", price: 500, volume: 200000 }, { time: "2021-03-08T09:00:00.000Z", company: "CompanyB", price: 20, volume: 100000 }, { time: "2021-03-08T10:00:00.000Z", company: "CompanyA", price: 490, volume: 205000 }, { time: "2021-03-08T10:00:00.000Z", company: "CompanyB", price: 22, volume: 105000 }, { time: "2021-03-08T11:00:00.000Z", company: "CompanyA" }, { time: "2021-03-08T11:00:00.000Z", company: "CompanyB", price: 24, volume: null }, { time: "2021-03-08T12:00:00.000Z", company: "CompanyA", price: 510, volume: 220000 }, { time: "2021-03-08T12:00:00.000Z", company: "CompanyB" }, { time: "2021-03-08T13:00:00.000Z", company: "CompanyA", price: 505, volume: 225000 }, { time: "2021-03-08T13:00:00.000Z", company: "CompanyB", price: 28, volume: 120000 } ] )
The following example uses the $locf operator to set null
and missing fields to the values from the last document with a non-null
value in the corresponding partition:
db.stock.aggregate( [ { $setWindowFields: { partitionBy: "$company", sortBy: { time: 1 }, output: { price: { $locf: "$price" }, volume: { $locf: "$volume" } } } } ] )
In the example:
partitionBy: "$company"partitions the documents bycompany. There are partitions forCompanyAandCompanyB.sortBy: { time: 1 }sorts the documents in each partition bytimein ascending order (1), so the earliesttimeis first.For documents where
priceorvolumeisnullor missing, the$locfoperator sets the missing field to the previous value for that field in the partition.
Example output:
[ { _id: ObjectId("61b11166b412e1948a407dc6"), time: '2021-03-08T09:00:00.000Z', company: 'CompanyA', price: 500, volume: 200000 }, { _id: ObjectId("61b11166b412e1948a407dc8"), time: '2021-03-08T10:00:00.000Z', company: 'CompanyA', price: 490, volume: 205000 }, { _id: ObjectId("61b11166b412e1948a407dca"), time: '2021-03-08T11:00:00.000Z', company: 'CompanyA', price: 490, volume: 205000 }, { _id: ObjectId("61b11166b412e1948a407dcc"), time: '2021-03-08T12:00:00.000Z', company: 'CompanyA', price: 510, volume: 220000 }, { _id: ObjectId("61b11166b412e1948a407dce"), time: '2021-03-08T13:00:00.000Z', company: 'CompanyA', price: 505, volume: 225000 }, { _id: ObjectId("61b11166b412e1948a407dc7"), time: '2021-03-08T09:00:00.000Z', company: 'CompanyB', price: 20, volume: 100000 }, { _id: ObjectId("61b11166b412e1948a407dc9"), time: '2021-03-08T10:00:00.000Z', company: 'CompanyB', price: 22, volume: 105000 }, { _id: ObjectId("61b11166b412e1948a407dcb"), time: '2021-03-08T11:00:00.000Z', company: 'CompanyB', price: 24, volume: 105000 }, { _id: ObjectId("61b11166b412e1948a407dcd"), time: '2021-03-08T12:00:00.000Z', company: 'CompanyB', price: 24, volume: 105000 }, { _id: ObjectId("61b11166b412e1948a407dcf"), time: '2021-03-08T13:00:00.000Z', company: 'CompanyB', price: 28, volume: 120000 } ]