Master MongoDB Aggregation Pipeline: Full Theory and Practical Guide

1. Introduction to Aggregation Pipeline

Aggregation pipeline is a powerful tool in MongoDB for data aggregation and processing. It processes documents through an ordered series of stages, each defined by an operator such as $match, $group, $sort, $project, $unwind, $limit, $lookup, $out, etc.

1.1 Pipeline processing

Data flows from input through each stage sequentially, performing filtering, grouping, sorting, and other transformations.

1.2 Stages

Each stage defines a specific operation and the order is fixed; data passes through stages in the defined sequence.

1.3 Operators

Examples: $match filters documents, $group groups and computes aggregates, $project creates or renames fields, $unwind expands array fields, $limit restricts output count, $lookup performs a left‑outer join with another collection, and $out can write the result to a collection.

1.4 Data flow

Data is read from a collection, passes through each defined stage, and the transformed documents are passed to the next stage until the pipeline finishes.

1.5 Output

The result is a cursor of processed documents; $out can write the result to another collection.

2. Using the Aggregation Pipeline

Typical steps: build the pipeline by selecting appropriate stages and operators, execute it with aggregate(), and process the returned cursor.

Example scenario: an orders collection with fields customer_id, product_id, order_date, and amount. Requirements include total sales per product, average order amount per customer‑product, and the top‑5 customers by average order amount with their purchased products.

Pipeline stages used: $group, $sort, $limit, $lookup, $unwind, and $project.

db.orders.aggregate([
  // Stage 1: group by product and customer to compute total sales
  {$group:{_id:{product_id:"$product_id",customer_id:"$customer_id"}, totalSales:{$sum:"$amount"}}},
  // Stage 2: group by customer to compute average per product and total sales per customer
  {$group:{_id:"$_id.customer_id", productSales:{$push:{productId:"$_id.product_id", avgAmount:{$avg:"$totalSales"}}}, totalSales:{$sum:"$totalSales"}}},
  // Stage 3: sort by average amount descending and limit to top 5
  {$sort:{"productSales.avgAmount":-1}},
  {$limit:5},
  // Stage 4: lookup customer details
  {$lookup:{from:"customers", localField:"_id", foreignField:"customer_id", as:"customerDetails"}},
  // Stage 5: unwind customer details array
  {$unwind:{path:"$customerDetails", includeArrayIndex:"index", preserveNullAndEmptyArrays:true}},
  // Stage 6: group by customer to list products and average amounts
  {$group:{_id:"$_id", customerName:{$first:"$customerDetails.name"}, customerEmail:{$first:"$customerDetails.email"}, products:{$push:{productId:"$productSales.productId", avgAmount:"$productSales.avgAmount"}}}},
  // Stage 7: sort by customer name
  {$sort:{customerName:1}}
])

The pipeline works as follows:

The first $group groups by product and customer, calculating total sales.

The second $group regroups by customer, computing the average order amount per product and the total sales per customer. $sort and $limit order the results by average amount and keep the top five customers. $lookup joins the customer IDs with the customers collection to fetch detailed information. $unwind expands the joined array so each document contains a single customer's details.

The final $group assembles each customer's purchased products and their average amounts.

A final $sort orders the output by customer name.

3. Common Use Cases

Data grouping and statistics : group by a field and compute count, average, max, etc.

Data filtering : use match operators to keep only documents meeting conditions.

Data sorting : order documents by a field.

Data transformation and calculation : use project to create new fields or compute values.