Aggregation Pipeline 1: $match, $group, $project, $unwind, $lookup and $facet

Version note: The examples in this post were run with MongoDB 9.0.8 Community Server and mongosh 2.6. All results came out the same on MongoDB 8.0.38; only the explain plans differ, shown in the relevant section.

Guess First

Bookstore's admin screen, Bookstore being the online bookstore used as the shared example across this site's series, shows monthly revenue. In the window functions post of the SQL Temelleri series, the same data gave 8959.20 for January 2025. Let's write two pipelines; both sum the total field of non-cancelled orders per month, and one has a $unwind in between:

// 07 - guess first: monthly revenue, Q1 2025 (SQL Temelleri 14: January 8959.20)
const months = { $match: { status: { $ne: "Cancelled" },
    orderedAt: { $gte: ISODate("2025-01-01"), $lt: ISODate("2025-04-01") } } };
const month = { $dateTrunc: { date: "$orderedAt", unit: "month" } };

const a = db.orders.aggregate([
    months,
    { $unwind: "$items" },
    { $group: { _id: month, revenue: { $sum: "$total" } } },
    { $sort: { _id: 1 } }
]).toArray();

const b = db.orders.aggregate([
    months,
    { $group: { _id: month, revenue: { $sum: "$total" } } },
    { $sort: { _id: 1 } }
]).toArray();

The developer who wrote A thought "let me open up the items, maybe I will break it down by book later". Do the two columns come out the same?

2025-01  A:  16664.70  B:   8959.20
2025-02  A:  27664.60  B:  13727.50
2025-03  A:  44839.10  B:  20367.60

B matches the SQL result to the cent. A is almost double, because $unwind split each order into as many documents as it has items, and each copy carried the order's total: the amount of an order with three items was counted three times. It is the same mistake as joining orders with order_items in SQL and summing o.total. In a pipeline each stage sees the output of the previous one, and after $unwind the assumption "one document = one order" no longer holds. In this post we go through the core stages of the aggregation pipeline on the Bookstore data, side by side with the GROUP BY results from SQL Temelleri. The scripts run as the application user and change nothing; the $lookup examples assume the indexes built by the 06-bookstore-indexes.js script from post 6.

Thinking in Pipelines and the SQL Counterparts

An SQL query is a single statement; the written order (SELECT first) differs from the logical processing order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY). In an aggregation pipeline the order you write is the processing order: documents pass through each stage from left to right, and each stage produces a new stream of documents. Roughly, the counterparts are:

$project picks fields, computes new ones and drops those not listed; $set (formerly $addFields) adds or changes fields on the existing document; $unset removes fields. In everyday work $set and $unset cause fewer surprises, because they keep the rest of the document.

The order you write is the processing order, but the optimizer can rearrange it as long as the result does not change. Let's deliberately write the $match after a $set:

// Written with $set first and $match second
const pipeline = [
    { $set: { year: { $year: "$orderedAt" } } },
    { $match: { "customer.customerId": 1 } },
    { $group: { _id: "$year", orders: { $sum: 1 }, spent: { $sum: "$total" } } },
    { $sort: { _id: 1 } }
];
print("plan:", plan(db.orders.explain().aggregate(pipeline)));
db.orders.aggregate(pipeline).forEach(r => print(" ", r._id, "orders", r.orders, "spent", r.spent));

// A $match on a field computed in the pipeline cannot move before the stage that computes it
print("plan:", plan(db.orders.explain().aggregate([
    { $set: { year: { $year: "$orderedAt" } } },
    { $match: { year: 2026 } },
    { $count: "orders" }
])));

plan: GROUP <- PROJECTION_DEFAULT <- PROJECTION_SIMPLE <- FETCH <- IXSCAN  then $sort
  2025 orders 25 spent Decimal128('21416.29')
  2026 orders 14 spent Decimal128('13717.95')
plan: PROJECTION_DEFAULT <- GROUP <- MATCH <- PROJECTION_DEFAULT <- PROJECTION_SIMPLE <- COLLSCAN

The plan helper (at the top of the script) prints the chain of stages from explain. In the first pipeline the $match was moved in front of the $set and became an IXSCAN on the { customerId, orderedAt } index from post 6, because customer.customerId is not a field computed by the $set. In the second, the filter looks at the year field the $set produces; the optimizer cannot move it forward, and the whole collection is scanned. The lesson: write filters on raw fields where you can; for "the year 2026" a date range on orderedAt uses an index, $year does not. The documentation lists the optimizer's other rearrangements too, such as combining $sort with $limit, merging consecutive $match stages and folding a $unwind that directly follows a $lookup into the $lookup.

