Aggregation Patterns Skill

This skill defines the conventions, rules, and best practices for MongoDB aggregation pipelines. Apply these rules when writing new pipelines, reviewing existing pipelines, or optimizing aggregation performance.

---

Table of Contents

1. Pipeline Stage Ordering Rules
2. $lookup vs App-Side Joins
3. $facet Patterns
4. $merge for Materialized Views
5. $out vs $merge
6. Pipeline Optimization Rules
7. Common Aggregation Patterns

---

Pipeline Stage Ordering Rules

Rule PO-1: $match Must Be the First Stage

Always place $match as the first stage in the pipeline. This filters documents early, reducing the workload for all subsequent stages. When $match is the first stage, it can use indexes.

// CORRECT — $match first, uses index on { status: 1, orderDate: 1 }
db.orders.aggregate([
  {
    $match: { status: 'completed', orderDate: { $gte: ISODate('2024-01-01') } },
  },
  { $group: { _id: '$customerId', total: { $sum: '$amount' } } },
  { $sort: { total: -1 } },
  { $limit: 100 },
]);

// WRONG — $match after $group (processes entire collection first)
db.orders.aggregate([
  {
    $group: {
      _id: { customerId: '$customerId', status: '$status' },
      total: { $sum: '$amount' },
    },
  },
  { $match: { '_id.status': 'completed' } },
  // By now, ALL orders have been grouped — waste of resources
]);

When multiple $match conditions exist, split them if an early $match can reduce the dataset significantly:

// CORRECT — split $match: index-supported filter first
db.orders.aggregate([
  { $match: { status: 'completed' } }, // Uses index
  { $lookup: { from: 'customers' /* ... */ } },
  { $match: { 'customer.tier': 'premium' } }, // Post-lookup filter
]);

Rule PO-2: $project Before $group

Reduce document size before grouping to minimize memory consumption during the $group stage.

// CORRECT — project only needed fields before $group
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $project: { customerId: 1, amount: 1, orderDate: 1 } },
  {
    $group: {
      _id: '$customerId',
      totalAmount: { $sum: '$amount' },
      orderCount: { $sum: 1 },
    },
  },
]);

// WRONG — $group receives full documents (50+ fields each)
db.orders.aggregate([
  { $match: { status: 'completed' } },
  // No projection — all fields flow into $group
  {
    $group: {
      _id: '$customerId',
      totalAmount: { $sum: '$amount' }, // Only uses 2 fields
      orderCount: { $sum: 1 },
    },
  },
  // Memory wasted holding 48 unused fields per document
]);

Rule PO-3: $sort + $limit Must Be Adjacent

When sorting and limiting results, place $sort immediately before $limit. MongoDB optimizes this combination by only tracking the top N documents during sorting, dramatically reducing memory usage.

// CORRECT — adjacent $sort + $limit (optimized)
db.products.aggregate([
  { $match: { status: 'active' } },
  { $group: { _id: '$category', avgPrice: { $avg: '$price' } } },
  { $sort: { avgPrice: -1 } },
  { $limit: 10 },
  // MongoDB only tracks top 10 during sort
]);

// WRONG — stage between $sort and $limit
db.products.aggregate([
  { $match: { status: 'active' } },
  { $group: { _id: '$category', avgPrice: { $avg: '$price' } } },
  { $sort: { avgPrice: -1 } },
  { $addFields: { rank: 'top' } }, // Breaks sort+limit coalescing
  { $limit: 10 },
  // MongoDB must sort ALL groups before limiting
]);

Rule PO-4: $match + $sort Uses Compound Indexes

When $match is followed by $sort at the pipeline start, and a compound index covers both, the operation is fully index-supported.

// Given index: { status: 1, createdAt: -1 }

// CORRECT — $match + $sort uses the compound index
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $sort: { createdAt: -1 } },
  { $limit: 50 },
]);
// IXSCAN: no in-memory sort needed

// WRONG — $sort on an unindexed field
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $sort: { total: -1 } }, // No index covers status + total sort
  { $limit: 50 },
  // In-memory sort of potentially millions of documents
]);

Rule PO-5: Avoid $unwind on Large Arrays

$unwind creates one document per array element. Prefer array operators ($filter, $map, $reduce, $size) when possible.

