-
通过实例讲清楚MongoDB九种聚合操作
欢迎大家关注公众号「JAVA前线」查看更多精彩分享文章，主要包括源码分析、实际应用、架构思维、职场分享、产品思考等等，同时欢迎大家加我个人微信「java_front」一起交流学习
1 文章概述
1.1 基本概念
MongoDB是一种非关系型数据库，数据最终存储为BSON（Binary JSON）类型。MongoDB包含三个重要逻辑概念：数据库、集合和文档，与关系型数据库相关概念映射如下图：
1.2 数据准备
1.2.1 逻辑设计
1.2.2 新增数据
// 创建数据库 use bookdb // 新增图书馆 db.library.insertMany([ { "_id": 1, "library_name": "图书馆_1" }, { "_id": 2, "library_name": "图书馆_2" }, { "_id": 3, "library_name": "图书馆_3" } ]) // 新增图书 db.book.insertMany([ { "_id": 1, "book_type": 1, "book_name": "图书_1", "book_price": 10, "book_count": 100, "borrowers": [{ "name": "张三", "id": 100 }, { "name": "李四", "id": 200 }], "library_id": 1 }, { "_id": 2, "book_type": 2, "book_name": "图书_2", "book_price": 20, "book_count": 100, "borrowers": [{ "name": "张三", "id": 100 }], "library_id": 2 }, { "_id": 3, "book_type": 1, "book_name": "图书_3", "book_price": 30, "book_count": 100, "borrowers": [{ "name": "张三", "id": 100 }, { "name": "王五", "id": 300 }], "library_id": 2 } ])- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
2 聚合概念
2.1 基本语法
MongoDB聚合语法第一个参数是管道：文档在上个管道处理完后传递给下个管道，第二个参数是选项：设置聚合操作特性
db.collection.aggregate(pipeline, options)- 1
2.2 聚合管道
常用聚合管道有以下九种类型：
$project：对文档进行投影
$limit：出管道内前N个文档
$skip：跳过管道内前N个文档
$sort：对文档进行排序
$out：输出管道中文档
$match：对文档进行筛选
$unwind：铺平文档中的数组字段
$lookup：对文档进行查询
$group：对文档进行分组
3 聚合实例
3.1 project
1表示展示某字段
0表示不展示某字段
借阅人编号和姓名拆分成两个数组
db.book.aggregate({ $project: { "_id": 0, "book_name": 1, "borrowerIds": "$borrowers.id", "borrowerNames": "$borrowers.name" } }) --------------------------------------------------------- // 1 { "book_name": "图书_1", "borrowerIds": [ 100, 200 ], "borrowerNames": [ "张三", "李四" ] } // 2 { "book_name": "图书_2", "borrowerIds": [ 100 ], "borrowerNames": [ "张三" ] } // 3 { "book_name": "图书_3", "borrowerIds": [ 100, 300 ], "borrowerNames": [ "张三", "王五" ] }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
3.2 limit
只展示一个投影结果
db.book.aggregate([ { $project: { "_id": 0, "book_name": 1, "borrowerIds": "$borrowers.id", "borrowerNames": "$borrowers.name" } }, { $limit: 1 } ]) --------------------------------------------------------- // 1 { "book_name": "图书_1", "borrowerIds": [ 100, 200 ], "borrowerNames": [ "张三", "李四" ] }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
3.3 skip
跳过一个且只展示一个投影结果
db.book.aggregate([ { $project: { "_id": 0, "book_name": 1, "borrowerIds": "$borrowers.id", "borrowerNames": "$borrowers.name" } }, { $skip: 1 }, { $limit: 1 } ]) --------------------------------------------------------- // 1 { "book_name": "图书_2", "borrowerIds": [ 100 ], "borrowerNames": [ "张三" ] }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
3.4 sort
db.book.aggregate([ { $project: { "_id": 1, "book_name": 1, "library_id": 1 } }, { $sort: { "library_id": - 1, // 降序 "_id": 1 // 升序 } } ]) --------------------------------------------------------- // 1 { "_id": 2, "book_name": "图书_2", "library_id": 2 } // 2 { "_id": 3, "book_name": "图书_3", "library_id": 2 } // 3 { "_id": 1, "book_name": "图书_1", "library_id": 1 }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
MongoDB内存排序有100M限制，如果排序数据过多需要设置选项
allowDiskUse=true，表示数据可以写入临时文件进行排序db.book.aggregate([ { $project: { "_id": 1, "book_name": 1, "library_id": 1 } }, { $sort: { "library_id": - 1, "_id": 1 } } ], { allowDiskUse: true })- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
3.5 out
投影结果输出到新集合
db.book.aggregate([ { $project: { "_id": 0, "book_name": 1, "borrowerIds": "$borrowers.id", "borrowerNames": "$borrowers.name" } }, { $out: "newCollection" } ]) db.newCollection.find() --------------------------------------------------------- // 1 { "_id": ObjectId("62bec0636f9c37787b9590b9"), "book_name": "图书_1", "borrowerIds": [ 100, 200 ], "borrowerNames": [ "张三", "李四" ] } // 2 { "_id": ObjectId("62bec0636f9c37787b9590ba"), "book_name": "图书_2", "borrowerIds": [ 100 ], "borrowerNames": [ "张三" ] } // 3 { "_id": ObjectId("62bec0636f9c37787b9590bb"), "book_name": "图书_3", "borrowerIds": [ 100, 300 ], "borrowerNames": [ "张三", "王五" ] }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
3.6 match
where book_name = xxx
db.book.aggregate([ { $match: { "book_name": "图书_2" } }, { $project: { "_id": 1, "book_name": 1, "library_id": 1 } } ]) --------------------------------------------------------- // 1 { "_id": 2, "book_name": "图书_2", "library_id": 2 }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
where library_id = 2 and price > 15
db.book.aggregate([ { $match: { $and: [ { "library_id": 2 }, { "book_price": { $gt: 25 } } ] } }, { $project: { "_id": 1, "book_name": 1, "library_id": 1, "book_price": 1 } } ]) --------------------------------------------------------- // 1 { "_id": 3, "book_name": "图书_3", "book_price": 30, "library_id": 2 }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
3.7 unwind
文档按照借阅人数组铺平
includeArrayIndex表示索引
db.book.aggregate([ { $unwind: { path: "$borrowers", includeArrayIndex: "idx" } } ]) --------------------------------------------------------- // 1 { "_id": 1, "book_type": 1, "book_name": "图书_1", "book_price": 10, "book_count": 100, "borrowers": { "name": "张三", "id": 100 }, "library_id": 1, "idx": NumberLong("0") } // 2 { "_id": 1, "book_type": 1, "book_name": "图书_1", "book_price": 10, "book_count": 100, "borrowers": { "name": "李四", "id": 200 }, "library_id": 1, "idx": NumberLong("1") } // 3 { "_id": 2, "book_type": 2, "book_name": "图书_2", "book_price": 20, "book_count": 100, "borrowers": { "name": "张三", "id": 100 }, "library_id": 2, "idx": NumberLong("0") } // 4 { "_id": 3, "book_type": 1, "book_name": "图书_3", "book_price": 30, "book_count": 100, "borrowers": { "name": "张三", "id": 100 }, "library_id": 2, "idx": NumberLong("0") } // 5 { "_id": 3, "book_type": 1, "book_name": "图书_3", "book_price": 30, "book_count": 100, "borrowers": { "name": "王五", "id": 300 }, "library_id": 2, "idx": NumberLong("1") }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
3.8 lookup
查询图书馆有哪些图书
lookup可以实现连表查询
MongoDB 3.4之前聚合语法：
-
from：待关联集合【book】
-
localField: 本集合关联键【library】
-
foreignField：待关联键【book】
-
as：待关联集合数据【book】
db.library.aggregate([ { $lookup: { from: "book", localField: "_id", foreignField: "library_id", as: "books_info" } } ]) --------------------------------------------------------- // 1 { "_id": 1, "library_name": "图书馆_1", "books_info": [ { "_id": 1, "book_type": 1, "book_name": "图书_1", "book_price": 10, "book_count": 100, "borrowers": [ { "name": "张三", "id": 100 }, { "name": "李四", "id": 200 } ], "library_id": 1 } ] } // 2 { "_id": 2, "library_name": "图书馆_2", "books_info": [ { "_id": 2, "book_type": 2, "book_name": "图书_2", "book_price": 20, "book_count": 100, "borrowers": [ { "name": "张三", "id": 100 } ], "library_id": 2 }, { "_id": 3, "book_type": 1, "book_name": "图书_3", "book_price": 30, "book_count": 100, "borrowers": [ { "name": "张三", "id": 100 }, { "name": "王五", "id": 300 } ], "library_id": 2 } ] } // 3 { "_id": 3, "library_name": "图书馆_3", "books_info": [ ] }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
MongoDB 3.4之后聚合语法：
-
from：待关联集合【book】
-
let：声明本集合字段在管道使用
-
pipeline：操作管道
db.library.aggregate([ { $lookup: { from: "book", let: { "lid": "$_id" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$$lid", "$library_id"] } ] } } } ], as: "books_info" } } ]) --------------------------------------------------------- // 1 { "_id": 1, "library_name": "图书馆_1", "books_info": [ { "_id": 1, "book_type": 1, "book_name": "图书_1", "book_price": 10, "book_count": 100, "borrowers": [ { "name": "张三", "id": 100 }, { "name": "李四", "id": 200 } ], "library_id": 1 } ] } // 2 { "_id": 2, "library_name": "图书馆_2", "books_info": [ { "_id": 2, "book_type": 2, "book_name": "图书_2", "book_price": 20, "book_count": 100, "borrowers": [ { "name": "张三", "id": 100 } ], "library_id": 2 }, { "_id": 3, "book_type": 1, "book_name": "图书_3", "book_price": 30, "book_count": 100, "borrowers": [ { "name": "张三", "id": 100 }, { "name": "王五", "id": 300 } ], "library_id": 2 } ] } // 3 { "_id": 3, "library_name": "图书馆_3", "books_info": [ ] }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
新增价格大于20查询条件
db.library.aggregate([ { $lookup: { from: "book", let: { "lid": "$_id" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$$lid", "$library_id"] }, { $gt: ["$book_price", 20] } ] } } } ], as: "books_info" } } ]) --------------------------------------------------------- // 1 { "_id": 1, "library_name": "图书馆_1", "books_info": [ ] } // 2 { "_id": 2, "library_name": "图书馆_2", "books_info": [ { "_id": 3, "book_type": 1, "book_name": "图书_3", "book_price": 30, "book_count": 100, "borrowers": [ { "name": "张三", "id": 100 }, { "name": "王五", "id": 300 } ], "library_id": 2 } ] } // 3 { "_id": 3, "library_name": "图书馆_3", "books_info": [ ] }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
3.9 group
3.9.1 简单统计
_id：图书类型作为分组键
count：每个类型有多少种书
db.book.aggregate([ { $group: { _id: "$book_type", count: { $sum: 1 } } } ]) --------------------------------------------------------- // 1 { "_id": 2, "count": 1 } // 2 { "_id": 1, "count": 2 }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
3.9.2 复杂统计
_id：图书类型作为分组键
type_count：每个类型有多少种书
type_book_count：每个类型有多少本书
minTotalPrice：每个类型总价最小值
maxTotalPrice：每个类型总价最大值
totalPrice：每个类型总价
avgPrice：每个类型平均价
db.book.aggregate([ { $group: { _id: "$book_type", type_count: { $sum: 1 }, type_book_count: { $sum: "$book_count" }, minTotalPrice: { $min: { $multiply: ["$book_price", "$book_count"] } }, maxTotalPrice: { $max: { $multiply: ["$book_price", "$book_count"] } }, totalPrice: { $sum: { $multiply: ["$book_price", "$book_count"] } }, avgPrice: { $avg: "$book_price" } } } ]) --------------------------------------------------------- // 1 { "_id": 2, "type_count": 1, "type_book_count": 100, "minTotalPrice": 2000, "maxTotalPrice": 2000, "totalPrice": 2000, "avgPrice": 20 } // 2 { "_id": 1, "type_count": 2, "type_book_count": 200, "minTotalPrice": 1000, "maxTotalPrice": 3000, "totalPrice": 4000, "avgPrice": 20 }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
3.9.3 空分组键
_id：空分组键表示统计全量数据
db.book.aggregate([ { $group: { _id: null, type_count: { $sum: 1 }, type_book_count: { $sum: "$book_count" }, minTotalPrice: { $min: { $multiply: ["$book_price", "$book_count"] } }, maxTotalPrice: { $max: { $multiply: ["$book_price", "$book_count"] } }, totalPrice: { $sum: { $multiply: ["$book_price", "$book_count"] } }, avgPrice: { $avg: "$book_price" } } } ]) --------------------------------------------------------- // 1 { "_id": null, "type_count": 3, "type_book_count": 300, "minTotalPrice": 1000, "maxTotalPrice": 3000, "totalPrice": 6000, "avgPrice": 20 }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
4 文章总结
第一介绍了MongoDB与关系型数据库关系，并且准备本文需要的测试数据，第二介绍了聚合语法和聚合管道相关概念，第三通过实例介绍了如何使用聚合操作，希望本文对大家有所帮助。
欢迎大家关注公众号「JAVA前线」查看更多精彩分享文章，主要包括源码分析、实际应用、架构思维、职场分享、产品思考等等，同时欢迎大家加我个人微信「java_front」一起交流学习
-
相关阅读:
微服务架构之演进历程
【RabbitMQ】——死信
55_Pandas.DataFrame 转换为 JSON 字符串/文件并保存 (to_json)
一本通1080；余数相同问题
（数据科学学习手札162）Python GIS神器geopandas 1.0版本发布
tomcat服务搭建（多实例部署和Nginx+Tomcat负载均衡、动静分离）
关于订单功能的处理和分析
NodeJs内置模块child_process
299. 猜数字游戏 Python
SpringBatch(10):ItemWriter详解
- 原文地址：https://blog.csdn.net/woshixuye/article/details/125570429
