MongoDB聚合查询

数据准备

db.users.drop()
db.users.insertMany([
	{"name": "n1", "age": 20, "gender": "m", "birth": new ISODate("2000-04-10"), "pswd": "1"},
	{"name": "n2", "age": 21, "gender": "f", "birth": new ISODate("1999-04-10"), "pswd": "2"},
	{"name": "n3", "age": 22, "gender": "m", "birth": new ISODate("1998-04-10"), "pswd": "3"},
	{"name": "n4", "age": 23, "gender": "f", "birth": new ISODate("1997-04-10"), "pswd": "4"},
	{"name": "n5", "age": 24, "gender": "m", "birth": new ISODate("1996-04-10"), "pswd": "5"}
]);

聚合查询

1. sum 求和，求记录数

   // null 表示不分组，有多少条数据就有多少组，然后每条数据累加1
   > db.users.aggregate([{"$group": {"_id": null, "count_user":{"$sum": 1}}}])
   { "_id" : null, "count_user" : 5 }
   
   // 字符串gender是常量，不分组
   > db.users.aggregate([{"$group": {"_id": "gender", "count_user":{"$sum": 1}}}])
   { "_id" : "gender", "count_user" : 5 }
   
   // $gender 表示用gender字段分组
   > db.users.aggregate([{"$group": {"_id": "$gender", "count_user":{"$sum": 1}}}])
   { "_id" : "m", "count_user" : 3 }
   { "_id" : "f", "count_user" : 2 }
   
   // 按照gender分组，然后求年龄的和，字符串age是常量，计算得0
   > db.users.aggregate([{"$group": {"_id": "$gender", "count_user":{"$sum": "age"}}}])
   { "_id" : "f", "count_user" : 0 }
   { "_id" : "m", "count_user" : 0 }
   
   // 按照gender分组，然后求年龄的和，$age是age字段，计算得0
   > db.users.aggregate([{"$group": {"_id": "$gender", "count_user":{"$sum": "$age"}}}])
   { "_id" : "m", "count_user" : 66 }
   { "_id" : "f", "count_user" : 44 }
   

2. match 分组条件查询

   • 放在分组前表示where
   • 放在分组后表示having

   // 查询年龄大于22的记录数，可以在数组的第一个参数传match表达式
   db.users.aggregate([{"$match": {"age": {"$gt":22}}}, {"$group": {"_id": null, "count_user": {"$sum": 1}}}])
   
   // 明确条件查询
   db.users.aggregate([{"$match": {"gender":"m"}}, {"$group": {"_id": null, "count_user": {"$sum": 1}}}])
   
   // 按性别分组，然后年龄大于21的数据
   db.users.aggregate([{"$match":{"age": {"$gt": 21}}}, {"$group": {"_id": "$gender", "count": {"$sum": 1}}}])
   
   // 按性别分组，找出人数大于2的分组
   db.users.aggregate([{"$group": {"_id": "$gender", "count": {"$sum": 1}}}, {"$match": {"count":{"$gt": 2}}}])
   

3. max和min，最大值和最小值

   > db.users.aggregate([{"$group": {"_id": null, "max_age": {"$max": "$age"}}}])
   { "_id" : null, "max_age" : 24 }
   
   > db.users.aggregate([{"$group": {"_id": null, "min_age": {"$min": "$age"}}}])
   { "_id" : null, "min_age" : 20 }
   
   > db.users.aggregate([{"$group": {"_id": null, "max_age": {"$max": "$age"}, "min_age": {"$min": "$age"}}}])
   { "_id" : null, "max_age" : 24, "min_age" : 20 }
   
   > db.users.aggregate([{"$group": {"_id": null, "max_age": {"$max": "$age"}, "min_age": {"$min": "$age"}, "min_birth": {"$min": "$birth"}}}])
   { "_id" : null, "max_age" : 24, "min_age" : 20, "min_birth" : ISODate("1996-04-10T00:00:00Z") }
   

4. avg 平均值, 字段只能是数学类型，字符串和时间不能用avg

   // 无分组平均年龄
   > db.users.aggregate([{"$group": {"_id": null, "avg_age": {"$avg": "$age"}}}])
   { "_id" : null, "avg_age" : 22 }
   
   // 性别分组平均年龄
   > db.users.aggregate([{"$group": {"_id": "$gender", "avg_age": {"$avg": "$age"}}}])
   { "_id" : "m", "avg_age" : 22 }
   { "_id" : "f", "avg_age" : 22 }
   
   

