MongoDB聚合查询

1.聚合查询

db.集合名.aggregate( {$group : {_id : '$filename1', aggregate_name : {$sum : '$filename2'} } } )

• $group：表示进行分组统计操作
• _id：分组字段，字段需要加$符号引用，并且需要加引号
• aggregate_name：进行分组统计后的结果名称，可自定义
• $sum：计算总和，其他$max、$min、$avg、$first、$last分别表示最大值、最小值、平均值、第一个、最后一个，sum和avg只能对数字进行计算
• $filename2：按照_id指定的字段分组后，对filename2字段进行统计，直接写1表示进行计数

通过以下数据进行聚合查询演示

> db.student.find()
{ "_id" : ObjectId("5dd8ba03202fc313bb9d053b"), "name" : "Alice", "age" : 23, "hobby" : "dance" }
{ "_id" : ObjectId("5dd8ba0a202fc313bb9d053c"), "name" : "Jack", "age" : 25, "hobby" : "running" }
{ "_id" : ObjectId("5dd8ba10202fc313bb9d053d"), "name" : "Jane", "age" : 26, "hobby" : "reading" }
{ "_id" : ObjectId("5dd8ba10202fc313bb9d053e"), "name" : "Bob", "age" : 25, "hobby" : "running" }
{ "_id" : ObjectId("5dd8ba1a202fc313bb9d053f"), "name" : "Alan", "age" : 22, "hobby" : "reading" }
{ "_id" : ObjectId("5dd8ba1a202fc313bb9d0540"), "name" : "Smith", "age" : 26, "hobby" : "reading" }
{ "_id" : ObjectId("5dda5170202fc313bb9d0544"), "name" : "Alex1", "age" : 26, "hobby" : "dance" }

按照hobby分组统计数量、按照age分组统计数量

> db.student.aggregate({$group:{_id:'$hobby',hobby_count:{$sum:1}}})
{ "_id" : "reading", "hobby_count" : 3 }
{ "_id" : "running", "hobby_count" : 2 }
{ "_id" : "dance", "hobby_count" : 2 }
> db.student.aggregate({$group:{_id:'$age',age_count:{$sum:1}}})
{ "_id" : 26, "age_count" : 3 }
{ "_id" : 25, "age_count" : 2 }
{ "_id" : 22, "age_count" : 1 }
{ "_id" : 23, "age_count" : 1 }

按照hobby分组统计age的总和、最大值、最小值、平均值

> db.student.aggregate({$group:{_id:'$hobby',age_sum:{$sum:'$age'}}})
{ "_id" : "reading", "age_sum" : 74 }
{ "_id" : "running", "age_sum" : 50 }
{ "_id" : "dance", "age_sum" : 49 }
> db.student.aggregate({$group:{_id:'$hobby',age_max:{$max:'$age'}}})
{ "_id" : "reading", "age_max" : 26 }
{ "_id" : "running", "age_max" : 25 }
{ "_id" : "dance", "age_max" : 26 }
> db.student.aggregate({$group:{_id:'$hobby',age_min:{$min:'$age'}}})
{ "_id" : "reading", "age_min" : 22 }
{ "_id" : "running", "age_min" : 25 }
{ "_id" : "dance", "age_min" : 23 }
> db.student.aggregate({$group:{_id:'$hobby',age_avg:{$avg:'$age'}}})
{ "_id" : "reading", "age_avg" : 24.666666666666668 }
{ "_id" : "running", "age_avg" : 25 }
{ "_id" : "dance", "age_avg" : 24.5 }

按照hobby分组统计第一次出现的age、按照hobby分组统计最后一次出现的name

> db.student.aggregate({$group:{_id:'$hobby',age_first:{$first:'$age'}}})
{ "_id" : "reading", "age_first" : 26 }
{ "_id" : "running", "age_first" : 25 }
{ "_id" : "dance", "age_first" : 23 }> db.student.aggregate({$group:{_id:'$hobby',name_last:{$last:'$name'}}})
{ "_id" : "reading", "name_last" : "Smith" }
{ "_id" : "running", "name_last" : "Bob" }
{ "_id" : "dance", "name_last" : "Alex1" }

2.字段别名

db.集合名.aggregate( {$project: {_id : 0, myfiled1:'$fieldname1', myfield2:'$fieldname2', ... } } )

• $project：表示进行取别名操作，且只输出project中出现的字段
• _id：如果省略_id默认输出，设置_id:0则不输出_id字段
• myfiled1：自定义的别名
• $fieldname1：自定义别名的列

例如，不输出_id，只输出name和age字段，且分别取别名为myname和myage

> db.student.aggregate({$project:{_id:0,myname:'$name',myage:'$age'}})
{ "myname" : "Alice", "myage" : 23 }
{ "myname" : "Jack", "myage" : 25 }
{ "myname" : "Jane", "myage" : 26 }
{ "myname" : "Bob", "myage" : 25 }
{ "myname" : "Alan", "myage" : 22 }
{ "myname" : "Smith", "myage" : 26 }
{ "myname" : "Alex1", "myage" : 26 }