Advanced MongoDB Aggregation Pipeline Examples

Real-World Use Cases with Full Code – Ready to Run in mongosh

Advanced MongoDB Aggregation Pipeline Examples

Real-World Use Cases with Full Code – Ready to Run in mongosh

Setup: Sample Data

use ecommerceDB
db.dropDatabase()

// Insert Products
db.products.insertMany([
  { name: "Laptop Pro", price: 1299, category: "Electronics", tags: ["laptop", "work", "premium"], inStock: true, rating: 4.5, reviews: 120 },
  { name: "Wireless Mouse", price: 59, category: "Electronics", tags: ["mouse", "wireless"], inStock: true, rating: 4.2, reviews: 85 },
  { name: "USB-C Hub", price: 89, category: "Electronics", tags: ["hub", "accessory"], inStock: false, rating: 4.0, reviews: 45 },
  { name: "Coffee Maker", price: 119, category: "Home", tags: ["kitchen", "coffee"], inStock: true, rating: 4.7, reviews: 200 },
  { name: "Yoga Mat", price: 29, category: "Fitness", tags: ["yoga", "exercise"], inStock: true, rating: 4.6, reviews: 300 }
])

// Insert Orders
db.orders.insertMany([
  { orderId: "ORD001", customer: "Alice", items: ["Laptop Pro", "Wireless Mouse"], total: 1358, status: "completed", date: ISODate("2025-01-15"), country: "USA" },
  { orderId: "ORD002", customer: "Bob", items: ["USB-C Hub"], total: 89, status: "pending", date: ISODate("2025-01-16"), country: "Canada" },
  { orderId: "ORD003", customer: "Alice", items: ["Coffee Maker", "Yoga Mat"], total: 148, status: "completed", date: ISODate("2025-01-17"), country: "USA" },
  { orderId: "ORD004", customer: "Charlie", items: ["Wireless Mouse", "Yoga Mat"], total: 88, status: "shipped", date: ISODate("2025-01-18"), country: "UK" },
  { orderId: "ORD005", customer: "Diana", items: ["Laptop Pro"], total: 1299, status: "completed", date: ISODate("2025-01-19"), country: "Germany" }
])

// Insert Customers
db.customers.insertMany([
  { name: "Alice", email: "alice@example.com", joined: ISODate("2024-01-01"), tier: "gold", age: 28 },
  { name: "Bob", email: "bob@example.com", joined: ISODate("2024-06-15"), tier: "silver", age: 35 },
  { name: "Charlie", email: "charlie@example.com", joined: ISODate("2025-01-01"), tier: "bronze", age: 42 },
  { name: "Diana", email: "diana@example.com", joined: ISODate("2023-12-01"), tier: "platinum", age: 31 }
])

1. Top 3 Customers by Total Spend (with Rank)

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: {
      _id: "$customer",
      totalSpent: { $sum: "$total" },
      orderCount: { $sum: 1 }
  }},
  { $sort: { totalSpent: -1 } },
  { $limit: 3 },
  { $setWindowFields: {
      sortBy: { totalSpent: -1 },
      output: {
        rank: { $rank: {} }
      }
  }},
  { $project: { _id: 0, customer: "$_id", totalSpent: 1, orderCount: 1, rank: 1 } }
])

Uses $setWindowFields (MongoDB 5.0+)

2. Monthly Revenue Trend (Last 12 Months)

db.orders.aggregate([
  { $match: { status: "completed" } },
  {
    $group: {
      _id: {
        year: { $year: "$date" },
        month: { $month: "$date" }
      },
      revenue: { $sum: "$total" },
      orders: { $sum: 1 }
    }
  },
  { $sort: { "_id.year": 1, "_id.month": 1 } },
  { $project: {
      month: {
        $dateToString: {
          format: "%Y-%m",
          date: { $dateFromParts: { year: "$_id.year", month: "$_id.month", day: 1 } }
        }
      },
      revenue: 1,
      orders: 1,
      _id: 0
    }
  }
])

3. Product Performance: Sales, Revenue, Avg Rating

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $unwind: "$items" },
  { $group: {
      _id: "$items",
      totalSold: { $sum: 1 },
      revenue: { $sum: "$total" }
  }},
  { $lookup: {
      from: "products",
      localField: "_id",
      foreignField: "name",
      as: "product"
  }},
  { $unwind: "$product" },
  { $project: {
      product: "$_id",
      totalSold: 1,
      revenue: 1,
      avgRating: "$product.rating",
      reviews: "$product.reviews",
      inStock: "$product.inStock"
  }},
  { $sort: { totalSold: -1 } }
])

