Convert SQL AVG, MIN, MAX to MongoDB Aggregate Accumulators

AVG, MIN, MAX Aggregate Functions

SQL's AVG, MIN, and MAX aggregate functions each have a direct MongoDB counterpart in the $group stage's accumulator operators.

Example SQL

SELECT department,
       AVG(salary) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY department

Generated MongoDB Query

db.employees.aggregate([
  {
    $group: {
      _id: "$department",
      avg_salary: { $avg: "$salary" },
      min_salary: { $min: "$salary" },
      max_salary: { $max: "$salary" },
      department: { $first: "$department" }
    }
  }
])

Accumulator Mapping

SQL Function	MongoDB Operator	Description
AVG(col)	{ $avg: "$col" }	Arithmetic mean of values
MIN(col)	{ $min: "$col" }	Smallest value
MAX(col)	{ $max: "$col" }	Largest value
SUM(col)	{ $sum: "$col" }	Sum of values
COUNT(*)	{ $sum: 1 }	Document count

Without GROUP BY

SQL allows aggregate functions without GROUP BY, which aggregates over the entire table:

SELECT AVG(price) AS avg_price FROM products

In MongoDB, use null as the _id to aggregate all documents:

db.products.aggregate([
  { $group: { _id: null, avg_price: { $avg: "$price" } } }
])

$min and $max on Non-Numeric Fields

Unlike SQL where MIN and MAX are typically used with numbers or dates, MongoDB's $min and $max follow BSON comparison order and work across types. Strings are compared lexicographically, dates chronologically, and different types follow MongoDB's comparison hierarchy.

Combining with $match

Add a $match stage before $group to filter documents before aggregation, equivalent to SQL WHERE before GROUP BY:

db.employees.aggregate([
  { $match: { status: "active" } },
  { $group: { _id: "$department", avg_salary: { $avg: "$salary" } } }
])