// CORRECT — use $filter instead of $unwind + $match + $group
db.orders.aggregate([
  { $match: { customerId: ObjectId('...') } },
  {
    $addFields: {
      electronicItems: {
        $filter: {
          input: '$items',
          as: 'item',
          cond: { $eq: ['$$item.category', 'electronics'] },
        },
      },
    },
  },
  { $match: { 'electronicItems.0': { $exists: true } } },
]);

// WRONG — $unwind for simple filtering
db.orders.aggregate([
  { $match: { customerId: ObjectId('...') } },
  { $unwind: '$items' }, // 50 items = 50 docs
  { $match: { 'items.category': 'electronics' } },
  {
    $group: {
      // Regroup back
      _id: '$_id',
      items: { $push: '$items' },
      orderDate: { $first: '$orderDate' },
    },
  },
]);

When $unwind is necessary, use preserveNullAndEmptyArrays:

// CORRECT — preserve documents with missing/empty arrays
{ $unwind: { path: "$tags", preserveNullAndEmptyArrays: true } }

// WRONG — silently drops documents without tags
{ $unwind: "$tags" }

Rule PO-6: Limit Documents Before Expensive Stages

Place $limit (or additional $match) before expensive stages like $lookup, $graphLookup, or complex $addFields.

// CORRECT — limit before $lookup
db.orders.aggregate([
  { $match: { status: 'completed' } },
  { $sort: { orderDate: -1 } },
  { $limit: 100 }, // Process only 100 orders
  {
    $lookup: {
      from: 'customers',
      localField: 'customerId',
      foreignField: '_id',
      as: 'customer',
    },
  },
]);

// WRONG — $lookup on entire result set, then limit
db.orders.aggregate([
  { $match: { status: 'completed' } },
  {
    $lookup: {
      // Looks up ALL completed orders
      from: 'customers',
      localField: 'customerId',
      foreignField: '_id',
      as: 'customer',
    },
  },
  { $sort: { orderDate: -1 } },
  { $limit: 100 }, // Wasted lookup on 99,900+ docs
]);

---

$lookup vs App-Side Joins

Rule LK-1: Use $lookup for Same-Database, Small-to-Medium Joins

$lookup is efficient when:

• Both collections are in the same database
• The foreign field is indexed
• The result set is manageable (not millions of joined documents)

// CORRECT — $lookup for order + customer join (same DB, indexed)
db.orders.aggregate([
  {
    $match: { status: 'completed', orderDate: { $gte: ISODate('2024-01-01') } },
  },
  { $limit: 100 },
  {
    $lookup: {
      from: 'customers',
      localField: 'customerId',
      foreignField: '_id',
      as: 'customer',
    },
  },
  { $unwind: '$customer' },
  {
    $project: {
      orderDate: 1,
      total: 1,
      'customer.name': 1,
      'customer.email': 1,
    },
  },
]);

Rule LK-2: Use App-Side Joins for Cross-Database or Large Datasets

When joining across databases, services, or when the joined collection is very large, perform the join in application code.

// CORRECT — app-side join for large datasets
const orders = await db
  .collection('orders')
  .find({ customerId: customerId })
  .sort({ orderDate: -1 })
  .limit(10)
  .toArray();

const productIds = [...new Set(orders.flatMap((o) => o.items.map((i) => i.productId)))];

const products = await db
  .collection('products')
  .find({ _id: { $in: productIds } })
  .project({ name: 1, price: 1, image: 1 })
  .toArray();

const productMap = new Map(products.map((p) => [p._id.toString(), p]));

const enrichedOrders = orders.map((order) => ({
  ...order,
  items: order.items.map((item) => ({
    ...item,
    product: productMap.get(item.productId.toString()),
  })),
}));

Rule LK-3: Always Index the Foreign Field

The foreignField in $lookup must be indexed. Without an index, MongoDB performs a collection scan for every input document.

// CORRECT — ensure index exists before using $lookup
db.products.createIndex({ sku: 1 });

db.orders.aggregate([
  {
    $lookup: {
      from: 'products',
      localField: 'items.sku',
      foreignField: 'sku', // Indexed field
      as: 'productDetails',
    },
  },
]);

// WRONG — $lookup on unindexed foreign field
// products.sku has no index
db.orders.aggregate([
  {
    $lookup: {
      from: 'products',
      localField: 'items.sku',
      foreignField: 'sku', // No index = collection scan per document
      as: 'productDetails',
    },
  },
]);
// With 10,000 orders and 50,000 products: 10,000 * 50,000 comparisons

Rule LK-4: Use Pipeline $lookup for Complex Conditions

