MongoDB 聚合管道(Aggregation Pipeline)

2022/3/2 19:19:28

本文主要是介绍MongoDB 聚合管道(Aggregation Pipeline),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

MongoDB 聚合管道(Aggregation Pipeline)
使用聚合管道可以对集合中的文档进行 变换 和 组合

实际项目中用途:表关联查询,数据的统计

常用的管道操作符

管道操作                 描述(description)
1.$project           增加,删除,重命名字段
2.$match            条件匹配,只满足条件的文档才能进入下一阶段
3.$limit                限制结果数量(用于基础查询的分页)
4.$skip                跳过文档数量(用于基础查询的分页)
5.$sort                条件排序       (用于基础查询的排序)
6.$group             条件组合结果
7.$lookup            $lookup操作符,用以引入其它集合的数据

1.$project
db.order.aggregate(
[
{
$project:{order_id:1,trade_no:1,all_price:1}
}
]
)
结果 指定字段
{
{"order_id":"1","trade_no":"111","all_price":100},
{"order_id":"1","trade_no":"111","all_price":100},
{"order_id":"1","trade_no":"111","all_price":100}
}
2.$match
db.order.aggregate(
[
{
$project:{trade_no:1,all_price:1}
},
{
$match:{"all_price":{$gte:90}}
}
]
)
结果是 满足条件的指定数据
{
{"trade_no":"111","all_price":100},
{"trade_no":"222","all_price":90}
}

6.$group
db.order_item.aggregate(
[
{
$group:{_id:"$order_id",total:{$sum:"$num"}}
}
]
)
结果是 统计数据

7.$lookup 关联查询
需求:实现下面结果
[
{
Order_id:'1',
Trade_no:' ',
Items:[
{
Title:'鼠标',
Price:20
},
{
Title:'键盘',
Price:20
}
]
},
{
Order_id:'2',
Trade_no:' ',
Items:[
{
Title:'鼠标',
Price:20
},
{
Title:'键盘',
Price:20
}
]
}
]
操作(固定写法
db.order.aggregate([
{
$lookup:
{
from:"order_item", // 关联表
localField:"order_id", // 关联字段 主表(order)关联字段
foreignField:"order_id", // 关联字段 子表(order_item)关联字段
as:"items"
}
}
])
db.order.aggregate([
{
$lookup:
{
from:"order_item",
localField:"order_id",
foreignField:"order_id",
as:"items"
}
},
{
$project:{trade_no:1,all_price:1,all_num:1,items:1} //**** items:1 必须写,不然查出来的关联集合items就丢失了
}
])
结果
{
"_id" : ObjectId("62148fa2393abd107e0ae77e"),
"order_id" : "1",
"uid" : 10,
"trade_no" : "111",
"all_price" : 100,
"all_num" : 2,
"items" : [
{ "_id" : ObjectId("62148fc2393abd107e0ae781"), "order_id" : "1", "title" : "商品鼠标1", "price" : 50, "num" : 1 },
{ "_id" : ObjectId("62148fc8393abd107e0ae782"), "order_id" : "1", "title" : "商品键盘2", "price" : 50, "num" : 1 }
]
}
{
"_id" : ObjectId("62148fad393abd107e0ae77f"),
"order_id" : "2",
"uid" : 7,
"trade_no" : "222",
"all_price" : 90,
"all_num" : 2,
"items" : [
{ "_id" : ObjectId("62148fcd393abd107e0ae783"), "order_id" : "2", "title" : "牛奶", "price" : 50, "num" : 1 },
{ "_id" : ObjectId("62148fd4393abd107e0ae784"), "order_id" : "2", "title" : "酸奶", "price" : 40, "num" : 1 }
]
}
{
"_id" : ObjectId("62148fb5393abd107e0ae780"),
"order_id" : "3",
"uid" : 9,
"trade_no" : "333",
"all_price" : 20,
"all_num" : 6,
"items" : [
{ "_id" : ObjectId("6214905e393abd107e0ae785"), "order_id" : "3", "title" : "矿泉水", "price" : 2, "num" : 5 },
{ "_id" : ObjectId("62149072393abd107e0ae786"), "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("62148fa2393abd107e0ae77e"),
"order_id" : "1",
"uid" : 10,
"trade_no" : "111",
"all_price" : 100,
"all_num" : 2,
"items" : [
{ "_id" : ObjectId("62148fc2393abd107e0ae781"), "order_id" : "1", "title" : "商品鼠标1", "price" : 50, "num" : 1 },
{ "_id" : ObjectId("62148fc8393abd107e0ae782"), "order_id" : "1", "title" : "商品键盘2", "price" : 50, "num" : 1 }
]
}
{
"_id" : ObjectId("62148fad393abd107e0ae77f"),
"order_id" : "2",
"uid" : 7,
"trade_no" : "222",
"all_price" : 90,
"all_num" : 2,
"items" : [
{ "_id" : ObjectId("62148fcd393abd107e0ae783"), "order_id" : "2", "title" : "牛奶", "price" : 50, "num" : 1 },
{ "_id" : ObjectId("62148fd4393abd107e0ae784"), "order_id" : "2", "title" : "酸奶", "price" : 40, "num" : 1 }
]
}
以下是为了上面的操作插入的 order表与order_item表的数据:
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":"2","title":"牛奶","price":50,"num":1})
db.order_item.insert({"order_id":"2","title":"酸奶","price":40,"num":1})

db.order_item.insert({"order_id":"2","title":"矿泉水","price":2,"num":5})
db.order_item.insert({"order_id":"2","title":"毛巾","price":10,"num":1})



这篇关于MongoDB 聚合管道(Aggregation Pipeline)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程