MongoDB aggregation pipeline: $match, $group, $lookup

An aggregation pipeline is an array of stages. Each stage takes the documents the previous one produced, changes them, and hands the result on. $match filters, $group collapses many documents into one per key, $lookup joins another collection, and the order you put them in decides whether the query uses an index, how much memory it needs, and whether it finishes in 40 milliseconds or fails with a memory error. The short version: filter first, group second, join last, and look at explain() before you trust any pipeline on a collection that will grow.

This guide builds one pipeline from scratch on a small shop dataset, explains what each common stage does, shows which stages can use an index and which cannot, and covers the 100 MB limit that catches people the first time a report runs against real data. It assumes you can already connect and run queries; if not, start with MongoDB connection strings for beginners.

What the pipeline is and how it differs from find()#

find() returns documents as they are stored, optionally filtered and projected. aggregate() can return something that was never stored: totals per customer, a count per day, orders joined with the customer who placed them. Anything you would write as GROUP BY, JOIN or a window function in SQL goes through the pipeline in MongoDB.

A pipeline is run with db.collection.aggregate([ stage1, stage2, ... ]). Documents flow through the stages in order, and each stage sees only what the one before it emitted. Two consequences follow, and most pipeline problems come back to one of them:

• A stage can only use fields that still exist at that point. If an early $project or $group drops a field, a later $match on it matches nothing, and no error tells you.
• Stages that need the whole input before they can emit anything ($group, $sort without an index, $bucket) hold documents in memory. Stages that pass documents through one at a time ($match, $project, $addFields, $limit) cost almost nothing.

The examples below use two collections. orders has one document per order, and customers has one per account:

db.orders.insertMany([  { customerId: 1, status: "paid", total: 42.5,    createdAt: ISODate("2026-10-01T09:12:00Z"),    items: [{ sku: "A-100", qty: 2, price: 10 }, { sku: "B-200", qty: 1, price: 22.5 }] },  { customerId: 2, status: "paid", total: 15,    createdAt: ISODate("2026-10-02T14:40:00Z"),    items: [{ sku: "A-100", qty: 1, price: 10 }, { sku: "C-300", qty: 1, price: 5 }] },  { customerId: 1, status: "refunded", total: 10,    createdAt: ISODate("2026-10-03T08:05:00Z"),    items: [{ sku: "A-100", qty: 1, price: 10 }] }])db.customers.insertMany([  { _id: 1, name: "Nino", plan: "pro" },  { _id: 2, name: "Dato", plan: "free" }])

Run these in mongosh. The same stage documents work unchanged from a driver; in Node.js, Python and Go the pipeline is simply an array of objects.

$match: filter first, and use an index#

$match takes the same query syntax as find(). It is the most important stage in the pipeline because it is the one that can use an index, and the only thing that can make everything after it cheaper.

db.orders.aggregate([  { $match: { status: "paid", createdAt: { $gte: ISODate("2026-10-01") } } }])

A $match at the very start of the pipeline is planned like a find() filter: if an index covers status and createdAt, MongoDB scans the index range instead of the whole collection. A $match later in the pipeline works on the output of earlier stages, so it cannot use a collection index at all. That is the reason for the rule "match early".

The optimiser helps in one case. If you write a $match after a $project or $addFields and the match only touches fields that were not changed by them, MongoDB moves the $match forward for you. It cannot do that if the match depends on a field the earlier stage computed, and you should not rely on it; write the pipeline in the order you want it to run.

Two details that bite:

• A plain $match compares a field with a value, not with another field. Field paths such as "$total" are only read in expressions. Use $expr when you need to compare two fields of the same document: { $match: { $expr: { $gt: ["$total", "$limit"] } } }. A $match with $expr cannot use an index for that comparison in the general case, so use it deliberately.
• Dates compare as dates only when they are stored as dates. If createdAt was inserted as an ISO string, $gte: ISODate(...) matches nothing. Check with db.orders.findOne() and look at the type before you write the range.

$group: one document per key#

$group collapses every incoming document that shares the same _id expression into a single output document, and computes accumulators over the group. Revenue per customer from paid orders:

db.orders.aggregate([  { $match: { status: "paid" } },  { $group: {      _id: "$customerId",      orders: { $sum: 1 },      revenue: { $sum: "$total" },      average: { $avg: "$total" },      lastOrder: { $max: "$createdAt" }  } },  { $sort: { revenue: -1 } }])

The _id of the group is whatever you want to group by. A field path ("$customerId"), a whole sub-document for a compound key ({ customer: "$customerId", day: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } } }), or null to collapse the whole input into a single document, which is how you compute a grand total.

These are the accumulators you will use most:

