For AI agents: a documentation index is available at https://www.mongodb.com/zh-cn/docs/llms.txt — markdown versions of all pages are available by appending .md to any URL path.

$linearFill (expression operator)

Definition

$linearFill

New in version 5.3.

Fills null and missing fields in a window using linear interpolation based on surrounding field values.

$linearFill is only available in the $setWindowFields stage.

Syntax

The $linearFill expression has this syntax:

{ $linearFill: <expression> }

For more information on expressions, see Expressions.

Behavior

$linearFill fills null and missing fields using linear interpolation based on surrounding non-null field values. The surrounding field values are determined by the sort order specified in $setWindowFields.

  • $linearFill fills null and missing values proportionally spanning the value range between surrounding non-null values. To determine the values for missing fields, $linearFill uses:

    • The difference of surrounding non-null values.

    • The number of null fields to fill between the surrounding values.

  • $linearFill can fill multiple consecutive null values if those values are preceded and followed by non-null values according to the sort order specified in $setWindowFields.

    Example

    If a collection contains these documents:

    { index: 0, value: 0 },{ index: 1, value: null },{ index: 2, value: null },{ index: 3, value: null },{ index: 4, value: 10 }

    After using $linearFill to fill the null values, the documents become:

    { index: 0, value: 0 },{ index: 1, value: 2.5 },{ index: 2, value: 5 },{ index: 3, value: 7.5 },{ index: 4, value: 10 }

    For a complete example, see Examples.

  • null values that are not preceded and followed by non-null values remain null.

Comparison of $fill and $linearFill

To fill missing field values using linear interpolation, you can use:

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 Linear Interpolation

To populate the missing price values using linear interpolation, use $linearFill inside of a $setWindowFields stage:

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

In the example:

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

  • output specifies:

    • price as the field for which to fill in missing values.

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

Example output:

[   {      _id: ObjectId("620ad555394d47411658b5ef"),      time: ISODate("2021-03-08T09:00:00.000Z"),      price: 500   },   {      _id: ObjectId("620ad555394d47411658b5f0"),      time: ISODate("2021-03-08T10:00:00.000Z"),      price: 507.5   },   {      _id: ObjectId("620ad555394d47411658b5f1"),      time: ISODate("2021-03-08T11:00:00.000Z"),      price: 515   },   {      _id: ObjectId("620ad555394d47411658b5f2"),      time: ISODate("2021-03-08T12:00:00.000Z"),      price: 505   },   {      _id: ObjectId("620ad555394d47411658b5f3"),      time: ISODate("2021-03-08T13:00:00.000Z"),      price: 495   },   {      _id: ObjectId("620ad555394d47411658b5f4"),      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" }               }         }   }] )

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

Restrictions

Back
$let
Next
$literal
Rate this page

On this page