$merge and $out: MongoDB & NoSQL lessons from production

Introduction

When your aggregation pipeline produces results that need to persist beyond a single query, you face a fundamental decision: $merge and $out: MongoDB & NoSQL lessons from production teaches us how to write those results back into your database strategically. We've all hit the moment where a complex aggregation—whether it's a sales summary, user activity report, or inventory snapshot—needs to live somewhere queryable for downstream systems. That's where these two operators become essential tools in our toolkit.

In this article, we'll explore the practical differences between $out and $merge, how to build materialized views that power real dashboards, and patterns we've validated in production across e-commerce, CRM, and reporting systems.

Understanding $out: Full Collection Replacement

$out is the simpler, more aggressive approach. It runs your aggregation pipeline and replaces the entire target collection with the results. If the collection doesn't exist, MongoDB creates it. If it does exist, $out truncates it completely and writes the new documents.

This behavior makes $out perfect for scenarios where you want a clean slate—daily snapshots, weekly rollups, or any report where you're building the entire result set fresh each time.

Real-world scenario: Daily sales summary

Imagine an e-commerce platform tracking orders across multiple regions. Each night, you want to generate a clean report showing sales by category, region, and payment method. Using $out ensures no stale data lingers from previous runs.

db.orders.aggregate([
  {
    $match: {
      createdAt: {
        $gte: new Date(new Date().setHours(0, 0, 0, 0)),
        $lt: new Date(new Date().setHours(23, 59, 59, 999))
      }
    }
  },
  {
    $group: {
      _id: {
        category: "$category",
        region: "$region",
        paymentMethod: "$paymentMethod"
      },
      totalSales: { $sum: "$amount" },
      orderCount: { $sum: 1 },
      avgOrderValue: { $avg: "$amount" }
    }
  },
  {
    $sort: { totalSales: -1 }
  },
  {
    $out: "daily_sales_summary"
  }
]);

When this pipeline runs, the daily_sales_summary collection is completely replaced. Dashboards and reports querying that collection get fresh data with no risk of partial results or duplicates from a failed intermediate run.

Important considerations with $out

$out locks the target collection during the write phase, which can block other operations. For high-traffic systems, this might create contention. Also, if your aggregation fails partway through, you're left with whatever the collection contained before—which could be stale data from yesterday's run. We've found it best to run $out operations during maintenance windows or off-peak hours when the cost of a collection lock is minimal.

Understanding $merge: Upsert-based Incremental Writes

$merge takes a different approach. Instead of replacing the entire collection, it upserts documents based on a key you define. If a document with matching keys exists, $merge updates it according to your merge strategy. If it doesn't exist, it inserts the new document. This preserves documents that aren't in the aggregation results, making it ideal for incremental updates and partial refreshes.

Real-world scenario: Progressive user activity metrics

In a CRM system, you track user engagement metrics—login count, last active time, engagement score. Instead of recalculating the entire user base every run, you can use $merge to update only the users active in the last hour, leaving dormant users untouched.

db.userActivity.aggregate([
  {
    $match: {
      timestamp: {
        $gte: new Date(Date.now() - 3600000) // Last hour
      }
    }
  },
  {
    $group: {
      _id: "$userId",
      loginCount: { $sum: 1 },
      lastActive: { $max: "$timestamp" },
      engagementScore: { $avg: "$score" }
    }
  },
  {
    $merge: {
      into: "user_metrics",
      whenMatched: "merge",
      whenNotMatched: "insert"
    }
  }
]);

This pipeline processes only recent activity and updates the user_metrics collection with new results. Users not in the last hour's data are left unchanged in the collection, so their historical metrics persist.

$merge strategies explained

$merge offers flexibility through different strategies:

• whenMatched: "merge" – Updates matching documents by merging new fields with existing ones. Existing fields not in the new document persist.
• whenMatched: "replace" – Replaces the entire matching document with the aggregation result.
• whenMatched: "keepExisting" – Leaves matching documents unchanged.
• whenMatched: [pipeline] – Runs a custom update pipeline to decide how to merge.
• whenNotMatched: "insert" – Inserts new documents (the default).
• whenNotMatched: "discard" – Ignores documents not already in the target collection.

Building Materialized Views for Production Reports

Materialized views—pre-computed result sets stored in collections—are the backbone of fast dashboards. Rather than running complex aggregations on every user request, you compute results offline and store them where frontends can query instantly.

Example: E-commerce inventory dashboard

In a multi-warehouse inventory system, building a real-time dashboard on-demand is expensive. Instead, you materialize an inventory view every few minutes, combining stock levels, pending orders, and reorder status.

