MongoDB聚合和管道

小满大王i
183 阅读6分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 16 天，点击查看活动详情

数据准备：

db.test003.insertMany([
    {name: "张飞", hometown: "蜀国", age: 30, sex: "男"},
    {name: "关羽", hometown: "蜀国", age: 40, sex: "男"},
    {name: "刘备", hometown: "蜀国", age: 50, sex: "男"},
    {name: "曹操", hometown: "魏国", age: 45, sex: "男"},
    {name: "司马懿", hometown: "魏国", age: 45, sex: "男"},
    {name: "孙权", hometown: "吴国", age: 50, sex: "男"},
    {name: "貂蝉", hometown: "未知", age: 18, sex: "女"},
    {name: "西施", hometown: "越国", age: 18, sex: "女"},
    {name: "王昭君", hometown: "西汉", age: 18, sex: "女"},
    {name: "杨玉环", hometown: "唐朝", age: 18, sex: "女"}
]);

1、聚合简介

聚合(aggregate)是基于数据处理的聚合管道，每个文档通过一个由多个阶段（stage）组成的管道，可以对每个阶段的管道进行分组、过滤等功能，然后经过一系列的处理，输出相应的结果。

语法格式：db.集合名称.aggregate({管道:{表达式}})

在这里插入图片描述

2、常用管道

在mongodb中，⽂档处理完毕后， 通过管道进⾏下⼀次处理

常用管道如下：

$group： 将集合中的⽂档分组， 可⽤于统计结果

$match： 过滤数据， 只输出符合条件的⽂档

$project： 修改输⼊⽂档的结构， 如重命名、 增加、 删除字段、 创建计算结果

$sort： 将输⼊⽂档排序后输出

$limit： 限制聚合管道返回的⽂档数

$skip： 跳过指定数量的⽂档， 并返回余下的⽂档

$unwind： 将数组类型的字段进⾏拆分

3、表达式

处理输⼊⽂档并输出

语法格式：表达式:'$列名'

常⽤表达式:

$sum： 计算总和， $sum:1 表示以⼀倍计数

$avg： 计算平均值

$min： 获取最⼩值

$max： 获取最⼤值

$push： 在结果⽂档中插⼊值到⼀个数组中

$first： 根据资源⽂档的排序获取第⼀个⽂档数据

$last： 根据资源⽂档的排序获取最后⼀个⽂档数据

4、$group

将集合中的文档分组，可用于统计结果

_id表示分组的依据，使用某个字段的格式为'$字段'

案例：

// 返回sex有哪些值
> db.test003.aggregate(
	{$group:{
		_id:"$sex"
		}
	}
)
{ "_id" : "男" }
{ "_id" : "女" }

//统计男生、女生分别的总人数
> db.test003.aggregate(
	{$group:
		{
		_id:"$sex",
		count:{$sum:1}
		}
	}
)
{ "_id" : "男", "count" : 6 }
{ "_id" : "女", "count" : 4 }

//统计男、女分别的平均年龄
> db.test003.aggregate(
	{$group:
		{
		_id:"$sex",
		count:{$sum:1},
		avg_age:{$avg:"$age"}
		}
	}
)
{ "_id" : "男", "count" : 6, "avg_age" : 43.333333333333336 }
{ "_id" : "女", "count" : 4, "avg_age" : 18 }

//按照hometown进行分组，获取不同组的平均年龄
> db.test003.aggregate(
	{$group:
		{
		_id:"$hometown",
		avg_age:{$avg:"$age"}
		}
	}
)
{ "_id" : "蜀国", "avg_age" : 40 }
{ "_id" : "唐朝", "avg_age" : 18 }
{ "_id" : "越国", "avg_age" : 18 }
{ "_id" : "吴国", "avg_age" : 50 }
{ "_id" : "西汉", "avg_age" : 18 }
{ "_id" : "未知", "avg_age" : 18 }
{ "_id" : "魏国", "avg_age" : 45 }

//统计不同性别的人物名字
> db.test003.aggregate(
	{$group:
		{
		_id:"$sex",
		name:{$push:"$name"}
		}
	}
)
{ "_id" : "男", "name" : [ "张飞", "关羽", "刘备", "曹操", "司马懿", "孙权" ] }
{ "_id" : "女", "name" : [ "貂蝉", "西施", "王昭君", "杨玉环" ] }

