MongoDB Unwind and Expanded Results

$unwind is one of the first aggregation stages people learn, and it’s behind a large share of wrong numbers in MongoDB reports. It’s not buggy. It does exactly one thing: takes a document with an array and outputs one document per element. Trouble starts when the rest of the pipeline forgets that every other field has been copied too.

Examples are mongosh for MongoDB 7 and 8. Outputs are illustrative; no server was available while writing.

What it does

db.orders.insertMany([
  { _id: 1, customer: "ana", shipping: 5, items: [
      { sku: "A1", qty: 2, price: 10 },
      { sku: "B2", qty: 1, price: 30 },
      { sku: "C3", qty: 1, price: 15 } ] },
  { _id: 2, customer: "ben", shipping: 5, items: [
      { sku: "A1", qty: 1, price: 10 } ] },
  { _id: 3, customer: "cleo", shipping: 0, items: [] }
])

db.orders.aggregate([{ $unwind: "$items" }])

{ _id: 1, customer: "ana", shipping: 5, items: { sku: "A1", qty: 2, price: 10 } }
{ _id: 1, customer: "ana", shipping: 5, items: { sku: "B2", qty: 1, price: 30 } }
{ _id: 1, customer: "ana", shipping: 5, items: { sku: "C3", qty: 1, price: 15 } }
{ _id: 2, customer: "ben", shipping: 5, items: { sku: "A1", qty: 1, price: 10 } }

Three things happened. Order 1 became three documents, each with the same _id, customer and shipping. items is no longer an array; in each copy it’s a single element. And order 3, with an empty array, vanished.

Double counting parent fields

Now someone writes a revenue report:

db.orders.aggregate([
  { $unwind: "$items" },
  { $group: {
      _id: null,
      itemRevenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } },
      shippingRevenue: { $sum: "$shipping" },
      orders: { $sum: 1 }
  } }
])
// { _id: null, itemRevenue: 75, shippingRevenue: 20, orders: 4 }

itemRevenue is right: 20 + 30 + 15 + 10 = 75. The other two are wrong. Shipping should be 10 (5 + 5 + 0), but order 1’s shipping was counted three times. There are three orders, not four. The pipeline counted line items and summed shipping per line item.

This is the core interview point: after $unwind, the grain of the stream is “one per element”, and any field from the parent is repeated. Summing it, counting rows or averaging it gives element-weighted results.

Fixes, depending on what you need:

Don’t unwind for per-order totals. Array expressions compute per-document values without expanding:

db.orders.aggregate([
  { $project: {
      shipping: 1,
      itemTotal: { $sum: { $map: { input: "$items", as: "i",
                    in: { $multiply: ["$$i.qty", "$$i.price"] } } } }
  } },
  { $group: { _id: null, itemRevenue: { $sum: "$itemTotal" },
              shippingRevenue: { $sum: "$shipping" }, orders: { $sum: 1 } } }
])
// { _id: null, itemRevenue: 75, shippingRevenue: 10, orders: 3 }

Count distinct parents if you must unwind: $addToSet: "$_id" and then $size, or group by _id first and then group again. That works, but for large data $addToSet grows in memory.

Group back per parent when you need element-level work followed by order-level totals: $unwind, filter or transform, then $group: { _id: "$_id", ... } to return to one document per order with $first for the parent fields.

Where unwinding is the right tool

Unwind shines when the question is genuinely about elements:

// units sold per SKU
db.orders.aggregate([
  { $unwind: "$items" },
  { $group: { _id: "$items.sku", units: { $sum: "$items.qty" } } },
  { $sort: { units: -1 } }
])
// { _id: "A1", units: 3 }, { _id: "B2", units: 1 }, { _id: "C3", units: 1 }

The grain you want (per SKU) comes from the elements, and nothing from the parent is being summed. That’s a safe use.

Disappearing documents

By default, $unwind drops documents where the field is missing, null or an empty array. Order 3 disappeared above. A report “orders per customer” built on unwind would never show Cleo. If you need them:

