Section 14: Cheatsheets
📊 MongoDB Aggregation Cheatsheet
Complete reference guide with live console examples - All operators, stages, and real-world patterns
Pipeline Stages
Core aggregation pipeline stages
$match
Filter Stage
Filters documents to pass only those that match the specified condition(s). Similar to find() but in the pipeline.
mongosh
> // Filter Manhattan restaurants
> db.restaurants.aggregate([
{ $match: {
borough: "Manhattan",
cuisine: "Italian"
} }
])
// Returns only Manhattan Italian restaurants
{ _id: ..., name: "Luigi's Pizza", borough: "Manhattan", cuisine: "Italian" }
{ _id: ..., name: "Roma Restaurant", borough: "Manhattan", cuisine: "Italian" }
// ... more results
💡 Best Practice
Always put $match as early as possible in your pipeline to reduce the number of documents processed in subsequent stages. This dramatically improves performance!
$group
Aggregation Stage
Groups documents by a specified expression and outputs one document for each distinct grouping. Use with accumulators like $sum, $avg, $max, $min.
mongosh
> // Count restaurants by borough
> db.restaurants.aggregate([
{ $group: {
_id: "$borough",
count: { $sum: 1 },
avgScore: { $avg: "$grades.0.score" }
} },
{ $sort: { count: -1 } }
])
// Grouped results with counts and averages
{ _id: "Manhattan", count: 1883, avgScore: 11.2 }
{ _id: "Queens", count: 738, avgScore: 10.8 }
{ _id: "Brooklyn", count: 684, avgScore: 11.5 }
$project
Reshape Stage
Reshapes documents by including, excluding, or adding new fields. Can also create computed fields.
mongosh
> // Select and rename fields
> db.restaurants.aggregate([
{ $match: { borough: "Manhattan" } },
{ $project: {
_id: 0,
restaurantName: "$name",
location: "$borough",
foodType: "$cuisine",
latestGrade: "$grades.0.grade",
isHighQuality: { $lt: ["$grades.0.score", 10] }
} },
{ $limit: 3 }
])
// Reshaped documents with computed field
{ restaurantName: "Shake Shack", location: "Manhattan", foodType: "Burgers", latestGrade: "A", isHighQuality: true }
{ restaurantName: "Joe's Pizza", location: "Manhattan", foodType: "Pizza", latestGrade: "A", isHighQuality: true }
$unwind
Array Stage
Deconstructs an array field from input documents to output a document for each element.
mongosh
> // Count movies per genre (genres is array)
> db.movies.aggregate([
{ $unwind: "$genres" },
{ $group: {
_id: "$genres",
count: { $sum: 1 },
avgRating: { $avg: "$imdb.rating" }
} },
{ $sort: { count: -1 } },
{ $limit: 5 }
])
// Each genre counted separately
{ _id: "Drama", count: 1854, avgRating: 7.2 }
{ _id: "Comedy", count: 1247, avgRating: 6.8 }
{ _id: "Action", count: 982, avgRating: 6.5 }
$lookup
Join Stage
Performs a left outer join to another collection in the same database. Like SQL LEFT JOIN.
mongosh
> // Join books with authors
> db.books.aggregate([
{ $lookup: {
from: "authors",
localField: "author_id",
foreignField: "_id",
as: "authorDetails"
} },
{ $project: {
title: 1,
authorName: { $arrayElemAt: ["$authorDetails.name", 0] }
} },
{ $limit: 3 }
])
// Books with author information
{ _id: ..., title: "To Kill a Mockingbird", authorName: "Harper Lee" }
{ _id: ..., title: "1984", authorName: "George Orwell" }
Grouping Accumulators
Used with $group stage for aggregations
| Operator | Description | Example | Output |
|---|---|---|---|
$sum |
Count or sum values | { $sum: 1 } |
Count of documents |
$avg |
Calculate average | { $avg: "$price" } |
Average price |
$min |
Find minimum | { $min: "$score" } |
Lowest score |
$max |
Find maximum | { $max: "$score" } |
Highest score |
$first |
First value in group | { $first: "$name" } |
First document's name |
$last |
Last value in group | { $last: "$date" } |
Last document's date |
$push |
Collect into array | { $push: "$item" } |
Array of all items |
$addToSet |
Unique values array | { $addToSet: "$category" } |
Unique categories |
Complete Example - All Accumulators
> // Comprehensive customer analysis
> db.orders.aggregate([
{ $group: {
_id: "$customerId",
totalOrders: { $sum: 1 },
totalSpent: { $sum: "$amount" },
avgOrder: { $avg: "$amount" },
maxOrder: { $max: "$amount" },
minOrder: { $min: "$amount" },
allProducts: { $push: "$productName" },
uniqueCategories: { $addToSet: "$category" }
} },
{ $limit: 2 }
])
// Rich customer statistics
{
_id: "CUST123",
totalOrders: 8,
totalSpent: 2450.00,
avgOrder: 306.25,
maxOrder: 599.99,
minOrder: 49.99,
allProducts: ["Laptop", "Mouse", "Keyboard", ...],
uniqueCategories: ["Electronics", "Accessories"]
}
Conditional Operators
If-else logic in aggregation pipelines
$cond
Ternary
Evaluates a boolean expression and returns one of two specified values.
mongosh
> // Categorize by score
> db.students.aggregate([
{ $project: {
name: 1,
score: 1,
result: {
$cond: [
{ $gte: ["$score", 75] },
"Pass",
"Fail"
]
}
} },
{ $limit: 3 }
])
{ name: "Alice", score: 85, result: "Pass" }
{ name: "Bob", score: 62, result: "Fail" }
{ name: "Carol", score: 91, result: "Pass" }
$switch
Multi-Branch
Multi-way conditional (like switch/case in programming). Evaluates multiple conditions.
mongosh
> // Categorize ratings into tiers
> db.movies.aggregate([
{ $project: {
title: 1,
rating: "$imdb.rating",
tier: {
$switch: {
branches: [
{ case: { $gte: ["$imdb.rating", 9] }, then: "Masterpiece" },
{ case: { $gte: ["$imdb.rating", 7] }, then: "Excellent" },
{ case: { $gte: ["$imdb.rating", 5] }, then: "Good" }
],
default: "Poor"
}
}
} },
{ $limit: 3 }
])
{ title: "The Shawshank Redemption", rating: 9.3, tier: "Masterpiece" }
{ title: "Inception", rating: 8.8, tier: "Excellent" }
{ title: "Avatar", rating: 7.8, tier: "Excellent" }
Array Operators
Manipulate and query arrays
Array Operations Complete Example
> // Array operations showcase
> db.products.aggregate([
{ $project: {
name: 1,
tags: 1,
// $size - array length
tagCount: { $size: "$tags" },
// $arrayElemAt - get specific element
firstTag: { $arrayElemAt: ["$tags", 0] },
lastTag: { $arrayElemAt: ["$tags", -1] },
// $slice - get subset
first2Tags: { $slice: ["$tags", 2] },
// $filter - filter array elements
premiumTags: {
$filter: {
input: "$tags",
as: "tag",
cond: { $in: ["$$tag", ["premium", "exclusive"]] }
}
}
} },
{ $limit: 1 }
])
{
name: "Gaming Laptop",
tags: ["electronics", "gaming", "premium", "portable"],
tagCount: 4,
firstTag: "electronics",
lastTag: "portable",
first2Tags: ["electronics", "gaming"],
premiumTags: ["premium"]
}
| Operator | Purpose | Syntax |
|---|---|---|
$size |
Get array length | { $size: "$arrayField" } |
$arrayElemAt |
Get element at index | { $arrayElemAt: ["$array", 0] } |
$slice |
Get array subset | { $slice: ["$array", 3] } |
$filter |
Filter array elements | { $filter: { input: "$arr", cond: ... } } |
$map |
Transform array elements | { $map: { input: "$arr", in: ... } } |
$reduce |
Reduce to single value | { $reduce: { input: "$arr", initialValue: 0, in: ... } } |
$in |
Check if value in array | { $in: ["value", "$array"] } |
$concatArrays |
Concatenate arrays | { $concatArrays: ["$arr1", "$arr2"] } |
String Operators
String manipulation and formatting
String Operations Showcase
> // String operations demo
> db.users.aggregate([
{ $project: {
firstName: 1,
lastName: 1,
email: 1,
// $concat - join strings
fullName: { $concat: ["$firstName", " ", "$lastName"] },
// $toUpper / $toLower - change case
upperName: { $toUpper: "$firstName" },
lowerEmail: { $toLower: "$email" },
// $strLenCP - string length
nameLength: { $strLenCP: "$firstName" },
// $substr - substring
initials: { $concat: [
{ $substr: ["$firstName", 0, 1] },
".",
{ $substr: ["$lastName", 0, 1] },
"."
] }
} },
{ $limit: 1 }
])
{
firstName: "John",
lastName: "Doe",
email: "John.Doe@Example.COM",
fullName: "John Doe",
upperName: "JOHN",
lowerEmail: "john.doe@example.com",
nameLength: 4,
initials: "J.D."
}
Date Operators
Date manipulation and extraction
Date Operations Complete Example
> // Extract date components
> db.orders.aggregate([
{ $project: {
orderDate: 1,
amount: 1,
// Extract date parts
year: { $year: "$orderDate" },
month: { $month: "$orderDate" },
day: { $dayOfMonth: "$orderDate" },
dayOfWeek: { $dayOfWeek: "$orderDate" },
// Format date as string
formattedDate: {
$dateToString: {
format: "%Y-%m-%d",
date: "$orderDate"
}
},
// Calculate date difference
daysAgo: {
$dateDiff: {
startDate: "$orderDate",
endDate: "$$NOW",
unit: "day"
}
}
} },
{ $limit: 1 }
])
{
orderDate: ISODate("2024-03-15T10:30:00.000Z"),
amount: 299.99,
year: 2024,
month: 3,
day: 15,
dayOfWeek: 6, // 1=Sunday, 7=Saturday
formattedDate: "2024-03-15",
daysAgo: 275
}
Math Operators
Arithmetic and numeric operations
| Operator | Operation | Example | Result |
|---|---|---|---|
$add |
Addition | { $add: [10, 5] } |
15 |
$subtract |
Subtraction | { $subtract: [100, 25] } |
75 |
$multiply |
Multiplication | { $multiply: ["$price", "$qty"] } |
price × qty |
$divide |
Division | { $divide: [100, 4] } |
25 |
$mod |
Modulo (remainder) | { $mod: [10, 3] } |
1 |
$pow |
Power | { $pow: [2, 3] } |
8 (2³) |
$sqrt |
Square root | { $sqrt: 16 } |
4 |
$round |
Round to decimals | { $round: [3.14159, 2] } |
3.14 |
$ceil |
Round up | { $ceil: 4.2 } |
5 |
$floor |
Round down | { $floor: 4.9 } |
4 |
$abs |
Absolute value | { $abs: -10 } |
10 |
Real-World Example - Calculate Invoice
> // Calculate invoice totals
> db.orders.aggregate([
{ $project: {
items: 1,
// Calculate subtotal
subtotal: {
$multiply: ["$price", "$quantity"]
},
// Calculate tax (8%)
tax: {
$multiply: [
{ $multiply: ["$price", "$quantity"] },
0.08
]
},
// Calculate total with rounding
total: {
$round: [
{ $add: [
{ $multiply: ["$price", "$quantity"] },
{ $multiply: [
{ $multiply: ["$price", "$quantity"] },
0.08
]}
]},
2
]
}
} },
{ $limit: 1 }
])
{
items: "Gaming Laptop",
subtotal: 1299.00,
tax: 103.92,
total: 1402.92
}
Real-World Complete Examples
Production-ready aggregation pipelines
E-commerce Customer Analytics
Multi-Stage Pipeline
Complete customer analysis with tier classification, spending patterns, and purchase history.
Top Customers Report
> // Comprehensive customer analysis
> db.orders.aggregate([
// Stage 1: Filter recent orders (last 6 months)
{
$match: {
orderDate: { $gte: new Date("2024-06-01") }
}
},
// Stage 2: Group by customer
{
$group: {
_id: "$customerId",
totalOrders: { $sum: 1 },
totalSpent: { $sum: "$amount" },
avgOrder: { $avg: "$amount" },
products: { $push: "$productName" }
}
},
// Stage 3: Calculate tier
{
$project: {
customerId: "$_id",
totalOrders: 1,
totalSpent: { $round: ["$totalSpent", 2] },
avgOrder: { $round: ["$avgOrder", 2] },
productCount: { $size: "$products" },
tier: {
$switch: {
branches: [
{ case: { $gte: ["$totalSpent", 10000] }, then: "💎 Diamond" },
{ case: { $gte: ["$totalSpent", 5000] }, then: "🥇 Gold" },
{ case: { $gte: ["$totalSpent", 1000] }, then: "🥈 Silver" }
],
default: "🥉 Bronze"
}
}
}
},
// Stage 4: Sort by total spent
{ $sort: { totalSpent: -1 } },
// Stage 5: Limit to top 10
{ $limit: 10 }
])
// Top customers with tier classification
[
{
customerId: "CUST001",
totalOrders: 28,
totalSpent: 15420.50,
avgOrder: 550.73,
productCount: 28,
tier: "💎 Diamond"
},
{
customerId: "CUST042",
totalOrders: 15,
totalSpent: 8750.00,
avgOrder: 583.33,
productCount: 15,
tier: "🥇 Gold"
}
// ... more results
]
Movie Genre Trends by Decade
Complex Analysis
Analyze movie genre performance across decades with ratings and counts.
Genre Analytics Pipeline
> // Decade-by-decade genre analysis
> db.movies.aggregate([
// Stage 1: Unwind genres array
{ $unwind: "$genres" },
// Stage 2: Group by decade and genre
{
$group: {
_id: {
decade: {
$multiply: [
{ $floor: { $divide: ["$year", 10] } },
10
]
},
genre: "$genres"
},
count: { $sum: 1 },
avgRating: { $avg: "$imdb.rating" },
topRated: {
$max: {
rating: "$imdb.rating",
title: "$title"
}
}
}
},
// Stage 3: Reshape output
{
$project: {
_id: 0,
decade: { $concat: [
{ $toString: "$_id.decade" },
"s"
] },
genre: "$_id.genre",
movieCount: "$count",
avgRating: { $round: ["$avgRating", 2] }
}
},
// Stage 4: Sort
{ $sort: { decade: -1, movieCount: -1 } },
// Stage 5: Limit results
{ $limit: 15 }
])
// Genre trends across decades
[
{ decade: "2020s", genre: "Drama", movieCount: 342, avgRating: 7.1 },
{ decade: "2020s", genre: "Action", movieCount: 298, avgRating: 6.8 },
{ decade: "2010s", genre: "Drama", movieCount: 1521, avgRating: 7.2 }
// ... more results
]
⚡ Performance Best Practices
- $match early: Put $match stages at the beginning to reduce documents processed
- Use indexes: Ensure $match and $sort stages can use indexes
- $project wisely: Exclude unnecessary fields early to reduce memory usage
- Avoid $lookup: Consider denormalization if you're doing many joins
- allowDiskUse: For large datasets, use
{ allowDiskUse: true } - $limit early: If you only need a few results, use $limit after $sort
💡 Quick Reference Commands
// Explain aggregation performance
db.collection.aggregate([...], { explain: true })
// Allow disk usage for large data
db.collection.aggregate([...], { allowDiskUse: true })
// Set max execution time
db.collection.aggregate([...], { maxTimeMS: 5000 })