SQL to Aggregation Mapping Chart
- Examples

SQL to Aggregation Mapping Chart

The aggregation pipeline allowsMongoDB to provide native aggregation capabilities that corresponds tomany common data aggregation operations in SQL.

The following table provides an overview of common SQL aggregationterms, functions, and concepts and the corresponding MongoDBaggregation 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$sortByCount`
join	`$lookup`
SELECT INTO NEW_TABLE	`$out`
MERGE INTO TABLE	`$merge` (Available starting in MongoDB 4.2)

For a list of all aggregation pipeline and expression operators, seeAggregation Pipeline Quick Reference.

Examples

The following table presents a quick reference of SQL aggregationstatements and the corresponding MongoDB statements. The examples inthe table assume the following conditions:

The SQL examples assume two tables, orders andorder_lineitem that join by the order_lineitem.order_id andthe orders.id columns.
The MongoDB examples assume one collection orders that containdocuments of the following prototype:

{
  cust_id: "abc123",
  ord_date: ISODate("2012-11-02T17:04:11.102Z"),
  status: 'A',
  price: 50,
  items: [ { sku: "xxx", qty: 25, price: 1 },
           { sku: "yyy", qty: 25, price: 1 } ]
}

SQL Example	MongoDB Example	Description
SELECT COUNT() AS countFROM orders	db.orders.aggregate( [ { $group: { _id: null, count: { $sum: 1 } } }] )	Count all recordsfrom `orders`
SELECT SUM(price) AS totalFROM orders	db.orders.aggregate( [ { $group: { _id: null, total: { $sum: "$price" } } }] )	Sum the `price` fieldfrom `orders`
SELECT cust_id, SUM(price) AS totalFROM ordersGROUP BY cust_id	db.orders.aggregate( [ { $group: { _id: "$cust_id", total: { $sum: "$price" } } }] )	For each unique `cust_id`,sum the `price` field.
SELECT cust_id, SUM(price) AS totalFROM ordersGROUP BY cust_idORDER BY total	db.orders.aggregate( [ { $group: { _id: "$cust_id", total: { $sum: "$price" } } }, { $sort: { total: 1 } }] )	For each unique `cust_id`,sum the `price` field,results sorted by sum.
SELECT cust_id, ord_date, SUM(price) AS totalFROM ordersGROUP BY cust_id, ord_date	db.orders.aggregate( [ { $group: { _id: { cust_id: "$cust_id", ord_date: { $dateToString: { format: "%Y-%m-%d", date: "$ord_date" }} }, total: { $sum: "$price" } } }] )	For each unique`cust_id`, `ord_date` grouping,sum the `price` field.Excludes the time portion of the date.
SELECT cust_id, count()FROM ordersGROUP BY cust_idHAVING count() > 1	db.orders.aggregate( [ { $group: { _id: "$cust_id", count: { $sum: 1 } } }, { $match: { count: { $gt: 1 } } }] )	For `cust_id` with multiple records,return the `cust_id` andthe corresponding record count.
SELECT cust_id, ord_date, SUM(price) AS totalFROM ordersGROUP BY cust_id, ord_dateHAVING total > 250	db.orders.aggregate( [ { $group: { _id: { cust_id: "$cust_id", ord_date: { $dateToString: { format: "%Y-%m-%d", date: "$ord_date" }} }, total: { $sum: "$price" } } }, { $match: { total: { $gt: 250 } } }] )	For each unique `cust_id`, `ord_date`grouping, sum the `price` fieldand return only where thesum is greater than 250.Excludes the time portion of the date.
SELECT cust_id, SUM(price) as totalFROM ordersWHERE status = 'A'GROUP BY cust_id	db.orders.aggregate( [ { $match: { status: 'A' } }, { $group: { _id: "$cust_id", total: { $sum: "$price" } } }] )	For each unique `cust_id`with status `A`,sum the `price` field.
SELECT cust_id, SUM(price) as totalFROM ordersWHERE status = 'A'GROUP BY cust_idHAVING total > 250	db.orders.aggregate( [ { $match: { status: 'A' } }, { $group: { _id: "$cust_id", total: { $sum: "$price" } } }, { $match: { total: { $gt: 250 } } }] )	For each unique `cust_id`with status `A`,sum the `price` field and returnonly where thesum is greater than 250.
SELECT cust_id, SUM(li.qty) as qtyFROM orders o, order_lineitem liWHERE li.order_id = o.idGROUP BY cust_id	db.orders.aggregate( [ { $unwind: "$items" }, { $group: { _id: "$cust_id", qty: { $sum: "$items.qty" } } }] )	For each unique `cust_id`,sum the correspondingline item `qty` fieldsassociated with theorders.
SELECT COUNT()FROM (SELECT cust_id, ord_date FROM orders GROUP BY cust_id, ord_date) as DerivedTable	db.orders.aggregate( [ { $group: { _id: { cust_id: "$cust_id", ord_date: { $dateToString: { format: "%Y-%m-%d", date: "$ord_date" }} } } }, { $group: { _id: null, count: { $sum: 1 } } }] )	Count the number of distinct`cust_id`, `ord_date` groupings.Excludes the time portion of the date.