5. concat 字符串拼接

   在 MongoDB 中可以使用 “$project” 来控制数据列的显示规则，可以执行的规则如下：

   • 普通列（{成员：1 | true}）：表示要显示的内容
   • "_id" 列（{"_id"：0 | false}）：表示 “_id” 列是否显示
   • 条件过滤列（{成员：表达式}）：满足表达式之后的数据可以进行显示

   db.users.aggregate([{"$project": {"nickname": "$name"}}]) // 投影，起别名
   
   db.users.aggregate([{"$project": {"info": {"$concat": ["$gender", "-", "$name"]}}}]) // 字符串拼接
   
   db.users.aggregate([{"$project": {"_id": "$name", "age": "$age"}}, {"$project": {"info": {"$concat": ["$_id", " - ", "$age"]}}}]) // 字符串拼接有整数报错
   

6. 字符串大小写转换 toUpper toLower

   db.users.aggregate([{"$project": {"upper_name": {"$toUpper": "$name"}, "lower_name": {"$toLower": "$gender"}}}])
   

7. 字符串截取 substr

   > db.users.update({}, {"$set": {pswd: "password123"}}, false, true)
   WriteResult({ "nMatched" : 5, "nUpserted" : 0, "nModified" : 5 })
   
   > db.users.find()
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4668"), "name" : "n1", "age" : 20, "gender" : "m", "birth" : ISODate("2000-04-10T00:00:00Z"), "pswd" : "password123" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4669"), "name" : "n2", "age" : 21, "gender" : "f", "birth" : ISODate("1999-04-10T00:00:00Z"), "pswd" : "password123" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466a"), "name" : "n3", "age" : 22, "gender" : "m", "birth" : ISODate("1998-04-10T00:00:00Z"), "pswd" : "password123" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466b"), "name" : "n4", "age" : 23, "gender" : "f", "birth" : ISODate("1997-04-10T00:00:00Z"), "pswd" : "password123" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466c"), "name" : "n5", "age" : 24, "gender" : "m", "birth" : ISODate("1996-04-10T00:00:00Z"), "pswd" : "password123" }
   
   
   > db.users.aggregate([{"$project": {"pswd_pre": {"$substr": ["$pswd", 0, 4]}}}]) // 字符串,起始位置,截取字符个数
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4668"), "pswd_pre" : "pass" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4669"), "pswd_pre" : "pass" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466a"), "pswd_pre" : "pass" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466b"), "pswd_pre" : "pass" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466c"), "pswd_pre" : "pass" }
   
   
   > db.users.aggregate([{"$project": {"pswd_pre": {"$substr": ["$pswd", 20, 4]}}}]) // 起始下标溢出为空
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4668"), "pswd_pre" : "" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4669"), "pswd_pre" : "" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466a"), "pswd_pre" : "" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466b"), "pswd_pre" : "" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466c"), "pswd_pre" : "" }
   

8. 时间日期格式化 dateToString

   > db.users.aggregate([{"$project": {"birth_format": {"$dateToString": {"format":"%Y-%m-%d %H:%M:%S", "date":"$birth"}}}}])
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4668"), "birth_format" : "2000-04-10 00:00:00" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4669"), "birth_format" : "1999-04-10 00:00:00" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466a"), "birth_format" : "1998-04-10 00:00:00" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466b"), "birth_format" : "1997-04-10 00:00:00" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466c"), "birth_format" : "1996-04-10 00:00:00" }
   
   
   // %j 一年的第N天
   // %w 一周的第N天
   // %U 一年的第N周
   > db.users.aggregate([{"$project": {"birth_format": {"$dateToString": {"format":"%Y-%m-%d %H:%M:%S %j_%w_%U", "date":"$birth"}}}}])
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4668"), "birth_format" : "2000-04-10 00:00:00 101_2_15" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff4669"), "birth_format" : "1999-04-10 00:00:00 100_7_14" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466a"), "birth_format" : "1998-04-10 00:00:00 100_6_14" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466b"), "birth_format" : "1997-04-10 00:00:00 100_5_14" }
   { "_id" : ObjectId("601cf85c7cfdb08d65ff466c"), "birth_format" : "1996-04-10 00:00:00 101_4_14" }