When join conditions are more complex than equality, use the pipeline form.

// CORRECT — pipeline $lookup with conditions and projection
db.orders.aggregate([
  { $match: { status: 'completed' } },
  {
    $lookup: {
      from: 'reviews',
      let: { orderId: '$_id', custId: '$customerId' },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [{ $eq: ['$orderId', '$$orderId'] }, { $eq: ['$customerId', '$$custId'] }],
            },
          },
        },
        { $match: { rating: { $gte: 4 } } },
        { $project: { rating: 1, comment: 1 } },
        { $limit: 3 },
      ],
      as: 'topReviews',
    },
  },
]);

Rule LK-5: $lookup + $unwind Coalescing

When $lookup is immediately followed by $unwind on the same field, MongoDB optimizes this into a single stage internally. Leverage this pattern.

// CORRECT — coalesced $lookup + $unwind
db.orders.aggregate([
  { $match: { status: 'completed' } },
  {
    $lookup: {
      from: 'customers',
      localField: 'customerId',
      foreignField: '_id',
      as: 'customer',
    },
  },
  { $unwind: '$customer' }, // Coalesced with $lookup by optimizer
]);

---

$facet Patterns

Rule FC-1: Use $facet for Multiple Analyses on the Same Data

$facet runs multiple sub-pipelines on the same input documents, avoiding multiple passes over the data.

// CORRECT — multiple analyses in one query
db.products.aggregate([
  { $match: { status: 'active' } },
  {
    $facet: {
      priceDistribution: [
        {
          $bucket: {
            groupBy: '$price',
            boundaries: [0, 25, 50, 100, 250, Infinity],
            output: { count: { $sum: 1 } },
          },
        },
      ],
      topCategories: [
        { $group: { _id: '$category', count: { $sum: 1 } } },
        { $sort: { count: -1 } },
        { $limit: 5 },
      ],
      overallStats: [
        {
          $group: {
            _id: null,
            totalProducts: { $sum: 1 },
            avgPrice: { $avg: '$price' },
            medianPrice: {
              $median: { input: '$price', method: 'approximate' },
            },
          },
        },
        { $project: { _id: 0 } },
      ],
    },
  },
]);

// WRONG — three separate queries for the same data
const priceDistribution = await db.products.aggregate([...]).toArray();
const topCategories = await db.products.aggregate([...]).toArray();
const overallStats = await db.products.aggregate([...]).toArray();
// Three passes over the collection vs one with $facet

Rule FC-2: Use $facet for Paginated Results with Count

Get paginated data and total count in a single query.

// CORRECT — pagination with $facet
db.products.aggregate([
  { $match: { category: 'electronics', status: 'active' } },
  {
    $facet: {
      metadata: [{ $count: 'total' }],
      data: [
        { $sort: { createdAt: -1 } },
        { $skip: 20 }, // Page 3, 10 per page
        { $limit: 10 },
        { $project: { name: 1, price: 1, rating: 1 } },
      ],
    },
  },
  { $unwind: '$metadata' },
  {
    $addFields: {
      total: '$metadata.total',
      page: 3,
      pageSize: 10,
      totalPages: { $ceil: { $divide: ['$metadata.total', 10] } },
    },
  },
  { $project: { metadata: 0 } },
]);

Rule FC-3: $facet Limitations

• Sub-pipeline output capped at 16 MB per facet (BSON document limit)
• Cannot nest $facet inside $facet
• Cannot use $out or $merge inside $facet
• All sub-pipelines share the same input documents
• 100 MB memory limit applies to the entire stage

// WRONG — $out inside $facet
db.orders.aggregate([
  {
    $facet: {
      summary: [
        { $group: { _id: '$status', count: { $sum: 1 } } },
        { $out: 'orderSummary' }, // ERROR: cannot use $out in $facet
      ],
    },
  },
]);

---

$merge for Materialized Views

Rule MV-1: Use $merge for Incremental Materialized Views

$merge enables incremental updates to materialized views by upserting results into a target collection.

// CORRECT — incremental daily stats materialized view
db.orders.aggregate([
  {
    $match: {
      orderDate: {
        $gte: ISODate('2024-03-15T00:00:00Z'),
        $lt: ISODate('2024-03-16T00:00:00Z'),
      },
      status: 'completed',
    },
  },
  {
    $group: {
      _id: {
        date: { $dateToString: { format: '%Y-%m-%d', date: '$orderDate' } },
        category: '$category',
      },
      revenue: { $sum: '$total' },
      orderCount: { $sum: 1 },
      avgOrderValue: { $avg: '$total' },
    },
  },
  {
    $merge: {
      into: 'dailyCategoryStats',
      on: '_id',
      whenMatched: 'replace',
      whenNotMatched: 'insert',
    },
  },
]);

