Introduction to Aggregate Pipelines: The `aggregate` Method and the Basic Stage
Aggregate pipelines are MongoDB’s most powerful data analysis tool—mastering them allows you to use MongoDB to replace 90% of SQL analysis scenarios.
This course provides an introduction to the basics of aggregation pipelines, stage concepts, and execution order.
1. What You'll Learn
- Basic Syntax of the
aggregate()Method - The Concept and Execution Order of Pipeline Stages
- Basic stage: $match, $group, $project, $sort, $limit, $skip, $count
- Simple Aggregation in Practice
2. What is an aggregation pipeline?
Concept Explanation: The Aggregation Pipeline is MongoDB’s most powerful data analysis framework. It breaks down data processing into multiple stages that are executed sequentially; each stage takes the output from the previous stage as its input and performs filtering, transformation, grouping, or statistical calculations to ultimately produce the final result. This “pipeline” model is based on the UNIX pipeline concept, making complex data analysis composable and easy to debug.
How It Works: The aggregation pipeline executes stage by stage in the order of the array. The first stage collects all documents in the aggregation, and each subsequent stage transforms the document stream (filtering, projecting, grouping, etc.), passing the output to the next stage. The MongoDB query optimizer attempts to push $match and $sort to the front end of the pipeline to leverage indexes. The memory limit for a single stage is 100 MB; exceeding this limit requires allowDiskUse: true.
Automatic Reordering by the Pipeline Optimizer: The MongoDB query optimizer automatically reorders certain stages to improve performance— 1. Moving $match Forward: If $match appears after $project/$group, the optimizer will attempt to move it forward (because the data volume after $match is smaller); 2. $sort + $match Merge: Consecutive $sort + $match operations may be merged into a single $sort + $match operation that uses an index; 3. $project Move Up: If $project only affects fields that are not needed by subsequent $match/$sort operations, the optimizer may move $project up to reduce the number of fields. However, the optimizer does not alter the user-defined semantics—the results are always the same as with the original order.
The Practical Impact of the 100MB Memory Limit: Each aggregation stage has a 100MB memory limit—exceeding this limit results in an "Exceeded memory limit" error. This limit is a design-level safeguard (to prevent a single aggregation query from exhausting the server’s memory). Workarounds: 1. set allowDiskUse: true (overflow is written to a temporary file; performance decreases but no error is thrown); 2. Use $match before $group to reduce the input volume; 3. Avoid using $push to collect large arrays in $group (use $sum for counting instead); 4. Split large aggregations into multiple smaller ones (process in batches). allowDiskUse should be a last resort—you should first optimize the pipeline structure to reduce memory requirements.
Comparison of Aggregation Pipes vs. SQL Aggregations: MongoDB aggregation pipes are conceptually equivalent to SQL’s SELECT...GROUP BY...HAVING, but they differ in implementation— 1. SQL combines all operations into a single statement, while MongoDB arranges them in a chained sequence of stages; 2. SQL’s WHERE corresponds to $match, GROUP BY corresponds to $group, HAVING corresponds to $match (after $group), SELECT corresponds to $project, and ORDER BY corresponds to $sort; 3. SQL subqueries correspond to nested pipelines or $lookup; 4. SQL window functions correspond to $setWindowFields. Key strategy for migrating from SQL to aggregation pipelines: Break down SQL clauses into independent stages and arrange them in the order of the data flow.
Aggregation Pipeline Learning Roadmap: The aggregation pipeline is divided into three stages, from beginner to expert—1. Beginner (this course): Master the seven basic stages ($match/$group/$project/$sort/$limit/$skip/$count) and the pipeline execution model; 2. Intermediate (Next Course): Master the expression system ($cond/$switch/$dateOperators/$typeOperators/$stringOperators/$arrayOperators) and complex transformations; 3. Advanced (Subsequent Courses): Master multi-collection joins ($lookup/$unwind), faceted search ($facet/$bucket), and text/geo search ($text/$geoNear). It is recommended to study in order, completing 3–5 hands-on exercises at each stage to reinforce your learning.
graph LR
A[Gathering<br/>1000 Documents] --> B[$match<br/>Filter]
B --> C[$group<br/>Group Aggregation]
C --> D[$sort<br/>Sort]
D --> E[$limit<br/>Restrictions]
E --> F[Results]
style B fill:#fff3cd
style C fill:#d4edda
sequenceDiagram
participant DB as MongoDB
participant S1 as $match
participant S2 as $group
participant S3 as $sort
participant S4 as $limit
DB->>S1: 1000 Document
Note over S1: Filter: category=Electronics
S1->>S2: 250 Document
Note over S2: Group by brand<br/>$sum, $avg
S2->>S3: 15 groups
Note over S3: Sort by total descending
S3->>S4: 15 groups
Note over S4: Take the previous 10
S4-->>DB: 10 Group Results
Similar to UNIX pipes:
# Find products with score > 4, sort by price descending, top 10
cat products.json | jq 'select(.rating > 4)' | jq 'sort_by(-.price)' | head -10
# MongoDB Aggregation
db.products.aggregate([
{ $match: { rating: { $gt: 4 } } },
{ $sort: { price: -1 } },
{ $limit: 10 }
]);
3. Basic Syntax of aggregate()
Concept Description: db.collection.aggregate(pipeline, options) is the entry method for executing the aggregate pipeline. pipeline is an array of stage objects, and options controls execution behavior (memory limits, timeouts, index hints). The aggregate function returns a cursor that supports toArray() for retrieving all results at once or forEach() for iterating through them one by one.
How It Works: When MongoDB receives an aggregate command, the query optimizer first analyzes the pipeline structure and attempts to move the $match stage forward (merging it with $sort to utilize the index), then executes each stage sequentially in the optimized order. Each stage maintains a document stream in memory; if the 100MB limit is exceeded, an error is thrown (except for allowDiskUse: true).
Production Configuration for Aggregation Options: The options parameter for aggregate must be configured appropriately in a production environment—1. allowDiskUse: true: Must be enabled for large aggregations (to prevent errors caused by the 100MB memory limit), but prioritize optimizing the pipeline to reduce memory requirements (disk I/O is 100 times slower than memory); 2. maxTimeMS: 30000: Set a timeout (30 seconds) to prevent slow aggregations from bringing down the entire instance; 3. hint: {category: 1}: Force the use of a specific index (to manually correct the optimizer if it selects the wrong index); 4. batchSize: 100: Controls the number of documents returned per batch (smaller batches reduce first-response time, while larger batches reduce network round trips). Aggregations in production environments must include maxTimeMS—aggregations without timeout protection are ticking time bombs.
Behavior of the Aggregation Pipeline Optimizer: MongoDB’s aggregation optimizer automatically performs certain optimizations— 1. $match pushdown: If $match appears after $group/$project, the optimizer will attempt to push it down to an earlier stage (though success is not guaranteed); 2. $sort + $limit optimization: Consecutive $sort + $limit operations are optimized into Top N mode (maintaining a heap of only N elements instead of a full sort); 3. Optimizations Not Performed: The optimizer will not reorder the sequence of user-defined stages (e.g., moving $match from the third position to the first) nor will it move $match forward after $group. Understanding the limits of the optimizer’s behavior helps you write more efficient pipelines by hand—“Don’t rely on the optimizer; place $match at the very beginning yourself.”
Two Ways to Consume Aggregate Cursors: aggregate returns a cursor, not an array—two consumption methods—1. cursor.toArray(): Retrieves all results into memory at once (suitable for small result sets, < 1,000 rows); the code is concise but has high memory usage; 2. cursor.forEach() / for await...of: Processes results one by one (suitable for large result sets or streaming processing); low memory usage but slightly more complex code. for await...of is recommended for production environments—even if the current result set is small, it will not cause an OutOfMemoryError when data grows in the future. Code using toArray() will suddenly encounter an OutOfMemoryError when the data volume increases from 100 records to 100,000 records.
| Parameter | Type | Description |
|---|---|---|
pipeline |
Array | Array of stage objects; executed in order |
options.allowDiskUse |
Boolean | Allows temporary writes to disk (default: false) |
options.maxTimeMS |
Number | Timeout (milliseconds) |
options.batchSize |
Number | Number of documents returned per batch |
options.hint |
String/Object | Force use of specified index |
// === Basic Usage ===
db.products.aggregate([
{ $match: { category: 'Electronics' } },
{ $group: { _id: '$brand', total: { $sum: 1 } } }
]);
// === Return to Cursor ===
const cursor = db.products.aggregate([...]);
const results = await cursor.toArray();
// === Aggregation Options ===
db.products.aggregate(
[{ $match: {} }],
{
allowDiskUse: true, // Allow disk usage(Processing Large Datasets)
maxTimeMS: 30000, // 30 Timeout in seconds
batchSize: 100, // Batch Size
hint: 'category_1' // Force Index Usage
}
);
4. A Detailed Explanation of the Foundational Stage
Concept Overview: The aggregation pipeline provides over 30 stage operators; this section focuses on the seven most basic ones: $match (Filter), $group (Group Aggregation), $project (Projection/Calculation), $sort (Sort), $limit (Limit Count), $skip (Skip), and $count (Count). When used in combination, these operators cover 80% of everyday analytical scenarios.
How It Works: Each stage has specific input-to-output transformation rules. $match does not alter the document structure; it only filters. $group merges multiple documents into groups, outputting one document per group. $project selects or calculates output fields. $sort/$limit/$skip control the order and number of documents. Key Optimization Principle: $match Apply as early as possible to reduce the processing load in subsequent stages.
graph TB
A[Foundation Stage] --> B[$match<br/>Filter Documents<br/>Similar to find filter]
A --> C[$group<br/>Group Aggregation<br/>$sum/$avg/$min/$max]
A --> D[$project<br/>Field Projection<br/>Select/Calculated Fields]
A --> E[$sort<br/>Sort<br/>1 ascending -1 descending]
A --> F[$limit/$skip<br/>Pagination<br/>Restrictions/Skip]
A --> G[$count<br/>Count<br/>Number of output documents]
B --> H["✅ Put it at the very front<br/>Reducing Data Volume Using Indexes"]
style B fill:#fff3cd
style C fill:#d4edda
style H fill:#d4edda
(1) $match: Filter documents
Principles of $match Pre-Optimization: $match is the only stage in the aggregation pipeline that can utilize indexes, so placing it at the very beginning of the pipeline is the most important optimization principle. Reasons: 1. $match executes before $group, directly using indexes for filtering to reduce the volume of data processed downstream; 2. The MongoDB query optimizer attempts to push $match down to execute before $lookup, but it does not reorder the sequence of user-defined stages; 3. Executing $match after $group means aggregating all data first and then filtering it, which wastes a significant amount of computational resources.
Aggregate vs. Find Selection Guide: When to use aggregate instead of find? Criteria: 1. If you need grouped statistics ($group) → must use aggregate; 2. If you need to calculate or rename fields ($project to calculate fields) → use aggregate; 3. If multi-step transformations are required (e.g., filtering, then grouping, then sorting) → use aggregate; 4. For simple CRUD queries → find is more efficient. aggregate incurs greater overhead than find (due to pipeline initialization and data transfer between stages), so avoid overusing it for simple queries.
Performance Differences Between $match and find: Although the $match syntax is the same as find, their execution contexts differ—find is a standalone query that the optimizer fully optimizes, while $match is part of a pipeline, and its optimization capabilities are limited by the pipeline structure. Key differences: 1. find can use a covered query (an indexed query that does not read documents), while $match always reads documents within the pipeline; 2. $hint takes effect directly in find, whereas hints in $match are passed through aggregate options; 3. The cursor returned by find supports batchSize control, while the aggregate cursor behaves similarly but has higher initialization overhead.
Best Practices for Utilizing $match Indexes: Conditions for $match to utilize indexes—1. $match must be the first stage of a pipeline (or the first stage of a sub-pipeline within a $lookup); otherwise, it cannot utilize the index; 2. The fields in the $match conditions must be indexed (either as single-field indexes or as prefixes in composite indexes); 3. The $match + $sort combination can use a composite index to satisfy both filtering and sorting; 4. Verify with explain(): db.orders.aggregate([{$match: {status: 'paid'}}]).explain() to check if an IXSCAN is hit. If you see COLLSCAN, it means $match is not utilizing the index—you need to create an index or adjust the pipeline order. In production environments, you should regularly check the slow query log to confirm that the $match in the aggregation pipeline is using an index.
// === $match Similar to find filter ===
db.products.aggregate([
{ $match: { category: 'Electronics', price: { $gte: 100 } } }
]);
| $match Advantages | Description |
|---|---|
| Early-stage filtering in the pipeline | Reduces the volume of data processed in subsequent stages |
| Indexes can be used | High performance (similar to find) |
(2) $group Group Aggregation
$group Grouping Principle: $group is the most critical stage in the aggregation pipeline—it groups the document stream by the _id field, performs accumulator calculations independently for each group, and outputs a single aggregation result. The value of _id determines the grouping granularity: a field reference ('_id: $category') groups by a single field, an object expression ('_id: {year, month}') groups by a combination of fields, and null indicates no grouping—the statistics are calculated for the entire collection. Understanding the "one-to-many → one-to-one" transformation of $group is key to mastering the aggregation pipeline.
Detailed Explanation of the $group Accumulator: $group provides several accumulators—$sum (sum/count), $avg (average), $min/$max (minimum/maximum), $first/$last (first/last values), and $push/$addToSet (collect into an array/create a unique array). Key differences: $sum: 1 counts (incrementing by 1 for each document), $sum: '$field' sums (accumulating field values); $push retains duplicate values, while $addToSet automatically removes duplicates; the values of $first/$last depend on the sort order of the input (unpredictable when $sort is not used). The number of documents decreases sharply after $group (N documents → M groups), resulting in a smaller processing load in subsequent stages.
$group’s _id Design Pattern: The _id of $group determines the grouping granularity and is the most critical design decision for aggregation results— 1. Single-field grouping (_id: '$category'): Aggregates by category, outputting one row for each category; 2. Multi-field combination grouping (_id: {category: '$category', status: '$status'}): Cross-tabulation by category and status, outputting one row for each combination; 3. Date grouping (_id: {year: {$year: '$createdAt'}, month: {$month: '$createdAt'}}): Analyzing trends by year and month; 4. Global statistics (_id: null): No grouping; aggregates the entire collection. The more granular the _id, the more groups there are; the results are more detailed but the aggregation effect is weaker. The coarser the _id, the fewer groups there are; the aggregation effect is stronger but more information is lost.
Combining Accumulators: Multiple accumulators in $group can be used simultaneously to generate comprehensive statistical results— 1. Calculate count, avg, min, and max simultaneously (the four basic statistics that cover descriptive statistical needs); 2. Use $push to collect all values within a group (e.g., collect all product names in each category), but note that $push may generate large arrays (use $slice to extract a subset or $size to count instead); 3. Use $addToSet to collect unique values (e.g., count how many different brands there are in each category), but $addToSet is slower than other accumulators (because it requires duplicate checks); 4. $sum + $cond is used to perform conditional counting (e.g., counting the number of products in each category where price > 1000: $sum: {$cond: [{$gt: ['$price', 1000]}, 1, 0]}).
Common Pitfalls and Troubleshooting for $group: There are three common pitfalls with $group—1. _id cannot be omitted: $group must specify _id (even if it is null); otherwise, an error will occur; 2. Fields are lost after $group: $group retains only the _id and accumulator fields; all original fields are lost (for example, to access the name field after $group, you must use $push: '$name' in the accumulator, or use $project before $group to preserve the required fields and then use $lookup after $group to retrieve them); 3. $group changes the document structure: Documents after $group are entirely new aggregation results with a structure completely different from the original documents—subsequent stages can only reference _id and fields generated by the aggregator. Troubleshooting tip: Run the aggregation stage by stage (omitting subsequent stages) and observe the output structure at each step.
// === Group by Field ===
db.products.aggregate([
{ $group: {
_id: '$category', // Grouping Field
count: { $sum: 1 }, // Count
avgPrice: { $avg: '$price' },
maxPrice: { $max: '$price' },
minPrice: { $min: '$price' }
}}
]);
// [
// { _id: 'Electronics', count: 250, avgPrice: 599, maxPrice: 1999, minPrice: 99 },
// { _id: 'Books', count: 200, avgPrice: 29, maxPrice: 79, minPrice: 9 },
// ...
// ]
// === Multi-Field Grouping ===
db.orders.aggregate([
{ $group: {
_id: { year: { $year: '$createdAt' }, month: { $month: '$createdAt' } },
total: { $sum: '$total' },
count: { $sum: 1 }
}}
]);
// === Grouping the Entire Set ===
db.products.aggregate([
{ $group: {
_id: null, // No groups，Statistics for the Entire Set
totalProducts: { $sum: 1 },
avgPrice: { $avg: '$price' }
}}
]);
(3) Projecting the $project field
Two Uses of $project: $project has two purposes—1. Field selection (similar to SQL’s SELECT), where 1 or 0 controls whether a field is output; 2. Field calculation (similar to SQL’s AS), using expressions to create new fields. Note: In the 1/0 rule for $project, _id is outputted by default (must be explicitly set to 0 to hide it). For other fields, if any one is set to 1, the rest default to 0 (whitelist mode); if any one is set to 0, the rest default to 1 (blacklist mode). Mixing these modes can lead to unexpected behavior.
// === Select Output Fields ===
db.products.aggregate([
{ $project: {
sku: 1,
title: 1,
price: 1,
discountedPrice: { $multiply: ['$price', 0.9] } // Calculate a New Field
}}
]);
// === Rename Field ===
db.products.aggregate([
{ $project: {
productName: '$title', // Rename title → productName
price: 1,
category: 1
}}
]);
(4) $sort Sorting
Memory Limits and Optimization for $sort: $sort performs sorting in memory and has a default memory limit of 100 MB—exceeding this limit will result in an error (unless allowDiskUse: true is set). Optimization strategies: 1. Use $match before $sort to reduce the data volume (sorting 1,000 records is much faster than sorting 100,000); 2. Create an index on the sort field (since the index is already sorted, MongoDB can return results directly in index order without needing to sort in memory); 3. When combining $sort with $limit, MongoDB maintains only the Top N heap (rather than sorting the entire dataset), reducing memory usage from O(N) to O(limit); 4. A composite index {category: 1, price: -1} can satisfy both $match and $sort, enabling a “covering query + sort.”
// === 1 ascending, -1 descending ===
db.products.aggregate([
{ $sort: { price: -1 } }
]);
// === Sorting by Multiple Fields ===
db.products.aggregate([
{ $sort: { category: 1, price: -1 } }
]);
(5) $limit / $skip Pagination
Performance Pitfalls of $skip: The larger the $skip value, the worse the performance—$skip(10000) requires MongoDB to scan and discard the first 10,000 documents; while these are not returned to the client, the overhead of scanning and sorting still exists. Advanced Pagination Optimization: 1. Cursor-based pagination (use _id: {$gt: lastId} instead of skip; performance remains constant); 2. Limit the maximum page number (e.g., only allow navigation up to page 50; if exceeded, prompt the user to narrow the search scope); 3. Top N mode using $sort + $limit (no skip; only retrieve the first N records). Offset pagination is only acceptable when the data volume is small (< 1,000 pages).
Implementation Details of Cursor-Based Pagination: Cursor-based pagination uses the sort value of the last record on the previous page as a "cursor"— 1. First page: Normal query $sort + $limit(N); 2. Subsequent pages: $match({createdAt: {$lt: lastCursor}, _id: {$lt: lastId}}) + $sort + $limit(N). Using two fields (sort field + _id) ensures the uniqueness of the cursor (the sort field may have duplicates, but _id will not); 3. Frontend transmission: The createdAt and _id of the last record on the previous page are used as the cursor parameters for the next page; 4. Advantages: Regardless of the page number, the query complexity remains constant at O(N) (only N records are scanned; no skipping); 5. Limitations: Cannot jump to arbitrary page numbers (only “next page” is supported); not suitable for scenarios requiring page number navigation. E-commerce product lists use cursor-based sorting, while administrative backends use offset-based sorting.
// === $limit Limit the number of results returned ===
db.products.aggregate([
{ $sort: { price: -1 } },
{ $limit: 10 }
]);
// === $skip Skip ===
db.products.aggregate([
{ $sort: { price: -1 } },
{ $skip: 20 },
{ $limit: 10 } // Items 21-30
]);
(6) $count Count
Use Cases for $count: $count is the simplest aggregation stage—it takes N documents as input and outputs a single document containing a count value. It is equivalent to $group({ _id: null, total: { $sum: 1 } }), but with more concise syntax. Common uses: 1. Counting the number of filtered documents ($match + $count); 2. As a sub-pipeline in $facet to obtain the total count (for pagination); 3. Combined with $match to perform conditional counting (e.g., “How many Electronics products are there?”).
// === Simple Counting ===
db.products.aggregate([
{ $match: { category: 'Electronics' } },
{ $count: 'totalElectronics' }
]);
// [ { totalElectronics: 250 } ]
// === equivalent to countDocuments ===
db.products.countDocuments({ category: 'Electronics' });
5. Accumulator
Concept Explanation: Accumulators are aggregation functions used in the $group phase that perform calculations on each group of documents and output a single result. $sum Sum, $avg Average, $min/$max Extreme Values, $first/$last First and Last Values, $push/$addToSet Array Accumulation. Accumulators are core tools for statistical analysis.
How It Works: The $group phase groups documents based on the _id field, and performs accumulator calculations independently for each group. $sum: 1 counts, $sum: '$field' sums, $avg: '$field' calculates the average, $push: '$field' collects all values into an array, and $addToSet: '$field' collects deduplicated values. _id: null indicates no grouping; statistics are calculated for the entire set.
graph LR
A[Grouping Field _id] --> B[Group 1]
A --> C[Group 2]
A --> D[Group 3]
B --> E["$sum: {$sum:1}<br/>$avg: {$avg:'$price'}<br/>$push: {$push:'$name'}"]
C --> F["$sum: {$sum:1}<br/>$avg: {$avg:'$price'}<br/>$push: {$push:'$name'}"]
D --> G["$sum: {$sum:1}<br/>$avg: {$avg:'$price'}<br/>$push: {$push:'$name'}"]
E --> H[Output one line per group<br/>Aggregated Results]
F --> H
G --> H
style H fill:#d4edda
(1) Complete List of Accumulators
| Accumulator | Meaning | Example |
|---|---|---|
$sum |
Sum | { $sum: '$price' } |
$avg |
Average | { $avg: '$price' } |
$min |
minimum value | { $min: '$price' } |
$max |
Maximum value | { $max: '$price' } |
$first |
First | { $first: '$name' } |
$last |
Last | { $last: '$name' } |
$push |
Array Accumulation | { $push: '$name' } |
$addToSet |
Removing Duplicates and Accumulating Values in an Array | { $addToSet: '$name' } |
$count |
Count (for top level) | { $count: 'total' } |
▶ Example 1: Accumulator in Action
Combined Use of Accumulators: In real-world business scenarios, multiple accumulators are typically combined within a single $group—for example, e-commerce category statistics require the simultaneous calculation of count ($sum: 1), totalRevenue ($sum: '$price'), avgPrice ($avg: '$price'), and maxPrice ($max: '$price'). All four metrics are calculated in a single $group operation, eliminating the need for multiple queries. The $group stage is the “data compression” phase—it takes N documents as input and outputs M groups (M << N), drastically reducing the processing load in subsequent stages.
Choosing Between $push and $addToSet: $push retains duplicate values (e.g., ['Electronics', 'Electronics', 'Books']), while $addToSet automatically removes duplicates (e.g., ['Electronics', 'Books']). Selection criteria: 1. Need a complete list of elements (including duplicates) → $push (e.g., "List of items from all orders"); 2. Only unique elements are needed → $addToSet (e.g., “Categories of purchased items”). Note: $push may result in very large arrays (e.g., popular categories with 10,000 product names); use $slice to limit the array length or $unwind + $group to restructure the array.
// === Count the number of items in each category and summarize the prices ===
db.products.aggregate([
{
$group: {
_id: '$category',
count: { $sum: 1 },
totalStock: { $sum: '$stock' },
avgPrice: { $avg: '$price' },
maxPrice: { $max: '$price' },
minPrice: { $min: '$price' },
topProducts: { $push: '$title' } // All Product Names
}
}
]);
// === $addToSet Cumulative deduplication ===
db.users.aggregate([
{
$group: {
_id: '$city',
uniqueRoles: { $addToSet: '$role' } // Set of Roles for Each City
}
}
]);
6. Optimizing Execution Order
Concept Explanation: The performance of aggregation pipelines is highly dependent on the order of stages. Core optimization principles: $match should be placed as early as possible; $project should reduce the number of fields after $group; and $sort and $match should be adjacent to take advantage of indexes. The MongoDB query optimizer automatically performs some optimizations (such as pushing $match before $lookup), but it does not alter the user-defined phase order.
How It Works: The optimizer attempts two types of optimization: (1) Move $match forward and merge it with $sort into an index scan; (2) Push $match down into the pipeline of $lookup. However, the optimizer does not reorder the sequence of user-defined phases—if $match comes after $group, it cannot be automatically moved forward.
graph LR
subgraph "✅ Optimization Order"
A1[$match<br/>Filter first] --> B1[$sort<br/>Sort] --> C1[$limit<br/>Restrictions]
end
subgraph "⚠️ Anti-pattern"
A2[$sort<br/>Full Sort] --> B2[$limit<br/>Restrictions] --> C2[$match<br/>Post-filtration]
end
style A1 fill:#d4edda
style C2 fill:#f8d7da
| Optimization Strategy | Effect | Applicable Conditions |
|---|---|---|
$match Place at the beginning |
Reduce the amount of subsequent data | Filter fields with indexes |
$match + $sort are adjacent |
Merged into an index scan | The sort field has a composite index |
$project Streamline fields |
Reduce memory usage | Use after $group |
hint() Forced Index |
Avoiding Full-Table Scans | When the Optimizer Selects the Wrong Index |
// ✅ Optimization:$match Put it at the very front
db.products.aggregate([
{ $match: { category: 'Electronics' } }, // Filter first(Using an Index)
{ $sort: { price: -1 } },
{ $limit: 10 }
]);
// ⚠️ Counterexample:$match Put it in the back
db.products.aggregate([
{ $sort: { price: -1 } },
{ $limit: 10 },
{ $match: { category: 'Electronics' } } // Filter only after processing，Waste of resources
]);
7. Comprehensive Practical Training
Pipeline Execution Principles and Memory Management: Each stage of the aggregation pipeline maintains a document stream in memory, with a memory limit of 100 MB per stage (an error is reported if this limit is exceeded, unless allowDiskUse: true is set). This means: 1. The number of group keys in the $group stage should not be excessive (millions of group keys will cause memory to overflow); 2. The $push accumulator collects all values into an array, which can easily exceed the limit when dealing with large data volumes; 3. $sort performs sorting in memory; for large datasets, an index is required to prevent overflow. Optimization strategies: Preprocess data before $match to reduce volume, use $project to streamline fields, and replace full sorting with $sort + $limit.
(1) E-commerce Sales Statistics
// === Monthly Sales Statistics ===
db.orders.aggregate([
{ $match: { status: 'paid', createdAt: { $gte: new Date('2026-01-01') } } },
{
$group: {
_id: {
year: { $year: '$createdAt' },
month: { $month: '$createdAt' }
},
totalSales: { $sum: '$total' },
orderCount: { $sum: 1 },
avgOrderValue: { $avg: '$total' }
}
},
{ $sort: { '_id.year': 1, '_id.month': 1 } }
]);
(2) Analysis of Product Categories
// === Product Categories + Price Range Analysis ===
db.products.aggregate([
{
$bucket: {
groupBy: '$price',
boundaries: [0, 100, 500, 1000, 5000, 10000],
default: 'Other',
output: {
count: { $sum: 1 },
products: { $push: '$title' }
}
}
}
]);
▶ Example 2: ShopHub E-commerce Category Analysis Pipeline
// Scene:ShopHub The operations team needs to analyze product data from multiple perspectives.
// Preparing the Data
db.products.insertMany([
{ sku: 'PHONE-001', title: 'Smartphone X', category: 'Electronics', brand: 'TechCorp', price: 599, stock: 120, rating: 4.5 },
{ sku: 'PHONE-002', title: 'Smartphone Y', category: 'Electronics', brand: 'DataFlow', price: 399, stock: 80, rating: 4.0 },
{ sku: 'LAPTOP-001', title: 'Laptop Pro', category: 'Electronics', brand: 'TechCorp', price: 1299, stock: 50, rating: 4.8 },
{ sku: 'BOOK-001', title: 'MongoDB Guide', category: 'Books', brand: 'AppVenture', price: 29, stock: 500, rating: 4.2 },
{ sku: 'BOOK-002', title: 'Node.js Mastery', category: 'Books', brand: 'AppVenture', price: 39, stock: 300, rating: 4.6 }
]);
// 1. Statistics by Category:Number of Products、Average Price、Highest Price、Brand List
db.products.aggregate([
{
$group: {
_id: '$category',
count: { $sum: 1 },
avgPrice: { $avg: '$price' },
maxPrice: { $max: '$price' },
minPrice: { $min: '$price' },
totalStock: { $sum: '$stock' },
brands: { $addToSet: '$brand' }
}
},
{ $sort: { count: -1 } }
]);
// 2. Price Range Distribution($bucket)
db.products.aggregate([
{
$bucket: {
groupBy: '$price',
boundaries: [0, 100, 500, 1000, 5000],
default: '5000+',
output: { count: { $sum: 1 }, titles: { $push: '$title' } }
}
}
]);
// 3. Highly Rated Products(rating >= 4.5)
db.products.aggregate([
{ $match: { rating: { $gte: 4.5 } } },
{ $project: { sku: 1, title: 1, price: 1, rating: 1, _id: 0 } },
{ $sort: { rating: -1, price: 1 } }
]);
Output: 1) Categorized statistics (Electronics: 3 items with an average price of 765.67; Books: 2 items with an average price of 34); 2) Price buckets (0–100: 2; 500–1,000: 1; 1,000–5,000: 1); 3) List of high-rated items.
$group's _id Design Pattern: The _id of $group determines the grouping granularity—1. Single-field grouping: _id: '$category' (grouped by category); 2. Multi-field grouping: _id: {category: '$category', brand: '$brand'} (cross-grouped by category and brand, outputting a composite key); 3. Date-based grouping: _id: {year: {$year: '$createdAt'}, month: {$month: '$createdAt'}} (grouped by year and month; commonly used for monthly reports); 4. Expression grouping: _id: {$cond: [{$gte: ['$price', 100]}, 'premium', 'budget']} (grouping by conditions, dynamic categorization); 5. null grouping: _id: null (no grouping; used for calculating global statistics, such as total number of orders and total sales). The design of _id determines the statistical dimension—a multi-field _id will produce a Cartesian product (one row for each combination), and the more fields there are, the more output rows there will be.
Combining Accumulators: The accumulators in $group can be combined—1. $sum + $avg: Calculate the number of items and the average price for each category (count: {$sum: 1}, avgPrice: {$avg: '$price'}); 2. $min + $max: Calculate the price range (minPrice: {$min: '$price'}, maxPrice: {$max: '$price'}); 3. $push + $addToSet: Collect values within a group (products: {$push: '$title'} to retain duplicates, brands: {$addToSet: '$brand'} to remove duplicates); 4. $first + $last: Retrieve the first and last values within a group (used in conjunction with $sort to retrieve the earliest and latest records). Note: $push and $addToSet may generate large arrays (collecting all values within each group); for large datasets, use them in conjunction with $limit or $slice.
❓ FAQ
find?find for simple queries and aggregate for complex analyses.hint() option forces the use of a specific index.allowDiskUse: true.aggregate?cursor.hasNext() and cursor.next(), or iterate directly in mongosh.📖 Summary
- Aggregation pipeline = Processing data through multiple stages in series
- Basic stages: $match, $group, $project, $sort, $limit, $skip, $count
- Accumulators: $sum, $avg, $min, $max, $first, $last, $push, $addToSet
- Place
$matchat the beginning to reduce processing load - Memory limit: 100 MB; can be exceeded using
allowDiskUse - Aggregation pipelines can use indexes
📝 Exercises
- Basic Problem (⭐): Use $group to count the number of items in each category and calculate the average price.
- Basic Question (⭐): Use $match + $sort + $limit to query the top 10 Electronics products by price.
- Advanced Problem (⭐⭐): Calculate the total number of orders and total sales by month ($year/$month + $group).
- Advanced Problem (⭐⭐): Use $project to calculate discountedPrice (price × 0.9).
- Challenge (⭐⭐⭐): Complete sales dashboard (sales analysis by month, category, and customer).
