What this usually means
The most common root cause is a mismatch between the actual data types in the collection and the types assumed in the pipeline. For example, a field stored as a string cannot be used with numeric operators like $sum without explicit conversion. Another frequent issue is stage ordering: $match before $group reduces input size, but if $match uses fields created by a later stage, it silently filters nothing. Memory limits (100 MB per stage default) can cause spilling to disk, which may produce partial results if not explicitly enabled. Lastly, $lookup behaves like a LEFT JOIN, and if the foreign field type differs from the local field, the join fails silently, returning empty arrays. Each of these problems can produce wrong results without throwing a MongoDB error.
The first ten minutes — establish facts before touching code.
- 1Run `db.collection.aggregate(pipeline, { explain: true })` to see the stage execution plan and check for IXSCAN vs COLLSCAN.
- 2Add a $match stage early that selects a known document ID, then inspect each stage's output by temporarily inserting a $limit(5) after it.
- 3Use `$project` with `$type` to verify field types: `{$project: {fieldType: {$type: "$fieldName"}}}` and examine the output.
- 4Check for implicit memory limits: add `{ allowDiskUse: true }` if the pipeline processes more than 100 MB per stage.
- 5Test the pipeline against a subset of documents that you know the expected result for, using `$sample` or a specific _id filter.
The specific files, logs, configs, and dashboards that usually own this bug.
- searchThe aggregation pipeline definition in application code (JavaScript, Python, Go, etc.)
- searchMongoDB server log for warnings about 'external sort' or 'spilling to disk'
- searchMongoDB Atlas metrics: Aggregation Pipeline stage memory usage chart
- searchCollection schema: run `db.collection.findOne()` to see actual field types
- searchIndex definitions: check if fields used in $match or $sort have appropriate indexes
- searchStage output by using `$merge` to a temporary collection and examining it
- searchThird-party driver versions: older drivers may not support newer aggregation stages ($lookup with pipeline syntax, etc.)
Practical causes, not theory. These are the things you will actually find.
- warningField type mismatch: string vs number, or string dates vs Date objects
- warningStage order: $group before $match, or $project before $match on projected fields
- warningMemory limit hit: >100 MB per stage without allowDiskUse: true, causing incomplete results
- warning$lookup foreignField type mismatch: local field is ObjectId but foreign field is string
- warningTimezone assumptions: $dateToString without timezone produces UTC when you expect local
- warningImplicit $limit from `$sample` or sorted $group causing missing data
Concrete fix directions. Pick the one that matches your root cause.
- buildAdd explicit type conversion using $convert or $toString/$toInt in a $project stage before grouping
- buildReorder stages: always put $match and $sort as early as possible to reduce data volume
- buildEnable allowDiskUse: true for pipelines that aggregate large collections
- buildFor $lookup, ensure foreignField type matches localField: use `$toObjectId` if needed
- buildUse `$dateFromString` with explicit timezone parameter for date conversions
- buildBreak complex pipelines into smaller, tested stages with intermediate results verified
A fix you cannot prove is a guess. Close the loop.
- verifiedRun the corrected pipeline against a known subset and compare output to expected values
- verifiedAdd `$count` after each major stage to confirm document counts match expectations
- verifiedCheck for NaN or null in numeric fields: `{ $match: { numericField: { $type: 'double' } } }`
- verifiedUse `explain: true` on the final pipeline to confirm indexes are used
- verifiedCompare results from the pipeline with a simple find() query for the same criteria
- verifiedRun the pipeline on a staging environment with production-size data before deploying
Things that make this bug worse or harder to find.
- warningAssuming fields are of a certain type without verifying with $type
- warningPlacing $project before $match, which prevents index usage on projected fields
- warningIgnoring the 100 MB memory limit and not enabling allowDiskUse
- warningUsing $lookup without checking that foreignField type matches localField
- warningNot considering timezone when working with $dateToString or $dateFromParts
- warningTesting only with small datasets that don't trigger memory spilling
Missing Sales Data in Monthly Report
Timeline
- 09:15Received alert: monthly sales report shows $0 for region EU
- 09:20Checked pipeline: $group by region, $sum of amount. Found 'amount' field present in documents.
- 09:30Ran pipeline on a single document with known amount 150.00 - result shows $sum: 0
- 09:35Used $project to check type: { amountType: { $type: '$amount' } } - returned 'string'
- 09:40Checked source: amount was stored as string due to legacy CSV import
- 09:45Added $project stage: { amountNum: { $toDouble: '$amount' } } before $group
- 09:50Reran pipeline - EU region now shows correct sum of $1.2M
- 10:00Applied fix to production, verified report matches expected values
- 10:15Added validation in import script to enforce numeric types
I was on call when the monthly sales report came out with $0 for the entire EU region. Our pipeline grouped by region and summed the 'amount' field. The pipeline ran without errors, and the EU documents definitely had positive amounts. I started by isolating one document with amount 150.00, but the pipeline still returned 0. That's when I realized the sum was silently converting to zero because $sum expects a number, but the field was a string.
I added a $project stage to check the type using $type, and sure enough, amount was 'string'. The source of the data was a CSV import that stored everything as strings. I added a $project stage with $toDouble to convert the field before the $group. The pipeline then returned the correct sum. The fix was a single line, but finding the type mismatch took 30 minutes of careful debugging.
After fixing the pipeline, I also updated the import script to validate and convert types at ingestion time. The lesson: never trust field types in MongoDB without explicit verification, especially when data comes from external sources. A type mismatch can cause silent wrong results in aggregation pipelines.
Root cause
The 'amount' field was stored as a string in the database, but the $sum accumulator requires numeric values. MongoDB does not automatically convert strings to numbers in aggregation, resulting in $sum treating the string as 0.
The fix
Added a $project stage before $group to convert the field: { $project: { amountNum: { $toDouble: '$amount' } } } and changed the $group to sum '$amountNum'.
The lesson
Always verify field types with $type before using numeric accumulators. Type mismatches are a silent killer in aggregation pipelines.
In MongoDB aggregation, the order of stages matters dramatically. A common mistake is placing $project before $match, which can prevent index usage. For example, if you have an index on 'status', but your pipeline does $project first to include only field 'name', the $match on 'status' after $project cannot use the index. Always put $match and $sort as early as possible.
Another ordering pitfall: using $match after $group to filter on aggregated results. That's fine, but if you accidentally filter on a field that doesn't exist yet (like a computed sum), $match will silently filter out all documents. Use $match before $group to reduce input size, and $match after $group to filter on aggregates.
$lookup performs an equality match between the local field and the foreign field. If the types don't match, the join returns empty arrays. For example, localField is ObjectId '507f1f77bcf86cd799439011' but foreignField is string '507f1f77bcf86cd799439011'. They look the same but are different types, so $lookup fails silently.
To fix, you can either convert the local field to string with $toString or convert the foreign field to ObjectId with $toObjectId. In MongoDB 4.0+, you can use $convert in the pipeline before $lookup. Alternatively, store both fields in the same type consistently.
Aggregation stages have a 100 MB memory limit by default. If a stage (like $group or $sort) exceeds this limit, MongoDB will either fail (throw error) or spill to disk if allowDiskUse is enabled. When spilling to disk, results are still correct but performance degrades. However, if allowDiskUse is not set, the pipeline will fail with an error like 'Sort operation used more than the maximum 33554432 bytes of RAM'. This is a clear error, not wrong results.
But there is a subtle case: when using $sample with a size that exceeds memory, or when $group's accumulator state overflows. In older MongoDB versions, some stages could produce incomplete results without error. Always enable allowDiskUse for production pipelines that process large collections, and monitor memory usage in Atlas.
Date handling in aggregation is a common source of off-by-one errors. $dateToString, $week, $month all operate in UTC by default. If your application expects local time, you must specify the timezone parameter. For example, {$dateToString: {format: '%Y-%m-%d', date: '$timestamp', timezone: 'America/New_York'}}.
Also, if your dates are stored as strings (e.g., '2023-01-15T10:30:00Z'), you need to convert them to Date objects using $dateFromString before using date operators. Missing this conversion can lead to null results for date fields.
Frequently asked questions
Why does my aggregation pipeline return empty results even though documents exist?
The most common reasons are: (1) A $match stage filters out all documents due to a wrong field name or type mismatch. Use $match with a known _id to test. (2) A $lookup with mismatched types (ObjectId vs string) returns empty arrays. (3) A $group stage uses a field that doesn't exist in the documents, grouping by null. Check field names and types with $type.
How do I debug an aggregation pipeline step by step?
Add a $limit(5) after each stage to see intermediate results. You can also use $merge to output to a temporary collection and inspect it. For production, create a copy of the pipeline and replace the final stage with $out or $merge to a debug collection.
What does the 'allowDiskUse' option do and when should I use it?
allowDiskUse: true allows aggregation stages to write temporary data to disk when they exceed the 100 MB memory limit. Use it for any pipeline that processes large collections or does heavy sorting/grouping. Without it, the pipeline may fail with a memory limit error. However, it does not fix wrong results caused by logic errors.
Why does $sum return 0 or NaN for fields that have numbers?
$sum only works with numeric types. If the field is a string (even if it contains numeric characters), $sum treats it as 0. Use $toDouble or $toInt to convert the field before summing. Also check for null or missing values, as $sum ignores them but they won't cause NaN.
How do I verify that a $lookup join is working correctly?
Test with a known pair of documents: pick one document from the local collection and get its foreign key value. Then check that the foreign collection has a document with that same value in the foreignField. Run a find() on both collections to confirm the types match. Use $project with $type on both fields to ensure they are the same BSON type.