Rule MV-2: Use $merge with Custom Pipeline for Complex Updates

When a simple merge/replace is not sufficient, use a pipeline in whenMatched.

// CORRECT — accumulate totals across runs
db.orders.aggregate([
  { $match: { processedAt: { $exists: false } } },
  {
    $group: {
      _id: '$customerId',
      newOrders: { $sum: 1 },
      newRevenue: { $sum: '$total' },
    },
  },
  {
    $merge: {
      into: 'customerLifetimeStats',
      on: '_id',
      whenMatched: [
        {
          $set: {
            totalOrders: { $add: ['$$ROOT.totalOrders', '$$new.newOrders'] },
            totalRevenue: { $add: ['$$ROOT.totalRevenue', '$$new.newRevenue'] },
            lastUpdated: new Date(),
          },
        },
      ],
      whenNotMatched: 'insert',
    },
  },
]);

Rule MV-3: Create Indexes on Materialized View Collections

Materialized view collections are query targets. Always create indexes suited to the expected read patterns.

// CORRECT — indexes on the materialized view
db.dailyCategoryStats.createIndex({ '_id.date': -1 });
db.dailyCategoryStats.createIndex({ '_id.category': 1, revenue: -1 });

Rule MV-4: Schedule Materialized View Updates

Use Atlas Triggers, cron jobs, or application schedulers to refresh materialized views regularly.

// CORRECT — scheduled refresh with timestamp tracking
const lastRun = await db.collection('jobState').findOne({ jobId: 'dailyStatsRefresh' });

const startFrom = lastRun?.lastProcessedDate || new Date('2024-01-01');
const now = new Date();

db.orders.aggregate([
  { $match: { orderDate: { $gte: startFrom, $lt: now } } },
  // ... pipeline ...
  { $merge: { into: 'dailyCategoryStats', on: '_id', whenMatched: 'replace' } },
]);

await db
  .collection('jobState')
  .updateOne(
    { jobId: 'dailyStatsRefresh' },
    { $set: { lastProcessedDate: now } },
    { upsert: true }
  );

---

$out vs $merge

Rule OM-1: Prefer $merge Over $out

$merge is the modern, flexible replacement for $out. Use $out only when you need atomic full collection replacement.

Feature	$out	$merge
Replaces target entirely	Yes (atomic)	No (per-document upsert)
Same database required	Yes (before 4.4)	No
Sharded target support	No	Yes
Incremental updates	No	Yes
Custom merge behavior	N/A	merge/replace/keep/fail
Pipeline on match	N/A	Yes
Use case	Full refresh only	Most materialized views

// CORRECT — $merge for daily updates (incremental)
db.events.aggregate([
  { $match: { date: { $gte: yesterday, $lt: today } } },
  { $group: { _id: '$eventType', count: { $sum: 1 } } },
  {
    $merge: {
      into: 'dailyEventStats',
      on: '_id',
      whenMatched: 'merge',
      whenNotMatched: 'insert',
    },
  },
]);

// WRONG — $out for incremental updates (destroys all existing data)
db.events.aggregate([
  { $match: { date: { $gte: yesterday, $lt: today } } },
  { $group: { _id: '$eventType', count: { $sum: 1 } } },
  { $out: 'dailyEventStats' }, // REPLACES entire collection with 1 day's data!
]);

Rule OM-2: Use $out for Full Snapshot Reports

$out is appropriate when you need a complete, consistent snapshot that atomically replaces the previous version.

// CORRECT — $out for weekly full report
db.orders.aggregate([
  { $match: { orderDate: { $gte: weekStart, $lt: weekEnd } } },
  {
    $group: {
      _id: { category: '$category', status: '$status' },
      count: { $sum: 1 },
      revenue: { $sum: '$total' },
    },
  },
  { $sort: { revenue: -1 } },
  { $out: 'weeklyReport' },
  // Atomically replaces the entire weeklyReport collection
]);

Rule OM-3: Warn Before Using $out

Always warn the user before executing a pipeline with $out, because it replaces the entire target collection. Lost data cannot be recovered without a backup.

