- Aggregation >
- Aggregation Reference >
- SQL to Aggregation Mapping Chart
SQL to Aggregation Mapping Chart¶
On this page
The aggregation pipeline allows MongoDB to provide native aggregation capabilities that corresponds to many common data aggregation operations in SQL. If you’re new to MongoDB you might want to consider the Frequently Asked Questions section for a selection of common questions.
The following table provides an overview of common SQL aggregation terms, functions, and concepts and the corresponding MongoDB aggregation operators:
SQL Terms, Functions, and Concepts | MongoDB Aggregation Operators |
---|---|
WHERE | $match |
GROUP BY | $group |
HAVING | $match |
SELECT | $project |
ORDER BY | $sort |
LIMIT | $limit |
SUM() | $sum |
COUNT() | $sum |
join | No direct corresponding operator; however, the
$unwind operator allows for somewhat similar
functionality, but with fields embedded within the document. |
See also
Examples¶
The following table presents a quick reference of SQL aggregation statements and the corresponding MongoDB statements. The examples in the table assume the following conditions:
The SQL examples assume two tables,
orders
andorder_lineitem
that join by theorder_lineitem.order_id
and theorders.id
columns.The MongoDB examples assume one collection
orders
that contain documents of the following prototype:The MongoDB statements prefix the names of the fields from the documents in the collection
orders
with a$
character when they appear as operands to the aggregation operations.
SQL Example | MongoDB Example | Description |
---|---|---|
Count all records
from orders |
||
Sum the price field
from orders |
||
For each unique cust_id ,
sum the price field. |
||
For each unique cust_id ,
sum the price field,
results sorted by sum. |
||
For each unique
cust_id , ord_date grouping,
sum the price field.
Excludes the time portion of the date. |
||
For cust_id with multiple records,
return the cust_id and
the corresponding record count. |
||
For each unique cust_id , ord_date
grouping, sum the price field
and return only where the
sum is greater than 250.
Excludes the time portion of the date. |
||
For each unique cust_id
with status A ,
sum the price field. |
||
For each unique cust_id
with status A ,
sum the price field and return
only where the
sum is greater than 250. |
||
For each unique cust_id ,
sum the corresponding
line item qty fields
associated with the
orders. |
||
Count the number of distinct
cust_id , ord_date groupings.
Excludes the time portion of the date. |