## **1、MongoDB 聚合管道(Aggregation Pipeline)**
使用聚合管道可以對集合中的文檔進行變換和組合。
實際項目:表關聯查詢、數據的統計。
MongoDB 中使用 db.COLLECTION\_NAME.aggregate(\[{\<stage\>},...\]) 方法 來構建和使用聚合管道。先看下官網給的實例,感受一下聚合管道的用法。

## **2、MongoDB Aggregation 管道操作符與表達式**
| 管道操作符 | Description |
| --- | --- |
| $project | 增加、刪除、重命名字段 |
| $match | 條件匹配。只滿足條件的文檔才能進入下 一階段 |
| $limit | 限制結果的數量 |
| $skip | 跳過文檔的數量 |
| $sort | 條件排序。 |
| $group | 條件組合結果 統計 |
| $lookup | $lookup 操作符 用以引入其它集合的數 據 (表關聯查詢) |
**SQL 和 NOSQL 對比:**
| WHERE | $match |
| --- | --- |
| GROUP BY | $group |
| HAVING | $match |
| SELECT | $project |
| ORDER BY | $sort |
| LIMIT | $limit |
| SUM() | $sum |
| COUNT() | $sum |
| join | $lookup |
**管道表達式:**
管道操作符作為“鍵”,所對應的“值”叫做管道表達式。
例如{$match:{status:"A"}},$match 稱為管道操作符,而 status:"A"稱為管道表達式, 是管道操作符的操作數(Operand)。
每個管道表達式是一個文檔結構,它是由字段名、字段值、和一些表達式操作符組成的。
| 常用表達式操作符 | Description |
| --- | --- |
| $addToSet | 將文檔指定字段的值去重 |
| $max | 文檔指定字段的最大值 |
| $min | 文檔指定字段的最小值 |
| $sum | 文檔指定字段求和 |
| $avg | 文檔指定字段求平均 |
| $gt | 大于給定值 |
| $lt | 小于給定值 |
| $eq | 等于給定值 |
## **3、 數據模擬**
```
db.order.insert({"order_id":"1","uid":10,"trade_no":"111","all_price":100,"all_num":2})
db.order.insert({"order_id":"2","uid":7,"trade_no":"222","all_price":90,"all_num":2})
db.order.insert({"order_id":"3","uid":9,"trade_no":"333","all_price":20,"all_num":6})
db.order_item.insert({"order_id":"1","title":"商品鼠標 1","price":50,num:1})
db.order_item.insert({"order_id":"1","title":"商品鍵盤 2","price":50,num:1})
db.order_item.insert({"order_id":"1","title":"商品鍵盤 3","price":0,num:1})
db.order_item.insert({"order_id":"2","title":"牛奶","price":50,num:1})
db.order_item.insert({"order_id":"2","title":"酸奶","price":40,num:1})
db.order_item.insert({"order_id":"3","title":"礦泉水","price":2,num:5})
db.order_item.insert({"order_id":"3","title":"毛巾","price":10,num:1})
```

## **4、 $project**
修改文檔的結構,可以用來重命名、增加或刪除文檔中的字段。
要求查找 order 只返回文檔中 trade\_no 和 all\_price 字段
```
db.order.aggregate([ { $project:{ trade_no:1, all_price:1 } } ])
```

## **5、 $match**
**作用**
用于過濾文檔。用法類似于 find() 方法中的參數。
```
db.order.aggregate([
{ $project:{ trade_no:1, all_price:1 } },
{ $match:{"all_price":{$gte:90}} } ]
)
```

## **6、 $group**
將集合中的文檔進行分組,可用于統計結果。
統計每個訂單的訂單數量,按照訂單號分組
```
db.order_item.aggregate( [
{ $group: {_id: "$order_id", total: {$sum: "$num"}} }
] )
```

## **7、 $sort**
將集合中的文檔進行排序。
```
db.order.aggregate([
{ $project:{ trade_no:1, all_price:1 } },
{ $match:{"all_price":{$gte:90}} },
{ $sort:{"all_price":-1} }
])
```


## **8、 $limit**
```
db.order.aggregate([
{ $project:{ trade_no:1, all_price:1 } },
{ $match:{"all_price":{$gte:90}} },
{ $sort:{"all_price":-1} },
{ $limit:1 }
])
```

## **9、 $skip**
```
db.order.aggregate([
{ $project:{ trade\_no:1, all_price:1 } },
{ $match:{"all_price":{$gte:90}} },
{ $sort:{"all_price":-1} },
{ $skip:1 }
])
```

