Aggregation Framework practice questions

Increase the default memory limit to 5GB using setParameter.

Why it fails: The 100MB memory limit is a hard-coded constraint for individual stages in the pipeline. While allowDiskUse enables spillover, there is no configuration setting to arbitrarily increase the default RAM limit per stage, making this approach invalid for managing memory usage in MongoDB deployments.

Avoid using $unwind because it always duplicates data in memory.

Why it fails: $unwind is a necessary tool for data transformation and is not inherently bad for memory if used correctly. While it increases the document count, it is not the primary cause of memory exhaustion compared to blocking stages like $group and $sort. It should be used judiciously, not avoided.

It must be the last stage in the pipeline.

Why it fails: $match is almost never the last stage, as that would prevent further data manipulation. It is typically placed as early as possible to reduce the input size for subsequent stages. Placing it last would perform all heavy computation on the entire collection before throwing away irrelevant data.

It can only filter on the _id field.

Why it fails: The $match stage supports filtering on any field, nested or otherwise, provided there is an index or the query is valid. Limiting it to the _id field would render the aggregation framework useless for most real-world scenarios where data is filtered by status, date, or other attributes.

It performs document-level transformations.

Why it fails: Transformation is the role of $project, $addFields, and $replaceRoot. $match is strictly a filtering stage that determines whether a document remains in the stream or is discarded. It does not alter the structure or content of the documents that pass the filter criteria.

[$group: { ... }, $match: { date: /2023/ }]

Why it fails: Grouping all documents before filtering forces the aggregation to compute totals for the entire collection history, including irrelevant years. This creates unnecessary overhead and memory pressure, as the $group stage must maintain state for every single document in the collection before the filter can discard the non-2023 entries.

[$project: { ... }, $group: { ... }, $match: { ... }]

Why it fails: Performing a projection before filtering is inefficient because it processes every document in the collection just to discard most of them later. The database engine must perform field transformation on the entire dataset, which wastes computing cycles that could have been saved by filtering early in the pipeline.

[$sort: { date: 1 }, $match: { ... }, $group: { ... }]

Why it fails: Sorting the entire collection before filtering is highly inefficient unless a covering index exists that satisfies the sort. Without an index, this forces an in-memory sort of the full dataset, which consumes significant system resources and slows down the aggregation process significantly compared to simple filtering.

$lookup can join data across different databases.

Why it fails: The $lookup stage is strictly restricted to collections located within the same MongoDB database. It cannot perform cross-database joins, which means data must be co-located if you intend to perform relational-style joins using this aggregation operator within a single pipeline execution context.

The 'as' field in $lookup is optional.

Why it fails: The 'as' field is a mandatory parameter for the $lookup stage. It specifies the name of the new array field that will be added to the input documents to store the results of the join operation, making it impossible to execute the stage without defining this output field name.

$lookup does not support sub-queries.

Why it fails: Starting from MongoDB 3.6, $lookup supports complex join conditions including sub-queries by using the 'let' and 'pipeline' parameters. This allows for far more advanced data correlation than simple equality matching, enabling developers to perform filtering, sorting, or limiting on the foreign collection during the join process itself.

{ amount: 1 }

Why it fails: An index on 'amount' is useful if you were filtering by amount, but it does not assist the $group stage in grouping by 'category'. Since the group operation is the first step, the index must cover the grouping field to allow for optimized grouping execution or to avoid full collection scans.

{ total: -1 }

Why it fails: The 'total' field is a computed result from the $group stage, not a field present in the original documents. You cannot create an index on a computed field in the collection, so an index on 'total' is impossible to create and irrelevant for the aggregation execution itself.

{ category: 1, amount: 1 }

Why it fails: While this compound index might be useful in some contexts, it provides no additional benefit over a simple index on 'category' for this specific $group operation. The aggregation only relies on 'category' for grouping and 'amount' for summation; the extra 'amount' field in the index increases storage size.

Using $group with $nullCount.

Why it fails: There is no built-in $nullCount accumulator in the MongoDB aggregation framework. While one could manually construct such a calculation using $cond and $sum, simply referring to a non-existent operator like $nullCount is incorrect and will cause the pipeline execution to fail with an error.

Using $sort to push nulls to the end.

Why it fails: While you can sort by a field, this does not help in 'handling' the missing values in terms of calculation or filtering. Sorting merely changes the order of the output. It does not provide a mechanism to replace or filter out the null values before they reach the calculation stage.

$set is faster than $project.

Why it fails: Both $set and $project perform operations within the memory of the aggregation engine. There is no significant performance difference between them, as the underlying transformation logic is essentially the same. The difference is purely in the syntax and how they handle the implicit inclusion of existing fields.

$project requires defining all fields.

