Fixing MongoDB Error: The $merge stage must be the last stage in the pipeline

Understanding the Error

Think of the $merge operator as the finish line for your data. In MongoDB, most aggregation stages transform documents and pass them down the line. However, $merge is an output action. It writes the final results directly into a collection. Because of this, MongoDB enforces a strict rule: $merge must be the absolute final stage in your aggregation array.

If you try to add a $sort, $project, or even a simple $limit after it, the database engine will stop you. You will see this specific error message:

MongoServerError: The $merge stage must be the last stage in the pipeline

Why This Happens

The aggregation framework operates like a conveyor belt. Each stage processes data and hands it off to the next. The $merge stage is unique because it interacts with the storage engine to perform heavy lifting like upserts or document replacements.

Once the data hits the destination collection, the pipeline's job is done. MongoDB does not allow you to keep processing documents that have already been committed to disk. This error usually pops up for three reasons:

• Accidental Appends: You are using .push() in a JavaScript loop to build a pipeline and added a stage after the merge logic.
• Template Errors: You copied a $project block from another query and pasted it at the bottom of your code.
• Post-Write Filtering: You mistakenly thought you could filter the results for your UI after writing them to the database.

How to Fix It

1. Locate the Out-of-Order Stage

Check your aggregation array for any curly braces {} appearing after the $merge block. Even a small $count stage at the end will break the query. Here is an example of a broken pipeline:

db.orders.aggregate([
  { $match: { status: "shipped" } },
  { $group: { _id: "$product_id", unitsSold: { $sum: "$quantity" } } },
  { 
    $merge: { 
      into: "sales_report", 
      whenMatched: "replace" 
    } 
  },
  { $sort: { unitsSold: -1 } } // ERROR: You cannot sort after merging
])

2. Reorder Your Pipeline

Fixing the issue is usually as simple as moving the misplaced stage. If you need to sort 10,000 documents before saving them, put the $sort stage before the $merge stage. If you need to see the results in your application, run a separate find() query on the target collection after the aggregation finishes.

Here is the corrected version:

db.orders.aggregate([
  { $match: { status: "shipped" } },
  { $group: { _id: "$product_id", unitsSold: { $sum: "$quantity" } } },
  { $sort: { unitsSold: -1 } }, // Correct: Data is sorted before the write
  { 
    $merge: { 
      into: "sales_report", 
      whenMatched: "replace",
      whenNotMatched: "insert"
    } 
  }
])

3. Watch Out for Dynamic Logic (Node.js)

When building pipelines programmatically, the order of execution in your code matters. A common mistake in Node.js looks like this:

const pipeline = [
  { $match: { year: 2023 } }
];

// Logic adds the merge stage
pipeline.push({ $merge: { into: "yearly_archive" } });

// A different function later adds a projection
pipeline.push({ $project: { _id: 0 } }); // This causes the crash

// Fix: Always ensure your merge logic is the last item pushed to the array.

Testing the Solution

Run your corrected aggregation. Note that $merge does not return a list of documents to your console; it usually returns an empty result set or success metadata. To verify the data, query the destination collection directly:

// 1. Run the aggregation first
db.orders.aggregate([...]); 

// 2. Check the new collection for the data
db.sales_report.find().limit(10);

Pro Tips

$merge vs $out

Both stages must be at the end of a pipeline. However, $merge is much more flexible. While $out deletes the existing collection and replaces it entirely, $merge can update specific fields in existing documents. Use $merge for large datasets where you only need to sync changes rather than rebuilding the whole table.

Performance and Memory

Placing a $sort before a $merge is perfectly valid, but be careful with large datasets. If your sort exceeds the 100MB RAM limit and you haven't indexed the fields, the pipeline will fail. Always try to use a $match stage early on to reduce the document count before the data reaches the final merge stage.