4. Customer Lifetime Value (CLV) with Tier

db.customers.aggregate([
  {
    $lookup: {
      from: "orders",
      localField: "name",
      foreignField: "customer",
      as: "orders"
    }
  },
  {
    $addFields: {
      totalSpent: {
        $sum: {
          $map: {
            input: { $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } } },
            in: "$$this.total"
          }
        }
      },
      orderCount: {
        $size: {
          $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } }
        }
      },
      avgOrderValue: {
        $cond: [
          { $gt: [{ $size: { $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } } } }, 0] },
          { $divide: [
            { $sum: { $map: { input: { $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } } }, in: "$$this.total" } } },
            { $size: { $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } } } }
          ]},
          0
        ]
      }
    }
  },
  {
    $project: {
      name: 1,
      email: 1,
      tier: 1,
      totalSpent: 1,
      orderCount: 1,
      avgOrderValue: { $round: ["$avgOrderValue", 2] },
      clv: { $round: [{ $multiply: ["$avgOrderValue", 12] }, 0] }  // Estimated yearly
    }
  },
  { $sort: { totalSpent: -1 } }
])

5. Inventory Alert: Low Stock + High Demand

db.products.aggregate([
  {
    $lookup: {
      from: "orders",
      let: { prodName: "$name" },
      pipeline: [
        { $match: { $expr: { $in: ["$$prodName", "$items"] } } },
        { $count: "orders" }
      ],
      as: "orderCount"
    }
  },
  {
    $addFields: {
      orderCount: { $ifNull: [{ $arrayElemAt: ["$orderCount.orders", 0] }, 0] }
    }
  },
  {
    $match: {
      inStock: false,
      orderCount: { $gte: 1 }
    }
  },
  {
    $project: {
      name: 1,
      orderCount: 1,
      rating: 1,
      alert: "OUT OF STOCK - HIGH DEMAND"
    }
  }
])

6. Funnel Analysis: Orders → Completed → Shipped

db.orders.aggregate([
  {
    $facet: {
      totalOrders: [{ $count: "count" }],
      completed: [
        { $match: { status: "completed" } },
        { $count: "count" }
      ],
      shipped: [
        { $match: { status: { $in: ["completed", "shipped"] } } },
        { $count: "count" }
      ]
    }
  },
  {
    $project: {
      total: { $arrayElemAt: ["$totalOrders.count", 0] },
      completed: { $arrayElemAt: ["$completed.count", 0] },
      shipped: { $arrayElemAt: ["$shipped.count", 0] }
    }
  },
  {
    $addFields: {
      completionRate: {
        $round: [
          { $multiply: [{ $divide: ["$completed", "$total"] }, 100] },
          1
        ]
      },
      shipmentRate: {
        $round: [
          { $multiply: [{ $divide: ["$shipped", "$total"] }, 100] },
          1
        ]
      }
    }
  }
])

7. Dynamic Date Range: Last 7 Days Sales

db.orders.aggregate([
  {
    $match: {
      status: "completed",
      date: {
        $gte: { $dateSubtract: { startDate: "$$NOW", unit: "day", amount: 7 } }
      }
    }
  },
  {
    $group: {
      _id: { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
      dailySales: { $sum: "$total" },
      orders: { $sum: 1 }
    }
  },
  { $sort: { _id: 1 } }
])

8. Bucket Products by Price Range

db.products.aggregate([
  {
    $bucket: {
      groupBy: "$price",
      boundaries: [0, 50, 100, 500, 1000, Infinity],
      default: "Unknown",
      output: {
        count: { $sum: 1 },
        products: { $push: "$name" },
        avgRating: { $avg: "$rating" }
      }
    }
  },
  {
    $project: {
      range: {
        $switch: {
          branches: [
            { case: { $eq: ["$_id", 0] }, then: "Under $50" },
            { case: { $eq: ["$_id", 50] }, then: "$50–$99" },
            { case: { $eq: ["$_id", 100] }, then: "$100–$499" },
            { case: { $eq: ["$_id", 500] }, then: "$500–$999" },
            { case: { $eq: ["$_id", 1000] }, then: "$1000+" }
          ],
          default: "Unknown"
        }
      },
      count: 1,
      avgRating: { $round: ["$avgRating", 1] },
      sampleProducts: { $slice: ["$products", 2] }
    }
  }
])

9. Correlation: Rating vs Price

db.products.aggregate([
  {
    $project: {
      name: 1,
      price: 1,
      rating: 1,
      priceBucket: {
        $switch: {
          branches: [
            { case: { $lt: ["$price", 50] }, then: "Low" },
            { case: { $lt: ["$price", 200] }, then: "Mid" },
            { case: { $gte: ["$price", 200] }, then: "High" }
          ],
          default: "Unknown"
        }
      }
    }
  },
  {
    $group: {
      _id: "$priceBucket",
      avgRating: { $avg: "$rating" },
      count: { $sum: 1 }
    }
  },
  { $sort: { _id: 1 } }
])

10. Export to New Collection

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$customer", total: { $sum: "$total" } } },
  { $merge: { into: "top_customers", whenMatched: "replace", whenNotMatched: "insert" } }
])