{ $unwind: { path: "$items", preserveNullAndEmptyArrays: true } }

Cleo’s order then comes through once with items missing. Now be careful again: a $sum: 1 counts that as one row, so “number of items” for Cleo becomes 1 instead of 0 unless you count $items conditionally.

The other option, includeArrayIndex: "pos", adds the element’s position, handy when order matters, such as “first item in each cart”.

Size and memory

Unwinding multiplies documents. An events collection with 1 million documents averaging 300 array entries becomes 300 million documents mid-pipeline. Every following stage processes that many, and a $group or $sort on them can spill to disk. So:

• $match before $unwind on parent fields to drop whole documents early.
• $project the array down to the fields you need before unwinding, so each copy is smaller.
• Use $filter to trim the array to relevant elements before unwinding, rather than unwinding everything and matching after.

{ $set: { items: { $filter: { input: "$items", as: "i", cond: { $eq: ["$$i.sku", "A1"] } } } } },
{ $unwind: "$items" }

There’s one place where the optimizer helps: $lookup immediately followed by $unwind of the joined field is coalesced, so the joined array never exists in full and can’t hit the 16 MB document limit.

Array operators that replace $unwind

A lot of pipelines unwind only to do something that an array expression could do in place, without changing the grain. A few worth knowing well:

• $size counts elements: { itemCount: { $size: "$items" } }. It errors on a missing field, so wrap it as { $size: { $ifNull: ["$items", []] } } when the array might be absent.
• $filter keeps matching elements: useful for “orders with more than two discounted items” when combined with $size.
• $map transforms each element, as in the revenue example above.
• $reduce folds an array into one value, for example concatenating SKUs into a string.
• $sortArray (5.2+) and $firstN/$lastN (5.2+) give you “top item by price” per document without unwinding and grouping back.

For example, the most expensive line per order:

db.orders.aggregate([
  { $project: { top: { $first: { $sortArray: { input: "$items", sortBy: { price: -1 } } } } } }
])
// { _id: 1, top: { sku: "B2", qty: 1, price: 30 } }
// { _id: 2, top: { sku: "A1", qty: 1, price: 10 } }
// { _id: 3 }   ($first of an empty array is missing, so top is omitted)

The unwind version would be $unwind, $sort, $group by _id with $first, which works but multiplies documents, needs a blocking sort over all of them, and drops order 3 unless you remember preserveNullAndEmptyArrays. The array version keeps one document per order throughout. A useful rule of thumb in interviews: if your pipeline unwinds and then groups by _id again, check whether an array operator does the job.

Interview practice

A dashboard shows “average order value” with this pipeline and reports $41.67 while finance says it’s $45. Orders have a total field and an items array.

db.orders.aggregate([
  { $match: { status: "paid" } },
  { $unwind: "$items" },
  { $match: { "items.category": { $ne: "gift-card" } } },
  { $group: { _id: null, avg: { $avg: "$total" } } }
])

What’s going wrong, and how should it be written?

Answer

The $avg runs over the unwound stream, so each order’s total is counted once per non-gift-card line item. Orders with many items pull the average toward their totals; orders with only gift cards drop out entirely. The result is an item-weighted average of order totals, not the average order value.

The $match on category also signals a second question: is the intent “average across orders that contain at least one non-gift-card item” or “average order value excluding gift cards”? Those differ. For the first, filter whole orders without unwinding:

db.orders.aggregate([
  { $match: { status: "paid", items: { $elemMatch: { category: { $ne: "gift-card" } } } } },
  { $group: { _id: null, avg: { $avg: "$total" } } }
])

For the second, compute a per-order value excluding gift cards with $filter and $sum, then average that per order. Either way, the grain stays one document per order, so each order counts once. I’d confirm which definition finance uses, and test with a small dataset including an order with several items and one with only gift cards, since those are exactly the cases that expose the bug.

Further reading

• MongoDB interview questions
• MongoDB MCQs
• MongoDB Aggregation Pipeline Stage Order
• MongoDB Multikey Indexes for Array Fields
• Official docs: $unwind (aggregation)