WARNING: This pipeline uses $out which will REPLACE the entire
"weeklyReport" collection.
  Current document count: 1,247
  After $out: Only new pipeline results will exist
  Previous data will be permanently deleted.

  To proceed, confirm "yes". To use incremental updates instead,
  switch to $merge.

---

Pipeline Optimization Rules

Rule OPT-1: Use explain() to Verify Index Usage

// CORRECT — always check execution plan
db.orders.explain('executionStats').aggregate([
  {
    $match: {
      status: 'completed',
      orderDate: { $gte: ISODate('2024-01-01') },
    },
  },
  { $group: { _id: '$customerId', total: { $sum: '$amount' } } },
]);

// Key metrics to check:
// - winningPlan.stage: "IXSCAN" (good) vs "COLLSCAN" (bad)
// - totalKeysExamined should be close to nReturned
// - executionTimeMillis should be acceptable for your SLA

Rule OPT-2: Use allowDiskUse Sparingly

allowDiskUse: true enables spilling to disk when a stage exceeds the 100 MB memory limit. Use it only when necessary, as it significantly slows execution.

// CORRECT — allowDiskUse with awareness
db.largeCollection.aggregate(
  [
    { $match: { status: 'active' } },
    { $group: { _id: '$region', total: { $sum: '$amount' } } },
    { $sort: { total: -1 } },
  ],
  { allowDiskUse: true }
  // NOTE: This pipeline may use disk for sort. Consider adding an index
  // on { status: 1, region: 1 } to avoid disk usage.
);

Rule OPT-3: Avoid Unnecessary $unwind + $group Cycles

The pattern $unwind -> process -> $group (to reconstitute) is expensive. Use array operators instead.

// CORRECT — $reduce for array computation
db.orders.aggregate([
  {
    $addFields: {
      totalValue: {
        $reduce: {
          input: '$items',
          initialValue: NumberDecimal('0'),
          in: {
            $add: ['$$value', { $multiply: ['$$this.price', '$$this.qty'] }],
          },
        },
      },
    },
  },
]);

// WRONG — $unwind + $group for the same computation
db.orders.aggregate([
  { $unwind: '$items' },
  {
    $group: {
      _id: '$_id',
      totalValue: { $sum: { $multiply: ['$items.price', '$items.qty'] } },
      // Must $first every other field to reconstitute the document
      customerId: { $first: '$customerId' },
      orderDate: { $first: '$orderDate' },
      status: { $first: '$status' },
    },
  },
]);

Rule OPT-4: Use $sample for Testing on Large Collections

When developing pipelines against large collections, use $sample to test with a subset.

// CORRECT — test pipeline with sample
db.largeCollection.aggregate([
  { $sample: { size: 1000 } }, // Random 1000 documents
  // ... rest of pipeline for testing ...
]);

Rule OPT-5: Avoid Repeated Pipeline Execution

If the same aggregation is needed multiple times, consider:

1. Materialized views with $merge for periodic refresh
2. Caching the results in application code
3. Creating a dedicated summary collection

// CORRECT — materialized view refreshed on schedule
// Run nightly:
db.orders.aggregate([
  { $match: { orderDate: { $gte: lastRefresh } } },
  { $group: { _id: '$category', revenue: { $sum: '$total' } } },
  { $merge: { into: 'categoryRevenue', on: '_id', whenMatched: 'replace' } },
]);

// Application reads from materialized view (fast):
const revenue = await db.collection('categoryRevenue').find().toArray();

---

Common Aggregation Patterns

Top N Per Group

// CORRECT — top 3 products per category
db.sales.aggregate([
  {
    $group: {
      _id: { category: '$category', product: '$productName' },
      revenue: { $sum: '$amount' },
    },
  },
  { $sort: { '_id.category': 1, revenue: -1 } },
  {
    $group: {
      _id: '$_id.category',
      topProducts: {
        $topN: {
          n: 3,
          sortBy: { revenue: -1 },
          output: { product: '$_id.product', revenue: '$revenue' },
        },
      },
    },
  },
]);

Running Totals with Window Functions

// CORRECT — running total and 7-day moving average (MongoDB 5.0+)
db.dailySales.aggregate([
  { $match: { storeId: 'store-001' } },
  { $sort: { date: 1 } },
  {
    $setWindowFields: {
      partitionBy: '$storeId',
      sortBy: { date: 1 },
      output: {
        runningTotal: {
          $sum: '$revenue',
          window: { documents: ['unbounded', 'current'] },
        },
        movingAvg7Day: {
          $avg: '$revenue',
          window: { range: [-6, 'current'], unit: 'day' },
        },
      },
    },
  },
]);