The difference on MongoDB 8.0: the results and the index use are the same, but the 8.0.38 plan was PROJECTION_SIMPLE <- FETCH <- IXSCAN then $set, $group, $sort, meaning $set and $group ran in the classic aggregation layer. 9.0.8 moved them into the query engine as well. In the second pipeline 8.0 also did a COLLSCAN.

$group and Accumulators

$group groups documents by the _id expression; each output field is an accumulator. The counterpart of the "books per category, cheapest and most expensive" query from the GROUP BY post of SQL Temelleri, with an addition SQL does not have:

// SQL Temelleri 10: GROUP BY category_id with COUNT, MIN, MAX
db.books.aggregate([
    { $group: {
        _id: "$category.categoryId",
        name: { $first: "$category.name" },
        books: { $sum: 1 },
        cheapest: { $min: "$listPrice" },
        priciest: { $max: "$listPrice" },
        top: { $top: { sortBy: { listPrice: -1 }, output: "$title" } }
    } },
    { $sort: { books: -1, _id: 1 } },
    { $limit: 4 }
]).forEach(c => print(String(c._id).padStart(4), (c.name ?? "-").padEnd(16), String(c.books).padStart(3),
    c.cheapest.toString().padStart(8), c.priciest.toString().padStart(8), " ", c.top));

// HAVING: a $match after $group filters the groups
print("customers with 20+ orders:");
db.orders.aggregate([
    { $group: { _id: "$customer.customerId", name: { $first: "$customer.name" }, orders: { $sum: 1 } } },
    { $match: { orders: { $gte: 20 } } },
    { $sort: { orders: -1 } }
]).forEach(c => print(" ", String(c._id).padStart(2), c.name.padEnd(18), c.orders));

// $avg of int values returns a double
printjson(db.reviews.aggregate([
    { $group: { _id: null, avg: { $avg: "$rating" }, total: { $sum: "$rating" }, reviews: { $count: {} } } },
    { $project: { _id: 0, avg: { $round: ["$avg", 6] }, total: 1, reviews: 1 } }
]).next());

   2 Classics          29   200.00   680.50   Emma
   3 Science Fiction    9   390.50   660.90   Foundation
  11 Programming        9   890.00  1450.00   Design Patterns
   5 Fantasy            6   200.50   600.00   The Fellowship of the Ring
customers with 20+ orders:
   1 Ada Lovelace       39
   3 Grace Hopper       33
  32 Ivan Sutherland    30
  21 Annie Easley       26
  14 John McCarthy      24
   2 Alan Turing        22
  30 Jean Bartik        22
  22 Joan Clarke        21
{
  total: 745,
  reviews: 181,
  avg: 4.116022
}

The numbers are the same as on the SQL side: Classics has 29 books, between 200.00 and 680.50. Two differences are instructive. In SQL, writing GROUP BY c.category_id and putting c.name in the SELECT raised an error; here the category name sits in the document as a copy, so $first picks it up and no join is needed. $top gives "the name of the group's most expensive book" with a single accumulator, which needs a separate window function or a subquery in SQL; it has siblings such as $topN, $bottom and $firstN. The counterpart of HAVING is a $match after the $group. The last output is the average review rating: in SQL Server AVG(rating) on an integer column returned 4 and needed a cast for the right result; $avg returns a double for int values as well, and 4.116022 is the same as AVG(rating * 1.0) in SQL.

$unwind: Opening an Array into Rows

The correct use of the mistake from "Guess First": sales per book. Here $unwind is needed, because the grouping key is inside the item. We compute the amount per item from the item, not from the order's total:

// Best sellers: units sold per book (cancelled orders excluded)
db.orders.aggregate([
    { $match: { status: { $ne: "Cancelled" } } },
    { $unwind: "$items" },
    { $group: { _id: "$items.bookId", title: { $first: "$items.title" }, units: { $sum: "$items.quantity" },
                revenue: { $sum: { $multiply: ["$items.quantity", "$items.unitPrice"] } } } },
    { $sort: { units: -1, _id: 1 } },
    { $limit: 3 }
]).forEach(b => print(String(b._id).padStart(3), b.title.padEnd(22), "units", b.units, " revenue", b.revenue.toString()));

