• Reference >
• Operators >
• Aggregation Pipeline Operators >
• $linearFill (aggregation)

$linearFill (aggregation)

Definition

$linearFill

New in version 5.3.

Fills null and missing fields in a window using :wikipedia:`linear interpolation <Linear_interpolation>` based on surrounding field values.

$linearFill is only available in the $setWindowFields stage.

Syntax

The $linearFill expression has this syntax:

copy

{ $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:

  copy

  { 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 :wikipedia:`linear interpolation <Linear_interpolation>`, you can use:

• The $fill stage with { method: "linear" }.

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

• The $linearFill operator inside of a $setWindowFields stage.

  When you use the $linearFill 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:

copy

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:

copy

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:

copy

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

• To use $linearFill, you must use the sortBy field to sort your data.
• When using $linearFill window function, $setWindowFields returns an error if there are any repeated values in the sortBy field in a single partition.

←   $let (aggregation) $literal (aggregation)  →

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.