Why it fails: While $project is often used for explicit control, you do not need to list every field. You can use it to add or modify fields while keeping others. However, the convention is that $project provides a stricter control over the output schema than $set, which is strictly additive.

$project cannot perform calculations.

Why it fails: $project is highly capable of performing complex calculations using operators like $add, $multiply, and $cond. It is one of the most powerful stages for data transformation and arithmetic operations, so stating it cannot perform calculations is factually incorrect regarding the framework's capabilities.

Using allowDiskUse: true.

Why it fails: allowDiskUse is a safety mechanism to prevent memory errors; it does not solve performance issues or optimize sharded aggregation. Relying on it just allows larger temporary files to be created, which is a symptom of poor pipeline design rather than a strategy for efficient sharded operations.

Using $lookup with a sharded collection.

Why it fails: Historically, $lookup had significant limitations with sharded collections as the foreign target. While support has improved, it is still a heavy operation that can lead to performance bottlenecks if not carefully configured, so it is rarely recommended as a primary strategy for optimizing sharded aggregations.

Increasing the batch size.

Why it fails: Batch size affects the communication between the client and the server, but it does not influence how the shards execute the aggregation pipeline. Changing the batch size will not improve the server-side aggregation performance on a sharded collection, which is determined by the pipeline stages themselves.

It can be used to join data from other collections.

Why it fails: $facet is designed for multi-faceted analysis of the current pipeline stream. It does not perform joins between different collections. While individual sub-pipelines inside the $facet might contain a $lookup stage, $facet itself is not a join operator and cannot be used to connect disparate data sets directly.

The result set is limited by a 16MB document size limit.

Why it fails: While the total size of the result document must respect the 16MB BSON limit, $facet is specifically subject to a 100MB RAM restriction on the individual aggregation stage. These are distinct limits; the 16MB document limit applies to any MongoDB document, but the 100MB limit is specific to aggregation staging.

The engine groups all documents into a single group.

Why it fails: While this might seem intuitive, the MongoDB aggregation framework strictly requires the _id field to be explicitly defined. If you want to group all documents into one single group, you must set _id to null or a static constant. Simply omitting the field results in a syntax error.

The pipeline ignores the grouping and returns no data.

Why it fails: The pipeline does not ignore the requirement for an _id field. It will explicitly fail to execute. Returning no data would imply the operation was successful but found no matches, whereas the absence of a required field is treated as a programmatic error during the validation process.

The engine groups by the first field in the document.

Why it fails: MongoDB does not make assumptions about which field to use for grouping. It requires explicit instruction from the developer. Relying on implicit field selection would be prone to errors, so the system enforces explicit declaration of the grouping key via the _id field, ensuring predictable aggregation behavior.

{ $group: { _id: "$category", avgRating: { $avg: "$rating" }, count: { $sum: 1 } } }, { $match: { count: { $gte: 5 } } }

Why it fails: This approach groups all documents first without any pre-filtering, forcing the server to process every single document in the collection. This increases memory usage and prevents the database engine from leveraging existing indexes effectively for the initial query.

{ $project: { category: 1, rating: 1 } }, { $group: { _id: "$category", avgRating: { $avg: "$rating" } } }, { $match: { avgRating: { $gte: 5 } } }

Why it fails: Projecting fields before grouping does not filter out documents based on review counts. Furthermore, filtering on the resulting average rating after grouping behaves like an SQL having clause, which processes all documents unnecessarily before discarding aggregated results.

{ $sort: { reviewCount: -1 } }, { $group: { _id: "$category", avgRating: { $avg: "$rating" }, minCount: { $min: "$reviewCount" } } }, { $match: { minCount: { $gte: 5 } } }

Why it fails: Sorting an entire collection prior to grouping is computationally expensive and unnecessary. Relying on minimum calculations inside the group stage to filter documents defeats the optimization benefits of early pipeline filtering.

$replaceRoot with { newRoot: { $mergeObjects: ["$$ROOT", { annualBonus: { $multiply: ["$salary", 0.1] } }] } }

Why it fails: $replaceRoot with $mergeObjects can add a field by merging the original document with a new object, but it is overly complex and error-prone for this simple task. It also replaces the root document, which can inadvertently alter field order or cause issues with nested fields. The straightforward $addFields stage is preferred and is the correct answer.

$project with { annualBonus: { $multiply: ["$salary", 0.1] } } and all other fields set to 0

Why it fails: Using $project with other fields set to 0 would exclude them, removing existing fields from the output. The requirement is to add a field without removing any others. While $project can compute the new field, the exclusion of other fields violates the scenario. $addFields is the appropriate stage for adding fields while retaining the rest.

$set with { annualBonus: { $multiply: ["$salary", 0.1] } }

