Aggregation for reporting dashboards in MongoDB - Step-by-Step Execution

Concept Flow - Aggregation for reporting dashboards

Start with collection

↓

Apply $match to filter data

↓

Use $group to aggregate

↓

Optionally $project to shape output

↓

Sort or limit results

↓

Output dashboard-ready data

Data flows from raw collection through filtering, grouping, shaping, and sorting to produce dashboard-ready summaries.

Execution Sample

MongoDB

db.sales.aggregate([
  {$match: {region: 'East'}},
  {$group: {_id: '$product', totalSales: {$sum: '$amount'}}},
  {$sort: {totalSales: -1}}
])

This query filters sales to the East region, sums sales by product, and sorts products by total sales descending.

Execution Table

Step	Stage	Input Documents	Action	Output Documents
1	$match	All sales documents	Filter where region = 'East'	Only sales from East region
2	$group	Filtered East sales	Group by product, sum amounts	One document per product with totalSales
3	$sort	Grouped documents	Sort by totalSales descending	Documents ordered by totalSales high to low
4	End	Sorted documents	Return results	Final aggregated data for dashboard

💡 Aggregation pipeline completes after $sort stage, producing dashboard-ready summary.

Variable Tracker

Variable	Start	After $match	After $group	After $sort	Final
documents	All sales	East region sales only	Grouped by product with totalSales	Sorted by totalSales descending	Dashboard summary data

Key Moments - 3 Insights

Why do we use $match first in the pipeline?

Using $match first reduces the number of documents early, making grouping and sorting faster and more efficient, as shown in execution_table step 1.

What does $group do with the _id field?

$group uses _id to define groups; here, _id is set to product, so sales are summed per product, as seen in execution_table step 2.

Why sort after grouping and not before?

Sorting after grouping orders the aggregated results, not raw data. Sorting raw data first wouldn't order the final totals correctly, as shown in execution_table step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output after the $match stage?

A All sales documents

B Sales documents only from the East region

C Grouped sales by product

D Sorted sales by total amount

💡 Hint

Check execution_table row 1 under Output Documents column.

At which step does the aggregation pipeline group sales by product?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at execution_table Stage column and find where $group is applied.

If we remove the $match stage, what will happen to the output?

A Only East region sales will be included

B No documents will be returned

C All sales from all regions will be grouped and sorted

D The pipeline will fail

💡 Hint

Refer to variable_tracker showing documents after $match and consider what happens if $match is skipped.

Concept Snapshot

Aggregation pipelines process data step-by-step.
Start with $match to filter documents.
Use $group to aggregate data by keys.
Optionally $project to format output.
Sort or limit results last.
This produces dashboard-ready summaries.

Full Transcript

Aggregation pipelines in MongoDB help create reporting dashboards by processing data step-by-step. First, $match filters documents to include only relevant data, like sales from a specific region. Next, $group aggregates data by a key, such as product, summing sales amounts. Then, $sort orders the aggregated results, for example by total sales descending. Finally, the pipeline outputs the processed data ready for dashboard display. This flow improves efficiency and clarity in reporting.

Practice

1. What is the main purpose of using the $group stage in a MongoDB aggregation pipeline for reporting dashboards?

easy

A. To add new fields to documents without changing existing ones

B. To combine documents and calculate summary values like totals or averages

C. To sort documents in ascending or descending order

D. To filter documents based on specific conditions

Solution

1. Step 1: Understand the role of $group

   The $group stage collects documents into groups and calculates summary statistics like sums or averages.

2. Step 2: Compare with other stages

   $match filters, $sort orders, and $addFields adds fields, but only $group aggregates data.

3. Final Answer:

   To combine documents and calculate summary values like totals or averages -> Option B

4. Quick Check:

   $group = summary aggregation [OK]

Hint: Remember $group is for summarizing data by categories [OK]

Common Mistakes:

• Confusing $group with $match filtering
• Thinking $group sorts data
• Assuming $group adds fields without aggregation

2. Which of the following is the correct syntax to start an aggregation pipeline in MongoDB?

easy

A. db.collection.aggregate([{ $match: { status: 'active' } }])

B. db.collection.find({ $match: { status: 'active' } })

C. db.collection.group({ status: 'active' })

D. db.collection.aggregateMatch({ status: 'active' })

Solution

1. Step 1: Identify the aggregation method

   The correct method to start aggregation is aggregate() with an array of stages.