// Verify
db.top_customers.find().pretty()

Bonus: Real-Time Dashboard Query

db.orders.aggregate([
  { $match: { date: { $gte: ISODate("2025-01-01") } } },
  {
    $facet: {
      revenueByStatus: [
        { $group: { _id: "$status", total: { $sum: "$total" } } }
      ],
      topProducts: [
        { $unwind: "$items" },
        { $group: { _id: "$items", count: { $sum: 1 } } },
        { $sort: { count: -1 } },
        { $limit: 5 }
      ],
      countryBreakdown: [
        { $group: { _id: "$country", orders: { $sum: 1 } } }
      ]
    }
  }
])

Pro Tips

Tip	Command
Test stage-by-stage	Add `.pretty()` after each stage
Debug with `$project`	See intermediate fields
Use `allowDiskUse`	`{ allowDiskUse: true }`
Index key fields	`date`, `status`, `customer`
Use Atlas Charts	Visualize results

Resources

You now have 10+ advanced, production-ready aggregation pipelines!
Copy → Paste → Run → Master MongoDB Analytics

Want a full analytics dashboard in Node.js + Chart.js? Ask me!

Last updated: Nov 12, 2025

Advanced MongoDB Aggregation Pipeline Examples

Real-World Use Cases with Full Code – Ready to Run in mongosh

Advanced MongoDB Aggregation Pipeline Examples

Real-World Use Cases with Full Code – Ready to Run in mongosh

Setup: Sample Data

use ecommerceDB
db.dropDatabase()

// Insert Products
db.products.insertMany([
  { name: "Laptop Pro", price: 1299, category: "Electronics", tags: ["laptop", "work", "premium"], inStock: true, rating: 4.5, reviews: 120 },
  { name: "Wireless Mouse", price: 59, category: "Electronics", tags: ["mouse", "wireless"], inStock: true, rating: 4.2, reviews: 85 },
  { name: "USB-C Hub", price: 89, category: "Electronics", tags: ["hub", "accessory"], inStock: false, rating: 4.0, reviews: 45 },
  { name: "Coffee Maker", price: 119, category: "Home", tags: ["kitchen", "coffee"], inStock: true, rating: 4.7, reviews: 200 },
  { name: "Yoga Mat", price: 29, category: "Fitness", tags: ["yoga", "exercise"], inStock: true, rating: 4.6, reviews: 300 }
])

// Insert Orders
db.orders.insertMany([
  { orderId: "ORD001", customer: "Alice", items: ["Laptop Pro", "Wireless Mouse"], total: 1358, status: "completed", date: ISODate("2025-01-15"), country: "USA" },
  { orderId: "ORD002", customer: "Bob", items: ["USB-C Hub"], total: 89, status: "pending", date: ISODate("2025-01-16"), country: "Canada" },
  { orderId: "ORD003", customer: "Alice", items: ["Coffee Maker", "Yoga Mat"], total: 148, status: "completed", date: ISODate("2025-01-17"), country: "USA" },
  { orderId: "ORD004", customer: "Charlie", items: ["Wireless Mouse", "Yoga Mat"], total: 88, status: "shipped", date: ISODate("2025-01-18"), country: "UK" },
  { orderId: "ORD005", customer: "Diana", items: ["Laptop Pro"], total: 1299, status: "completed", date: ISODate("2025-01-19"), country: "Germany" }
])