Why it fails: $set is an alias for $addFields and would technically work, but the question asks for the stage that accomplishes the task; both $addFields and $set are valid. However, in this context, $addFields is the canonical answer, and $set is not the best choice because the exam expects recognition of $addFields as the primary stage for adding fields. The scenario does not mention $set as an option, so it is incorrect here.

Use $graphLookup to recursively join the collections and then filter fields with $project.

Why it fails: $graphLookup is designed for recursive searches across a collection, such as hierarchical data. It is not appropriate for a simple equality join between orders and customers. Using it here would be incorrect and inefficient. The scenario requires a straightforward join with field selection, which is best handled by $lookup with a sub-pipeline.

Use $lookup with localField and foreignField, then use $unwind and $replaceRoot to extract only name and email.

Why it fails: Using $unwind and $replaceRoot after $lookup would deconstruct the joined array and replace the root document, which is a complex and inefficient way to select fields. It also changes the document structure significantly, potentially losing the original order fields unless carefully managed. This approach is overly complicated and not the intended method for simple field selection.

Use $lookup with localField and foreignField, then a subsequent $project stage to include only the desired fields from the joined array.

Why it fails: While a subsequent $project can reshape the joined array, it operates after the join, meaning the entire customer document is fetched and then trimmed. This is less efficient than shaping during the join. The scenario asks for the approach that correctly shapes the joined data, and while this works, it is not optimal. The pipeline form of $lookup is preferred for efficiency.

db.orders.aggregate([ { $group: { _id: '$customerId', totalItems: { $sum: { $size: '$items' } } } }, { $match: { status: 'completed' } } ])

Why it fails: This fails because it groups before filtering, so it includes orders of all statuses. Also, $size counts the number of array elements, not the sum of qty values. The $match after $group refers to the grouped documents, which no longer contain a 'status' field, making the filter ineffective.

db.orders.aggregate([ { $match: { status: 'completed' } }, { $unwind: '$items' }, { $group: { _id: '$customerId', totalItems: { $sum: '$items.qty' } } } ])

Why it fails: Although this pipeline correctly unwinds the items array and sums the qty field, it produces the same result as the correct answer but is less efficient for large arrays. However, it is not incorrect; the question asks for the correct snippet, and this is also valid. But since only one answer is correct, this option is considered incorrect because it uses $unwind, which can multiply documents and may exceed memory limits, whereas the correct answer avoids that.

db.orders.aggregate([ { $match: { status: 'completed' } }, { $group: { _id: '$customerId', totalItems: { $sum: '$items' } } } ])

Why it fails: This incorrectly attempts to sum the entire 'items' array directly. $sum expects numeric values, and when given an array of subdocuments, it will ignore them, resulting in 0 or an error. It does not extract the 'qty' field or flatten the array, so the total is wrong.

$match on { status: "error" }, then $count by service

Why it fails: $count is a stage that returns a single document with the total count of documents passing through it. It does not group by a field like service. To count per service, you must use $group with $sum: 1. The $count stage would give the total number of errors across all services, not per service, so this option fails the requirement.

$group by service with $sum: 1, then $match on { status: "error" }

Why it fails: Grouping before filtering would count all documents per service, not just errors, and the subsequent $match would filter on the grouped documents, which no longer have a status field. This sequence is incorrect because the $match after $group would not work as intended; the status field is lost after grouping. Filtering must occur before grouping.

$project to include only service and status, then $match on { status: "error" }, then $group by service with $sum: 1

Why it fails: While this sequence would work, the initial $project is unnecessary and adds overhead. The $match can directly filter on status without a preceding $project. The extra stage increases processing time and memory usage. The most efficient and correct sequence is to filter first with $match, then group, making this option suboptimal.

{ $addFields: { deviceKey: { $concat: [ "$deviceId", "" ] } } }

Why it fails: $concat only accepts string operands. When it encounters an ObjectId or any non-string value it errors out rather than coercing the value, so legacy documents would abort the pipeline. This approach cannot normalize mixed BSON types and therefore fails to produce the single consistent group key the scenario requires.

{ $project: { deviceKey: { $ifNull: [ "$deviceId", "unknown" ] } } }

Why it fails: $ifNull substitutes a fallback only when the field is null or missing. It performs no type conversion, so an ObjectId remains an ObjectId and groups separately from the string identifiers. The pipeline would produce split groups for the same physical device, defeating the goal of a unified group key.

{ $addFields: { deviceKey: { $convert: { input: "$deviceId", to: "string" } } } }

Why it fails: The $convert operator is capable of this conversion, but without an onError or onNull handler it throws an error the moment a document contains a value it cannot convert, halting the entire pipeline. In a mixed-type collection that risk is real, so this form is less robust than the dedicated $toString shorthand, which is designed for exactly this coercion.