## **10、 $lookup 表關聯**
```
db.order.aggregate([
{
$lookup: { from: "order_item", localField: "order_id", foreignField: "order_id", as: "items" }
}
])
{ "_id": ObjectId("5b743d8c2c327f8d1b360540"), "order_id": "1", "uid": 10, "trade_no": "111", "all_price": 100, "all_num": 2, "items": [
{ "_id": ObjectId("5b743d9c2c327f8d1b360543"), "order_id": "1", "title": "商品鼠標 1", "price": 50, "num": 1 },
{ "_id": ObjectId("5b743da12c327f8d1b360544"), "order_id": "1", "title": "商品鍵盤 2", "price": 50, "num": 1 },
{ "_id": ObjectId("5b74f457089f78dc8f0a4f3b"), "order_id": "1", "title": "商品鍵盤 3", "price": 0, "num": 1 }
] }
{ "_id": ObjectId("5b743d902c327f8d1b360541"), "order_id": "2", "uid": 7, "trade_no": "222", "all_price": 90, "all_num": 2, "items": [
{ "_id": ObjectId("5b743da52c327f8d1b360545"), "order_id": "2", "title": "牛奶", "price": 50, "num": 1 },
{ "_id": ObjectId("5b743da92c327f8d1b360546"), "order_id": "2", "title": "酸奶", "price": 40, "num": 1 }
] }
{ "_id": ObjectId("5b743d962c327f8d1b360542"), "order_id": "3", "uid": 9, "trade_no": "333", "all_price": 20, "all_num": 6, "items": [
{ "_id": ObjectId("5b743dad2c327f8d1b360547"), "order_id": "3", "title": "礦泉水", "price": 2, "num": 5 },
{ "_id": ObjectId("5b743dff2c327f8d1b360548"), "order_id": "3", "title": "毛巾", "price": 10, "num": 1 }
] }
db.order.aggregate([
{ $lookup: { from: "order_item", localField: "order_id", foreignField: "order_id", as: "items" } },
{ $match:{"all_price":{$gte:90}} }
])
{ "_id": ObjectId("5b743d8c2c327f8d1b360540"), "order_id": "1", "uid": 10, "trade_no": "111", "all_price": 100, "all_num": 2, "items": [
{ "_id": ObjectId("5b743d9c2c327f8d1b360543"), "order_id": "1", "title": "商品鼠標 1", "price": 50, "num": 1 },
{ "_id": ObjectId("5b743da12c327f8d1b360544"), "order_id": "1", "title": "商品鍵盤 2", "price": 50, "num": 1 },
{ "_id": ObjectId("5b74f457089f78dc8f0a4f3b"), "order_id": "1", "title": "商品鍵盤 3", "price": 0, "num": 1 }
] }
{ "_id": ObjectId("5b743d902c327f8d1b360541"), "order_id": "2", "uid": 7, "trade_no": "222", "all_price": 90, "all_num": 2, "items": [
{ "_id": ObjectId("5b743da52c327f8d1b360545"), "order_id": "2", "title": "牛奶", "price": 50, "num": 1 },
{ "_id": ObjectId("5b743da92c327f8d1b360546"), "order_id": "2", "title": "酸奶", "price": 40, "num": 1 }
] }
db.order.aggregate([
{ $lookup: { from: "order\_item", localField: "order_id", foreignField: "order_id", as: "items" } },
{ $project:{ trade_no:1, all\_price:1,items:1 } },
{ $match:{"all\_price":{$gte:90}} },
{ $sort:{"all_price":-1} },
])
{ "_id": ObjectId("5b743d8c2c327f8d1b360540"), "trade_no": "111", "all_price": 100, "items": [
{ "_id": ObjectId("5b743d9c2c327f8d1b360543"), "order_id": "1", "title": "商品鼠標 1", "price": 50, "num": 1 },
{ "_id": ObjectId("5b743da12c327f8d1b360544"), "order_id": "1", "title": "商品鍵盤 2", "price": 50, "num": 1 },
{ "_id": ObjectId("5b74f457089f78dc8f0a4f3b"), "order_id": "1", "title": "商 品鍵盤 3", "price": 0, "num": 1 }
] }
{ "_id": ObjectId("5b743d902c327f8d1b360541"), "trade_no": "222", "all_price": 90, "items": [
{ "_id": ObjectId("5b743da52c327f8d1b360545"), "order_id": "2", "title": "牛奶", "price": 50, "num": 1 },
{ "_id": ObjectId("5b743da92c327f8d1b360546"), "order_id": "2", "title": "酸奶", "price": 40, "num": 1 }
] }
```

- 前言
- 一、MongoDb 數據庫介紹、安裝、使用
- 二、MongoDB 數據庫創建刪除、表(集合) 創建刪除、數據增刪改查
- 三、MongoDB 索引 和 explain 的使用
- 四、Nodejs 操作 MongoDb 數據庫
- 五、關系型數據庫表(集合)與表(集合)之 間的幾種關系
- 六、MongoDB 的高級查詢 aggregate 聚合管道
- 七、mongoose 入門以及 mongoose 實現數據 的增、刪、改、查
- 八、Mongoose 預定義模式修飾符 Getters 與 Setters 自定義修飾符
- 九、Mongoose 索引、Mongoose 內置 CURD 方 法、擴展 Mongoose Model 的靜態方法和 實例方法
- 十、Mongoose 數據校驗
- 十一、Mongoose 中使用 aggregate 聚合管道
- 十二、Mongoose 中使用 populate 實現關聯查詢
- 十三、MongoDb 數據庫的導出導入