MongoDB的高级查询aggregate聚合管道

一、MongoDB 聚合管道&＃xff08;Aggregation Pipeline&＃xff09;

使用聚合管道可以对集合中的文档进行变换和组合。

实际项目&＃xff1a;表关联查询、数据的统计。

MongoDB 中使用 db.COLLECTION_NAME.aggregate([{},...]) 方法 来构建和使用聚合管道。
先看下官网给的实例&＃xff0c;感受一下聚合管道的用法。

二、MongoDB Aggregation 管道操作符与表达式

SQL 和 NOSQL 对比:

管道表达式: 管道操作符作为“键”,所对应的“值”叫做管道表达式。

例如{KaTeX parse error: Expected &＃39;EOF&＃39;, got &＃39;}&＃39; at position 19: …ch:{status:"A"}}̲&＃xff0c;match 称为管道操作符&＃xff0c;而 status:"A"称为管道表达式&＃xff0c; 是管道操作符的操作数(Operand)。

每个管道表达式是一个文档结构&＃xff0c;它是由字段名、字段值、和一些表达式操作符组成的。

三、 数据模拟

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})


四、 $project

修改文档的结构&＃xff0c;可以用来重命名、增加或删除文档中的字段。

要求查找 order 只返回文档中order_id, trade_no 和 all_price 字段

五、 $match 作用用于过滤文档。

用法类似于 find() 方法中的参数。

六、 $group

将集合中的文档进行分组&＃xff0c;可用于统计结果。

统计每个订单的订单数量&＃xff0c;按照订单号分组

七、 $sort

将集合中的文档进行排序。

八、 $limit

九、 $skip

十、 $lookup 表关联

db.order.aggregate([{$lookup: {from: "order_item",localField: "order_id",foreignField: "order_id",as: "items"}
}])


查询结果&＃xff1a;

// 1
{"_id": ObjectId("606ecfbdbb390000fa004964"),"order_id": "1","uid": 10,"trade_no": "111","all_price": 100,"all_num": 2,"items": [{"_id": ObjectId("606ecfbdbb390000fa004967"),"order_id": "1","title": "商品鼠标 1","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa004968"),"order_id": "1","title": "商品键盘 2","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa004969"),"order_id": "1","title": "商品键盘 3","price": 0,"num": 1}]
}// 2
{"_id": ObjectId("606ecfbdbb390000fa004965"),"order_id": "2","uid": 7,"trade_no": "222","all_price": 90,"all_num": 2,"items": [{"_id": ObjectId("606ecfbdbb390000fa00496a"),"order_id": "2","title": "牛奶","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa00496b"),"order_id": "2","title": "酸奶","price": 40,"num": 1}]
}// 3
{"_id": ObjectId("606ecfbdbb390000fa004966"),"order_id": "3","uid": 9,"trade_no": "333","all_price": 20,"all_num": 6,"items": [{"_id": ObjectId("606ecfbdbb390000fa00496c"),"order_id": "3","title": "矿泉水","price": 2,"num": 5},{"_id": ObjectId("606ecfbdbb390000fa00496d"),"order_id": "3","title": "毛巾","price": 10,"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}
}, ])


查询结果&＃xff1a;

// 1
{"_id": ObjectId("606ecfbdbb390000fa004964"),"trade_no": "111","all_price": 100,"items": [{"_id": ObjectId("606ecfbdbb390000fa004967"),"order_id": "1","title": "商品鼠标 1","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa004968"),"order_id": "1","title": "商品键盘 2","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa004969"),"order_id": "1","title": "商品键盘 3","price": 0,"num": 1}]
}// 2
{"_id": ObjectId("606ecfbdbb390000fa004965"),"trade_no": "222","all_price": 90,"items": [{"_id": ObjectId("606ecfbdbb390000fa00496a"),"order_id": "2","title": "牛奶","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa00496b"),"order_id": "2","title": "酸奶","price": 40,"num": 1}]
}