// Insert Customers
db.customers.insertMany([
  { name: "Alice", email: "alice@example.com", joined: ISODate("2024-01-01"), tier: "gold", age: 28 },
  { name: "Bob", email: "bob@example.com", joined: ISODate("2024-06-15"), tier: "silver", age: 35 },
  { name: "Charlie", email: "charlie@example.com", joined: ISODate("2025-01-01"), tier: "bronze", age: 42 },
  { name: "Diana", email: "diana@example.com", joined: ISODate("2023-12-01"), tier: "platinum", age: 31 }
])

1. Top 3 Customers by Total Spend (with Rank)

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: {
      _id: "$customer",
      totalSpent: { $sum: "$total" },
      orderCount: { $sum: 1 }
  }},
  { $sort: { totalSpent: -1 } },
  { $limit: 3 },
  { $setWindowFields: {
      sortBy: { totalSpent: -1 },
      output: {
        rank: { $rank: {} }
      }
  }},
  { $project: { _id: 0, customer: "$_id", totalSpent: 1, orderCount: 1, rank: 1 } }
])

Uses $setWindowFields (MongoDB 5.0+)

2. Monthly Revenue Trend (Last 12 Months)

db.orders.aggregate([
  { $match: { status: "completed" } },
  {
    $group: {
      _id: {
        year: { $year: "$date" },
        month: { $month: "$date" }
      },
      revenue: { $sum: "$total" },
      orders: { $sum: 1 }
    }
  },
  { $sort: { "_id.year": 1, "_id.month": 1 } },
  { $project: {
      month: {
        $dateToString: {
          format: "%Y-%m",
          date: { $dateFromParts: { year: "$_id.year", month: "$_id.month", day: 1 } }
        }
      },
      revenue: 1,
      orders: 1,
      _id: 0
    }
  }
])

3. Product Performance: Sales, Revenue, Avg Rating

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $unwind: "$items" },
  { $group: {
      _id: "$items",
      totalSold: { $sum: 1 },
      revenue: { $sum: "$total" }
  }},
  { $lookup: {
      from: "products",
      localField: "_id",
      foreignField: "name",
      as: "product"
  }},
  { $unwind: "$product" },
  { $project: {
      product: "$_id",
      totalSold: 1,
      revenue: 1,
      avgRating: "$product.rating",
      reviews: "$product.reviews",
      inStock: "$product.inStock"
  }},
  { $sort: { totalSold: -1 } }
])

4. Customer Lifetime Value (CLV) with Tier

db.customers.aggregate([
  {
    $lookup: {
      from: "orders",
      localField: "name",
      foreignField: "customer",
      as: "orders"
    }
  },
  {
    $addFields: {
      totalSpent: {
        $sum: {
          $map: {
            input: { $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } } },
            in: "$$this.total"
          }
        }
      },
      orderCount: {
        $size: {
          $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } }
        }
      },
      avgOrderValue: {
        $cond: [
          { $gt: [{ $size: { $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } } } }, 0] },
          { $divide: [
            { $sum: { $map: { input: { $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } } }, in: "$$this.total" } } },
            { $size: { $filter: { input: "$orders", cond: { $eq: ["$$this.status", "completed"] } } } }
          ]},
          0
        ]
      }
    }
  },
  {
    $project: {
      name: 1,
      email: 1,
      tier: 1,
      totalSpent: 1,
      orderCount: 1,
      avgOrderValue: { $round: ["$avgOrderValue", 2] },
      clv: { $round: [{ $multiply: ["$avgOrderValue", 12] }, 0] }  // Estimated yearly
    }
  },
  { $sort: { totalSpent: -1 } }
])

5. Inventory Alert: Low Stock + High Demand

db.products.aggregate([
  {
    $lookup: {
      from: "orders",
      let: { prodName: "$name" },
      pipeline: [
        { $match: { $expr: { $in: ["$$prodName", "$items"] } } },
        { $count: "orders" }
      ],
      as: "orderCount"
    }
  },
  {
    $addFields: {
      orderCount: { $ifNull: [{ $arrayElemAt: ["$orderCount.orders", 0] }, 0] }
    }
  },
  {
    $match: {
      inStock: false,
      orderCount: { $gte: 1 }
    }
  },
  {
    $project: {
      name: 1,
      orderCount: 1,
      rating: 1,
      alert: "OUT OF STOCK - HIGH DEMAND"
    }
  }
])

6. Funnel Analysis: Orders → Completed → Shipped

