Advanced MongoDB Aggregation & Indexing

Overview

MongoDB's aggregation framework is a data processing pipeline where documents flow through stages — each stage transforms, groups, filters, or joins the data. Mastering it unlocks near-real-time analytics without a separate data warehouse.

Pipeline Anatomy

db.orders.aggregate([
  { $match: { status: "completed", createdAt: { $gte: ISODate("2024-01-01") } } },
  { $group: { _id: "$userId", total: { $sum: "$amount" }, count: { $sum: 1 } } },
  { $sort: { total: -1 } },
  { $limit: 10 },
  { $lookup: { from: "users", localField: "_id", foreignField: "_id", as: "user" } },
  { $unwind: "$user" },
  { $project: { _id: 0, name: "$user.name", total: 1, count: 1 } },
]);

Compound Indexes

Always match index key order to your query's equality + sort + range pattern:

// Supports: { status: "completed", createdAt: { $gte: ... } } + sort by total
db.orders.createIndex({ status: 1, createdAt: -1, total: -1 });

Rules:

1. Equality fields first
2. Sort fields next
3. Range fields last

Covered Queries

A covered query is satisfied entirely by the index — MongoDB never reads documents.

// Index covers all fields in query + projection
db.products.createIndex({ category: 1, price: 1, _id: 0 });
db.products.find({ category: "electronics" }, { price: 1, _id: 0 }).explain("executionStats");
// Look for: "stage": "IXSCAN", totalDocsExamined: 0

Bucket Pattern for Time-Series

Avoid millions of tiny documents. Group readings into hourly/daily buckets:

// Instead of one document per sensor reading:
{
  sensorId: "s1",
  hour: ISODate("2024-10-01T14:00:00Z"),
  count: 60,
  min: 21.2,
  max: 23.8,
  avg: 22.4,
  readings: [22.1, 22.4, 21.9, /* ... */]
}

// Upsert each reading into its bucket
db.metrics.updateOne(
  { sensorId: "s1", hour: bucketStart, count: { $lt: 60 } },
  {
    $inc: { count: 1 },
    $min: { min: value },
    $max: { max: value },
    $push: { readings: value },
  },
  { upsert: true }
);

$facet for Multi-Dimension Analytics

db.products.aggregate([
  { $match: { active: true } },
  {
    $facet: {
      byCategory: [{ $group: { _id: "$category", count: { $sum: 1 } } }],
      priceRange: [
        {
          $bucket: {
            groupBy: "$price",
            boundaries: [0, 50, 100, 500, 1000],
            default: "1000+",
            output: { count: { $sum: 1 } },
          },
        },
      ],
      topRated: [{ $sort: { rating: -1 } }, { $limit: 5 }, { $project: { name: 1, rating: 1 } }],
    },
  },
]);

Index Intersection vs Compound

MongoDB can merge two single-field indexes (index intersection), but a purpose-built compound index is almost always faster. Use explain("executionStats") to verify:

db.orders.find({ userId: "u1", status: "pending" }).explain("executionStats");
// totalKeysExamined should be close to nReturned

Key Takeaways

• Put equality, sort, then range fields in compound indexes in that order
• Use $facet to run multiple aggregation branches in a single pipeline pass
• The Bucket pattern reduces document count by orders of magnitude for time-series data
• Always explain() in staging before shipping a new query to production