President The correspondence between SQL and MongoDB aggregation

This article translated from: https://docs.mongodb.com/manual/reference/sql-aggregation-comparison/

Because I am also learning MongoDB, the project to use aggregation, see the document this good translation (for reference only)

The aggregation function in SQL corresponds to a pipeline in MongoDB:

WHERE             $matchGROUP by     $groupHAVING             $matchSELECT             $projectORDER by     $sortLIMIT             $limitSUM ()             $sumCOUNT ()             $sumjoin             $lookup

Example:

Create a document, populate the data first

/* 0 */{  "_id": ObjectId ("5812b447311bb4272016496a"),  "cust_id": "abc123",  "Ord_date": Isodate (" 2012-11-02t17:04:11.102z "),  " status ":" A ",  " price ": +,  " items ": [{      " SKU ":" XXX ",      " qty ": 25,
    "Price": 1    }, {      "SKU": "YYY",      "qty": +,      "price": 1    }]}/* 1 */{  "_id": ObjectId ("58131 494311BB418B058FCBA "),  " cust_id ":" A ",  " Ord_date ": Isodate (" 2012-11-02t17:04:11.102z "),  " status ": "B",  "price": +,  "items": [{      "SKU": "XXX",      "qty": +, "Price      ": 1    }, {      "sku": "YY Y ",      " qty ": +,      " price ": 1    }]}/* 2 */{  " _id ": ObjectId (" 581314B6311BB418B058FCBB "),  " Cust_ ID ":" AB ",  " Ord_date ": Isodate (" 2012-11-02t17:04:11.102z "),  " status ":" E ",  " price ": $,  " items  ": [{      " SKU ":" XXX ",      " qty ": +,      " price ": 1    }, {      " SKU ":" YYY ",      " qty ": +,      " price ": 1    }]}

Example 1:

Sql:

SELECT Count (*) as COUNT from  orders

　　

Mongodb:

Db.orders.aggregate ([{$group: {_id:null, count:{$sum: 1}}])

Example 2:

Sql:

SELECT SUM (price) as total from orders

Mongodb:

Db.orders.aggregate ([{$group: {_id:null, total:{$sum: "$PR Ice "}}}])

Example 3:

Sql:

SELECT Cust_id,sum (price) as total from orders GROUP by cust_id

Mongodb:

Db.orders.aggregate ([{$group: {    _id: "$cust _id",　　　　　　　　　　　　Total: {$sum: "$price"}}　　　　　　　　　　}, {$sort: {total:1}} ])        

Example 4:

Sql:

SELECT cust_id, Ord_date,sum (price) as total from  orders  GROUP by cust_id, ord_date

Mongodb:

Db.orders.aggregate ([{$group: {_id: {cust_id: ' $cust _id ', ord_date: {m　　　　　　onth:{$month: "$ord _date"}, day:{$dayOfMonth: "$ord _date"}, year:{$year: "$ord _date"}} }, total:{$sum: "$price"}}}])

　　　　　

　

President The correspondence between SQL and MongoDB aggregation