$push and $addToSet are the dangerous ones. They keep every value in memory for every group, and the finished group is one document with a 16 MB size limit. $group with _id: "$customerId" and $push: "$$ROOT" on a large collection is the standard way to hit both limits at once. If you only need the latest three orders per customer, use the top-N accumulators ($topN, $firstN) on a version that has them, rather than pushing everything and slicing afterwards.

A date report is the same stage with a different key. Orders per day:

db.orders.aggregate([  { $match: { createdAt: { $gte: ISODate("2026-10-01"), $lt: ISODate("2026-11-01") } } },  { $group: {      _id: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } },      orders: { $sum: 1 }  } },  { $sort: { _id: 1 } }])

The key is a string in UTC unless you pass timezone. If your reports are for people in one time zone, pass it (timezone: "Asia/Tbilisi"), or an order placed at 01:00 local time lands on the previous day.

$project, $addFields, $unwind and $count#

Four smaller stages do most of the reshaping around $match and $group.

$project keeps, drops or computes fields. { $project: { _id: 0, customerId: 1, total: 1 } } returns only those two. $addFields (and its alias $set) adds computed fields while keeping everything else, which is usually what you want mid-pipeline.

$unwind turns an array field into one document per element. It is how you aggregate over items:

db.orders.aggregate([  { $match: { status: "paid" } },  { $unwind: "$items" },  { $group: {      _id: "$items.sku",      units: { $sum: "$items.qty" },      revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } }  } },  { $sort: { revenue: -1 } },  { $limit: 5 }])

$unwind multiplies documents, so put it after the $match and keep an eye on the fan-out: a collection of a million orders with ten items each becomes ten million documents passing through the next stage. Documents whose array is missing or empty are dropped unless you write { $unwind: { path: "$items", preserveNullAndEmptyArrays: true } }.

$count is a stage that emits one document with the number of inputs: { $count: "paidOrders" }. $sort, $limit and $skip work as in find(), with one difference that matters later: $sort followed directly by $limit is optimised into a top-k sort that keeps only k documents in memory.

$lookup: joining collections#

$lookup is MongoDB's left outer join. For each incoming document it fetches matching documents from another collection and attaches them as an array:

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

as is always an array, even when exactly one document matches, which is why a $unwind follows. Leave it out and you get customer: [ { ... } ], which surprises people once. If no document matches, the array is empty, and the $unwind above would then drop the order. Add preserveNullAndEmptyArrays: true if you want the order to survive a missing customer.

Three rules keep a $lookup fast:

1. Index the foreign field. For every incoming document, $lookup runs a query on the other collection. On _id that is already indexed. On customerId in orders (when you join in the other direction) it is not, and without an index each lookup scans the whole collection: a thousand incoming documents against a hundred thousand foreign ones is a hundred million comparisons.
2. Cut the input first. Every document that reaches $lookup costs a query. $match and $limit before it, never after.
3. Join in the direction that starts from the small side. Starting from the few documents that survive a filter and looking up into the big collection through an index is cheap. The reverse is not.

When you need to filter or reshape the joined documents, use the pipeline form, with let to pass local fields in:

db.customers.aggregate([  { $match: { plan: "pro" } },  { $lookup: {      from: "orders",      let: { cid: "$_id" },      pipeline: [        { $match: { $expr: { $eq: ["$customerId", "$$cid"] }, status: "paid" } },        { $project: { _id: 0, total: 1, createdAt: 1 } },        { $sort: { createdAt: -1 } },        { $limit: 3 }      ],      as: "recentOrders"  } }])

Index use inside a pipeline-form $lookup is more limited than in the simple form. An $expr equality on a field can use an index, but you should confirm it with explain() rather than assume; that is covered below. For a join you run constantly, the answer is often not a faster $lookup but a different schema: embed what you always read together, as MongoDB schema design and indexes describes. A $lookup is a fine tool for reporting and an expensive one for the hot path of a web request.

The joined array also lives inside a single document, so the 16 MB document limit applies to it. Joining a customer to every order they have ever placed, with no $limit, will eventually fail on the customer who has the most orders.

Which stages use an index#

Only the stages at the front of a pipeline can touch an index, and only some of them:

The reasoning is simple: an index is a structure over stored documents. The moment a stage changes the shape of the stream, there is nothing left to point an index at. This is why the order "match, sort, group, lookup" is the default, and why a pipeline that begins with $project or $addFields and then filters is a warning sign.

For a pipeline that filters on equality and sorts, the same compound index rules apply as for find(): equality fields first, then sort, then range. The ESR rule explanation covers how to order the fields. For the paid-orders-by-date report above:

db.orders.createIndex({ status: 1, createdAt: -1 })