2. Step 2: Check syntax correctness

   db.collection.aggregate([{ $match: { status: 'active' } }]) is valid; others use wrong methods or syntax.

3. Final Answer:

   db.collection.aggregate([{ $match: { status: 'active' } }]) -> Option A

4. Quick Check:

   aggregate() starts pipeline [OK]

Hint: Use aggregate() with an array of stages to start [OK]

Common Mistakes:

• Using find() instead of aggregate() for pipelines
• Trying to use group() as a method
• Incorrect method names like aggregateMatch()

3. Given the collection sales with documents like { product: 'A', quantity: 5 } and { product: 'B', quantity: 3 }, what will this aggregation return?

db.sales.aggregate([
  { $group: { _id: '$product', totalQuantity: { $sum: '$quantity' } } },
  { $sort: { totalQuantity: -1 } }
])

medium

A. [{ _id: 'A', totalQuantity: 8 }]

B. [{ _id: 'B', totalQuantity: 3 }, { _id: 'A', totalQuantity: 5 }]

C. [{ _id: 'A', totalQuantity: 5 }, { _id: 'B', totalQuantity: 3 }]

D. SyntaxError due to missing fields

Solution

1. Step 1: Group by product and sum quantity

   The $group stage groups documents by product and sums their quantities: 'A' -> 5, 'B' -> 3.

2. Step 2: Sort by totalQuantity descending

   The $sort orders results by totalQuantity descending, so 'A' (5) comes before 'B' (3).

3. Final Answer:

   [{ _id: 'A', totalQuantity: 5 }, { _id: 'B', totalQuantity: 3 }] -> Option C

4. Quick Check:

   Group sums then sort desc = correct order [OK]

Hint: Group sums then sort descending by total [OK]

Common Mistakes:

• Mixing up sort order ascending vs descending
• Expecting combined total instead of grouped sums
• Thinking syntax error occurs

4. Identify the error in this aggregation pipeline:

db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $group: { _id: '$customerId', total: { $sum: '$amount' } } },
  { $sort: { total: 1 } },
  { $project: { customerId: 1, total: 1 } }
])

medium

A. The $sort stage must come before $group

B. The $sum operator cannot be used inside $group

C. The $match stage should be after $group

D. The $project stage uses a field not present in the grouped output

Solution

1. Step 1: Check fields after $group

   After grouping by _id: '$customerId', the output fields are _id and total. The original customerId field is renamed to _id.

2. Step 2: Analyze $project stage

   The $project tries to include customerId, which does not exist; it should use _id instead.

3. Final Answer:

   The $project stage uses a field not present in the grouped output -> Option D

4. Quick Check:

   Use _id after $group, not original field [OK]

Hint: After $group, use _id field names in $project [OK]

Common Mistakes:

• Assuming original fields remain after $group
• Placing $sort before $group incorrectly
• Misusing $sum outside $group

5. You want to create a dashboard showing the average order amount per customer, but only for customers with more than 3 orders. Which aggregation pipeline correctly achieves this?

hard

A. [{ $group: { _id: '$customerId', avgAmount: { $avg: '$amount' }, count: { $sum: 1 } } }, { $match: { count: { $gt: 3 } } }]

B. [{ $match: { count: { $gt: 3 } } }, { $group: { _id: '$customerId', avgAmount: { $avg: '$amount' } } }]

C. [{ $group: { _id: '$customerId', avgAmount: { $avg: '$amount' } } }, { $match: { avgAmount: { $gt: 3 } } }]

D. [{ $group: { _id: '$customerId', avgAmount: { $avg: '$amount' } } }, { $sort: { avgAmount: -1 } }]

Solution

1. Step 1: Group by customer and calculate average and count

   Use $group to get average order amount and count of orders per customer.

2. Step 2: Filter customers with more than 3 orders

   Apply $match after grouping to keep only customers where count > 3.

3. Final Answer:

   [{ $group: { _id: '$customerId', avgAmount: { $avg: '$amount' }, count: { $sum: 1 } } }, { $match: { count: { $gt: 3 } } }] -> Option A

4. Quick Check:

   Group then filter by count > 3 [OK]

Hint: Filter after grouping to use aggregated counts [OK]

Common Mistakes:

• Filtering by count before grouping
• Filtering by avgAmount instead of count
• Missing count calculation in $group