$unionWith, $densify and $fill

conceptThe concept

$unionWith (4.4) appends the results of another collection, optionally after its own pipeline, to the current stream. It is SQL's UNION ALL: no deduplication, and the stages after it see documents from both sources. Shape both sides to the same fields first so later stages work on one schema.

$densify (5.1) adds documents for missing values of a date or number field: hours with no readings, days with no orders. You give a step (and a unit for dates) and bounds: "full" (from the smallest to the largest value seen), "partition" (per partition), or an explicit [lower, upper) range. partitionByFields densifies each series (each sensor, each store) separately. New documents contain only the densified field and the partition fields.

$fill (5.3) sets values for null or missing fields: value (a constant or expression, such as 0 for "no orders that day"), method: "locf" (last observation carried forward) or method: "linear" (interpolate between the neighbours). Methods need sortBy; partitionBy or partitionByFields keep series apart.

whyWhy it matters

Charts and reports need one row per time slot. Without densify, a day with no sales simply disappears from the chart and a seven-day average silently averages six days. Without unionWith, combining orders and support tickets into one customer timeline means two queries and a merge in application code.

Use caseReal-world scenario

An operations dashboard shows cancelled orders per day for September. Grouping by day returns only the days that had cancellations; $densify adds the missing days and $fill sets them to 0, so the chart has thirty bars and the weekly average is correct. A customer-support view uses $unionWith to show one customer's orders and site events in a single timeline.

Try itExamples

Run and edit these in the interactive lesson.

db.orders.aggregate([
  { $match: { customerId: 66 } },
  { $project: { _id: 0, at: "$createdAt", what: { $concat: ["order ", "$status"] } } },
  { $unionWith: { coll: "events", pipeline: [
    { $match: { customerId: 66 } },
    { $project: { _id: 0, at: "$ts", what: "$type" } }
  ] } },
  { $sort: { at: -1 } },
  { $limit: 6 }
])

db.orders.aggregate([
  { $limit: 2 },
  { $project: { _id: 0, source: "orders" } },
  { $unionWith: { coll: "orders", pipeline: [{ $limit: 2 }, { $project: { _id: 0, source: "orders" } }] } },
  { $count: "documents" }
])

db.orders.aggregate([
  { $match: { status: "cancelled", createdAt: { $gte: ISODate("2026-09-01"), $lt: ISODate("2026-10-01") } } },
  { $group: { _id: { $dateTrunc: { date: "$createdAt", unit: "day" } }, cancelled: { $sum: 1 } } },
  { $densify: { field: "_id", range: { step: 1, unit: "day", bounds: [ISODate("2026-09-01"), ISODate("2026-10-01")] } } },
  { $fill: { output: { cancelled: { value: 0 } } } },
  { $sort: { _id: 1 } },
  { $limit: 10 }
])

db.lab_readings.drop()
db.lab_readings.insertMany([
  { sensor: "s1", t: ISODate("2026-10-01T00:00:00Z"), temp: 20 },
  { sensor: "s1", t: ISODate("2026-10-01T03:00:00Z"), temp: 26 }
])
db.lab_readings.aggregate([
  { $densify: { field: "t", range: { step: 1, unit: "hour", bounds: "full" } } },
  { $fill: { sortBy: { t: 1 }, output: { temp: { method: "linear" } } } },
  { $project: { _id: 0, t: 1, temp: 1 } }
])

db.lab_readings.drop()
db.lab_readings.insertMany([
  { sensor: "s1", t: ISODate("2026-10-01T00:00:00Z"), state: "ok" },
  { sensor: "s1", t: ISODate("2026-10-01T03:00:00Z"), state: "alarm" },
  { sensor: "s2", t: ISODate("2026-10-01T01:00:00Z"), state: "ok" },
  { sensor: "s2", t: ISODate("2026-10-01T02:00:00Z"), state: "ok" }
])
db.lab_readings.aggregate([
  { $densify: { field: "t", partitionByFields: ["sensor"], range: { step: 1, unit: "hour", bounds: "partition" } } },
  { $fill: { partitionByFields: ["sensor"], sortBy: { t: 1 }, output: { state: { method: "locf" } } } },
  { $sort: { sensor: 1, t: 1 } },
  { $project: { _id: 0 } }
])

db.orders.aggregate([{ $fill: { output: { total: { method: "linear" } } } }])

internalsUnder the hood

$unionWith runs the sub-pipeline as a separate cursor; indexes on the other collection are used by its leading $match. $densify and $fill with methods sort their input (or require it sorted) and hold partitions in memory, subject to the 100 MB stage limit unless allowDiskUse is enabled (default on since 6.0). Densifying a very wide range with a tiny step can create millions of documents, so filter first and choose bounds deliberately.

Avoid / preferDo this, not that

// two queries, merged and sorted in application code
const a = db.orders.find({ customerId: 66 }).toArray()
const b = db.events.find({ customerId: 66 }).toArray()

db.orders.aggregate([
  { $match: { customerId: 66 } }, { $project: { _id: 0, at: "$createdAt", what: "$status" } },
  { $unionWith: { coll: "events", pipeline: [{ $match: { customerId: 66 } }, { $project: { _id: 0, at: "$ts", what: "$type" } }] } },
  { $sort: { at: -1 } }, { $limit: 20 }
])

{ $densify: { field: "ts", range: { step: 1, unit: "second", bounds: "full" } } }

{ $match: { ts: { $gte: start, $lt: end } } },
{ $group: { _id: { $dateTrunc: { date: "$ts", unit: "minute" } }, n: { $sum: 1 } } },
{ $densify: { field: "_id", range: { step: 1, unit: "minute", bounds: [start, end] } } }

PitfallsCommon mistakes

• Forgetting that $unionWith does not deduplicate. Add a $group on a key afterwards if you need UNION semantics.
• Running $densify with bounds "full" and a one-second step over months of data, creating millions of documents.
• Using locf for measurements that should be interpolated (temperature) or linear for states that should be carried forward (on/off).
• Filling with 0 before densifying, so the new documents have no value at all.

LimitsWhen not to use it

Do not densify inside the database when the client can render gaps itself (many charting libraries can), or when the series is large and only a summary is needed. For combining many collections on every request, a single collection with a discriminator field is usually simpler and faster than a chain of $unionWith stages.

SecuritySecurity notes

$unionWith reads another collection with the caller's privileges, so a user needs find on every collection in the union. That is useful: building a report view with $unionWith does not let a user read data they could not read directly. When you expose such a pipeline as a view, grant access to the view and check the underlying grants.

ChallengeTry it yourself

db.orders.aggregate([
  { $match: { status: "paid", createdAt: { $gte: ISODate("2026-09-01"), $lt: ISODate("2026-09-08") } } },
  { $group: { _id: { $dateTrunc: { date: "$createdAt", unit: "day" } }, paid: { $sum: 1 } } },
  { $densify: { field: "_id", range: { step: 1, unit: "day", bounds: [ISODate("2026-09-01"), ISODate("2026-09-08")] } } },
  { $fill: { output: { paid: { value: 0 } } } },
  { $sort: { _id: 1 } }
])

InterviewQuestions you should be able to answer

QuizCheck your understanding

5 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.