serves both the $match on status and a range on createdAt, and delivers documents already sorted by date if the pipeline asks for it.

Reading explain() for a pipeline

Do not guess whether the index was used. Ask:

db.orders.explain("executionStats").aggregate([  { $match: { status: "paid", createdAt: { $gte: ISODate("2026-10-01") } } },  { $group: { _id: "$customerId", revenue: { $sum: "$total" } } }])

In the output, find the input stage. IXSCAN with an index name means the index was used. COLLSCAN means every document was read. Compare totalDocsExamined with nReturned at the first stage: examining 500,000 documents to return 800 means the filter is not selective for the index you have, or there is none. On a pipeline with $lookup, the explain output shows the foreign collection's access separately, and a COLLSCAN there is the missing index on foreignField.

If the first stage reads $cursor with a queryPlanner block, that is the part that was pushed down to the query engine, and it is the only part an index can serve. Everything listed after it ran in the aggregation layer.

Plain explain() (no verbosity) only plans and never runs the pipeline, which is the safe choice against a production database. executionStats runs it, so on a large collection run it on a copy or with a $limit you would not otherwise have.

The 100 MB limit and allowDiskUse#

Each blocking stage in a pipeline may use up to 100 megabytes of RAM. Blocking stages are the ones that have to see all their input before emitting a result: $group, $sort (when no index provides the order), $bucket, $bucketAuto, $setWindowFields over big partitions and a few others. Stages that stream documents through, such as $match, $project and $addFields, do not count against it. The limit is per stage and not per pipeline, so a pipeline with a $group followed by a $sort can use 100 MB for each.

When a stage exceeds it, behaviour depends on the server version and on the allowDiskUse option:

• On MongoDB 6.0 and later, the server setting allowDiskUseByDefault is true unless the deployment turned it off, so a stage that outgrows 100 MB writes temporary files to disk and carries on, more slowly.
• On earlier versions, or where allowDiskUseByDefault is false, the stage fails with an error such as Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting or Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskUse: true as an option to let it spill.

db.orders.aggregate(  [    { $group: { _id: "$customerId", orders: { $push: "$_id" } } },    { $sort: { _id: 1 } }  ],  { allowDiskUse: true })

In a driver the same option goes in the options argument of aggregate(). Check your driver's documentation for the exact spelling, because it follows the language's conventions.

allowDiskUse is a safety net, not a fix. It changes a hard failure into a slow query: the stage writes sorted runs into the _tmp directory under the database path and merges them, which costs disk I/O and, on a small plan, competes with everything else the server is doing. It also needs free disk space in the data directory. A pipeline that spills routinely is telling you something, and the usual remedies are, in order:

1. Move $match earlier, or add the index it needs, so fewer documents reach the blocking stage.
2. Drop fields you do not need with $project before $group or $sort. A stage holds whole documents, and a 4 KB document with a 40-byte field you need costs a hundred times more than it should.
3. Put a $limit straight after a $sort, so only the top few are kept.
4. Replace $push: "$$ROOT" and similar with a small accumulator that holds one number.
5. For a report you run daily over millions of documents, precompute it into a summary collection with $merge (or $out) on a schedule and read the summary.

Two limits are separate from the 100 MB figure and allowDiskUse does nothing for them. A single document cannot exceed 16 MB, whether it is a stored one, a group that grew too large or the result of a $lookup. And $facet, which runs several sub-pipelines on the same input, holds its output in one document, so its whole result must fit in 16 MB.

The 100 MB limit also does not mean the server needs 100 MB of spare RAM for each stage: it is a ceiling, and a small $group uses a few kilobytes. But on a plan with 1 GB of memory, several users running large reports at once can use more than the WiredTiger cache and the operating system have to spare. The sizing section of MongoDB schema design and indexes explains how the cache and the rest of the process share memory.

A worked pipeline from slow to fast#

Take a monthly revenue report with the customer's name and plan, written the way it is easy to write:

db.orders.aggregate([  { $lookup: { from: "customers", localField: "customerId",               foreignField: "_id", as: "customer" } },  { $unwind: "$customer" },  { $match: { status: "paid", createdAt: { $gte: ISODate("2026-10-01") } } },  { $group: { _id: "$customer.plan", revenue: { $sum: "$total" }, orders: { $sum: 1 } } },  { $sort: { revenue: -1 } }])

It returns the right answer and is wrong in three ways. The $match comes after the $lookup, so the join runs for every order ever placed. The $match fields are on the orders, so the optimiser can often move it forward, but that is a rewrite you should make yourself. And the $lookup copies the whole customer document onto each order before $group, which only needs plan.

Rewritten:

db.orders.aggregate([  { $match: { status: "paid", createdAt: { $gte: ISODate("2026-10-01") } } },  { $group: { _id: "$customerId", revenue: { $sum: "$total" }, orders: { $sum: 1 } } },  { $lookup: { from: "customers", localField: "_id",               foreignField: "_id", as: "customer" } },  { $unwind: "$customer" },  { $group: { _id: "$customer.plan", revenue: { $sum: "$revenue" }, orders: { $sum: "$orders" } } },  { $sort: { revenue: -1 } }])

The filter uses the { status: 1, createdAt: -1 } index. The first $group reduces the stream to one document per paying customer before the join, so $lookup runs once per customer and not once per order. The second $group rolls those up by plan. Run explain("executionStats") on both and the difference is in totalDocsExamined and in the time per stage.

Running aggregations safely on a live database#

A few habits keep a report from taking the site down with it.

• Try it on a limited input first. Put { $limit: 1000 } straight after the $match while you develop, and remove it when the result is right.
• Set a time limit. maxTimeMS on the command stops a runaway pipeline: db.orders.aggregate([...], { maxTimeMS: 20000 }). A report that cannot finish in twenty seconds should not be a web request.
• Find and kill what is running. db.currentOp() lists operations and db.killOp(opid) stops one, as described in the profiler section of MongoDB schema design and indexes.
• Use a dedicated user for reports. Give it the read role on the one database, so a typo in a pipeline cannot reach $out or $merge into a live collection. MongoDB users and roles covers the role to use.
• Back up before any `$out` or `$merge`. $out replaces the target collection when the pipeline finishes. Take a mongodump first if the target is something you care about.

On RE:NODE, a MongoDB plan comes with credentials generated for you and backup slots you can run on demand or on the Schedules tab, which is the sensible way to take a backup before a pipeline that writes. The plan's memory is a hard limit: a server that runs out of it is stopped and restarted clean rather than left to swap, so a very large in-memory $group on a small plan is a reason to rethink the pipeline before a reason to upgrade. The MongoDB database hosting page lists the tiers.

Troubleshooting#

`Exceeded memory limit for $group, but didn't allow external sort.` A $group went past 100 MB. Pass { allowDiskUse: true }, then do the real fix: filter earlier, project away unused fields, avoid $push of whole documents.

`Sort exceeded memory limit of 104857600 bytes`. The same limit on a $sort with no index behind it. Add an index that matches the sort, or put a $limit after it.

`BSONObj size: ... is invalid` or "document exceeds maximum size 16777216". A group or a $lookup array became bigger than 16 MB. Limit what you collect, join with a narrower pipeline, or restructure the data.

The pipeline returns nothing and no error. Usually a field that no longer exists at that stage, a $unwind on a field that is missing, or a date stored as a string. Cut the pipeline back to its first stage, check the output, and add stages one at a time.

A `$lookup` is very slow. explain("executionStats") and look for COLLSCAN on the foreign collection. Index foreignField, and reduce the number of incoming documents.

`$match` is slow despite an index. It is probably not the first stage, or the filter has a type mismatch (a number against a string), or it starts with a negation or a regular expression with a leading wildcard. Look at the IXSCAN bounds in explain().

Results differ between runs. $group does not return groups in any particular order, and $first and $last depend on the incoming order. Add a $sort first when the result depends on order.

FAQ#

Is the aggregation pipeline faster than find()?

Not by itself. For a simple filter, find() and a pipeline that only has a $match do the same work. The pipeline is faster than doing the same job in application code, because grouping and joining happen inside the server and only the result crosses the network.

Does $match use an index?

Yes, when it is the first stage, or follows only stages the optimiser can move it past. A $match placed after a $group, $project that renames fields, or $unwind runs on transformed documents and cannot use a collection index.

What does allowDiskUse do?

It lets a blocking stage write temporary files to disk when it goes over the 100 MB memory limit, instead of failing. The query finishes, but slower. On MongoDB 6.0 and later the server default is already to allow it, unless an administrator changed allowDiskUseByDefault.

Can I join more than two collections?

Yes, by chaining $lookup stages, but each adds a query per incoming document, so the cost multiplies. If you chain more than two or three on a hot query, the data probably belongs in one document.

How is $lookup different from a SQL JOIN?

It is a left outer join that returns the matches as an array inside each document, not as extra rows. It runs one lookup per incoming document, and it is not a good fit for joining two very large collections in full. SQL databases are built for that; if your data is mostly relational, PostgreSQL or MongoDB is worth reading first.

When should I precompute instead of aggregating?

When the same expensive report is read far more often than the data changes. Run the pipeline on a schedule with $merge into a summary collection, and have the application read that. A dashboard that re-aggregates a million documents on every page load is the usual mistake.