$min and $max accumulators in MongoDB - Step-by-Step Execution

Concept Flow - $min and $max accumulators

Start Aggregation Pipeline

↓

Group Documents by _id

↓

Apply $min and $max on Fields

↓

Calculate Minimum and Maximum Values

↓

Output Grouped Result with Min and Max

↓

End

The aggregation groups documents, then calculates minimum and maximum values for specified fields using $min and $max accumulators.

Execution Sample

MongoDB

db.sales.aggregate([
  { $group: {
      _id: "$item",
      minPrice: { $min: "$price" },
      maxPrice: { $max: "$price" }
    }
  }
])

This query groups sales by item and finds the minimum and maximum price for each item.

Execution Table

Step	Document Processed	Group _id	Current minPrice	Current maxPrice	Action
1	{item: "apple", price: 5}	apple	5	5	Initialize minPrice and maxPrice with 5
2	{item: "apple", price: 3}	apple	3	5	Update minPrice to 3 (3 < 5)
3	{item: "apple", price: 8}	apple	3	8	Update maxPrice to 8 (8 > 5)
4	{item: "banana", price: 2}	banana	2	2	Initialize minPrice and maxPrice with 2
5	{item: "banana", price: 4}	banana	2	4	Update maxPrice to 4 (4 > 2)
6	{item: "banana", price: 1}	banana	1	4	Update minPrice to 1 (1 < 2)
7	No more documents	-	-	-	Aggregation completes, output grouped results

💡 All documents processed; final min and max values computed per group.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	Final
minPrice (apple)	N/A	5	3	3	3	3	3	3
maxPrice (apple)	N/A	5	5	8	8	8	8	8
minPrice (banana)	N/A	N/A	N/A	N/A	2	2	1	1
maxPrice (banana)	N/A	N/A	N/A	N/A	2	4	4	4

Key Moments - 3 Insights

Why does minPrice update when a smaller price is found but not when a larger price is found?

Because $min only updates when the new value is less than the current minPrice, as shown in execution_table rows 2 and 3.

Why do we initialize minPrice and maxPrice with the first document's price?

Initialization sets a starting point for comparison; without it, we wouldn't have a baseline to compare other prices, as seen in rows 1 and 4.

What happens if all documents for a group have the same price?

Both minPrice and maxPrice remain that same value since no smaller or larger values appear, consistent with the logic in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is maxPrice for 'apple' after processing the third document?

A 3

B 8

C 5

D Undefined

💡 Hint

Check row 3 under 'Current maxPrice' for 'apple'.

At which step does minPrice for 'banana' update to 1?

A Step 4

B Step 5

C Step 6

D Step 7

💡 Hint

Look at the 'Current minPrice' for 'banana' in rows 4, 5, and 6.

If the price of the second 'apple' document was 6 instead of 3, what would minPrice be after step 2?

A 5

B 3

C 6

D Undefined

💡 Hint

Compare the new price with the initial minPrice in row 1 and see if it updates in row 2.

Concept Snapshot

$min and $max accumulators find the smallest and largest values in grouped documents.
Use inside $group stage in aggregation pipeline.
Syntax: { $min: "$field" }, { $max: "$field" }.
They update values only when a smaller or larger value appears.
Useful for summarizing data ranges per group.

Full Transcript

This visual execution shows how MongoDB's $min and $max accumulators work in an aggregation pipeline. Documents are grouped by the 'item' field. For each group, the pipeline tracks the minimum and maximum 'price' values. Initially, the first document's price sets both minPrice and maxPrice. As more documents are processed, minPrice updates only if a smaller price is found, and maxPrice updates only if a larger price is found. The execution table traces each document's effect on these values step-by-step. The variable tracker summarizes how minPrice and maxPrice change for each group. Key moments clarify common confusions, such as why initialization is needed and how updates happen. The quiz tests understanding by referencing specific steps and values in the execution. This helps beginners see exactly how $min and $max accumulate values during aggregation.

Practice

1. What do the $min and $max accumulators do in a MongoDB aggregation pipeline?

easy

A. $min and $max sort documents in ascending and descending order respectively.

B. $min and $max both find the average value in a group.

C. $min finds the first document and $max finds the last document in a group.

D. $min finds the smallest value and $max finds the largest value in a group.

Solution

1. Step 1: Understand the purpose of $min and $max

   $min returns the smallest value from the grouped documents, while $max returns the largest value.

2. Step 2: Compare with other options

   The other options describe different operations like average, first/last document, or sorting, which are not what $min and $max do.

3. Final Answer:

   $min finds the smallest value and $max finds the largest value in a group. -> Option D

4. Quick Check:

   $min = smallest, $max = largest [OK]

Hint: Remember: min = smallest, max = largest value [OK]

Common Mistakes:

• Confusing $min and $max with average functions
• Thinking they return documents instead of values
• Mixing accumulators with sorting operators

2. Which of the following is the correct syntax to use $max in a $group stage to find the highest score from a field named score?

easy

A. { $group: { _id: null, maxScore: { $max: "$score" } } }

B. { $group: { _id: null, maxScore: { max: "$score" } } }

