一、MongoDB 聚合管道(Aggregation Pipeline)
使用聚合管道可以对集合中的文档进行变换和组合。
实际项目:表关联查询、数据的统计。
MongoDB 中使用 db.COLLECTION_NAME.aggregate([{<stage>},...])
方法 来构建和使用聚合管道。
先看下官网给的实例,感受一下聚合管道的用法。
二、MongoDB Aggregation 管道操作符与表达式
SQL 和 NOSQL 对比:
管道表达式: 管道操作符作为“键”,所对应的“值”叫做管道表达式。
例如{KaTeX parse error: Expected 'EOF', got '}' at position 19: …ch:{status:"A"}}̲,match 称为管道操作符,而 status:"A"称为管道表达式, 是管道操作符的操作数(Operand)。
每个管道表达式是一个文档结构,它是由字段名、字段值、和一些表达式操作符组成的。
三、 数据模拟
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
修改文档的结构,可以用来重命名、增加或删除文档中的字段。
要求查找 order 只返回文档中order_id, trade_no 和 all_price 字段
五、 $match 作用用于过滤文档。
用法类似于 find() 方法中的参数。
六、 $group
将集合中的文档进行分组,可用于统计结果。
统计每个订单的订单数量,按照订单号分组
七、 $sort
将集合中的文档进行排序。
八、 $limit
九、 $skip
十、 $lookup 表关联
db.order.aggregate([{
$lookup: {
from: "order_item",
localField: "order_id",
foreignField: "order_id",
as: "items"
}
}])
查询结果:
// 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
}
}, ])
查询结果:
// 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
}
]
}