// Books with an empty authors array disappear after $unwind unless preserveNullAndEmptyArrays is set
for (const preserve of [false, true]) {
    const n = db.books.aggregate([
        { $unwind: { path: "$authors", preserveNullAndEmptyArrays: preserve } },
        { $count: "n" }
    ]).next().n;
    print(`preserveNullAndEmptyArrays: ${preserve} -> ${n} documents (books: ${db.books.countDocuments()})`);
}

 26 Dune                   units 32  revenue 12222.65
 35 Nineteen Eighty-Four   units 30  revenue 15449.19
 23 Animal Farm            units 28  revenue 7644.00
preserveNullAndEmptyArrays: false -> 83 documents (books: 77)
preserveNullAndEmptyArrays: true -> 85 documents (books: 77)

Revenue was computed inside the item as quantity * unitPrice; this is where storing the item price as a snapshot of the order moment, back in post 4, pays off. The second output is $unwind's silent behavior: the 77 books grew to 83 documents because of books with several authors, but the two books with no author (Beowulf and One Thousand and One Nights, authors: []) vanished completely. It is like the difference between INNER JOIN and LEFT JOIN in SQL: preserveNullAndEmptyArrays: true passes a document with an empty or missing array through once, without the field, and the count becomes 85. Whether you want to lose these two books in a "books per author" report is your decision.

$lookup: Simple and with a Pipeline

In post 4 we built the model to embed data that is read together, and set aside reports and infrequent reads as the place for $lookup. Revenue per category is exactly such a report: the order item has no category, so we need to look at the book.

// The order item has no category: look up the book for it
db.orders.aggregate([
    { $match: { status: { $ne: "Cancelled" }, orderedAt: { $gte: ISODate("2025-01-01"), $lt: ISODate("2026-01-01") } } },
    { $unwind: "$items" },
    { $lookup: { from: "books", localField: "items.bookId", foreignField: "_id", as: "book" } },
    { $unwind: "$book" },
    { $group: { _id: { $ifNull: ["$book.category.name", "(none)"] },
                revenue: { $sum: { $multiply: ["$items.quantity", "$items.unitPrice"] } } } },
    { $sort: { revenue: -1 } },
    { $limit: 3 }
]).forEach(c => print(c._id.padEnd(16), c.revenue.toString().padStart(10)));

// Pipeline $lookup: only the latest 2 reviews of each book, only the fields the page needs
printjson(db.books.aggregate([
    { $match: { _id: 26 } },
    { $lookup: {
        from: "reviews",
        localField: "_id",
        foreignField: "bookId",
        pipeline: [
            { $sort: { reviewedAt: -1 } },
            { $limit: 2 },
            { $project: { _id: 0, rating: 1, by: "$customer.name" } }
        ],
        as: "latestReviews"
    } },
    { $project: { _id: 0, title: 1, latestReviews: 1 } }
]).next());

Classics           74029.97
Science Fiction    41803.66
Programming        38160.00
{
  title: 'Dune',
  latestReviews: [
    {
      rating: 5,
      by: 'Adele Goldberg'
    },
    {
      rating: 5,
      by: 'Frances Allen'
    }
  ]
}

The result of $lookup is always an array; since we expect a single book, a $unwind follows right after, and the optimizer combines the two into one stage. The second example is the short syntax supported since 5.0: a sub-pipeline next to the localField/foreignField equality. Instead of pulling all reviews from the foreign collection and trimming them afterwards, the sort, limit and projection run on the reviews' side. It is the alternative to the subset pattern from post 5: subset keeps the copy at write time, the pipeline $lookup computes it on every read. Subset for a frequently read screen such as the book page, $lookup for a screen that is rarely opened.

How the join runs shows up in explain. I ran a $lookup that joins every book with its reviews, with and without the index on reviews.bookId:

with index   : IndexedLoopJoin  docsExamined 258  keysExamined 181
without index: HashJoin  docsExamined 258  keysExamined 0

Both plans contain an EQ_LOOKUP stage. With the index, each book was looked up in the index (IndexedLoopJoin). Without the index, the 181 reviews were read once and matched in memory (HashJoin). At this size both are cheap, but the in-memory table grows with the foreign collection, and the documentation explicitly warns that an equality $lookup without an index on foreignField will likely perform poorly. A $lookup with a pipeline does not run in this engine, and the foreignField index matters just as much for it.

