The "$out must be the last stage in the pipeline" error occurs when you try to use the $out aggregation stage anywhere except as the final stage in a MongoDB aggregation pipeline. The $out stage writes pipeline results to a collection and must be the last operation. This error prevents pipeline execution and requires reordering stages to place $out at the end.
The "$out must be the last stage in the pipeline" error is a MongoDB aggregation pipeline validation error that occurs when you attempt to use the $out stage in any position other than the final stage of your aggregation pipeline. In MongoDB aggregation, the $out stage is a special terminal operation that writes the results of the aggregation pipeline to a specified collection. Unlike other pipeline stages that transform or filter data as it flows through the pipeline, $out performs a write operation that concludes the pipeline execution. Key characteristics of the $out stage: - **Terminal operation**: $out must be the last stage because it writes data to disk - **Collection creation/replacement**: Creates a new collection or replaces an existing one - **Pipeline conclusion**: No further stages can process data after $out - **Write concern**: Respects the write concern of the operation When MongoDB validates an aggregation pipeline, it checks that if $out is present, it appears exactly once and as the final stage. If $out appears earlier in the pipeline or if stages follow it, MongoDB rejects the entire pipeline with this error. Common scenarios where this error occurs: - **Accidental stage ordering**: Adding stages after $out during pipeline development - **Pipeline composition**: Combining multiple pipelines where $out isn't at the end - **Template/code generation**: Automated code that doesn't enforce $out position rules - **Refactoring mistakes**: Moving stages around without updating $out position
The most straightforward fix is to ensure $out is the last stage in your pipeline array.
Example of incorrect pipeline with $out in the middle:
// WRONG: $out is not the last stage
const wrongPipeline = [
{ $match: { status: 'active' } },
{ $out: 'active_users' }, // ERROR: $out here, but stages follow
{ $project: { name: 1, email: 1 } },
{ $sort: { name: 1 } }
];Correct pipeline with $out as the last stage:
// CORRECT: $out is the last stage
const correctPipeline = [
{ $match: { status: 'active' } },
{ $project: { name: 1, email: 1 } },
{ $sort: { name: 1 } },
{ $out: 'active_users' } // Correct: $out is last
];
// Execute the pipeline
await db.collection('users').aggregate(correctPipeline).toArray();If you need to perform operations after writing to a collection:
1. Run two separate aggregations
2. First pipeline ends with $out
3. Second pipeline reads from the output collection
Example of splitting pipelines:
// First pipeline: Transform and write
const writePipeline = [
{ $match: { status: 'active' } },
{ $project: { name: 1, email: 1 } },
{ $out: 'active_users' }
];
// Second pipeline: Process the written data
const processPipeline = [
{ $match: { name: { $regex: /^A/ } } },
{ $sort: { name: 1 } }
// No $out here unless it's the final operation
];
await db.collection('users').aggregate(writePipeline).toArray();
await db.collection('active_users').aggregate(processPipeline).toArray();Ensure your pipeline contains only one $out stage. Multiple $out stages are not allowed.
Example of pipeline with multiple $out stages (incorrect):
// WRONG: Multiple $out stages
const wrongPipeline = [
{ $match: { type: 'A' } },
{ $out: 'type_a_results' }, // First $out
{ $match: { type: 'B' } },
{ $out: 'type_b_results' } // Second $out - ERROR
];Solutions for multiple output needs:
Option 1: Separate pipelines for each output:
// Pipeline for type A
const pipelineA = [
{ $match: { type: 'A' } },
{ $out: 'type_a_results' }
];
// Pipeline for type B
const pipelineB = [
{ $match: { type: 'B' } },
{ $out: 'type_b_results' }
];
await db.collection('items').aggregate(pipelineA).toArray();
await db.collection('items').aggregate(pipelineB).toArray();Option 2: Use $facet for multiple aggregations in one pipeline:
// Using $facet to compute multiple aggregations
const facetPipeline = [
{
$facet: {
typeA: [
{ $match: { type: 'A' } },
// Process type A data
],
typeB: [
{ $match: { type: 'B' } },
// Process type B data
]
}
}
];
// Then write results separately
const results = await db.collection('items').aggregate(facetPipeline).toArray();
// Write type A results
await db.collection('type_a_results').insertMany(results[0].typeA);
// Write type B results
await db.collection('type_b_results').insertMany(results[0].typeB);Option 3: Use $merge instead of $out for more flexible writing:
// $merge allows more control and can be used with other stages
const mergePipeline = [
{ $match: { type: 'A' } },
{ $project: { _id: 0, data: '$$ROOT' } },
{ $merge: {
into: 'type_a_results',
on: '_id',
whenMatched: 'replace',
whenNotMatched: 'insert'
}
}
];
// Note: $merge also has positioning rules but is more flexible than $outWhen building pipelines dynamically, add validation to ensure $out is last.
Example validation function:
function validatePipeline(pipeline) {
const outStages = pipeline.filter(stage => stage.$out !== undefined);
if (outStages.length === 0) {
return { valid: true }; // No $out stage is fine
}
if (outStages.length > 1) {
return {
valid: false,
error: 'Multiple $out stages found. Only one $out stage is allowed.'
};
}
const lastStage = pipeline[pipeline.length - 1];
if (!lastStage.$out) {
return {
valid: false,
error: '$out must be the last stage in the pipeline.'
};
}
return { valid: true };
}
// Usage
const pipeline = [
{ $match: { active: true } },
{ $project: { name: 1 } },
{ $out: 'active_users' }
];
const validation = validatePipeline(pipeline);
if (!validation.valid) {
console.error('Pipeline validation failed:', validation.error);
// Fix the pipeline
}Automatically fix $out position in dynamic pipelines:
function fixPipelineOrder(pipeline) {
// Find $out stage
const outIndex = pipeline.findIndex(stage => stage.$out !== undefined);
if (outIndex === -1) {
return pipeline; // No $out stage
}
if (outIndex === pipeline.length - 1) {
return pipeline; // $out is already last
}
// Remove $out from its current position
const outStage = pipeline[outIndex];
const withoutOut = pipeline.filter((_, index) => index !== outIndex);
// Add $out to the end
return [...withoutOut, outStage];
}
// Example usage
const brokenPipeline = [
{ $match: { status: 'active' } },
{ $out: 'temp_results' }, // In wrong position
{ $sort: { name: 1 } }
];
const fixedPipeline = fixPipelineOrder(brokenPipeline);
console.log(fixedPipeline);
// Output: [{ $match: { status: 'active' } }, { $sort: { name: 1 } }, { $out: 'temp_results' }]Using a pipeline builder class:
class PipelineBuilder {
constructor() {
this.stages = [];
this.hasOut = false;
}
match(query) {
if (this.hasOut) throw new Error('Cannot add stages after $out');
this.stages.push({ $match: query });
return this;
}
project(fields) {
if (this.hasOut) throw new Error('Cannot add stages after $out');
this.stages.push({ $project: fields });
return this;
}
out(collectionName) {
if (this.hasOut) throw new Error('Only one $out stage allowed');
this.stages.push({ $out: collectionName });
this.hasOut = true;
return this;
}
build() {
return this.stages;
}
}
// Usage
const builder = new PipelineBuilder();
const pipeline = builder
.match({ active: true })
.project({ name: 1, email: 1 })
.out('active_users')
.build(); // Automatically ensures $out is lastConsider using $merge instead of $out when you need more control over output behavior. $merge has different positioning rules and capabilities.
Key differences between $out and $merge:
- $out: Must be last stage, replaces entire collection
- $merge: Can have limited stages after it, supports upsert/merge operations
Example using $merge:
// $merge example - can have some stages after it in certain cases
const mergePipeline = [
{ $match: { department: 'engineering' } },
{ $project: { name: 1, salary: 1 } },
{ $merge: {
into: 'engineering_salaries',
on: '_id',
whenMatched: 'merge', // Merge fields instead of replace
whenNotMatched: 'insert'
}
}
// Limited additional processing possible after $merge in some cases
];
// $merge with pipeline variable
const mergeWithLet = [
{
$merge: {
into: 'summary_stats',
on: 'date',
whenMatched: [
{
$set: {
total: { $add: ['$$new.total', '$total'] },
count: { $add: ['$$new.count', '$count'] }
}
}
],
whenNotMatched: 'insert',
let: { new: '$$ROOT' }
}
}
];When to use $merge instead of $out:
1. Incremental updates: When you want to update existing documents
2. Data aggregation: When you need to merge data rather than replace
3. Pipeline continuation: When limited processing after write is needed
4. Conditional writes: More control over matched/not-matched behavior
Important $merge limitations:
- Still has positioning constraints (typically last or near-last)
- More complex syntax than $out
- May not be available in older MongoDB versions
- Performance characteristics differ from $out
Implement debugging to catch $out position errors early in development.
Add pipeline validation in development/test environments:
// Development-only pipeline validation
function debugPipeline(pipeline, name = 'unnamed') {
const outStages = pipeline.reduce((acc, stage, index) => {
if (stage.$out) acc.push({ index, stage });
return acc;
}, []);
if (outStages.length > 0) {
console.log('Pipeline "' + name + '" has ' + outStages.length + ' $out stage(s):');
outStages.forEach(({ index, stage }) => {
console.log(' - Position ' + index + ': ' + JSON.stringify(stage));
if (index !== pipeline.length - 1) {
console.error(' ERROR: $out at position ' + index + ' but pipeline has ' + pipeline.length + ' stages total');
console.error(' Stages after $out:', pipeline.slice(index + 1));
}
});
}
return pipeline;
}
// Usage
const pipeline = debugPipeline([
{ $match: { active: true } },
{ $out: 'temp' }, // Will show error
{ $sort: { name: 1 } }
], 'userProcessing');Unit test pipeline ordering:
// Jest test example
describe('Aggregation Pipeline Validation', () => {
test('$out must be last stage', () => {
const validPipeline = [
{ $match: { active: true } },
{ $sort: { name: 1 } },
{ $out: 'results' }
];
const invalidPipeline = [
{ $match: { active: true } },
{ $out: 'results' },
{ $sort: { name: 1 } }
];
// Test validation function
expect(validatePipeline(validPipeline).valid).toBe(true);
expect(validatePipeline(invalidPipeline).valid).toBe(false);
expect(validatePipeline(invalidPipeline).error).toContain('$out must be the last stage');
});
test('Only one $out stage allowed', () => {
const multiOutPipeline = [
{ $match: { type: 'A' } },
{ $out: 'type_a' },
{ $match: { type: 'B' } },
{ $out: 'type_b' }
];
expect(validatePipeline(multiOutPipeline).valid).toBe(false);
expect(validatePipeline(multiOutPipeline).error).toContain('Multiple $out stages');
});
});Log pipeline structure in production (sanitized):
// Safe logging of pipeline structure
function logPipelineStructure(pipeline) {
const structure = pipeline.map(stage => {
const key = Object.keys(stage)[0];
return {
stage: key,
hasOut: key === '$out',
collection: stage.$out || null
};
});
console.log('Pipeline structure:', {
stageCount: pipeline.length,
hasOut: structure.some(s => s.hasOut),
outPosition: structure.findIndex(s => s.hasOut),
stages: structure.map(s => s.stage)
});
return structure;
}
// Usage
const pipeline = [
{ $match: { userId: '123' } },
{ $lookup: { from: 'profiles', localField: 'userId', foreignField: '_id', as: 'profile' } },
{ $out: 'user_with_profile' }
];
const analysis = logPipelineStructure(pipeline);
if (analysis.hasOut && analysis.outPosition !== pipeline.length - 1) {
console.error('WARNING: $out is not the last stage');
}To avoid $out position errors, understand how MongoDB executes aggregation pipelines.
Pipeline execution model:
1. Stages process sequentially: Each stage receives input from previous stage
2. $out is terminal: It writes results and ends pipeline execution
3. No continuation: Stages after $out would have no data to process
Visualizing pipeline flow:
Collection → [$match] → [$project] → [$group] → [$out] → Written to collection
↑
Pipeline ends here
Collection → [$match] → [$out] → [$sort] → ERROR!
↑
$out writes data, $sort has nothing to sortWhy $out must be last:
- Data flow interruption: $out writes data to disk, breaking the in-memory pipeline
- Transaction boundaries: $out may involve transactions that shouldn't be followed by reads
- Performance optimization: MongoDB can optimize knowing $out is the final operation
- Consistency guarantees: Ensures all pipeline operations complete before write
Alternative patterns when you need intermediate results:
Pattern 1: Temporary collections
// Write intermediate results, then process
const stage1 = [
{ $match: { year: 2024 } },
{ $group: { _id: '$month', total: { $sum: '$amount' } } },
{ $out: 'temp_monthly_totals' }
];
const stage2 = [
{ $match: { total: { $gt: 1000 } } },
{ $sort: { total: -1 } },
{ $out: 'high_value_months' }
];
await db.sales.aggregate(stage1);
await db.temp_monthly_totals.aggregate(stage2);
// Clean up temporary collection
await db.temp_monthly_totals.drop();Pattern 2: Use $facet for parallel processing
// Process data in parallel branches
const pipeline = [
{
$facet: {
monthly: [
{ $group: { _id: '$month', total: { $sum: '$amount' } } }
],
category: [
{ $group: { _id: '$category', total: { $sum: '$amount' } } }
]
}
},
{ $out: 'analysis_results' } // Single $out writes all facet results
];Pattern 3: Application-side processing
// Process in application, then write
const pipeline = [
{ $match: { status: 'pending' } },
{ $project: { _id: 1, data: 1 } }
];
// Get results in application
const results = await db.collection('items').aggregate(pipeline).toArray();
// Process in JavaScript
const processed = results.map(item => ({
...item,
processedAt: new Date(),
hash: computeHash(item.data)
}));
// Write processed results
await db.collection('processed_items').insertMany(processed);MongoDB Aggregation Pipeline Optimization with $out:
When $out is properly positioned as the last stage, MongoDB can apply several optimizations:
1. Pipeline Coalescence: MongoDB may combine multiple stages before $out for efficiency
2. Write Optimization: Knowing the pipeline ends with $out allows optimized write patterns
3. Memory Management: Final stage being $out informs memory allocation strategies
$out vs $merge Performance Characteristics:
- Position Requirement: $out must be absolute last, $merge typically last with some exceptions
- Collection Behavior: $out replaces entire collection, $merge merges/upserts into existing
- Index Preservation: $out drops existing indexes, $merge preserves target collection indexes
- Transaction Support: $out has full transaction support, $merge support varies
- Concurrent Operations: $out locks collection during write, $merge has more granular locking
Using $out in Sharded Clusters:
In sharded environments, $out has additional considerations:
- Output Collection Sharding: $out creates an unsharded collection by default
- Shard Key Considerations: If output needs sharding, use $merge or manual sharding
- Balancer Impact: Large $out operations may trigger chunk migrations
Monitoring $out Operations:
Monitor these metrics for $out operations:
- Duration: Time taken for $out to complete
- Document Count: Number of documents written
- Collection Size: Growth of output collection
- Lock Percentage: How much time spent waiting for locks
Best Practices for Production $out Usage:
1. Test with Small Data: Test pipeline with $out on subset before full dataset
2. Monitor Disk Space: $out creates temporary files, ensure sufficient disk
3. Use Appropriate Write Concern: Balance durability vs performance needs
4. Consider $merge for Incremental Updates: Better for frequent updates
5. Index Output Collection: Create indexes after $out completes, not before
6. Handle Interruptions: $out operations can be killed, have retry logic
7. Validate Output: Verify document counts and data integrity after $out
Troubleshooting $out Failures:
Common failure scenarios and solutions:
- Disk Full: $out needs temporary disk space, monitor and increase
- Permission Denied: Ensure write permissions on target database/collection
- Collection Locked: Check for other operations locking target collection
- Network Timeout: Increase timeout for large $out operations
- Memory Exhaustion: Use allowDiskUse: true for memory-intensive pipelines
unknown operator: $invalidOperator
How to fix "unknown operator: $invalidOperator" in MongoDB
StaleShardVersion: shard version mismatch
How to fix "StaleShardVersion: shard version mismatch" in MongoDB
MongoOperationTimeoutError: Operation timed out
How to fix "MongoOperationTimeoutError: Operation timed out" in MongoDB
MongoServerError: PlanExecutor error during aggregation :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation.
How to fix "QueryExceededMemoryLimitNoDiskUseAllowed" in MongoDB
MissingSchemaError: Schema hasn't been registered for model
How to fix "MissingSchemaError: Schema hasn't been registered for model" in MongoDB/Mongoose