db.inventory.aggregate([
  {
    $lookup: {
      from: "warehouses",
      localField: "warehouseId",
      foreignField: "_id",
      as: "warehouse"
    }
  },
  {
    $unwind: "$warehouse"
  },
  {
    $lookup: {
      from: "pendingOrders",
      localField: "productId",
      foreignField: "productId",
      as: "pending"
    }
  },
  {
    $group: {
      _id: {
        productId: "$productId",
        warehouseId: "$warehouseId"
      },
      currentStock: { $first: "$quantity" },
      warehouseLocation: { $first: "$warehouse.location" },
      pendingOrderCount: { $sum: { $size: "$pending" } },
      lastUpdated: { $max: new Date() }
    }
  },
  {
    $addFields: {
      status: {
        $cond: {
          if: { $lte: ["$currentStock", 10] },
          then: "low",
          else: { $cond: { if: { $lte: ["$currentStock", 50] }, then: "medium", else: "healthy" } }
        }
      }
    }
  },
  {
    $merge: {
      into: "inventory_view",
      on: ["_id.productId", "_id.warehouseId"],
      whenMatched: "merge",
      whenNotMatched: "insert"
    }
  }
]);

The on field specifies which fields constitute the merge key. Now your dashboard queries inventory_view` directly, getting results in milliseconds instead of seconds.

Running Aggregations on a Schedule for Reports

To keep materialized views fresh, you need scheduled execution. We typically pair MongoDB aggregations with external scheduling—either MongoDB's built-in Atlas Triggers (for cloud deployments) or external cron jobs that call your aggregation logic.

Scheduling with MongoDB Atlas Triggers

If you're on MongoDB Atlas, scheduled triggers are straightforward. Create a trigger that fires every N minutes and executes your aggregation pipeline. No external infrastructure needed.

Scheduling with application-level jobs

For self-hosted or hybrid setups, we often use Node.js with a library like node-cron or dedicated job queues like Bull/BullMQ. Here's a simplified pattern:

const cron = require('node-cron');
const { MongoClient } = require('mongodb');

const client = new MongoClient(process.env.MONGODB_URI);

// Run every 5 minutes
cron.schedule('*/5 * * * *', async () => {
  try {
    const db = client.db('myapp');
    
    await db.collection('orders').aggregate([
      {
        $match: {
          status: { $in: ['pending', 'processing'] },
          createdAt: { $gte: new Date(Date.now() - 86400000) } // Last 24 hours
        }
      },
      {
        $group: {
          _id: "$status",
          count: { $sum: 1 },
          totalAmount: { $sum: "$amount" },
          avgAmount: { $avg: "$amount" }
        }
      },
      {
        $merge: {
          into: "order_status_metrics",
          whenMatched: "merge",
          whenNotMatched: "insert"
        }
      }
    ]).toArray();
    
    console.log('Order metrics updated successfully');
  } catch (error) {
    console.error('Aggregation failed:', error);
    // Alert on-call engineer via Slack, PagerDuty, etc.
  }
});

This job runs every 5 minutes, updating the metrics collection. If it fails, you catch it and alert. Over time, your materialized view stays fresh and queryable.

Common Mistakes

Using $out in a pipeline with $lookup from the same collection

MongoDB doesn't allow $out to write to a collection that's being read from earlier in the same pipeline via $lookup. If you need to aggregate data from the orders collection and write results back to it, use $merge with a filtered key instead, or write to a separate collection and copy results afterward.

Forgetting to define the merge key in $merge

If you omit the on field in $merge, MongoDB uses _id by default. If your aggregation changes _id structure (e.g., from a single value to an object), every result becomes a new document, and you never actually update existing rows. Always explicitly define your merge key.

Running expensive aggregations too frequently

A 5-minute schedule sounds good until you realize your aggregation scans 100 million documents and takes 2 minutes to complete. Schedule based on query cost and freshness requirements, not arbitrary intervals. Start with hourly schedules for heavy aggregations and adjust based on monitoring.

Ignoring the performance cost of indexes on materialized view collections

Your materialized view collection still needs indexes if dashboards and applications query it frequently. Without indexes, you're just shifting the performance problem from aggregation time to query time. Index based on actual access patterns—typically on status fields, date ranges, and foreign keys.

Key Takeaways

• $out replaces, $merge upserts: Use $out for complete refreshes where a clean slate is acceptable. Use $merge for incremental updates that preserve unmodified documents.
• Materialized views are your friend: Pre-compute complex aggregations into queryable collections. Dashboards and reports will thank you.
• Schedule wisely: Match aggregation frequency to query cost and freshness requirements. Heavy aggregations belong on longer schedules during off-peak windows.
• Always define merge keys explicitly: Don't rely on default _id behavior. Know which fields identify uniqueness in your materialized view.
• Index your materialized views: They're just collections—they need indexes for production performance.
• Monitor and alert: Failed aggregations mean stale data. Log failures, measure execution time, and alert when schedules slip.