// 使用$$ROOT可以将文档内容加入到结果集的数组中
> db.test003.aggregate(
	{$group:
		{
		_id:"$sex",
		name:{$push:"$$ROOT"}
		}
	}
)
{ "_id" : "男", "name" : [ { "_id" : ObjectId("621cbd0aea5c14fd51410b33"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }, { "_id" : ObjectId("621cbd0aea5c14fd51410b34"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }, { "_id" : ObjectId("621cbd0aea5c14fd51410b35"), "name" : " 刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }, { "_id" : ObjectId("621cbd0aea5c14fd51410b36"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }, { "_id" : ObjectId("621cbd0aea5c14fd51410b37"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }, { "_id" : ObjectId("621cbd0aea5c14fd51410b38"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" } ] }
{ "_id" : "女", "name" : [ { "_id" : ObjectId("621cbd0aea5c14fd51410b39"), "name" : "貂蝉", "hometown" : "未知", "age" : 18, "sex" : "女" }, { "_id" : ObjectId("621cbd0aea5c14fd51410b3a"), "name" : "西施", "hometown" : "越国", "age" : 18, "sex" : "女" }, { "_id" : ObjectId("621cbd0aea5c14fd51410b3b"), "name" : " 王昭君", "hometown" : "西汉", "age" : 18, "sex" : "女" }, { "_id" : ObjectId("621cbd0aea5c14fd51410b3c"), "name" : "杨玉环", "hometown" : "唐朝", "age" : 18, "sex" : "女" } ] }

_id:null：将集合中所有文档分为一组

案例：求总人数、平均年龄

> db.test003.aggregate(
	{$group:
		{
		_id:null,
		count:{$sum:1},
		avg_age:{$avg:"$age"}
		}
	}
)
{ "_id" : null, "count" : 10, "avg_age" : 33.2 }

总结：

  • $group对应的字典中有几个键，结果中就有几个键
  • 分组依据需要放到_ id后面
  • 取不同的字段的值需要使用$，如：$hometown、$age、$sex
  • 取字典嵌套的字典中值的时候$_id.字段名
  • 同时取多个键进行分组：{$group:{_id:{字段名1:"$字段名1",字段名2:"字段名2"}}}；输出结果：{_id:{字段名1:"",字段名2:""}

5、$project

修改输入文档的结构，如重命名、增加、删除字段、创建计算结果；简单来说就是修改输入输出的值

案例1：查询姓名、年龄

> db.test003.aggregate({$project:{_id:0, name:1, age:1}})
{ "name" : "张飞", "age" : 30 }
{ "name" : "关羽", "age" : 40 }
{ "name" : "刘备", "age" : 50 }
{ "name" : "曹操", "age" : 45 }
{ "name" : "司马懿", "age" : 45 }
{ "name" : "孙权", "age" : 50 }
{ "name" : "貂蝉", "age" : 18 }
{ "name" : "西施", "age" : 18 }
{ "name" : "王昭君", "age" : 18 }
{ "name" : "杨玉环", "age" : 18 }

案例2：查询男、女人数，输出人数


> db.test003.aggregate(
	{$group:{_id:'$sex', count:{$sum:1}}},
	{$project:{_id:0, count:1}}
)
{ "count" : 4 }
{ "count" : 6 }

6、$match

用于过滤数据，只输出符合条件的文档

  • 使用MongoDB的标准查询操作
  • match是管道命令，能将结果交给后一个管道，但是find不可以

案例1：查询年龄大于20的

> db.test003.aggregate(
	{$match:{age:{$gt:20}}}
)
{ "_id" : ObjectId("621cbd0aea5c14fd51410b33"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("621cbd0aea5c14fd51410b34"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }
{ "_id" : ObjectId("621cbd0aea5c14fd51410b35"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("621cbd0aea5c14fd51410b36"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("621cbd0aea5c14fd51410b37"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("621cbd0aea5c14fd51410b38"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

案例2：查询年龄大于等于18的男生、女生人数

> db.test003.aggregate(
	{$match:{age:{$gte:18}}},
	{$group:{_id:'$sex',count:{$sum:1}}}
)
{ "_id" : "男", "count" : 6 }
{ "_id" : "女", "count" : 4 }

7、$sort

将输入文档排序后输出

例1：查询学生信息，按年龄升序

> db.test003.aggregate({$sort:{age:1}})
{ "_id" : ObjectId("622080a6d0f7b3df134da712"), "name" : "貂蝉", "hometown" : "未知", "age" : 18, "sex" : "女" }
{ "_id" : ObjectId("622080a6d0f7b3df134da713"), "name" : "西施", "hometown" : "越国", "age" : 18, "sex" : "女" }
{ "_id" : ObjectId("622080a6d0f7b3df134da714"), "name" : "王昭君", "hometown" : "西汉", "age" : 18, "sex" : "女" }
{ "_id" : ObjectId("622080a6d0f7b3df134da715"), "name" : "杨玉环", "hometown" : "唐朝", "age" : 18, "sex" : "女" }
{ "_id" : ObjectId("622080a6d0f7b3df134da70c"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da70d"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da70f"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da710"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da70e"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da711"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

例2：查询男生、女生人数，按人数降序


> db.test003.aggregate(
	{$group:{_id:'$sex',counter:{$sum:1}}},
	{$sort:{age:-1}}
)
{ "_id" : "男", "counter" : 6 }
{ "_id" : "女", "counter" : 4 }

8、$limit

限制聚合管道返回的文档数量

案例：查询2条学生信息


> db.test003.aggregate({$limit:2})
{ "_id" : ObjectId("622080a6d0f7b3df134da70c"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da70d"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }

9、$skip

跳过指定数量的文档，并返回余下的文档

例1：查询从第3条开始:人物信息

> db.test003.aggregate({$skip:2})
{ "_id" : ObjectId("622080a6d0f7b3df134da70e"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da70f"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da710"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da711"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("622080a6d0f7b3df134da712"), "name" : "貂蝉", "hometown" : "未知", "age" : 18, "sex" : "女" }
{ "_id" : ObjectId("622080a6d0f7b3df134da713"), "name" : "西施", "hometown" : "越国", "age" : 18, "sex" : "女" }
{ "_id" : ObjectId("622080a6d0f7b3df134da714"), "name" : "王昭君", "hometown" : "西汉", "age" : 18, "sex" : "女" }
{ "_id" : ObjectId("622080a6d0f7b3df134da715"), "name" : "杨玉环", "hometown" : "唐朝", "age" : 18, "sex" : "女" }

例2：查询从第3条开始，取第二条数据

> db.test003.aggregate(
	{$skip:2},
	{$limit:1}
)
{ "_id" : ObjectId("622080a6d0f7b3df134da70e"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
  • 注意顺序：先写skip, 再写limit

10、$unwind

将文档中的某一个数组类型字段拆分成多条，每条包含数组中的一个值

语法格式：db. 集合名称.aggregate({$unwind:'$字段名称’})

案例：

> db.test004.insert({_id:1, item:'t-shirt', size:['S','M','L']})
WriteResult({ "nInserted" : 1 })
> db.test004.aggregate({$unwind: '$size'})
{ "_id" : 1, "item" : "t-shirt", "size" : "S" }
{ "_id" : 1, "item" : "t-shirt", "size" : "M" }
{ "_id" : 1, "item" : "t-shirt", "size" : "L" }

练习：数据库中有一条数据：{"username":"Alex","tags": ['C#','Java','C++']}，如何获取该tag列表的长度？

//先插入数据

> db.test004.insert({"username":"Alex","tags": ['C#','Java','C++']})
WriteResult({ "nInserted" : 1 })
//查看数据
> db.test004.find()
{ "_id" : ObjectId("6220b04d383dd803412e9a3f"), "username" : "Alex", "tags" : [ "C#", "Java", "C++" ] }
//拆分数据
> db.test004.aggregate({$match:{username:"Alex"}},{$unwind:"$tags"})
{ "_id" : ObjectId("6220b04d383dd803412e9a3f"), "username" : "Alex", "tags" : "C#" }
{ "_id" : ObjectId("6220b04d383dd803412e9a3f"), "username" : "Alex", "tags" : "Java" }
{ "_id" : ObjectId("6220b04d383dd803412e9a3f"), "username" : "Alex", "tags" : "C++" }
//把上面得三条结果给$group，然后统计条数
> db.test004.aggregate({$match:{username:"Alex"}},{$unwind:"$tags"},{$group:{_id:null, sum:{$sum:1}}})
{ "_id" : null, "sum" : 3 }

属性preserveNullAndEmptyArrays值为true表示保留属性值为空的⽂档；值为false表示丢弃属性值为空的⽂档

用法：

在这里插入图片描述

avatar
文章
阅读
粉丝