MongoDB Aggregation Pipeline: A Practical Example with Real Data

When an application stores thousands or millions of documents in MongoDB, retrieving records is only part of the job. You may also need to group sales by category, calculate total revenue, find the best-selling products, or summarize orders by month.

You could fetch all the documents into your application and calculate these results in C#. However, that approach can consume unnecessary memory and transfer more data than the application needs.

MongoDB's aggregation pipeline lets you filter, group, sort, and transform documents directly in the database. Each stage performs a specific operation, and the output of one stage becomes the input to the next.

In this article, we will build a practical sales-reporting example using MongoDB aggregation, explain the most useful stages, and cover common mistakes that can affect performance and accuracy.

What Is a MongoDB Aggregation Pipeline?

An aggregation pipeline is a sequence of processing stages that operate on documents.

For example, a sales report might need to:

Select orders that have been completed.
Group the orders by product category.
Calculate the total revenue for each category.
Count the orders in each category.
Sort the results by revenue.

Instead of implementing each calculation in application code, you can describe the entire operation as a pipeline.

The most commonly used stages include:

Stage	Purpose
$match	Filters documents
$group	Groups documents and calculates values
$project	Selects or reshapes fields
$sort	Sorts the results
$limit	Restricts the number of returned documents
$unwind	Expands an array into separate documents
$lookup	Combines documents from another collection
$set	Adds or updates fields

The order of stages matters. Filtering early can reduce the number of documents that later stages must process.

Step 1: Create a Sample Orders Collection

Imagine an online store that records orders in a MongoDB collection named orders.

Each document contains an order number, customer, status, date, and list of purchased items.

db.orders.insertMany([
  {
    orderNumber: "ORD-1001",
    customer: "Alice",
    status: "Completed",
    orderDate: ISODate("2026-01-10T10:00:00Z"),
    items: [
      { product: "Laptop", category: "Electronics", quantity: 1, price: 1200 },
      { product: "Mouse", category: "Accessories", quantity: 2, price: 25 }
    ]
  },
  {
    orderNumber: "ORD-1002",
    customer: "Bob",
    status: "Completed",
    orderDate: ISODate("2026-01-15T11:00:00Z"),
    items: [
      { product: "Laptop", category: "Electronics", quantity: 1, price: 1200 }
    ]
  },
  {
    orderNumber: "ORD-1003",
    customer: "Alice",
    status: "Pending",
    orderDate: ISODate("2026-01-18T09:00:00Z"),
    items: [
      { product: "Keyboard", category: "Accessories", quantity: 1, price: 80 }
    ]
  },
  {
    orderNumber: "ORD-1004",
    customer: "Charlie",
    status: "Completed",
    orderDate: ISODate("2026-02-05T14:00:00Z"),
    items: [
      { product: "Monitor", category: "Electronics", quantity: 2, price: 350 }
    ]
  }
]);

Each item has a quantity and price. To calculate item revenue, multiply quantity by price.

For this example, we will treat Completed orders as sales. Pending orders will not be included in the report.

In a production system, define clearly whether a completed order represents recognized revenue, especially if refunds, cancellations, taxes, shipping fees, or discounts must be included.

Step 2: Filter Completed Orders with $match

The first stage selects only completed orders.

db.orders.aggregate([
  {
    $match: {
      status: "Completed"
    }
  }
]);

The pipeline returns three documents from our sample data. The pending order is excluded.

Filtering early is a useful habit because it reduces the amount of data processed by subsequent stages.

For frequently used filters, consider creating an appropriate index:

db.orders.createIndex({ status: 1 });

Whether this index improves performance depends on how many documents match the status and what other queries use the collection.

Step 3: Calculate Revenue with $unwind and $group

An order can contain multiple items. If you want to calculate revenue by product category, you first need to process each item separately.

The $unwind stage expands the items array so that each item becomes part of an individual pipeline document.

Then $group collects the items by category and calculates revenue.

