使用聚合管道可以对集合的文档进行变换和组合。
MongoDB 中使用 db.COLLECTION_NAME.aggregate([{<stage>},...])方法来构建和使用聚合管道。
实际项目中，主要用于表关联查询、数据的统计。
管道操作符
-
$project：增加、删除、重命名字段 -
$match：条件匹配。只满足条件的文档才能进入下一阶段。 -
$limit：限制结果的数量。 -
$skip：跳过文档的数量。 -
$sort：条件排序 -
$group：条件组合结果。 -
$lookup：用于引入其它集合的数据
数据模拟
> db.order_item.find()
{ "_id" : ObjectId("5fc7103218fa5641d31317f9"), "order_id" : "1", "title" : "商品1", "price" : "50", "num" : 1 }
{ "_id" : ObjectId("5fc7104318fa5641d31317fa"), "order_id" : "1", "title" : "商品键盘", "price" : "100", "num" : 1 }
{ "_id" : ObjectId("5fc7106018fa5641d31317fb"), "order_id" : "2", "title" : "牛奶", "price" : "50", "num" : 1 }
{ "_id" : ObjectId("5fc7107718fa5641d31317fc"), "order_id" : "2", "title" : "酸奶", "price" : "50", "num" : 1 }
{ "_id" : ObjectId("5fc7108d18fa5641d31317fd"), "order_id" : "3", "title" : "零食1", "price" : "20", "num" : 1 }
{ "_id" : ObjectId("5fc710a718fa5641d31317fe"), "order_id" : "3", "title" : "方便面", "price" : "50", "num" : 5 }
> db.order.find()
{ "_id" : ObjectId("5fc71569212c818aed7b30ca"), "order_id" : "1", "trade_no" : "111", "all_price" : 100 }
{ "_id" : ObjectId("5fc71578212c818aed7b30cb"), "order_id" : "1", "trade_no" : "112", "all_price" : 108 }
{ "_id" : ObjectId("5fc7158a212c818aed7b30cc"), "order_id" : "2", "trade_no" : "221", "all_price" : 200 }
{ "_id" : ObjectId("5fc71594212c818aed7b30cd"), "order_id" : "2", "trade_no" : "222", "all_price" : 80 }
{ "_id" : ObjectId("5fc715a4212c818aed7b30ce"), "order_id" : "3", "trade_no" : "332", "all_price" : 50 }
{ "_id" : ObjectId("5fc715ac212c818aed7b30cf"), "order_id" : "3", "trade_no" : "333", "all_price" : 60 }
$project
修改文档的结构，可以用来重命名、增加或删除文档中的字段。
查找order 只返回文档中order_id和all_price 字段
db.order.aggregate([
{
$project:{order_id:1,all_price:1}
}
])
$match
用于过滤文档，用法类似于find() 方法中的参数
查找order里order_id和all_price 字段并且 all_price大于等于 80 的数据
db.order.aggregate([
{
$project:{order_id:1,all_price:1}
},
{
$match:{"all_price":{$gte:80}}
}
])
$group
将集合中的文档进行分组，可用于统计结果。
统计每个订单的订单数量，按照订单号分组
db.order_item.aggregate([{
{
$group:{
_id:"$order_id",
total:{$sum:"$num"}
}
}
])
// ->
{ "_id" : "2", "total" : 2 }
{ "_id" : "3", "total" : 6 }
{ "_id" : "1", "total" : 2 }
$limit 和 $skip
$limit 和 $skip 用于限制和跳过相应的文档，与find 中 limit 与skip 类似。
取出order 里面的第三条和第四条数据
db.order.aggregate([{$skip:2},{$limit:2}])
// ->
{ "_id" : ObjectId("5fc7158a212c818aed7b30cc"), "order_id" : "2", "trade_no" : "221", "all_price" : 200 }
{ "_id" : ObjectId("5fc71594212c818aed7b30cd"), "order_id" : "2", "trade_no" : "222", "all_price" : 80 }
$sort
$sort 用于对数据进行排序，1代表升序，-1代表降序。
order 里all_price 字段进行升序排列
db.order.aggregate([{$sort:{all_price:1}}])
// ->
{ "_id" : ObjectId("5fc715a4212c818aed7b30ce"), "order_id" : "3", "trade_no" : "332", "all_price" : 50 }
{ "_id" : ObjectId("5fc715ac212c818aed7b30cf"), "order_id" : "3", "trade_no" : "333", "all_price" : 60 }
{ "_id" : ObjectId("5fc71594212c818aed7b30cd"), "order_id" : "2", "trade_no" : "222", "all_price" : 80 }
{ "_id" : ObjectId("5fc71569212c818aed7b30ca"), "order_id" : "1", "trade_no" : "111", "all_price" : 100 }
{ "_id" : ObjectId("5fc71578212c818aed7b30cb"), "order_id" : "1", "trade_no" : "112", "all_price" : 108 }
{ "_id" : ObjectId("5fc7158a212c818aed7b30cc"), "order_id" : "2", "trade_no" : "221", "all_price" : 200 }
$lookup
$lookup :用于表关联查询
order 关联 order_item 查询，放入 items 中
db.order.aggregate([
{
$lookup:{
from:"order_item",
localField:"order_id",
foreignField:"order_id",
as:"items"
}
}
])
from: 数据库中指定要执行连接的集合。
localField：指定当前文档的字段。$lookup从from集合的文档中对localField和foreignField执行相等匹配。如果当前文档不包含localField，则$lookup会将字段视为具有null的值以进行匹配。
foreignField：指定from集合中文档的字段。$lookup从from集合的文档中对localField和foreignField执行相等匹配。如果from集合中的文档不包含foreignField，则$lookup会将值视为null以便进行匹配。
as：指定生成新数组字段的名称。新的数组字段包含来自from集合的匹配文档。
// 查询出来的数据
{
"_id": ObjectId("5fc71569212c818aed7b30ca"),
"order_id": "1",
"trade_no": "111",
"all_price": 100,
"items": [{
"_id": ObjectId("5fc7103218fa5641d31317f9"),
"order_id": "1",
"title": "商品1",
"price": "50",
"num": 1
}, {
"_id": ObjectId("5fc7104318fa5641d31317fa"),
"order_id": "1",
"title": "商品键盘",
"price": "100",
"num": 1
}]
}
{
"_id": ObjectId("5fc71578212c818aed7b30cb"),
"order_id": "1",
"trade_no": "112",
"all_price": 108,
"items": [{
"_id": ObjectId("5fc7103218fa5641d31317f9"),
"order_id": "1",
"title": "商品1",
"price": "50",
"num": 1
}, {
"_id": ObjectId("5fc7104318fa5641d31317fa"),
"order_id": "1",
"title": "商品键盘",
"price": "100",
"num": 1
}]
}
{
"_id": ObjectId("5fc7158a212c818aed7b30cc"),
"order_id": "2",
"trade_no": "221",
"all_price": 200,
"items": [{
"_id": ObjectId("5fc7106018fa5641d31317fb"),
"order_id": "2",
"title": "牛奶",
"price": "50",
"num": 1
}, {
"_id": ObjectId("5fc7107718fa5641d31317fc"),
"order_id": "2",
"title": "酸奶",
"price": "50",
"num": 1
}]
}
{
"_id": ObjectId("5fc71594212c818aed7b30cd"),
"order_id": "2",
"trade_no": "222",
"all_price": 80,
"items": [{
"_id": ObjectId("5fc7106018fa5641d31317fb"),
"order_id": "2",
"title": "牛奶",
"price": "50",
"num": 1
}, {
"_id": O
bjectId("5fc7107718fa5641d31317fc"),
"order_id": "2",
"title": "酸奶",
"price": "50",
"num": 1
}]
}
{
"_id": ObjectId("5fc715a4212c818aed7b30ce"),
"order_id": "3",
"trade_no": "332",
"all_price": 50,
"items": [{
"_id": ObjectId("5fc7108d18fa5641d31317fd"),
"order_id": "3",
"title": "零食1",
"price": "20",
"num": 1
}, {
"_id": ObjectId("5fc710a718fa5641d31317fe"),
"order_id": "3",
"title": "方便面",
"price": "50",
"num": 5
}]
}
{
"_id": ObjectId("5fc715ac212c818aed7b30cf"),
"order_id": "3",
"trade_no": "333",
"all_price": 60,
"items": [{
"_id": ObjectId("5fc7108d18fa5641d31317fd"),
"order_id": "3",
"title": "零食1",
"price": "20",
"num": 1
}, {
"_id": ObjectId("5fc710a718fa5641d31317fe"),
"order_id": "3",
"title": "方便面",
"price": "50",
"num": 5
}]
}
网友评论