db.orders.aggregate([
  {
    $facet: {
      totalOrders: [{ $count: "count" }],
      completed: [
        { $match: { status: "completed" } },
        { $count: "count" }
      ],
      shipped: [
        { $match: { status: { $in: ["completed", "shipped"] } } },
        { $count: "count" }
      ]
    }
  },
  {
    $project: {
      total: { $arrayElemAt: ["$totalOrders.count", 0] },
      completed: { $arrayElemAt: ["$completed.count", 0] },
      shipped: { $arrayElemAt: ["$shipped.count", 0] }
    }
  },
  {
    $addFields: {
      completionRate: {
        $round: [
          { $multiply: [{ $divide: ["$completed", "$total"] }, 100] },
          1
        ]
      },
      shipmentRate: {
        $round: [
          { $multiply: [{ $divide: ["$shipped", "$total"] }, 100] },
          1
        ]
      }
    }
  }
])

7. Dynamic Date Range: Last 7 Days Sales

db.orders.aggregate([
  {
    $match: {
      status: "completed",
      date: {
        $gte: { $dateSubtract: { startDate: "$$NOW", unit: "day", amount: 7 } }
      }
    }
  },
  {
    $group: {
      _id: { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
      dailySales: { $sum: "$total" },
      orders: { $sum: 1 }
    }
  },
  { $sort: { _id: 1 } }
])

8. Bucket Products by Price Range

db.products.aggregate([
  {
    $bucket: {
      groupBy: "$price",
      boundaries: [0, 50, 100, 500, 1000, Infinity],
      default: "Unknown",
      output: {
        count: { $sum: 1 },
        products: { $push: "$name" },
        avgRating: { $avg: "$rating" }
      }
    }
  },
  {
    $project: {
      range: {
        $switch: {
          branches: [
            { case: { $eq: ["$_id", 0] }, then: "Under $50" },
            { case: { $eq: ["$_id", 50] }, then: "$50–$99" },
            { case: { $eq: ["$_id", 100] }, then: "$100–$499" },
            { case: { $eq: ["$_id", 500] }, then: "$500–$999" },
            { case: { $eq: ["$_id", 1000] }, then: "$1000+" }
          ],
          default: "Unknown"
        }
      },
      count: 1,
      avgRating: { $round: ["$avgRating", 1] },
      sampleProducts: { $slice: ["$products", 2] }
    }
  }
])

9. Correlation: Rating vs Price

db.products.aggregate([
  {
    $project: {
      name: 1,
      price: 1,
      rating: 1,
      priceBucket: {
        $switch: {
          branches: [
            { case: { $lt: ["$price", 50] }, then: "Low" },
            { case: { $lt: ["$price", 200] }, then: "Mid" },
            { case: { $gte: ["$price", 200] }, then: "High" }
          ],
          default: "Unknown"
        }
      }
    }
  },
  {
    $group: {
      _id: "$priceBucket",
      avgRating: { $avg: "$rating" },
      count: { $sum: 1 }
    }
  },
  { $sort: { _id: 1 } }
])

10. Export to New Collection

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$customer", total: { $sum: "$total" } } },
  { $merge: { into: "top_customers", whenMatched: "replace", whenNotMatched: "insert" } }
])

// Verify
db.top_customers.find().pretty()

Bonus: Real-Time Dashboard Query

db.orders.aggregate([
  { $match: { date: { $gte: ISODate("2025-01-01") } } },
  {
    $facet: {
      revenueByStatus: [
        { $group: { _id: "$status", total: { $sum: "$total" } } }
      ],
      topProducts: [
        { $unwind: "$items" },
        { $group: { _id: "$items", count: { $sum: 1 } } },
        { $sort: { count: -1 } },
        { $limit: 5 }
      ],
      countryBreakdown: [
        { $group: { _id: "$country", orders: { $sum: 1 } } }
      ]
    }
  }
])

Pro Tips

Tip	Command
Test stage-by-stage	Add `.pretty()` after each stage
Debug with `$project`	See intermediate fields
Use `allowDiskUse`	`{ allowDiskUse: true }`
Index key fields	`date`, `status`, `customer`
Use Atlas Charts	Visualize results

Resources

You now have 10+ advanced, production-ready aggregation pipelines!
Copy → Paste → Run → Master MongoDB Analytics

Want a full analytics dashboard in Node.js + Chart.js? Ask me!

Last updated: Nov 12, 2025