$facet: the Search Page in One Query

The search page shows a result list, a total count and filter counts in the sidebar (category, language, price range). Instead of five separate queries, $facet runs several sub-pipelines on the same input:

// 07 - $facet: one query for the search page: results, total and filter counts
const result = db.books.aggregate([
    { $match: { isActive: true, listPrice: { $lte: Decimal128("600") } } },
    { $facet: {
        results: [
            { $sort: { listPrice: 1, _id: 1 } },
            { $limit: 3 },
            { $project: { _id: 0, title: 1, listPrice: 1 } }
        ],
        total: [{ $count: "books" }],
        byCategory: [{ $sortByCount: { $ifNull: ["$category.name", "(none)"] } }, { $limit: 3 }],
        byLanguage: [{ $sortByCount: "$language" }],
        byPrice: [{ $bucket: { groupBy: "$listPrice", boundaries: [0, 300, 450, 601], default: "other",
                               output: { books: { $sum: 1 } } } }]
    } }
]).next();

results: Crime and Punishment (200.00), Peter Pan (200.00), A Wizard of Earthsea (200.50)
total: 53
byCategory: Classics 25, Science Fiction 6, Science 5
byLanguage: en 50, tr 3
byPrice: [0..) 14, [300..) 17, [450..) 22

All the data for the page came back in one request. $sortByCount is shorthand for a $group plus a $sort; $bucket distributes values into ranges by the given boundaries, and values outside them land in the default bucket. Keep two constraints in mind. First, the sub-pipelines inside $facet cannot use indexes; put the filter in the $match before the $facet, where the index can work. Second, the output of $facet is a single document subject to the 16 MB limit; returning large result lists from sub-pipelines without $limit is a short path to hitting it. For the relevance and typo tolerance of a real search engine, the $searchMeta stage of MongoDB Search, mentioned in post 6, computes facets from its own index.

In Production

Know the memory limits. There is a 100 MB limit per stage; since allowDiskUseByDefault has been on by default since 6.0, stages such as $group and $sort that exceed it write temporary files, and the query runs but slows down. There are exceptions that cannot spill: the $push accumulator raises an error if it exceeds 100 MB. 9.0 added a total memory limit per query on top of these; the details and an experiment are in post 8.

Do not tie heavy reports to screen requests. If the monthly revenue or category report scans every order each time a page opens, it is better to precompute the result into a collection (a materialized view with $merge, post 8) or to read the report from a secondary (post 12).

Read pipelines with explain too. db.collection.explain("executionStats").aggregate(...) shows index use, the $lookup strategy and which stages run in the query engine and which in the classic layer. In a slow pipeline, the first things to check are whether the $match comes first and how many documents leave the first stages.

Common Pitfalls

Blowing up the document count with $unwind + $group. As in "Guess First", summing top-level totals after $unwind multiplies the result; with large arrays, memory and time multiply too. Open an array only when the grouping key is inside it; if you want an order-level total, use $sum: "$total" directly, or compute it with array expressions such as $sum and $reduce over the array without opening the document.

Using $lookup on every request like a relational join. Building the book page with four $lookup stages across books, authors, categories and reviews on every visit is the aggregation version of the relational copy we measured in post 4. For frequently read screens the right tool is the model: embedding, extended references or subsets. $lookup is for reports and infrequent reads.

Filtering on a computed field. Filtering on a field produced by $set stops the optimizer from moving the $match forward and takes the index out of play; if there is a way to write the same condition on a raw field, use it.

A Small Experiment

★☆☆ For each month of 2025, compute the number of orders, the number of cancelled orders and the cancellation rate with a single $group ($cond for the conditional sum). Compare it with the conditional aggregation query in SQL Temelleri.

★★☆ Find the category each customer spent the most on: open the items, $lookup the book, sum per customer and category, then pick the highest per customer with $top. Show with explain which part of the pipeline uses an index.

★★★ Add an "author" facet to the search page (the authors array, with books that have an empty array counted as "(anonymous)") and add paging to the result list. Measure the cost of $skip as the page number grows, as in post 3, and discuss when splitting it into two separate queries instead of $facet would be better.

In the next post we move on to the advanced stages of the pipeline: running totals and moving averages with $setWindowFields (side by side with the window function results from SQL Temelleri), the category tree with $graphLookup, empty days with $densify and $fill, materialized views with $merge, and the per-query memory limit in 9.0.