Computed pattern for pre-aggregation in MongoDB - Step-by-Step Execution

Concept Flow - Computed pattern for pre-aggregation

Start: Raw Data Collection

↓

Define Computed Fields

↓

Apply Aggregation Pipeline

↓

Compute and Store Aggregated Results

↓

Use Pre-Aggregated Data for Queries

↓

End

This flow shows how raw data is processed by defining computed fields, aggregating them, storing results, and then using these pre-aggregated results for faster queries.

Execution Sample

MongoDB

db.sales.aggregate([
  { $project: {
      item: 1,
      totalPrice: { $multiply: ["$price", "$quantity"] }
    }
  },
  { $group: { _id: "$item", totalSales: { $sum: "$totalPrice" } } }
])

This MongoDB aggregation computes total price per sale and then sums total sales per item.

Execution Table

Step	Pipeline Stage	Input Document	Computed Field	Group Key	Aggregated Value
1	$project	{"item":"apple", "price":2, "quantity":3}	totalPrice = 2*3=6
2	$project	{"item":"banana", "price":1, "quantity":5}	totalPrice = 1*5=5
3	$project	{"item":"apple", "price":2, "quantity":2}	totalPrice = 2*2=4
4	$group	{"item":"apple", "totalPrice":6}		apple	6
5	$group	{"item":"banana", "totalPrice":5}		banana	5
6	$group	{"item":"apple", "totalPrice":4}		apple	6+4=10
7	End				Final aggregated totals: apple=10, banana=5

💡 All documents processed; aggregation complete with pre-aggregated totals per item.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	Final
totalPrice	N/A	6	5	4	N/A	N/A	N/A	N/A
totalSales_apple	0	6	6	10	10	10	10	10
totalSales_banana	0	0	5	5	5	5	5	5

Key Moments - 2 Insights

Why do we compute 'totalPrice' before grouping?

Computing 'totalPrice' in the $project stage creates a new field for each document, which can then be summed in the $group stage. This separation ensures accurate aggregation as shown in execution_table rows 1-3 for computation and 4-6 for grouping.

How does the $group stage accumulate totals?

The $group stage sums 'totalPrice' values for each unique 'item' key. For example, in rows 4 and 6, 'apple' totals are added step-by-step to reach the final sum 10.

Visual Quiz - 3 Questions

Look at the execution_table, what is the computed 'totalPrice' for the second document at step 2?

A 6

B 4

C 5

D 10

💡 Hint

Check the 'Computed Field' column at step 2 in the execution_table.

At which step does the total sales for 'apple' become 10?

A Step 5

B Step 6

C Step 4

D Step 7

💡 Hint

Look at the 'Aggregated Value' for 'apple' in the execution_table rows.

If the quantity of the first 'apple' document changed from 3 to 4, what would be the new 'totalPrice' at step 1?

A 8

B 10

C 6

D 12

💡 Hint

Multiply price 2 by new quantity 4 as shown in the 'Computed Field' logic.

Concept Snapshot

MongoDB pre-aggregation uses $project to compute new fields (e.g., totalPrice = price * quantity).
Then $group sums these computed fields by keys (e.g., item).
This pattern speeds up queries by storing aggregated results.
Use $project before $group to prepare data for aggregation.
Final output is grouped totals ready for fast retrieval.

Full Transcript

This visual execution traces the MongoDB computed pattern for pre-aggregation. Starting with raw sales data, each document computes a totalPrice by multiplying price and quantity in the $project stage. Then, the $group stage sums totalPrice values grouped by item names. The execution table shows each step: computing totalPrice for each document, then accumulating sums per item. Variable tracking reveals how totalPrice and totalSales for each item change after each step. Key moments clarify why computation happens before grouping and how sums accumulate. The visual quiz tests understanding of computed values and aggregation steps. This pattern helps store pre-aggregated data for faster queries.

Practice

1. What is the main purpose of using a computed pattern for pre-aggregation in MongoDB?

easy

A. To delete all raw data after aggregation

B. To create indexes on every field automatically

C. To calculate and store summary data ahead of time for faster queries

D. To replicate data across multiple servers

Solution

1. Step 1: Understand pre-aggregation concept

   Pre-aggregation means computing summary data before queries run, so queries are faster.

2. Step 2: Identify the main goal

   The goal is to store computed summaries to avoid recalculating on every query.

3. Final Answer:

   To calculate and store summary data ahead of time for faster queries -> Option C