Pivot / Crosstab

// CORRECT — monthly sales pivoted by region
db.sales.aggregate([
  { $match: { year: 2024 } },
  {
    $group: {
      _id: { region: '$region', month: '$month' },
      total: { $sum: '$amount' },
    },
  },
  {
    $group: {
      _id: '$_id.region',
      monthlySales: { $push: { month: '$_id.month', total: '$total' } },
    },
  },
  {
    $project: {
      region: '$_id',
      sales: {
        $arrayToObject: {
          $map: {
            input: '$monthlySales',
            as: 'm',
            in: { k: { $toString: '$$m.month' }, v: '$$m.total' },
          },
        },
      },
    },
  },
]);

Time-Based Gap Filling (MongoDB 5.1+)

// CORRECT — hourly event counts with gap filling
db.events.aggregate([
  {
    $match: {
      timestamp: {
        $gte: ISODate('2024-03-15T00:00:00Z'),
        $lt: ISODate('2024-03-16T00:00:00Z'),
      },
    },
  },
  {
    $group: {
      _id: { $dateTrunc: { date: '$timestamp', unit: 'hour' } },
      count: { $sum: 1 },
    },
  },
  {
    $densify: {
      field: '_id',
      range: {
        step: 1,
        unit: 'hour',
        bounds: [ISODate('2024-03-15T00:00:00Z'), ISODate('2024-03-16T00:00:00Z')],
      },
    },
  },
  {
    $fill: {
      output: { count: { value: 0 } },
    },
  },
  { $sort: { _id: 1 } },
]);

Multi-Level Grouping with Subtotals

// CORRECT — hierarchy: region > city with $facet
db.sales.aggregate([
  { $match: { year: 2024 } },
  {
    $facet: {
      byCity: [
        {
          $group: {
            _id: { region: '$region', city: '$city' },
            revenue: { $sum: '$amount' },
          },
        },
        { $sort: { '_id.region': 1, revenue: -1 } },
      ],
      byRegion: [
        {
          $group: {
            _id: '$region',
            revenue: { $sum: '$amount' },
            cityCount: { $addToSet: '$city' },
          },
        },
        { $addFields: { cityCount: { $size: '$cityCount' } } },
        { $sort: { revenue: -1 } },
      ],
      grandTotal: [
        {
          $group: {
            _id: null,
            totalRevenue: { $sum: '$amount' },
            orderCount: { $sum: 1 },
          },
        },
        { $project: { _id: 0 } },
      ],
    },
  },
]);

Ranking with Window Functions

// CORRECT — rank employees by salary within department
db.employees.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$department',
      sortBy: { salary: -1 },
      output: {
        departmentRank: { $rank: {} },
        denseRank: { $denseRank: {} },
        percentileRank: {
          $documentNumber: {},
        },
      },
    },
  },
  { $match: { departmentRank: { $lte: 5 } } },
]);

Union Multiple Collections

// CORRECT — combine data from multiple collections
db.activeOrders.aggregate([
  {
    $project: {
      orderId: '$_id',
      source: { $literal: 'active' },
      total: 1,
      date: '$orderDate',
    },
  },
  {
    $unionWith: {
      coll: 'archivedOrders',
      pipeline: [
        {
          $project: {
            orderId: '$_id',
            source: { $literal: 'archived' },
            total: 1,
            date: '$orderDate',
          },
        },
      ],
    },
  },
  { $sort: { date: -1 } },
  { $limit: 100 },
]);

Conditional Aggregation

// CORRECT — conditional grouping with $switch
db.orders.aggregate([
  {
    $addFields: {
      sizeBucket: {
        $switch: {
          branches: [
            { case: { $lt: ['$total', 50] }, then: 'small' },
            { case: { $lt: ['$total', 200] }, then: 'medium' },
            { case: { $lt: ['$total', 1000] }, then: 'large' },
          ],
          default: 'enterprise',
        },
      },
    },
  },
  {
    $group: {
      _id: '$sizeBucket',
      count: { $sum: 1 },
      revenue: { $sum: '$total' },
      avgValue: { $avg: '$total' },
    },
  },
  { $sort: { revenue: -1 } },
]);