$lookup, $unwind & $facet

The reports in the previous lesson showed customer IDs, not names, because orders only store customerId. To combine data from two collections you need $lookup, MongoDB's version of a join. Two more stages complete the toolkit: $unwind turns array elements into separate documents, and $facet runs several pipelines over the same data at once.

In this lesson you will add customer names to reports, total sales by city, find customers who never ordered, report per item, and build a catalogue summary in a single query. Start with freshly loaded data and use shop.

$lookup: joining another collection

$lookup takes each input document, finds matching documents in another collection and adds them as an array field. You name four things: the other collection (from), the field in the current document (localField), the field in the other collection (foreignField) and the name of the new array (as):

db.orders.aggregate([
  { $match: { _id: 1004 } },
  { $lookup: { from: 'customers', localField: 'customerId', foreignField: '_id', as: 'customer' } },
  { $project: { status: 1, total: 1, 'customer.name': 1, 'customer.city': 1 } }
])

[
  {
    _id: 1004,
    status: 'delivered',
    total: 4298,
    customer: [ { name: 'Kabir Singh', city: 'Rohtak' } ]
  }
]

The result is always an array, even when exactly one document matches, because in general a lookup could find many (or none). The from collection must be in the same database.

Each lookup runs a query on the other collection for every input document. Make sure foreignField is indexed; here it is _id, which always is. For a lookup on a field such as orders.customerId, create an index on it first.

$unwind: one document per array element

$unwind takes a document with an array and outputs one copy of the document per element, with the array field replaced by that single element. On an order with two items:

db.orders.aggregate([
  { $match: { _id: 1001 } },
  { $unwind: '$items' },
  { $project: { item: '$items.name', qty: '$items.qty' } }
])

[
  { _id: 1001, item: 'Wireless Mouse', qty: 1 },
  { _id: 1001, item: 'USB-C Cable', qty: 2 }
]

One order became two documents. This is how you report on items across all orders: unwind first, then $group by product, as in the exercise of The Aggregation Pipeline.

$unwind is also the usual partner of $lookup: unwinding a one-element customer array turns it into a plain embedded document, which is easier to use. Be aware that documents with an empty or missing array are dropped by $unwind. To keep them, use the long form: { $unwind: { path: '$customer', preserveNullAndEmptyArrays: true } }.

Top customers with names

Now the "top three customers" report from the last lesson can show names. Group first, then look up only the three winners, so the join runs three times instead of eleven:

db.orders.aggregate([
  { $match: { status: { $ne: 'cancelled' } } },
  { $group: { _id: '$customerId', spent: { $sum: '$total' } } },
  { $sort: { spent: -1, _id: 1 } },
  { $limit: 3 },
  { $lookup: { from: 'customers', localField: '_id', foreignField: '_id', as: 'c' } },
  { $unwind: '$c' },
  { $project: { _id: 0, name: '$c.name', city: '$c.city', spent: 1 } }
])

[
  { spent: 20997, name: 'Priya Nair', city: 'Kochi' },
  { spent: 11895, name: 'Aarav Sharma', city: 'Jaipur' },
  { spent: 6197, name: 'Kabir Singh', city: 'Rohtak' }
]

The order of stages matters for speed: reduce the data with $match, $group and $limit before an expensive $lookup whenever the result allows it.

Sales by city

Sometimes the field you group by lives in the other collection. Revenue by the customer's city needs the lookup before the group:

db.orders.aggregate([
  { $match: { status: { $ne: 'cancelled' } } },
  { $lookup: { from: 'customers', localField: 'customerId', foreignField: '_id', as: 'c' } },
  { $unwind: '$c' },
  { $group: { _id: '$c.city', revenue: { $sum: '$total' }, orders: { $sum: 1 } } },
  { $sort: { revenue: -1 } }
])

[
  { _id: 'Kochi', revenue: 20997, orders: 2 },
  { _id: 'Jaipur', revenue: 13993, orders: 4 },
  { _id: 'Rohtak', revenue: 6197, orders: 2 },
  { _id: 'Pune', revenue: 3946, orders: 2 },
  { _id: 'Lucknow', revenue: 1298, orders: 1 }
]

Jaipur has the most orders (Aarav's three and Farhan's one), but Kochi earns the most. If this report ran thousands of times a day, it would be a sign that the city should be copied into each order, a design choice discussed in Schema Design: Embed or Reference.

Finding documents with no match

Looking up from the other side, customers to orders, gives each customer an array of their orders. An empty array means no orders at all, the MongoDB version of a SQL LEFT JOIN ... WHERE ... IS NULL:

