mongodb aggregation queries vs sql queries

These are mongodb aggregation queries equivalent of SQL queries written in the comments below. These examples are taken directly from mongodb documentations.

Count all records from orders collections
[code language=”javascript”]
//SELECT COUNT(*) AS count FROM orders;
db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] );
[/code]

Sum the price field from orders.
[code language=”javascript”]
//SELECT SUM(price) AS total FROM orders;
db.orders.aggregate( [
{
$group: {
_id: null,
total: { $sum: "$price" }
}
}
] );
[/code]

For each unique cust_id sum the price field.
[code language=”javascript”]
//SELECT cust_id, SUM(price) AS total FROM orders GROUP BY cust_id;
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] );
[/code]

For each unique cust_id, sum the price field, results sorted by sum.
[code language=”javascript”]
//SELECT cust_id, SUM(price) AS total FROM orders GROUP BY cust_id ORDER BY total;
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $sort: { total: 1 } }
] );
[/code]

For each unique cust_id, ord_date grouping, sum the price field. Excludes the time portion of the date.
[code language=”javascript”]
//SELECT cust_id, ord_date, SUM(price) AS total FROM orders GROUP BY cust_id, ord_date;
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
}
] );
[/code]

For cust_id with multiple records, return the cust_id and the corresponding record count.
[code language=”javascript”]
// SELECT cust_id, count(*) FROM orders GROUP BY cust_id HAVING count(*) > 1;
db.orders.aggregate( [
{
$group: {
_id: "$cust_id",
count: { $sum: 1 }
}
},
{ $match: { count: { $gt: 1 } } }
] );
[/code]

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.
[code language=”javascript”]
// SELECT cust_id,
// ord_date,
// SUM(price) AS total
// FROM orders
// GROUP BY cust_id,
// ord_date
// HAVING total > 250

db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] );
[/code]

For each unique cust_id with status A, sum the price field.
[code language=”javascript”]
// SELECT cust_id,
// SUM(price) as total
// FROM orders
// WHERE status = ‘A’
// GROUP BY cust_id;

db.orders.aggregate( [
{ $match: { status: ‘A’ } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
}
] );
[/code]

For each unique cust_id with status A, sum the price field and return only where the sum is greater than 250.
[code language=”javascript”]
// SELECT cust_id,
// SUM(price) as total
// FROM orders
// WHERE status = ‘A’
// GROUP BY cust_id
// HAVING total > 250;
db.orders.aggregate( [
{ $match: { status: ‘A’ } },
{
$group: {
_id: "$cust_id",
total: { $sum: "$price" }
}
},
{ $match: { total: { $gt: 250 } } }
] );
[/code]

For each unique cust_id, sum the corresponding line item qty fields associated with the orders.
[code language=”javascript”]
// SELECT cust_id,
// SUM(li.qty) as qty
// FROM orders o,
// order_lineitem li
// WHERE li.order_id = o.id
// GROUP BY cust_id;

db.orders.aggregate( [
{ $unwind: "$items" },
{
$group: {
_id: "$cust_id",
qty: { $sum: "$items.qty" }
}
}
] );
[/code]

Count the number of distinct cust_id, ord_date groupings. Excludes the time portion of the date.
[code language=”javascript”]
// 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: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
}
}
},
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] );
[/code]