Back to DocumentationMQL Documentation

$fill

The `$fill` stage is used to fill missing or null values in documents within the aggregation pipeline. It provides various methods to populate missing data, including using static values, linear interpolation, or values from previous/next documents.

Syntax

{
  $fill: {
    sortBy: <sort specification>,
    partitionBy: <partition fields>,
    partitionByFields: <array of partition field names>,
    output: {
      <field1>: { value: <expression> },
      <field2>: { method: <string> }
    }
  }
}

Parameters

sortByobject

Specifies the sort order for documents when applying fill methods that depend on document order.

partitionByobject

Optional. Groups documents into partitions. Fill operations are applied within each partition separately.

partitionByFieldsobject

Optional. Alternative syntax for partitionBy using an array of field names.

outputobjectrequired

Specifies the fields to fill and the method or value to use for filling missing data.

Examples

Sample Data

{
  "_id": "2cf3f885-9962-4b67-a172-aa9039e9ae2f",
  "name": "First Up Consultants | Bed and Bath Center",
  "sales": { "totalSales": 37701 }
}

Fill missing values with static value

Fill missing totalSales values with a default value of 0.

This query fills missing sales values in the salesByCategory array.

Query:

db.stores.aggregate([{
  $match: { company: { $in: ["First Up Consultants"] } }
}, {
  $unwind: "$sales.salesByCategory"
}, {
  $fill: {
    output: {
      "sales.salesByCategory.totalSales": { value: 0 }
    }
  }
}, {
  $group: {
    _id: "$_id",
    name: { $first: "$name" },
    salesByCategory: { $push: "$sales.salesByCategory" }
  }
}])

Output:

[
  {
    "_id": "affdc09c-7356-4fff-a857-e8301f57159c",
    "name": "First Up Consultants | Sports Gear Pantry",
    "salesByCategory": [
      { "categoryName": "Baseball Gear", "totalSales": 33878 },
      { "categoryName": "Volleyball Gear", "totalSales": 34031 }
    ]
  }
]

Fill using last observation carried forward

Fill missing part-time staff data using the last known value.

This query fills missing staff data within each store group.

Query:

db.stores.aggregate([{
  $fill: {
    sortBy: { "_id": 1 },
    output: {
      "staff.totalStaff.partTime": { method: "locf" }
    }
  }
}, {
  $project: {
    name: 1,
    "staff.totalStaff": 1
  }
}])

Output:

[
  {
    "_id": "00003278-4226-4ca7-871d-e80d8f414431",
    "name": "Wide World Importers | Camera Depot",
    "staff": { "totalStaff": { "fullTime": 20, "partTime": 6 } }
  }
]

Related