4. Quick Check:

   Pre-aggregation = faster queries [OK]

Hint: Pre-aggregation means compute summaries before queries [OK]

Common Mistakes:

• Confusing pre-aggregation with data deletion
• Thinking it automatically creates indexes
• Mixing it with data replication

2. Which MongoDB aggregation stage is primarily used to compute grouped summary data for pre-aggregation?

easy

A. $match

B. $group

C. $project

D. $sort

Solution

1. Step 1: Recall aggregation stages

   $group is used to group documents and compute summary values like sums or counts.

2. Step 2: Identify correct stage for pre-aggregation

   Pre-aggregation requires grouping and computing, so $group is the right stage.

3. Final Answer:

   $group -> Option B

4. Quick Check:

   $group = grouping and computing summaries [OK]

Hint: Use $group to compute grouped summaries [OK]

Common Mistakes:

• Choosing $match which filters but does not compute
• Confusing $project with grouping
• Selecting $sort which only orders data

3. Given the following aggregation pipeline, what will be the output?

[{ $group: { _id: "$category", total: { $sum: "$amount" } } }]

Assuming the collection has documents: {category: "A", amount: 10}, {category: "B", amount: 5}, {category: "A", amount: 15}.

medium

A. [{_id: null, total: 30}]

B. [{_id: "A", total: 10}, {_id: "B", total: 5}, {_id: "A", total: 15}]

C. [{_id: "A", total: 15}, {_id: "B", total: 5}]

D. [{_id: "A", total: 25}, {_id: "B", total: 5}]

Solution

1. Step 1: Group documents by category

   Documents with category "A" are grouped together, and category "B" separately.

2. Step 2: Sum amounts per group

   For "A": 10 + 15 = 25; for "B": 5.

3. Final Answer:

   [{_id: "A", total: 25}, {_id: "B", total: 5}] -> Option D

4. Quick Check:

   Sum amounts by category = correct totals [OK]

Hint: Sum amounts grouped by _id field [OK]

Common Mistakes:

• Not grouping sums correctly
• Returning individual documents instead of grouped
• Using null _id which sums all documents

4. Identify the error in this aggregation pipeline for pre-aggregation:

[{ $group: { _id: "$category", total: { $sum: "amount" } } }]

medium

A. Missing $ before amount in $sum operator

B. Incorrect use of $group stage

C. Missing $match stage before $group

D. Using _id field incorrectly

Solution

1. Step 1: Check $sum operator syntax

   $sum requires a field path starting with $, but "amount" is missing the $.

2. Step 2: Confirm correct syntax

   It should be $sum: "$amount" to sum the amount field values.

3. Final Answer:

   Missing $ before amount in $sum operator -> Option A

4. Quick Check:

   $sum needs $field syntax [OK]

Hint: Field names in aggregation need $ prefix [OK]

Common Mistakes:

• Forgetting $ before field names in operators
• Assuming $match is mandatory before $group
• Misusing _id field in $group

5. You want to pre-aggregate sales data by month and product category, storing total sales and average price. Which aggregation pipeline correctly computes these values?

hard

A. [{ $group: { _id: { month: { $month: "$date" }, category: "$category" }, totalSales: { $sum: "$amount" }, avgPrice: { $avg: "$price" } } }]

B. [{ $group: { _id: "$date", totalSales: { $sum: "$amount" }, avgPrice: { $avg: "$price" } } }]

C. [{ $group: { _id: { month: "$date", category: "$category" }, totalSales: { $sum: "$amount" }, avgPrice: { $avg: "$price" } } }]

D. [{ $group: { _id: { month: { $month: "$date" }, category: "$category" }, totalSales: { $sum: "$price" }, avgPrice: { $avg: "$amount" } } }]

Solution

1. Step 1: Group by month and category correctly

   Use $month operator on "$date" to extract month, and include "$category" as second key.

2. Step 2: Compute total sales and average price correctly

   Sum the "$amount" field for totalSales and average the "$price" field for avgPrice.

3. Final Answer:

   [{ $group: { _id: { month: { $month: "$date" }, category: "$category" }, totalSales: { $sum: "$amount" }, avgPrice: { $avg: "$price" } } }] -> Option A

4. Quick Check:

   Group by month+category, sum amount, avg price [OK]

Hint: Use $month for date grouping and correct fields for sum/avg [OK]

Common Mistakes:

• Grouping by full date instead of month
• Swapping sum and avg fields
• Not using $month operator for month extraction