MongoDB聚合操作
目录
聚合操作
插入一匹数据：
db.orders.insertMany(
[
{
zip:"000001",
phone:"13101010101",
name:"xiaowang",
status:"created",
shippingFee:10,
orderLines:[
{product:"Huawei Meta30 Pro",sku:"2002",qty:100,price:6000,cost:5599},
{product:"Huawei Meta40 Pro",sku:"2003",qty:10,price:7000,cost:6599},
{product:"Huawei Meta40 5G",sku:"2004",qty:80,price:4000,cost:3700}
]
},
{
zip:"000002",
phone:"13101010102",
name:"xiaoli",
status:"created",
shippingFee:10,
orderLines:[
{product:"Huawei Meta30 Pro",sku:"2002",qty:100,price:5666,cost:3434},
{product:"Huawei Meta40 Pro",sku:"2003",qty:10,price:7888,cost:5666},
{product:"Huawei Meta40 5G",sku:"2004",qty:80,price:5667,cost:2345}
]
},
{
zip:"000003",
phone:"13101010103",
name:"xiaomei",
status:"created",
shippingFee:10,
orderLines:[
{product:"Huawei Meta30 Pro",sku:"2002",qty:100,price:7000,cost:3456},
{product:"Huawei Meta40 Pro",sku:"2003",qty:10,price:7896,cost:7866},
{product:"Huawei Meta40 5G",sku:"2004",qty:80,price:4688,cost:4000}
]
}]
);
添加两个字段，值为每个订单的原价总价和 订单总额
mongos> db.orders.aggregate([{$addFields: { totalPrice:{ $sum: "$orderLines.price"}, totalCost: { $sum: "$orderLines.cost"}, }},{$sort:{totalPrice:-1}}]).pretty();
{
"_id" : ObjectId("628460e9a12629c0c09689ec"),
"zip" : "000003",
"phone" : "13101010103",
"name" : "xiaomei",
"status" : "created",
"shippingFee" : 10,
"orderLines" : [
{
"product" : "Huawei Meta30 Pro",
"sku" : "2002",
"qty" : 100,
"price" : 7000,
"cost" : 3456
},
{
"product" : "Huawei Meta40 Pro",
"sku" : "2003",
"qty" : 10,
"price" : 7896,
"cost" : 7866
},
{
"product" : "Huawei Meta40 5G",
"sku" : "2004",
"qty" : 80,
"price" : 4688,
"cost" : 4000
}
],
"totalPrice" : 19584,
"totalCost" : 15322
}
{
"_id" : ObjectId("628460e9a12629c0c09689eb"),
"zip" : "000002",
"phone" : "13101010102",
"name" : "xiaoli",
"status" : "created",
"shippingFee" : 10,
"orderLines" : [
{
"product" : "Huawei Meta30 Pro",
"sku" : "2002",
"qty" : 100,
"price" : 5666,
"cost" : 3434
},
{
"product" : "Huawei Meta40 Pro",
"sku" : "2003",
"qty" : 10,
"price" : 7888,
"cost" : 5666
},
{
"product" : "Huawei Meta40 5G",
"sku" : "2004",
"qty" : 80,
"price" : 5667,
"cost" : 2345
}
],
"totalPrice" : 19221,
"totalCost" : 11445
}
{
"_id" : ObjectId("628460e9a12629c0c09689ea"),
"zip" : "000001",
"phone" : "13101010101",
"name" : "xiaowang",
"status" : "created",
"shippingFee" : 10,
"orderLines" : [
{
"product" : "Huawei Meta30 Pro",
"sku" : "2002",
"qty" : 100,
"price" : 6000,
"cost" : 5599
},
{
"product" : "Huawei Meta40 Pro",
"sku" : "2003",
"qty" : 10,
"price" : 7000,
"cost" : 6599
},
{
"product" : "Huawei Meta40 5G",
"sku" : "2004",
"qty" : 80,
"price" : 4000,
"cost" : 3700
}
],
"totalPrice" : 17000,
"totalCost" : 15898
}第一个阶段：
针对整个集合添加两个字段：
totalPrice: 订单原价总额
totalCost：订单实际总额
并将结果传到第二个阶段
第二个阶段：
按订单总价进行排序
聚合管道操作符
$project 对输入文档进行再次投影
$match 对输入文档进行筛选
$limit 筛选出管道内前 N 篇文档
$skip 跳过管道内前N篇文档
$unwind 展开输入文档中的数组字段
$sort 对输入文档进行排序
$lookup 对输入文档进行查询操作
$group 对输入文档进行分组
$out 对管道中的文档输出
$project ： 投影操作， 将原始字段投影成指定名称， 如将 集合中的 name投影成 userName
db.collection.aggregate({$project:{ 重命名rename:"$filed"}});$project 可以灵活控制输出文档的格式，也可以剔除不需要的字段>
mongos> db.orders.aggregate({$project:{userName:"$name","_id":0,"zip":1,"orderLines.product":1}});
{ "zip" : "000001", "orderLines" : [ { "product" : "Huawei Meta30 Pro" }, { "product" : "Huawei Meta40 Pro" }, { "product" : "Huawei Meta40 5G" } ], "userName" : "xiaowang" }
{ "zip" : "000003", "orderLines" : [ { "product" : "Huawei Meta30 Pro" }, { "product" : "Huawei Meta40 Pro" }, { "product" : "Huawei Meta40 5G" } ], "userName" : "xiaomei" }
{ "zip" : "000002", "orderLines" : [ { "product" : "Huawei Meta30 Pro" }, { "product" : "Huawei Meta40 Pro" }, { "product" : "Huawei Meta40 5G" } ], "userName" : "xiaoli" }
$match
$match 进行文档筛选
>
{ "_id" : ObjectId("62810333da0a567116cba9f0"), "zip" : "000001", "phone" : "13101010101", "name" : "xiaowang", "status" : "created", "shippingFee" : 10, "orderLines" : [ { "product" : "Huawei Meta30 Pro", "sku" : "2002", "qty" : 100, "price" : 6000, "cost" : 5599 }, { "product" : "Huawei Meta40 Pro", "sku" : "2003", "qty" : 10, "price" : 7000, "cost" : 6599 }, { "product" : "Huawei Meta40 5G", "sku" : "2004", "qty" : 80, "price" : 4000, "cost" : 3700 } ] }
mongos> db.orders.aggregate({$match:{"name":"xiaowang"}}).pretty();
{
"_id" : ObjectId("628460e9a12629c0c09689ea"),
"zip" : "000001",
"phone" : "13101010101",
"name" : "xiaowang",
"status" : "created",
"shippingFee" : 10,
"orderLines" : [
{
"product" : "Huawei Meta30 Pro",
"sku" : "2002",
"qty" : 100,
"price" : 6000,
"cost" : 5599
},
{
"product" : "Huawei Meta40 Pro",
"sku" : "2003",
"qty" : 10,
"price" : 7000,
"cost" : 6599
},
{
"product" : "Huawei Meta40 5G",
"sku" : "2004",
"qty" : 80,
"price" : 4000,
"cost" : 3700
}
]
}
注意：筛选管道操作和其他管道操作配合时候时，尽量放到开始阶段，这样可以减少后续管道操作符要操作的文档数，提升效率
将筛选 和 投影结合使用
mongos> db.orders.aggregate({$match:{"name":"xiaowang"}},{$project:{"_id":0,"phone":1,"name":1}}).pretty();
{ "phone" : "13101010101", "name" : "xiaowang" }
> db.orders.aggregate({$match:{$and:[{"orderLines.price":{$gt:0}},{"name":"xiaowang"}]}}).pretty();
{
"_id" : ObjectId("62810333da0a567116cba9f0"),
"zip" : "000001",
"phone" : "13101010101",
"name" : "xiaowang",
"status" : "created",
"shippingFee" : 10,
"orderLines" : [
{
"product" : "Huawei Meta30 Pro",
"sku" : "2002",
"qty" : 100,
"price" : 6000,
"cost" : 5599
},
{
"product" : "Huawei Meta40 Pro",
"sku" : "2003",
"qty" : 10,
"price" : 7000,
"cost" : 6599
},
{
"product" : "Huawei Meta40 5G",
"sku" : "2004",
"qty" : 80,
"price" : 4000,
"cost" : 3700
}
]
}
$unwind
将数组打平
db.userInfo.insertOne(
{ "nickName" : "xixi",
"age" : 35,
"tags" : ["80","IT","BeiJing"]
}
);
mongos> db.orders.aggregate({$unwind:{path:"$orderLines"}}).pretty()
{
"_id" : ObjectId("628460e9a12629c0c09689ea"),
"zip" : "000001",
"phone" : "13101010101",
"name" : "xiaowang",
"status" : "created",
"shippingFee" : 10,
"orderLines" : {
"product" : "Huawei Meta30 Pro",
"sku" : "2002",
"qty" : 100,
"price" : 6000,
"cost" : 5599
}
}
{
"_id" : ObjectId("628460e9a12629c0c09689ea"),
"zip" : "000001",
"phone" : "13101010101",
"name" : "xiaowang",
"status" : "created",
"shippingFee" : 10,
"orderLines" : {
"product" : "Huawei Meta40 Pro",
"sku" : "2003",
"qty" : 10,
"price" : 7000,
"cost" : 6599
}
}
{
"_id" : ObjectId("628460e9a12629c0c09689ea"),
"zip" : "000001",
"phone" : "13101010101",
"name" : "xiaowang",
"status" : "created",
"shippingFee" : 10,
"orderLines" : {
"product" : "Huawei Meta40 5G",
"sku" : "2004",
"qty" : 80,
"price" : 4000,
"cost" : 3700
}
}
{
"_id" : ObjectId("628460e9a12629c0c09689ec"),
"zip" : "000003",
"phone" : "13101010103",
"name" : "xiaomei",
"status" : "created",
"shippingFee" : 10,
"orderLines" : {
"product" : "Huawei Meta30 Pro",
"sku" : "2002",
"qty" : 100,
"price" : 7000,
"cost" : 3456
}
}
{
"_id" : ObjectId("628460e9a12629c0c09689ec"),
"zip" : "000003",
"phone" : "13101010103",
"name" : "xiaomei",
"status" : "created",
"shippingFee" : 10,
"orderLines" : {
"product" : "Huawei Meta40 Pro",
"sku" : "2003",
"qty" : 10,
"price" : 7896,
"cost" : 7866
}
}
{
"_id" : ObjectId("628460e9a12629c0c09689ec"),
"zip" : "000003",
"phone" : "13101010103",
"name" : "xiaomei",
"status" : "created",
"shippingFee" : 10,
"orderLines" : {
"product" : "Huawei Meta40 5G",
"sku" : "2004",
"qty" : 80,
"price" : 4688,
"cost" : 4000
}
}
{
"_id" : ObjectId("628460e9a12629c0c09689eb"),
"zip" : "000002",
"phone" : "13101010102",
"name" : "xiaoli",
"status" : "created",
"shippingFee" : 10,
"orderLines" : {
"product" : "Huawei Meta30 Pro",
"sku" : "2002",
"qty" : 100,
"price" : 5666,
"cost" : 3434
}
}
{
"_id" : ObjectId("628460e9a12629c0c09689eb"),
"zip" : "000002",
"phone" : "13101010102",
"name" : "xiaoli",
"status" : "created",
"shippingFee" : 10,
"orderLines" : {
"product" : "Huawei Meta40 Pro",
"sku" : "2003",
"qty" : 10,
"price" : 7888,
"cost" : 5666
}
}
{
"_id" : ObjectId("628460e9a12629c0c09689eb"),
"zip" : "000002",
"phone" : "13101010102",
"name" : "xiaoli",
"status" : "created",
"shippingFee" : 10,
"orderLines" : {
"product" : "Huawei Meta40 5G",
"sku" : "2004",
"qty" : 80,
"price" : 5667,
"cost" : 2345
}
}includeArrayIndex: 加上数组元素的索引值， 赋值给后面指定的字段
> db.userInfo.aggregate(
... {$unwind: {path: "$tags",includeArrayIndex:"arrIndex"}}
... );
{ "_id" : ObjectId("62810c05b2a6ca89761d3cd2"), "nickName" : "xixi", "age" : 35, "tags" : "80", "arrIndex" : NumberLong(0) }
{ "_id" : ObjectId("62810c05b2a6ca89761d3cd2"), "nickName" : "xixi", "age" : 35, "tags" : "IT", "arrIndex" : NumberLong(1) }
{ "_id" : ObjectId("62810c05b2a6ca89761d3cd2"), "nickName" : "xixi", "age" : 35, "tags" : "BeiJing", "arrIndex" : NumberLong(2) }
preserveNullAndEmptyArrays:true
展开时保留空数组，或者不存在数组字段的文档
db.userInfo.aggregate(
{ $unwind:
{
path: "$tags",
includeArrayIndex:"arrIndex",
preserveNullAndEmptyArrays:true}
}
);
$lookup
使用单一字段值进行查询
$lookup:{
from: 需要关联的文档,
localField: 本地字段，
foreignField: 外部文档关联字段，
as 作为新的字段，添加到文档中
}
db.account.insertMany(
[
{_id:1,name:"zhangsan",age:19},
{_id:2,name:"lisi",age:20}
]
);db.accountDetail.insertMany(
[
{aid:1,address:["address1","address2"]}
]
);> db.accountDetail.aggregate(
... {
... $lookup:
... {
... from:"account",
... localField:"aid",
... foreignField:"_id",
... as: "field1"
... }
... }
... );
{ "_id" : ObjectId("62810ccbb2a6ca89761d3cd3"), "aid" : 1, "address" : [ "address1", "address2" ], "field1" : [ { "_id" : 1, "name" : "zhangsan", "age" : 19 } ] }
$group 对某些字段分组
$group:{
_id: 对哪个字段进行分组，
field1:{ accumulator1: expression1 }
}group 聚合操作默认不会对输出结果进行排序
对于group ，聚合操作主要有以下几种
$addToSet ：将分组中的元素添加到一个数组中，并且自动去重
$avg 返回分组中的平均值， 非数值直接忽略
$first 返回分组中的第一个元素
$last 返回分组中的最后一个元素
$max 返回分组中的最大元素
$min 回分组中的最小元素
$push 创建新的数组，将值添加进去
$sum 求分组数值元素和
构造数据：
db.sales.insertMany(
[
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") },
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") },
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") },
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") },
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:12:00Z") },
{ "_id" : 6, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:12:00Z") }
]
);
$addToSet
查看每天，卖出哪几种商品项目
按每天分组， 将商品加入到去重数组中
> db.sales.aggregate(
... [
... {
... $group:
... {
... _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
... itemsSold: { $addToSet: "$item" }
... }
... }
... ]
... )
{ "_id" : { "day" : 1, "year" : 2014 }, "itemsSold" : [ "abc" ] }
{ "_id" : { "day" : 46, "year" : 2014 }, "itemsSold" : [ "xyz", "abc" ] }
{ "_id" : { "day" : 34, "year" : 2014 }, "itemsSold" : [ "xyz", "jkl" ] }
$avg: 求数值的平均值
> db.sales.aggregate(
... [
... {
... $group:
... {
... _id: "$item",
... avgAmount: { $avg: { $multiply: [ "$price", "$quantity" ] } },
... avgQuantity: { $avg: "$quantity" }
... }
... }
... ]
... )
{ "_id" : "xyz", "avgAmount" : 41.666666666666664, "avgQuantity" : 8.333333333333334 }
{ "_id" : "abc", "avgAmount" : 60, "avgQuantity" : 6 }
{ "_id" : "jkl", "avgAmount" : 20, "avgQuantity" : 1 }
$push
创建新的数组，存储，每个分组中元素的信息
> db.sales.aggregate(
... [
... {
... $group:
... {
... _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
... itemsSold: { $push: { item: "$item", quantity: "$quantity" } }
... }
... }
... ]
... )
{ "_id" : { "day" : 1, "year" : 2014 }, "itemsSold" : [ { "item" : "abc", "quantity" : 2 } ] }
{ "_id" : { "day" : 46, "year" : 2014 }, "itemsSold" : [ { "item" : "abc", "quantity" : 10 }, { "item" : "xyz", "quantity" : 10 }, { "item" : "xyz", "quantity" : 10 } ] }
{ "_id" : { "day" : 34, "year" : 2014 }, "itemsSold" : [ { "item" : "jkl", "quantity" : 1 }, { "item" : "xyz", "quantity" : 5 } ] }
group 阶段有 100m内存的使用限制， 默认情况下，如果超过这个限制会直接返回 error，
可以通过设置 allowDiskUse 为 true 来避免异常， allowDiskUse 为 true 将利用临时文件来辅助实现group操作。
$out
将聚合结果写入另一个文档
> db.sales.aggregate(
... [
... {
... $group:
... {
... _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
... itemsSold: { $push: { item: "$item", quantity: "$quantity" } }
... }
... },
... { $out:"output"}
... ]
... )
> db.output.find()
{ "_id" : { "day" : 34, "year" : 2014 }, "itemsSold" : [ { "item" : "jkl", "quantity" : 1 }, { "item" : "xyz", "quantity" : 5 } ] }
{ "_id" : { "day" : 46, "year" : 2014 }, "itemsSold" : [ { "item" : "abc", "quantity" : 10 }, { "item" : "xyz", "quantity" : 10 }, { "item" : "xyz", "quantity" : 10 } ] }
{ "_id" : { "day" : 1, "year" : 2014 }, "itemsSold" : [ { "item" : "abc", "quantity" : 2 } ] }
更多聚合操作参考： Aggregation Pipeline Stages — MongoDB Manual
管道优化
1. 投影优化
聚合管道可以确定它是否仅需要文档中的字段的子集来获得结果。如果是这样，管道将只使用那些必需的字段，减少通过管道的数据量。
2. 管道符号执行顺序优化
对于包含投影阶段($project或$unset或$addFields或$set)后跟$match阶段的聚合管道，MongoDB 将$match阶段中不需要在投影阶段计算的值的任何过滤器移动到投影前的新$match阶段
3. $sort + $match
如果序列中带有$sort后跟$match，则$match会移动到$sort之前，以最大程度的减少要排序的对象的数量
4. $project/ $unset + $skip序列优化
当有一个$project或$unset之后跟有$skip序列时，$skip 会移至$project之前。
5.$limit+ $limit合并
当$limit紧接着另一个时 $limit，两个阶段可以合并为一个阶段 $limit，其中限制量为两个初始限制量中的较小者。
6. skip+ $skip 合并
当$skip紧跟另一个$skip，这两个阶段可合并成一个单一的$skip，其中跳过量为总和的两个初始跳过量。
7. $match+ $match合并
当一个$match紧随另一个紧随其后时 $match，这两个阶段可以合并为一个单独 $match的条件 $and
{ $match: { year: 2014 } },
{ $match: { status: "A" } }
优化后
{ $match: { $and: [ { "year" : 2014 }, { "status" : "A" } ] } }
使用explain命令查看执行计划
> db.orders.explain().aggregate({$match:{$and:[{"orderLines.price":{$gt:0}},{"name":"xiaowang"}]}})
{
"explainVersion" : "1",
"queryPlanner" : {
"namespace" : "orders.orders",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"name" : {
"$eq" : "xiaowang"
}
},
{
"orderLines.price" : {
"$gt" : 0
}
}
]
},
"queryHash" : "FCAA6906",
"planCacheKey" : "9306620D",
"optimizedPipeline" : true,
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"name" : {
"$eq" : "xiaowang"
}
},
{
"orderLines.price" : {
"$gt" : 0
}
}
]
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"command" : {
"aggregate" : "orders",
"pipeline" : [
{
"$match" : {
"$and" : [
{
"orderLines.price" : {
"$gt" : 0
}
},
{
"name" : "xiaowang"
}
]
}
}
],
"explain" : true,
"cursor" : {},
"lsid" : {
"id" : UUID("c8c00fb9-faf1-438c-9ea3-8048904beb63")
},
"$db" : "orders"
},
"serverInfo" : {
"host" : "87edec9e36d5",
"port" : 27017,
"version" : "5.0.5",
"gitVersion" : "d65fd89df3fc039b5c55933c0f71d647a54510ae"
},
"serverParameters" : {
"internalQueryFacetBufferSizeBytes" : 104857600,
"internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
"internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
"internalQueryProhibitBlockingMergeOnMongoS" : 0,
"internalQueryMaxAddToSetBytes" : 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600
},
"ok" : 1
}
相关文章
- MongoDB管理: 如何让mongod产生core文件？
- 【mongodb系统学习之八】mongodb shell常用操作
- 【Mongodb】如何创建mongodb的replica set
- Redis、Memcache和MongoDB的区别
- 用 mongodb 储存多态消息/提醒类数据（转）
- 在scrapy中使用mongodb管道
- mongodb基础整理篇————常规操作[二]
- java mongodb 基础系列---查询，排序，limit，$in,$or,输出为list,创建索引,$ne 非操作
- mac下mongodb安装和python操作mongodb
- redis和mongodb
- python操作mongodb
- mongodb基础整理篇————聚合操作[三]
- Linux学习：CentOS 7 安装mongoDB
- MongoDB基本文件操作
- [转]MongoDB更新操作replaceOne()实例讲解
- 【MongoDB】Serveral common command of MongoDb
- 【MongoDB】在windows平台mongodb切片集群（三）
- MongoDB存储引擎选择
- NoSQL四种——kv存储（memcache，Riak），列存储（Cassandra，Hbase），文档类（mongoDB，CouchDB），图数据库（neo4j）
- 第一部分 mongodb 基础篇
- python操作MongoDB
- MongoDB技术全系列
- 【Python】MongoDB数据库安装和Pymongo操作学习
- Mongodb数据库的安装部署及基本使用
- Mongodb的安装部署