Catalog My learning Blog
MongoDB Tutorial: Schema Design, Indexing and Sharding · Phase 3 · Lesson 15 · 3 min read

MongoDB $lookup and $group: Joining Data

On this page
  1. Joining collections in MongoDB
  2. $group accumulators
  3. $lookup: a left outer join
  4. $lookup with a pipeline
  5. Keeping $lookup fast
  6. When a $lookup is a schema design smell
  7. Check yourself

Joining collections in MongoDB

Revenue per customer lives in orders; customer names and countries live in customers (they're referenced, not embedded, Day 4). The report needs both. In MongoDB that's $group to summarize and $lookup to join.

$group accumulators

$group collapses documents that share a key into one, computing values with accumulators:

AccumulatorResult
$sumSum, or count with $sum: 1
$avg, $min, $maxAverage, minimum, maximum
$first, $lastFirst or last value in the current order (sort first)
$push, $addToSetArray of values, or of unique values
$top, $bottom, $topNThe top or bottom document(s) by a sort, per group

Group by several fields with a compound key: _id: { customer: "$customerId", month: { $month: "$createdAt" } }. _id: null groups everything into one document.

$lookup: a left outer join

JavaScript
db.orders.aggregate([
  { $match: { customerId: { $in: [8428, 48028] }, status: "paid" } },
  { $group: { _id: "$customerId", revenue: { $sum: "$total" } } },
  { $lookup: { from: "customers", localField: "_id", foreignField: "_id", as: "customer" } },
  { $unwind: "$customer" },
  { $project: { _id: 0, name: "$customer.name", country: "$customer.country", revenue: 1 } },
  { $sort: { revenue: -1 } }
])

Against test data on MongoDB 9:

Code
{ revenue: 2595, name: "Sam Lee", country: "DE" }
{ revenue: 2184, name: "Kai Moe", country: "NO" }

$lookup adds an array field (customer) with all matching documents, empty if none: a left outer join. $unwind turns the one-element array into a plain sub-document; add preserveNullAndEmptyArrays: true to keep rows without a match.

Orders are filtered and grouped per customer, then $lookup attaches each customer's document from the customers collection.
Orders are filtered and grouped per customer, then $lookup attaches each customer's document from the customers collection.

$lookup with a pipeline

The pipeline form lets you filter or reshape the joined documents:

JavaScript
{ $lookup: {
    from: "orders",
    localField: "_id", foreignField: "customerId",      // the join condition
    pipeline: [ { $match: { status: "paid" } }, { $sort: { createdAt: -1 } }, { $limit: 3 } ],
    as: "recentPaidOrders"
} }

Keeping $lookup fast

  • Index the foreign field. $lookup runs a query on foreignField for each input document. Without an index there (for example on orders.customerId), each lookup is a collection scan.
  • Reduce rows before joining. $match and $group first, as above: join 2 grouped rows, not 66,000 orders.
  • Limit what you pull in with the pipeline form or a $project afterwards.
  • $lookup into a sharded collection works in recent versions but crosses shards, so it costs more.

When a $lookup is a schema design smell

An occasional $lookup in a report is normal. If the application's hot path needs $lookup on every request, the model probably doesn't match the access pattern. Consider embedding a snapshot or an extended reference (Day 4): store customerName on the order if order lists always show it.

Check yourself

An empty array in the as field. $lookup is a left outer join, so the input document is always kept. Follow it with $match on a non-empty array if you only want documents that have a match.

That the foreignField in payments is indexed, since each order otherwise triggers a full scan of payments, and that the pipeline filters or groups orders before the lookup so it runs for fewer documents.

Use $lookup with a pipeline: match orders for that customer with status: "paid", sort by date descending, and $limit to 3. An index on { customerId: 1, status: 1, createdAt: -1 } makes each inner lookup fast.

When a common page needs a join on every request, the data that's read together is stored apart. Embedding or copying the needed fields usually serves that access pattern better.

$sum, $avg, $min, $max, $count, $push to collect values into an array, and $first or $last after sorting.