db.customers.aggregate([
  { $lookup: { from: 'orders', localField: '_id', foreignField: 'customerId', as: 'orders' } },
  { $match: { orders: { $size: 0 } } },
  { $project: { name: 1, city: 1 } }
])

[ { _id: 8, name: 'Isha Verma', city: 'Kochi' } ]

Isha has never ordered, so the marketing team might send her a welcome coupon.

Advanced: $lookup with a pipeline

A lookup can run its own small pipeline on the joined collection, to filter or reshape the matches before they are attached. Jaipur customers with only their pending orders:

db.customers.aggregate([
  { $match: { city: 'Jaipur' } },
  { $lookup: {
      from: 'orders',
      localField: '_id',
      foreignField: 'customerId',
      pipeline: [{ $match: { status: 'pending' } }, { $project: { total: 1 } }],
      as: 'pending'
  } },
  { $project: { name: 1, pending: 1 } }
])

[
  {
    _id: 1,
    name: 'Aarav Sharma',
    pending: [ { _id: 1011, total: 3499 } ]
  },
  {
    _id: 7,
    name: 'Farhan Khan',
    pending: [ { _id: 1010, total: 2098 } ]
  }
]

This form, with localField/foreignField and a pipeline, has worked since MongoDB 5.0. For conditions other than equality you can instead define variables with let and compare them inside the pipeline with $expr.

$facet: several reports in one query

A shop's category page usually shows several summaries at once: how many products are in each category, how many fall in each price band, and the top-rated items. $facet runs several sub-pipelines on the same input and returns all their results in one document:

db.products.aggregate([
  { $facet: {
      byCategory: [{ $group: { _id: '$category', count: { $sum: 1 } } }, { $sort: { count: -1, _id: 1 } }, { $limit: 3 }],
      priceBands: [{ $bucket: { groupBy: '$price', boundaries: [0, 1000, 5000, 20000], output: { count: { $sum: 1 } } } }],
      topRated: [{ $sort: { rating: -1 } }, { $limit: 2 }, { $project: { _id: 0, name: 1, rating: 1 } }]
  } }
])

[
  {
    byCategory: [
      { _id: 'Accessories', count: 5 },
      { _id: 'Audio', count: 1 },
      { _id: 'Books', count: 1 }
    ],
    priceBands: [
      { _id: 0, count: 3 },
      { _id: 1000, count: 5 },
      { _id: 5000, count: 2 }
    ],
    topRated: [
      { name: 'Learn Python Book', rating: 4.8 },
      { name: 'Portable SSD 1TB', rating: 4.7 }
    ]
  }
]

$bucket sorts values into ranges: _id: 0 is "₹0 up to but not including ₹1,000", and so on. Three products cost under ₹1,000, five cost ₹1,000 to ₹4,999 and two cost ₹5,000 or more. $facet is ideal for search pages with filters and counts. Remember that its single output document is subject to the 16 MB document limit, so keep each facet small with $limit or grouping.

Common mistakes

• Expecting an object from $lookup. The as field is always an array. Use $unwind or $first to get a single document.
• Losing documents in $unwind. Empty or missing arrays are dropped unless you set preserveNullAndEmptyArrays: true.
• Joining before reducing. Run $match, $group and $limit before $lookup when the result allows it.
• Using $lookup for data that is always read together. If every order page needs the customer name, consider storing it in the order instead of joining every time.

Key points

• $lookup adds matching documents from another collection in the same database as an array field.
• Index the foreignField of a lookup, and reduce documents before joining.
• $unwind outputs one document per array element and drops empty arrays unless told to keep them.
• A lookup followed by $match on an empty array finds documents with no related documents.
• $lookup can run its own pipeline to filter and shape the joined documents.
• $facet runs several pipelines on the same input and returns all results in one document.

db.products.aggregate([
  { $lookup: {
      from: 'orders',
      localField: '_id',
      foreignField: 'items.productId',
      pipeline: [{ $match: { status: { $ne: 'cancelled' } } }],
      as: 'orders'
  } },
  { $unwind: { path: '$orders', preserveNullAndEmptyArrays: true } },
  { $unwind: { path: '$orders.items', preserveNullAndEmptyArrays: true } },
  { $group: {
      _id: '$_id',
      name: { $first: '$name' },
      sold: { $sum: { $cond: [{ $eq: ['$orders.items.productId', '$_id'] }, '$orders.items.qty', 0] } }
  } },
  { $sort: { sold: -1, name: 1 } },
  { $project: { _id: 0, name: 1, sold: 1 } }
])