$sort by accountId and date, then $group with { _id: "$accountId", cumulativeSum: { $sum: "$amount" } }, then $project with { cumulativeSum: 1 }

Why it fails: This option is similar to the first but adds a $project to include only the cumulativeSum field. However, the $group still computes a total sum, not a cumulative sum. The $project does not change the fact that the aggregation is a final total. Therefore, this pipeline does not produce a running total.

$sort by accountId and date, then $group with { _id: "$accountId", amounts: { $push: "$amount" } }, then $project with { cumulativeSum: { $sum: "$amounts" } }

Why it fails: This pipeline collects all amounts into an array and then sums the entire array, resulting in a total sum per account, not a cumulative sum. The $project stage sums the array but does not produce a running total over time. Thus, it fails to compute the cumulative sum as required.

$sort by accountId and date, then $group with { _id: "$accountId", cumulativeSum: { $sum: "$amount" } }

Why it fails: This pipeline computes the total sum per account, not a cumulative sum over time. The $group stage aggregates all transactions into a single sum per account, losing the running total aspect. While sorting is correct, the $group does not produce a running total; it produces a final total. Hence, it does not meet the requirement.

The $sort stage is always executed before $group regardless of pipeline order to optimize performance.

Why it fails: The aggregation pipeline executes stages in the order specified. There is no automatic reordering that places $sort before $group. In fact, placing $sort before $group would be inefficient because it would sort all documents before grouping. The optimizer may make some adjustments, but it does not reorder stages in this manner. This statement is incorrect.

The $group stage automatically spills to disk when it exceeds 100 MB, so no configuration is needed.

Why it fails: The $group stage does not automatically spill to disk. By default, if it exceeds 100 MB, it will produce an error. To allow spilling, you must explicitly set allowDiskUse: true. Without this setting, the pipeline will fail. This statement is incorrect because it implies automatic spilling.

The $sort stage can use an index if it is the first stage in the pipeline and the sort field is indexed.

Why it fails: While $sort can use an index when it is the first stage, the scenario describes a pipeline that includes $group before $sort. After $group, the documents are transformed, and the original indexes are no longer applicable. Therefore, the $sort in this pipeline cannot use an index. This statement is incorrect for the given scenario.

db.sales.aggregate([ { $group: { _id: "$storeId", totalAmount: { $sum: "$amount" }, count: { $sum: 1 } } }, { $match: { totalAmount: { $gt: 100 } } } ])

Why it fails: This pipeline groups all documents first and then filters groups where totalAmount is greater than 100. This is incorrect because the filter condition should apply to individual transactions (amount > 100), not to the aggregated total per store. The result would include only stores whose total sales exceed 100, which is different from the requirement.

db.sales.aggregate([ { $group: { _id: "$storeId", totalAmount: { $sum: "$amount" }, count: { $sum: 1 } } }, { $match: { amount: { $gt: 100 } } } ])

Why it fails: This pipeline groups all documents first, including those with amount less than or equal to 100, and then tries to filter on amount after the $group stage. However, after $group, the amount field no longer exists; the output documents have _id, totalAmount, and count. The $match on amount will not match any documents because amount is not present, resulting in no output. Also, filtering after grouping is inefficient.

db.sales.aggregate([ { $match: { amount: { $gt: 100 } } }, { $group: { _id: "$storeId", totalAmount: { $sum: "$amount" }, count: { $sum: 1 } } }, { $project: { storeId: "$_id", totalAmount: 1, count: 1, _id: 0 } } ])

Why it fails: This pipeline includes an extra $project stage to reshape the output, which is not required by the scenario. While it correctly filters and groups, the additional projection changes the output format by removing _id and adding storeId. The question asks for a report showing total sales amount and number of transactions per store, but does not specify the exact output shape; the extra $project is unnecessary and may not align with expected output.

{ $project: { _id: 0, name: 1, skillCount: { $count: "$skills" } } }

Why it fails: `$count` is an accumulator available in `$group` and `$setWindowFields`, not a valid expression in `$project`. Using it in `$project` would result in an error. The scenario requires the number of elements in the `skills` array, which is obtained with `$size`, not `$count`.

{ $project: { _id: 0, name: 1, skillCount: { $arrayElemAt: ["$skills", 0] } } }

Why it fails: `$arrayElemAt` returns the element at a specified index, so this expression would return the first skill string, not the number of skills. The scenario asks for the count of skills, so the result would be a string instead of a numeric count. The correct operator is `$size`, which returns the array length.

{ $project: { _id: 0, name: 1, skillCount: { $sum: "$skills" } } }

Why it fails: `$sum` is an accumulator used in `$group`, not an array operator in `$project`. In a `$project` stage, `$sum` is not valid and would cause an error. Even if it were accepted, summing array elements would add string values or fail, not count them. The correct operator for counting array elements is `$size`.