Aggregation for reporting dashboards in MongoDB - Mini Project: Build & Apply

Aggregation for reporting dashboards

📖 Scenario: You work for an online store that wants to create a simple sales report dashboard. The dashboard will show total sales and the number of orders per product category.

🎯 Goal: Build a MongoDB aggregation pipeline step-by-step to calculate total sales and order counts grouped by product category.

📋 What You'll Learn

Create a sales collection with sample sales documents

Add a filter stage to select sales from the last month

Group sales by category to calculate total sales amount and order count

Sort the results by total sales in descending order

💡 Why This Matters

🌍 Real World

Aggregation pipelines are used in real dashboards to summarize and analyze sales data quickly.

💼 Career

Understanding aggregation is essential for data analysts and backend developers working with MongoDB to build reports.

1

DATA SETUP: Create the sales collection with sample documents

Create a variable called sales that contains an array of 5 sales documents. Each document must have these exact fields and values: { product: 'Laptop', category: 'Electronics', amount: 1200, date: new Date('2024-05-10') }, { product: 'Headphones', category: 'Electronics', amount: 150, date: new Date('2024-05-15') }, { product: 'Coffee Maker', category: 'Home Appliances', amount: 80, date: new Date('2024-05-20') }, { product: 'Blender', category: 'Home Appliances', amount: 60, date: new Date('2024-04-25') }, { product: 'Notebook', category: 'Stationery', amount: 5, date: new Date('2024-05-18') }.

MongoDB

# Create a variable called sales with 5 sales documents as described
# Your code here

Hint

Use const sales = [ ... ] and include the exact documents with the given fields and values.

2

CONFIGURATION: Define the date filter for last month sales

Create a variable called lastMonth and set it to a JavaScript Date object representing May 1, 2024.

MongoDB

const sales = [
  { product: 'Laptop', category: 'Electronics', amount: 1200, date: new Date('2024-05-10') },
  { product: 'Headphones', category: 'Electronics', amount: 150, date: new Date('2024-05-15') },
  { product: 'Coffee Maker', category: 'Home Appliances', amount: 80, date: new Date('2024-05-20') },
  { product: 'Blender', category: 'Home Appliances', amount: 60, date: new Date('2024-04-25') },
  { product: 'Notebook', category: 'Stationery', amount: 5, date: new Date('2024-05-18') }
];

// Create a variable lastMonth set to new Date('2024-05-01')
# Your code here

Hint

Use const lastMonth = new Date('2024-05-01') to set the date filter.

3

CORE LOGIC: Build the aggregation pipeline to filter and group sales

Create a variable called pipeline that is an array with these stages: 1) a $match stage to filter sales with date greater than or equal to lastMonth, 2) a $group stage that groups by category and calculates totalSales as the sum of amount and orderCount as the count of documents.

MongoDB

const sales = [
  { product: 'Laptop', category: 'Electronics', amount: 1200, date: new Date('2024-05-10') },
  { product: 'Headphones', category: 'Electronics', amount: 150, date: new Date('2024-05-15') },
  { product: 'Coffee Maker', category: 'Home Appliances', amount: 80, date: new Date('2024-05-20') },
  { product: 'Blender', category: 'Home Appliances', amount: 60, date: new Date('2024-04-25') },
  { product: 'Notebook', category: 'Stationery', amount: 5, date: new Date('2024-05-18') }
];

const lastMonth = new Date('2024-05-01');

// Create an aggregation pipeline array with $match and $group stages
# Your code here

Hint

Use { $match: { date: { $gte: lastMonth } } } and { $group: { _id: "$category", totalSales: { $sum: "$amount" }, orderCount: { $sum: 1 } } } in the pipeline array.

4

COMPLETION: Add a $sort stage to order results by total sales

Add a $sort stage to the pipeline array that sorts documents by totalSales in descending order.

MongoDB

const sales = [
  { product: 'Laptop', category: 'Electronics', amount: 1200, date: new Date('2024-05-10') },
  { product: 'Headphones', category: 'Electronics', amount: 150, date: new Date('2024-05-15') },
  { product: 'Coffee Maker', category: 'Home Appliances', amount: 80, date: new Date('2024-05-20') },
  { product: 'Blender', category: 'Home Appliances', amount: 60, date: new Date('2024-04-25') },
  { product: 'Notebook', category: 'Stationery', amount: 5, date: new Date('2024-05-18') }
];

const lastMonth = new Date('2024-05-01');

const pipeline = [
  { $match: { date: { $gte: lastMonth } } },
  { $group: { _id: "$category", totalSales: { $sum: "$amount" }, orderCount: { $sum: 1 } } },
  // Add $sort stage here
];
# Your code here

Hint

Add { $sort: { totalSales: -1 } } as the last stage in the pipeline array.

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