db.orders.aggregate([
  {
    $match: {
      status: "Completed"
    }
  },
  {
    $unwind: "$items"
  },
  {
    $group: {
      _id: "$items.category",
      totalRevenue: {
        $sum: {
          $multiply: [
            "$items.quantity",
            "$items.price"
          ]
        }
      },
      totalUnitsSold: {
        $sum: "$items.quantity"
      }
    }
  }
]);

The expected results are:

Category	Total revenue	Units sold
Electronics	3100	4
Accessories	50	2

The Electronics total comes from the laptop sales and monitor sale:

Laptop: 1 × 1200 + 1 × 1200 = 2400
Monitor: 2 × 350 = 700
Total: 3100

The Accessories total comes from two mice at 25 each.

Notice that the pending keyboard order does not appear because the pipeline filters orders before unwinding their items.

Step 4: Count Orders and Sort Results

Suppose the business also wants to know how many orders contain items from each category.

You can extend the pipeline:

db.orders.aggregate([
  {
    $match: {
      status: "Completed"
    }
  },
  {
    $unwind: "$items"
  },
  {
    $group: {
      _id: "$items.category",
      totalRevenue: {
        $sum: {
          $multiply: [
            "$items.quantity",
            "$items.price"
          ]
        }
      },
      totalUnitsSold: {
        $sum: "$items.quantity"
      },
      orderCount: {
        $addToSet: "$orderNumber"
      }
    }
  },
  {
    $project: {
      _id: 0,
      category: "$_id",
      totalRevenue: 1,
      totalUnitsSold: 1,
      orderCount: {
        $size: "$orderCount"
      }
    }
  },
  {
    $sort: {
      totalRevenue: -1
    }
  }
]);

Here, $addToSet collects unique order numbers for each category. The $project stage converts the resulting array into a count and gives the category field a clearer name.

The result is sorted from highest revenue to lowest.

For our sample data, Electronics has two orders containing electronic products, while Accessories appears in one completed order.

This distinction is important: counting documents after $unwind does not necessarily count original orders. An order with three items becomes three pipeline documents. Using a set of order numbers avoids counting the same order multiple times within a category.

Step 5: Group Sales by Month

Aggregation pipelines are also useful for reporting over time.

To calculate monthly revenue, group orders by year and month after filtering out orders that should not count as sales.

db.orders.aggregate([
  {
    $match: {
      status: "Completed"
    }
  },
  {
    $unwind: "$items"
  },
  {
    $group: {
      _id: {
        year: {
          $year: "$orderDate"
        },
        month: {
          $month: "$orderDate"
        }
      },
      totalRevenue: {
        $sum: {
          $multiply: [
            "$items.quantity",
            "$items.price"
          ]
        }
      }
    }
  },
  {
    $sort: {
      "_id.year": 1,
      "_id.month": 1
    }
  }
]);

This pipeline groups item revenue by the year and month of the order date.

For the sample data, January revenue is 2450, and February revenue is 700.

The example uses MongoDB's default date-part behavior. If the business operates in a particular time zone, use the appropriate time-zone options for date calculations so that orders near midnight are assigned to the intended month.

Step 6: Find the Best-Selling Products

You can also group sales by product rather than category.

db.orders.aggregate([
  {
    $match: {
      status: "Completed"
    }
  },
  {
    $unwind: "$items"
  },
  {
    $group: {
      _id: "$items.product",
      unitsSold: {
        $sum: "$items.quantity"
      },
      revenue: {
        $sum: {
          $multiply: [
            "$items.quantity",
            "$items.price"
          ]
        }
      }
    }
  },
  {
    $sort: {
      revenue: -1
    }
  },
  {
    $limit: 5
  },
  {
    $project: {
      _id: 0,
      product: "$_id",
      unitsSold: 1,
      revenue: 1
    }
  }
]);

This pipeline calculates revenue for each product, sorts the products by revenue, and returns up to five results.

The $limit stage is useful when a report only needs the highest-performing products rather than every product in the collection.

If two products have the same revenue and the order matters, add a secondary sort field to make the result ordering deterministic.

Step 7: Join Customer Data with $lookup

Sometimes an order contains only a customer identifier, while customer details are stored in another collection.

