Assignments Of Day 11: Aggregation Framework – Part 2 (Advanced)
Assignment 1: Filtering Orders by Status and Calculating Discount
Task:
Write an aggregation query to retrieve all orders where the status is "shipped", includes the orderId and totalAmount, and calculates a discountedPrice (20% discount on totalAmount).
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$match: { status: "shipped" } // Filters orders where status is "shipped"
},
{
$project: {
orderId: 1, // Includes orderId field
totalAmount: 1, // Includes totalAmount field
discountedPrice: { $multiply: ["$totalAmount", 0.8] } // Calculates 20% discount
}
}
])
Explanation:
1. $match: Filters documents where status is "shipped".
2. $project:
o Includes orderId and totalAmount.
o Adds a new field discountedPrice that calculates 80% of totalAmount (i.e., 20% discount).
Assignment 2: Grouping Products by Category and Calculating Average Price
Task:
Group products by their category and calculate the average price of products within each category.
Solution:
javascript
CopyEdit
db.products.aggregate([
{
$group: {
_id: "$category", // Groups by category
averagePrice: { $avg: "$price" } // Calculates the average price in each category
}
}
])
Explanation:
1. $group: Groups the products by category and calculates the average price of products in each category using the $avg operator.
Assignment 3: Finding Orders Above a Certain Amount and Sorting by Date
Task:
Write a query to find orders where the totalAmount is greater than 500, and sort them by orderDate in ascending order.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$match: { totalAmount: { $gt: 500 } } // Filters orders with totalAmount > 500
},
{
$sort: { orderDate: 1 } // Sorts the results by orderDate in ascending order
}
])
Explanation:
1. $match: Filters orders where totalAmount is greater than 500.
2. $sort: Sorts the filtered documents by orderDate in ascending order (1).
Assignment 4: Calculating Total Sales by Category
Task:
Group the orders by category, and calculate the total sales (sum of totalAmount) for each category.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: "$category", // Groups orders by category
totalSales: { $sum: "$totalAmount" } // Sums the totalAmount for each category
}
}
])
Explanation:
1. $group: Groups orders by category and calculates the sum of totalAmount for each category using the $sum operator.
Assignment 5: Finding the Highest Paying Customer
Task:
Write a query to find the customer with the highest totalAmount spent, and return their customerId and totalAmount.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: "$customerId", // Groups by customerId
totalSpent: { $sum: "$totalAmount" } // Sums totalAmount for each customer
}
},
{
$sort: { totalSpent: -1 } // Sorts by totalSpent in descending order
},
{
$limit: 1 // Limits the result to the top 1 customer
}
])
Explanation:
1. $group: Groups the orders by customerId and calculates the total amount spent by each customer.
2. $sort: Sorts the customers by the totalSpent field in descending order.
3. $limit: Limits the result to the top customer.
Assignment 6: Filter Employees by Salary Range and Calculate Average Salary
Task:
Find employees with a salary between 40,000 and 60,000, and calculate their average salary.
Solution:
javascript
CopyEdit
db.employees.aggregate([
{
$match: { salary: { $gte: 40000, $lte: 60000 } } // Filters employees with salary in range
},
{
$group: {
_id: null, // No grouping (calculates for all employees)
averageSalary: { $avg: "$salary" } // Calculates the average salary
}
}
])
Explanation:
1. $match: Filters employees whose salary is between 40,000 and 60,000.
2. $group: Computes the average salary of the filtered employees using $avg.
Assignment 7: Sorting Products by Stock Availability
Task:
Write a query to sort products by stock in descending order, showing only the productName and stock fields.
Solution:
javascript
CopyEdit
db.products.aggregate([
{
$sort: { stock: -1 } // Sorts products by stock in descending order
},
{
$project: {
productName: 1, // Includes productName
stock: 1 // Includes stock field
}
}
])
Explanation:
1. $sort: Sorts products based on stock in descending order (-1).
2. $project: Includes only the productName and stock fields in the result.
Assignment 8: Grouping Orders by Date and Calculating Total Amount
Task:
Group orders by orderDate and calculate the total totalAmount for each day.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$group: {
_id: "$orderDate", // Groups by orderDate
dailyTotal: { $sum: "$totalAmount" } // Sums totalAmount for each day
}
}
])
Explanation:
1. $group: Groups the orders by orderDate and calculates the sum of totalAmount for each day using $sum.
Assignment 9: Filtering and Projecting Products with Specific Tags
Task:
Retrieve products with the tag "sale", and project only the productName and tags fields.
Solution:
javascript
CopyEdit
db.products.aggregate([
{
$match: { tags: "sale" } // Filters products with the tag "sale"
},
{
$project: {
productName: 1, // Includes productName
tags: 1 // Includes tags field
}
}
])
Explanation:
1. $match: Filters products whose tags array contains the value "sale".
2. $project: Includes only the productName and tags fields in the output.
Assignment 10: Calculating Total Revenue by Year
Task:
Group the orders by year (orderDate), calculate the total totalAmount for each year, and sort the results by year in ascending order.
Solution:
javascript
CopyEdit
db.orders.aggregate([
{
$project: {
year: { $year: "$orderDate" }, // Extracts the year from orderDate
totalAmount: 1 // Includes totalAmount field
}
},
{
$group: {
_id: "$year", // Groups by year
totalRevenue: { $sum: "$totalAmount" } // Sums the totalAmount for each year
}
},
{
$sort: { _id: 1 } // Sorts by year in ascending order
}
])
Explanation:
1. $project: Extracts the year from orderDate using $year and includes totalAmount.
2. $group: Groups by the extracted year and calculates the total revenue for each year.
3. $sort: Sorts the results by year in ascending order.
Summary of Solutions:
These assignments cover a wide range of aggregation operations in MongoDB, such as filtering ($match), projecting ($project), grouping ($group), and sorting ($sort). Each task is designed to help students develop a deeper understanding of how to use the MongoDB aggregation framework to analyze and transform data.