C. { $group: { _id: null, maxScore: { $max: score } } }

D. { $group: { _id: null, maxScore: { $max: "score" } } }

Solution

1. Step 1: Check the correct use of $max accumulator

   The correct syntax requires $max as the operator and the field name as a string with a $ prefix inside quotes: "$score".

2. Step 2: Identify syntax errors in other options

   { $group: { _id: null, maxScore: { max: "$score" } } } misses the $ before max, { $group: { _id: null, maxScore: { $max: score } } } lacks quotes around field name, and { $group: { _id: null, maxScore: { $max: "score" } } } uses quotes without $ prefix, all invalid.

3. Final Answer:

   { $group: { _id: null, maxScore: { $max: "$score" } } } -> Option A

4. Quick Check:

   Use $max: "$field" syntax [OK]

Hint: Always prefix field names with $ and use quotes inside accumulators [OK]

Common Mistakes:

• Omitting $ before accumulator name
• Not quoting the field name with $
• Using field name without $ or quotes

3. Given the collection documents:

{ "name": "Alice", "score": 85 }, { "name": "Bob", "score": 92 }, { "name": "Charlie", "score": 78 }

What will be the output of this aggregation?

[{ $group: { _id: null, minScore: { $min: "$score" }, maxScore: { $max: "$score" } } }]

medium

A. { "_id": null, "minScore": 85, "maxScore": 85 }

B. { "_id": null, "minScore": 78, "maxScore": 92 }

C. { "_id": null, "minScore": 78, "maxScore": 78 }

D. { "_id": null, "minScore": 92, "maxScore": 85 }

Solution

1. Step 1: Identify minimum and maximum scores from documents

   The scores are 85, 92, and 78. The smallest is 78 and the largest is 92.

2. Step 2: Understand the aggregation output

   The $group stage groups all documents (_id: null) and calculates minScore as 78 and maxScore as 92.

3. Final Answer:

   { "_id": null, "minScore": 78, "maxScore": 92 } -> Option B

4. Quick Check:

   Min = 78, Max = 92 [OK]

Hint: Scan values, pick smallest for $min and largest for $max [OK]

Common Mistakes:

• Mixing up min and max values
• Assuming $min or $max returns documents instead of values
• Forgetting to use $ before field names

4. Consider this aggregation pipeline:

[{ $group: { _id: "$category", minPrice: { $min: price }, maxPrice: { $max: "$price" } } }]

What is the error in this pipeline?

medium

A. Field name 'price' in $min is missing quotes and $ prefix.

B. Using $max and $min together is not allowed.

C. The _id field cannot be a field name.

D. The pipeline is correct and has no errors.

Solution

1. Step 1: Check syntax of $min and $max usage

   The $min accumulator uses price without quotes and $ prefix, which is incorrect. It should be "$price".

2. Step 2: Verify other parts of the pipeline

   $max is correctly used with "$price". Grouping by "$category" is valid. Using both accumulators together is allowed.

3. Final Answer:

   Field name 'price' in $min is missing quotes and $ prefix. -> Option A

4. Quick Check:

   Field names need "$" prefix inside accumulators [OK]

Hint: Always quote and prefix field names with $ inside accumulators [OK]

Common Mistakes:

• Omitting $ before field names inside accumulators
• Thinking $min and $max cannot be used together
• Misunderstanding _id field usage in $group

5. You have a collection of sales documents with fields region and amount. You want to find the smallest and largest sale amounts per region, but only for regions where the smallest sale is above 100. Which aggregation pipeline correctly achieves this?

hard

A. [{ $group: { _id: "$region", minSale: { $min: "$amount" }, maxSale: { $max: "$amount" }, filter: { $gt: ["$minSale", 100] } } }]

B. [{ $match: { amount: { $gt: 100 } } }, { $group: { _id: "$region", minSale: { $min: "$amount" }, maxSale: { $max: "$amount" } } }]

C. [{ $group: { _id: "$region", minSale: { $min: "$amount" }, maxSale: { $max: "$amount" } } }, { $match: { minSale: { $gt: 100 } } }]

D. [{ $group: { _id: "$region", minSale: { $min: "$amount" }, maxSale: { $max: "$amount" } } }, { $match: { maxSale: { $gt: 100 } } }]

Solution

1. Step 1: Group by region and calculate min and max sales

   The $group stage groups documents by region and calculates minSale and maxSale using $min and $max on amount.

2. Step 2: Filter groups where minSale is greater than 100

   The $match stage after grouping filters groups where minSale is greater than 100, exactly as required.

3. Final Answer:

   [{ $group: { _id: "$region", minSale: { $min: "$amount" }, maxSale: { $max: "$amount" } } }, { $match: { minSale: { $gt: 100 } } }] -> Option C

4. Quick Check:

   Group first, then filter on aggregated minSale [OK]

Hint: Filter after grouping to use aggregated fields in conditions [OK]

Common Mistakes:

• Filtering before grouping, which filters documents not groups
• Trying to filter inside $group stage incorrectly
• Filtering on maxSale instead of minSale