The $lookup stage can combine data from two collections.

For example, suppose the customers collection contains documents with an _id and name.

db.orders.aggregate([
  {
    $match: {
      status: "Completed"
    }
  },
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customerDetails"
    }
  },
  {
    $unwind: {
      path: "$customerDetails",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $project: {
      orderNumber: 1,
      status: 1,
      customerName: "$customerDetails.name"
    }
  }
]);

This example assumes orders contain a customerId field that matches the _id field in customers. The earlier sample documents use a customer name instead, so you would need to adapt the schema or join fields before running this example against that sample collection.

The preserveNullAndEmptyArrays option keeps orders that do not have a matching customer document. Without it, $unwind would remove documents whose lookup result is empty.

Use $lookup when a report needs related data, but check the collection sizes, indexes, and execution plan when joins become expensive.

Step 8: Improve Aggregation Performance

An aggregation pipeline can perform well on large collections, but its performance depends on the data model and the stages used.

Filter early

Place selective $match stages near the beginning when doing so preserves the intended result. An early filter may allow MongoDB to use an index and reduce the documents passed to later stages.

Index frequently filtered fields

For example, if reports often filter orders by status and date, consider a compound index:

db.orders.createIndex({
  status: 1,
  orderDate: 1
});

The best index depends on the query patterns and data distribution. Compound index field order matters, so test with representative queries rather than adding indexes automatically.

Avoid unnecessary document expansion

The $unwind stage can multiply the number of documents processed. Use it only when the report needs to process array elements individually.

Inspect the execution plan

Use explain to investigate the pipeline:

db.orders.explain("executionStats").aggregate([
  {
    $match: {
      status: "Completed"
    }
  },
  {
    $unwind: "$items"
  },
  {
    $group: {
      _id: "$items.category",
      totalRevenue: {
        $sum: {
          $multiply: [
            "$items.quantity",
            "$items.price"
          ]
        }
      }
    }
  }
]);

Review the execution statistics and the stages used by the query. Look for excessive documents examined, expensive stages, and opportunities to reduce the amount of data processed.

Some aggregation stages may need substantial memory or may spill to disk, depending on the operation and configuration. Test large workloads rather than assuming a pipeline that works on sample data will scale equally well.

Common Mistakes to Avoid

Counting unwound documents as orders

After $unwind, one order can produce multiple pipeline documents. If you need a count of unique orders, count distinct order identifiers or group at the order level first.

Calculating revenue without considering order status

Including pending, cancelled, or refunded orders can produce misleading financial reports. Define the business rules before writing the pipeline.

Forgetting missing or invalid fields

Documents may not have every expected property. Missing quantities, inconsistent data types, or malformed business values can affect calculations. Validate data when it enters the collection and handle exceptional cases explicitly.

Assuming $lookup always improves a query

A join can add considerable work, especially when collections are large or the join fields are not indexed appropriately. Use it when the report needs related data and measure its impact.

Creating too many indexes

Indexes improve some reads but add storage and write costs. Base index decisions on measured query patterns and execution plans.

Best Practices for Production Applications

Use aggregation for calculations that can be performed efficiently in the database.
Apply selective filters early when the query semantics allow it.
Use $group for summaries and $project to return only the required fields.
Count distinct identifiers when documents have been expanded with $unwind.
Use indexes that match common filter and join conditions.
Inspect execution statistics with explain.
Validate numeric fields and business rules before relying on calculated totals.
Account for time zones in date-based reports.
Test pipelines against realistic data volumes.
Keep business rules for completed sales, refunds, and cancellations consistent across reports.

Summary

MongoDB aggregation pipelines let you transform documents and calculate useful summaries without moving all the raw data into application memory.

With stages such as $match, $unwind, $group, $project, and $sort, you can build reports for revenue, product performance, monthly sales, and customer activity.

The key is to understand what each stage does, especially when arrays are expanded and counts are calculated. Combine clear business rules with appropriate indexes and execution-plan checks to build aggregation pipelines that remain accurate and practical as your data grows.