A chart can have a missing reading at a known time position, or a stored reading whose value is null. Those are different gaps. $densify can create the missing position in aggregation output, while $fill can assign a value under a chosen policy. Neither operation makes the generated value an actual observation. This reference targets MongoDB 8.0 and uses an integer sequence for a small device series. It preserves an observed flag so interpolated rows remain distinguishable. A separate complete mongosh exercise demonstrates last-observation-carried-forward behavior, including a leading gap that has no earlier value to carry.
Separate missing rows from missing values
A collection with sequence positions zero, two, and four has no document at one or three. A fill operation cannot assign a value to a document that does not exist in its input. Densify first adds those positions under a specified step and bounds. A document already present at position two with a null measurement presents another problem: the row exists, but its field needs a policy. Fill handles missing or null values on rows it receives.
Keep those stages separate in the explanation and implementation. A report may need densify without imputation to show explicit gaps. It may need fill without densify when all rows already exist. Or it may use both, as the main fixture does. The resulting rows remain aggregation output unless a write stage intentionally stores them. Do not describe densify as automatically inserting documents into the source collection. The fixture checks the source count after its report to make that read-versus-write boundary visible.
Choose partitions, steps, and bounds carefully
Each device should have its own sequence. partitionByFields tells densify which fields identify independent series. The field being densified must follow the documented numeric or date contract, and step defines the interval between generated positions. For a date field, an appropriate unit is also required. A step of one minute over a year can generate hundreds of thousands of rows per series, so calculate the expected expansion before running an unbounded report.
Explicit bounds include their lower edge and exclude their upper edge for generated positions. In the fixture, bounds [0, 5] with step one produce positions zero through four. Existing input documents outside an explicit generation range are not automatically filtered out merely because the bounds were supplied. Use an ordinary match stage when the final report must exclude them. MongoDB 8.0 also has documented behavior changes for equal bounds, so pin versions when testing edge cases. Bounds are an output-generation contract, not a substitute for all query and resource filters.
Select an imputation method that fits the metric
Linear fill interpolates numeric values between known anchors according to the ordered field. It does not infer arbitrary real-world behavior. The measured values ten at zero and thirty at two produce twenty at one in the fixture. That is a reporting assumption. A sensor could actually have spiked or failed between observations. Preserve quality flags and explain the method to people reading the chart. Linear interpolation needs suitable numeric anchors and the documented unique sort behavior within its partition.
Last observation carried forward, or locf, uses the preceding available value. It can be appropriate for a state that remains in effect until changed, but misleading for a new event count or a volatile measurement. A leading gap has no earlier observation to carry, so it remains unavailable. A constant fill such as zero is another explicit choice, not a universally correct missing-data default. Zero sales, missing sales ingestion, and a sensor's zero reading have different meanings. Choose from the business interpretation rather than whatever makes a line look smooth.
Preserve provenance and finalize the report
Before densifying, mark source readings as observed. Afterward, supply false for newly generated rows and fill only the intended measurement field. This lets downstream code distinguish measured and derived values. If source documents can themselves have null values, consider an additional value-quality flag so observed does not falsely imply a valid measurement. Retain enough provenance to explain what the report did without storing unnecessary raw data in every row.
Apply a final sort when consumers require a defined order. Project the fields needed for the chart or export and keep resource limits explicit. If generated summaries are persisted with merge or another write stage, define refresh and stale-row policy; the read pipeline alone does not create that lifecycle. Test endpoints, partitions, leading gaps, and duplicates rather than only one interior gap. A clean-looking output is not proof that interpolation was semantically justified or that a large production range is affordable. The fixtures isolate the small documented mechanics so those larger decisions remain visible.
MongoDB documentation and version notes
MongoDB Densify and Fill Examples
Each example shows the code, the output it produces and what each part does.
Densifying sensor gaps with linear interpolation
Run the complete mongosh script on MongoDB 8.0. Three observed device readings are separated by two missing integer positions. Densify generates those positions, fill interpolates the measurement, and a final sort produces a chart-ready sequence. The output retains observed true only for the original rows. The source collection still contains three documents after the report, proving that the added positions were not automatically stored. The range is intentionally tiny and finite. Its values demonstrate the stated linear policy without claiming that any generated measurement was physically observed.
const readings = db.getSiblingDB('codevra_ref_gaps').getCollection('readings');
readings.deleteMany({});
readings.insertMany([{ _id: 1, device: 'A', t: 0, value: 10, observed: true },
{ _id: 2, device: 'A', t: 2, value: 30, observed: true },
{ _id: 3, device: 'A', t: 4, value: 50, observed: true }]);
const report = readings.aggregate([
{ $densify: { field: 't', partitionByFields: ['device'], range: { step: 1, bounds: [0, 5] } } },
{ $set: { observed: { $ifNull: ['$observed', false] } } },
{ $fill: { partitionBy: '$device', sortBy: { t: 1 }, output: { value: { method: 'linear' } } } },
{ $sort: { device: 1, t: 1 } }
]).toArray().map(x => ({ t: x.t, value: x.value, observed: x.observed }));
print(JSON.stringify(report));
print(readings.countDocuments({}));[{"t":0,"value":10,"observed":true},{"t":1,"value":20,"observed":false},{"t":2,"value":30,"observed":true},{"t":3,"value":40,"observed":false},{"t":4,"value":50,"observed":true}]
3MongoDB Densify and Fill in Real Projects
Carry the last observed state forward
Carry the latest available state forward across an existing sequence. Include one leading missing value, one interior null, and one later missing field. The solution does not need densify because every position already has a row. locf fills the interior and later gaps from preceding values, while the leading gap remains unavailable. Map absent results to null only for consistent printed output. That display conversion does not mean the input field was rewritten. This exercise highlights why last-observation-carried-forward is directional and why it is not equivalent to searching for the nearest value on either side.
const rows = db.getSiblingDB('codevra_ref_gaps').getCollection('states');
rows.deleteMany({});
rows.insertMany([{ _id: 1, t: -1 }, { _id: 2, t: 0, value: 10 },
{ _id: 3, t: 1, value: null }, { _id: 4, t: 2, value: 15 }, { _id: 5, t: 3 }]);
const report = rows.aggregate([
{ $fill: { sortBy: { t: 1 }, output: { value: { method: 'locf' } } } },
{ $sort: { t: 1 } }
]).toArray().map(x => [x.t, x.value ?? null]);
print(JSON.stringify(report));[[-1,null],[0,10],[1,10],[2,15],[3,15]]MongoDB Densify and Fill FAQ
Does fill create a missing time position?
No. It supplies values on rows in its input. Use densify first when the report needs positions with no document. The stages solve distinct problems and can be used independently. A chart may intentionally keep generated positions empty rather than interpolate them. Decide whether the business question needs a regular sequence, a filled measurement, or both before assembling the pipeline.
Are densified rows inserted into the source?
Not by this read aggregation. They exist in the pipeline output, and the main source count remains three. A deliberate write stage can persist a result, but that requires refresh, provenance, and stale-row policies. Do not confuse a report's generated rows with original observations. Preserve flags so users and later calculations can distinguish measured data from derived positions and values.
Why does the leading locf gap remain null?
There is no earlier available value to carry. locf is directional and does not borrow a future observation. A different fill policy might use interpolation or a constant, but each changes the semantic assumption. The practice renders the unavailable result as null for display only. Choose a policy consistent with the metric rather than automatically replacing every gap with zero or a later value.
Can a large densify range be expensive?
Yes. A small step across a long period and many partitions can generate a very large output. Estimate row expansion, limit the requested range, and inspect resource behavior with representative data. Explicit bounds govern generation but do not automatically remove every input document outside that range. Use filters and final ordering deliberately. A five-position fixture validates mechanics, not production capacity.
MongoDB Densify and Fill Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
What value does the worked locf report show at the leading position -1?
MongoDB Densify and Fill Quiz
2 questions to check what you learned.
Why preserve observed false on generated rows?
- It distinguishes derived positions from actual source readings
- It makes the interpolated measurement physically observed
- It causes densify to insert source documents automatically
Which stage adds positions one and three in the main fixture?
- densify
- fill alone
- The final sort