1、聚合简介
在MongoDB中,使用聚合框架可以对集合中的文档进行变换和组合,完成一些复杂的查询操作。聚合框架通过多个阶段来创建一个管道(pipeline),用于对一连串的文档进行处理。这些构件包括但不限于:
聚合操作就是通过aggregate()
函数来完成一系列的聚合查询,主要用于处理如:统计,平均值,求和等,并返回计算后的结果。
语法:
db.collection.aggregate{
[
{$group:{_id:"$分组键名"}, "$分组键名", 别名:{聚合运算: "$运算列"} },
{条件筛选:{键名:{运算条件:运算值}}}
]
}
管道操作符
操作符 | 描述 |
---|---|
$match | 过滤数据,只输出符合结果的文档(也可以对分组的数组做过滤) |
$project | 投射,选择想要的字段或对字段进行重命名 |
$group | 将集合中的文档分组,可用于统计结果 |
$unwind | 拆分 |
$sort | 排序 |
$limit | 限制查询条数 |
$skip | 跳过一些条数 |
$lookup | 多表关联查询 |
表达式操作符
操作符 | 描述 |
---|---|
$sum | 计算总和,{$sum: 1}表示返回总和×1的值(即总和的数量),使用{$sum: ‘$制定字段‘}也能直接获取制定字段的值的总和 |
$avg | 求平均值 |
$min | 求最小值 |
$max | 求最大值 |
$push | 将结果文档中插入值到一个数组中 |
$first | 根据文档的排序获取第一个文档数据 |
$skip | 跳过一些条数 |
$last | 同理,获取最后一个数据 |
2、简单练习
造一个测试文档,后面围绕这个文档做聚合操作
db.orders.insertMany([
{
cust_id: "a1300123",
ord_date: ISODate("2020-06-22T17:04:11.102Z"),
status: ‘success‘,
price: 85,
items: [ { sku: "beef", qty: 30, amount: 1 },
{ sku: "mutton", qty: 25, amount: 1 },
{ sku: "beer", qty: 10, amount: 3 }
]
},
{
cust_id: "a1300123",
ord_date: ISODate("2020-06-22T17:04:11.102Z"),
status: ‘success‘,
price: 100,
items: [ { sku: "beef", qty: 30, amount: 1 },
{ sku: "mutton", qty: 25, amount: 2 },
{ sku: "beer", qty: 10, amount: 3 }
]
},
{
cust_id: "a1300124",
ord_date: ISODate("2020-06-22T17:04:11.102Z"),
status: ‘success‘,
price: 85,
items: [ { sku: "beef", qty: 30, amount: 1 },
{ sku: "mutton", qty: 25, amount: 1 },
{ sku: "beer", qty: 10, amount: 1 }
]
},
{
cust_id: "a1300124",
ord_date: ISODate("2020-06-22T17:04:11.102Z"),
status: ‘success‘,
price: 105,
items: [ { sku: "beef", qty: 30, amount: 2 },
{ sku: "mutton", qty: 25, amount: 1 },
{ sku: "beer", qty: 10, amount: 1 }
]
},
])
1.统计orders集合所有记录
db.orders.aggregate( [
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
] )
// 结果
{ "_id": null, "count": 4 }
Note:这里的$sum:1 表示的就是统计所有记录
2.计算orders集合所有文档price的总和
db.orders.aggregate([
{
$group: {
_id: null,
total_price: { $sum: "$price"}
}
}
])
// 结果
{ "_id": null, "total_price": 375 }
3.对于每一个唯一的cust_id,计算price总和
db.orders.aggregate([
{
$group: {
_id: ‘$cust_id‘,
total_price: { $sum: "$price"}
}
}
])
// 结果
{ "_id": "a1300123", "total_price": 185 }
{ "_id": "a1300124", "total_price": 190 }
4.对每一个唯一对cust_id和ord_date分组,计算price总和,不包括日期的时间部分
db.orders.aggregate( [
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date: {
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: { $sum: "$price" }
}
}
] )
// 结果
{ "_id": { "cust_id": "a1300124", "ord_date": { "month": 6, "day": 22, "year": 2020 } }, "total": 190 }
{ "_id": { "cust_id": "a1300123", "ord_date": { "month": 6, "day": 22, "year": 2020 } }, "total": 185 }
5.对于有多个记录的cust_id,返回cust_id和对应的数量
db.orders.aggregate([
{
$group: {
_id: "$cust_id",
count: {$sum : 1}
}
}
])
// 结果
{ "_id": "a1300123", "count": 2 }
{ "_id": "a1300124", "count": 2 }
6.对每个唯一的cust_id和ord_date分组,计算价格总和,并只返回price总和大于等于190的记录,且排除日期的时间部分
db.orders.aggregate([
{
$group: {
_id: {
cust_id: "$cust_id",
ord_date:{
month: { $month: "$ord_date" },
day: { $dayOfMonth: "$ord_date" },
year: { $year: "$ord_date"}
}
},
total: {$sum: "$price"}
}
},
{ $match: {total: {$gte: 190 }}}
])
// 结果
{ "_id": { "cust_id": "a1300124", "ord_date": { "month": 6, "day": 22, "year": 2020 } }, "total": 190 }
7.对每个唯一的cust_id且status=success,计算price总和
db.orders.aggregate([
{$match: { status: ‘success‘} },
{
$group:{
_id: ‘$cust_id‘,
total: { $sum: ‘$price‘}
}
}
])
// 结果
{ "_id": "a1300124", "total": 190 }
{ "_id": "a1300123", "total": 185 }
8.统计每个orders文档里菜单的价格 * 购买数量
db.orders.aggregate( [
{ $unwind: "$items" },
{$project: {cust_id: ‘$cust_id‘,total: { $multiply: ["$items.qty", "$items.amount"]}}},
])
// 结果
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a1"), "total": 30 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a1"), "total": 25 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a1"), "total": 30 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a2"), "total": 30 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a2"), "total": 50 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a2"), "total": 30 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a3"), "total": 30 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a3"), "total": 25 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a3"), "total": 10 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a4"), "total": 60 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a4"), "total": 25 }
{ "_id": ObjectId("5ef0230a3e2cd6e9f70b94a4"), "total": 10 }
3、聚合操作
模拟数据
db.dev.insertMany([
{title: ‘Linux运维班‘, description: ‘某小厂工程师主讲‘, url: ‘www.qylinux.com‘, tags: [‘Linux基础‘, ‘linux‘] ,price: 12000},
{title: ‘Linux架构师‘, description: ‘某大厂资深工程师主讲‘, url: ‘www.qylinux.com‘, tags: [‘Linux架构‘, ‘linux‘] ,price: 18000},
{title: ‘Python自动化运维‘, description: ‘鹅厂高级自动化运维经理主讲‘, url: ‘www.qypython.com‘, tags: [‘Python‘, ‘运维‘, ‘自动化运维‘] ,price: 21500},
{title: ‘Python全栈‘, description: ‘AWS开发经理主讲‘, url: ‘www.qypython.com‘, tags: [‘Python‘, ‘AWS‘, ‘前端‘] ,price: 25600},
{title: ‘Golang全栈‘, description: ‘Google资深工程师主讲‘, url: ‘www.qygolang.com‘, tags: [‘Golang‘, ‘21世纪C语言‘] ,price: 25600},
{title: ‘AWS架构师‘, description: ‘AWS东南亚首席CTO主讲‘, url: ‘www.qyaws.com‘, tags: [‘AWS‘, ‘云计算‘, ‘虚拟化‘] ,price: 18000},
])
3.1 求和-$sum
1.查询dev集合中一共有多少个文档
// sql
select count(*) AS count FORM dev
// mongodb
db.dev.aggregate([
{
$group:{
_id: null,
count: { $sum: 1}
}
}
])
返回结果:
{ "_id": null, "count": 6 }
参数解释:
$group:分组,代表聚合的分组条件。
_id:分组的字段,不能缺少,必须要有,如果根据某字段的值分组,则定义为_id: ‘$字段名‘,所以此案例中的null代表一个固定的字面值‘null‘。
count:返回结果字段名,可以自定义,类似SQL中的字段别名。
$sum:求和表达式,相当于SQL中的sum()。
1:累加值
2.查询dev集合中所有price
键中键值的总和
// $price表示文档中的price字段的值
db.dev.aggregate([
{
$group: {
_id: null,
totalPrice: { $sum: ‘$price‘ }
}
}
])
返回结果:
{ "_id": null, "totalPrice": 120700 }
3.对每个title
进行分组并计算每组的price
的总和
// $price表示文档中的price字段的值
db.dev.aggregate([
{
$group: {
_id: ‘$title‘,
totalPrice: { $sum: ‘$price‘ }
}
}
])
返回结果:
{ "_id": "Linux运维班", "totalPrice": 12000 }
{ "_id": "Python全栈", "totalPrice": 25600 }
{ "_id": "Linux架构师", "totalPrice": 18000 }
{ "_id": "Golang全栈", "totalPrice": 25600 }
{ "_id": "Python自动化运维", "totalPrice": 21500 }
{ "_id": "AWS架构师", "totalPrice": 18000 }
3.2 过滤-$match
$match
匹配条件,相当于SQL中的where子句,代表聚合之前进行条件筛选
1.查询dev集合中有多少文档的price大于20000
db.dev.aggregate([
{
$match: {
price: { $gt: 20000}
}
},
{
$group: {
_id: null,
count: { $sum: 1 }
}
}
])
返回结果:
{ "_id": null, "count": 3 }
2.查询dev集合,根据title分组计算出每组的price总和,并过滤掉总和小于等于20000的文档
db.dev.aggregate([
{
$group: {
_id: ‘$title‘,
totalPrice: { $sum: ‘$price‘ }
}
},
{
$match: {
totalPrice: { $lte: 20000 }
}
}
])
返回结果:
{ "_id": "Linux运维班", "totalPrice": 12000 }
{ "_id": "Linux架构师", "totalPrice": 18000 }
{ "_id": "AWS架构师", "totalPrice": 18000 }
3.3 最大值-$max
查询dev集合中price最大的文档,
$max: ‘$price‘
:计算price键的最大值
db.dev.aggregate([
{
$group: {
_id: null,
maxPirce: { $max: ‘$price‘ }
}
}
])
返回结果:
{ "_id": null, "maxPirce": 25600 }
3.4 最小值-$min
查询dev集合中price最小的文档,
$min: ‘$price‘
:计算price键的最小值
db.dev.aggregate([
{
$group: {
_id: null,
minPirce: { $min: ‘$price‘ }
}
}
])
返回结果:
{ "_id": null, "minPirce": 12000 }
3.5 平均值-$avg
查询dev集合中price的平均值,
$avg: ‘$price‘
计算price键的平均值
db.dev.aggregate([
{
$group: {
_id: null,
avgPrice: { $avg: ‘$price‘ }
}
}
])
返回结果:
{ "_id": null, "avgPrice": 20116.666666666668 }
3.6 统计结果返回数组-$push
查询dev集合,按照price分组并返回它们的title,如果price相同则使用数组返回它们的title。
$push: ‘$title‘
:如果price相同则使用数组返回它们的title
db.dev.aggregate([
{
$group: {
_id: ‘$price‘,
title: { $push: ‘$title‘ }
}
}
])
返回结果:
{ "_id": 25600, "title": [ "Python全栈", "Golang全栈" ] }
{ "_id": 12000, "title": [ "Linux运维班" ] }
{ "_id": 18000, "title": [ "Linux架构师", "AWS架构师" ] }
{ "_id": 21500, "title": [ "Python自动化运维" ] }
3.7 数组字段拆分-$unwind
查询dev集合,将数组中的内容拆分显示
$unwind: ‘$tags‘
:对数组中的元素进行拆分显示
db.dev.aggregate([
{ $unwind: ‘$tags‘ }
])
返回结果:
....... 省略文档 ........
Fetched 15 record(s) in 3ms
3.8 管道操作
管道在Unix和Linux中一般用于将当前命令的输出结果作为下一个命令的参数。
MongoDB的聚合管道将MongoDB文档在一个管道处理完毕后将结果传递给下一个管道处理,管道操作是可以重复的。
管道操作符是按照书写的顺序依次执行的,每个操作符都会接受这一串的文档,然后对文档做相应的转换操作,最后将转换后的文档作为结果传递给下一个操作符(对于最后一个管道操作符,是将结果返回给客户端),称为流式工作方式
管道操作符:$match、$group、$sort、$skip、$unwind .......
Note:管道操作符只能处理当前聚合的文档,而不能处理管道以外的其它文档。
3.8.1、聚合投影约束-$project
$project
操作符:我们可以使用$project操作符做聚合投影操作
1.查询dev集合,将数组中的内容拆分显示,并只显示title键与tags键的值
db.dev.aggregate([
{ $unwind: ‘$tags‘ },
{
$project: {
_id: 0,
title: ‘$title‘,
tags: ‘$tags‘
}
}
])
返回结果:
{ "title": "Linux运维班", "tags": "Linux基础" }
{ "title": "Linux运维班", "tags": "linux" }
{ "title": "Linux架构师", "tags": "Linux架构" }
{ "title": "Linux架构师", "tags": "linux" }
{ "title": "Python自动化运维", "tags": "Python" }
{ "title": "Python自动化运维", "tags": "运维" }
{ "title": "Python自动化运维", "tags": "自动化运维" }
{ "title": "Python全栈", "tags": "Python" }
{ "title": "Python全栈", "tags": "AWS" }
{ "title": "Python全栈", "tags": "前端" }
{ "title": "Golang全栈", "tags": "Golang" }
{ "title": "Golang全栈", "tags": "21世纪C语言" }
{ "title": "AWS架构师", "tags": "AWS" }
{ "title": "AWS架构师", "tags": "云计算" }
{ "title": "AWS架构师", "tags": "虚拟化" }
2.查询dev集合,将数组中的内容拆分显示,要求值显示title键与tags键的值并将title键改为Title
db.dev.aggregate([
{ $unwind: ‘$tags‘ },
{
$project: {
_id: 0,
Title: ‘$title‘,
tags: ‘$tags‘
}
}
])
返回结果:
{ "Title": "Linux运维班", "tags": "Linux基础" }
{ "Title": "Linux运维班", "tags": "linux" }
{ "Title": "Linux架构师", "tags": "Linux架构" }
{ "Title": "Linux架构师", "tags": "linux" }
{ "Title": "Python自动化运维", "tags": "Python" }
{ "Title": "Python自动化运维", "tags": "运维" }
{ "Title": "Python自动化运维", "tags": "自动化运维" }
{ "Title": "Python全栈", "tags": "Python" }
{ "Title": "Python全栈", "tags": "AWS" }
{ "Title": "Python全栈", "tags": "前端" }
{ "Title": "Golang全栈", "tags": "Golang" }
{ "Title": "Golang全栈", "tags": "21世纪C语言" }
{ "Title": "AWS架构师", "tags": "AWS" }
{ "Title": "AWS架构师", "tags": "云计算" }
{ "Title": "AWS架构师", "tags": "虚拟化" }
3.8.2、字符串处理-$project
$project
中可以通过MongoDB的字符串操作符对投影的内容做字符串处理
1.查询dev集合,将数组中的内容拆分显示,将title中的值转换为小写并命名为New_Title,将tags的值转换为大写并命名为New_Tags。
New_Title:{ $toLower: ‘$title‘}:将title中的值转换为小写
New_Tags:{ $toUpper: ‘$tags‘}: 将tags中的值转换为大写
db.dev.aggregate([
{ $unwind: ‘$tags‘ },
{
$project: {
_id: 0,
New_Title: { $toLower: ‘$title‘},
New_Tags: { $toUpper: ‘$tags‘}
}
}
])
返回结果:
{ "New_Title": "linux运维班", "New_Tags": "LINUX基础" }
{ "New_Title": "linux运维班", "New_Tags": "LINUX" }
{ "New_Title": "linux架构师", "New_Tags": "LINUX架构" }
{ "New_Title": "linux架构师", "New_Tags": "LINUX" }
{ "New_Title": "python自动化运维", "New_Tags": "PYTHON" }
{ "New_Title": "python自动化运维", "New_Tags": "运维" }
{ "New_Title": "python自动化运维", "New_Tags": "自动化运维" }
{ "New_Title": "python全栈", "New_Tags": "PYTHON" }
{ "New_Title": "python全栈", "New_Tags": "AWS" }
{ "New_Title": "python全栈", "New_Tags": "前端" }
{ "New_Title": "golang全栈", "New_Tags": "GOLANG" }
{ "New_Title": "golang全栈", "New_Tags": "21世纪C语言" }
{ "New_Title": "aws架构师", "New_Tags": "AWS" }
{ "New_Title": "aws架构师", "New_Tags": "云计算" }
{ "New_Title": "aws架构师", "New_Tags": "虚拟化" }
2.查询dev集合,将数组中的内容拆分显示,将title字段和tags字段的值拼接为一个完整字符串并在Title_Tags字段中显示。
db.dev.aggregate([
{ $unwind: ‘$tags‘ },
{
$project: {
_id: 0,
Title_Tags: { $concat: [‘$title‘,‘-‘,‘$tags‘]},
}
}
])
返回结果:
{ "Title_Tags": "Linux运维班-Linux基础" }
{ "Title_Tags": "Linux运维班-linux" }
{ "Title_Tags": "Linux架构师-Linux架构" }
{ "Title_Tags": "Linux架构师-linux" }
{ "Title_Tags": "Python自动化运维-Python" }
{ "Title_Tags": "Python自动化运维-运维" }
{ "Title_Tags": "Python自动化运维-自动化运维" }
{ "Title_Tags": "Python全栈-Python" }
{ "Title_Tags": "Python全栈-AWS" }
{ "Title_Tags": "Python全栈-前端" }
{ "Title_Tags": "Golang全栈-Golang" }
{ "Title_Tags": "Golang全栈-21世纪C语言" }
{ "Title_Tags": "AWS架构师-AWS" }
{ "Title_Tags": "AWS架构师-云计算" }
{ "Title_Tags": "AWS架构师-虚拟化" }
Note:$concat的数组中给定需要拼接的值。
3.查询dev集合,将数组中的内容拆分显示,只显示title字段的前3个字符,并命名为Title_Prefix
Title_Prefix: { $substr: [‘$title‘,0,3]}:将title的值从0开始截取3位,并命名为Title_Prefix
db.dev.aggregate([
{ $unwind: ‘$tags‘ },
{
$project: {
_id: 0,
Title_Prefix: { $substr: [‘$title‘,0,3]},
}
}
])
返回结果:
{ "Title_Prefix": "Lin" }
{ "Title_Prefix": "Lin" }
{ "Title_Prefix": "Lin" }
{ "Title_Prefix": "Lin" }
{ "Title_Prefix": "Pyt" }
{ "Title_Prefix": "Pyt" }
{ "Title_Prefix": "Pyt" }
{ "Title_Prefix": "Pyt" }
{ "Title_Prefix": "Pyt" }
{ "Title_Prefix": "Pyt" }
{ "Title_Prefix": "Gol" }
{ "Title_Prefix": "Gol" }
{ "Title_Prefix": "AWS" }
{ "Title_Prefix": "AWS" }
{ "Title_Prefix": "AWS" }
对于$substr
只能匹配ASCII的数据,对于中文要使用$substrCP
db.dev.aggregate([
{ $unwind: ‘$tags‘ },
{
$project: {
_id: 0,
Title_Prefix: { $substrCP: [‘$title‘,0,6]},
}
}
])
返回结果:
{ "Title_Prefix": "Linux运" }
{ "Title_Prefix": "Linux运" }
{ "Title_Prefix": "Linux架" }
{ "Title_Prefix": "Linux架" }
{ "Title_Prefix": "Python" }
{ "Title_Prefix": "Python" }
{ "Title_Prefix": "Python" }
{ "Title_Prefix": "Python" }
{ "Title_Prefix": "Python" }
{ "Title_Prefix": "Python" }
{ "Title_Prefix": "Golang" }
{ "Title_Prefix": "Golang" }
{ "Title_Prefix": "AWS架构师" }
{ "Title_Prefix": "AWS架构师" }
{ "Title_Prefix": "AWS架构师" }
3.8.3、算数运算-$project
$project
中我们可以通过MongoDB的算数操作符对投影的内容进行运算处理
1.查询dev集合中数据,显示title和price字段,为price字段的数据做加1操作,显示字段名为New_Price
db.dev.aggregate([
{
$project: {
_id: 0,
title: 1,
New_Price: { $add: [‘$price‘,1]}
}
}
])
返回结果:
{ "title": "Linux运维班", "New_Price": 12001 }
{ "title": "Linux架构师", "New_Price": 18001 }
{ "title": "Python自动化运维", "New_Price": 21501 }
{ "title": "Python全栈", "New_Price": 25601 }
{ "title": "Golang全栈", "New_Price": 25601 }
{ "title": "AWS架构师", "New_Price": 18001 }
2.查询dev集合中数据,显示title和price字段,为price字段的数据做减1操作,显示字段名为New_Price
db.dev.aggregate([
{
$project: {
_id: 0,
title: 1,
New_Price: { $subtract: [‘$price‘,1]}
}
}
])
返回结果:
{ "title": "Linux运维班", "New_Price": 11999 }
{ "title": "Linux架构师", "New_Price": 17999 }
{ "title": "Python自动化运维", "New_Price": 21499 }
{ "title": "Python全栈", "New_Price": 25599 }
{ "title": "Golang全栈", "New_Price": 25599 }
{ "title": "AWS架构师", "New_Price": 17999 }
3.查询dev集合中数据,显示title和price字段,为price字段的数据做乘2操作,显示字段名为New_Price
db.dev.aggregate([
{
$project: {
_id: 0,
title: 1,
New_Price: { $multiply: [‘$price‘,2]}
}
}
])
返回结果:
{ "title": "Linux运维班", "New_Price": 24000 }
{ "title": "Linux架构师", "New_Price": 36000 }
{ "title": "Python自动化运维", "New_Price": 43000 }
{ "title": "Python全栈", "New_Price": 51200 }
{ "title": "Golang全栈", "New_Price": 51200 }
{ "title": "AWS架构师", "New_Price": 36000 }
4.查询dev集合中数据,显示title和price字段,为price字段的数据做除2操作,显示字段名为New_Price
db.dev.aggregate([
{
$project: {
_id: 0,
title: 1,
New_Price: { $divide: [‘$price‘,2]}
}
}
])
返回结果:
{ "title": "Linux运维班", "New_Price": 6000 }
{ "title": "Linux架构师", "New_Price": 9000 }
{ "title": "Python自动化运维", "New_Price": 10750 }
{ "title": "Python全栈", "New_Price": 12800 }
{ "title": "Golang全栈", "New_Price": 12800 }
{ "title": "AWS架构师", "New_Price": 9000 }
5.查询dev集合中数据,显示title和price字段,为price字段的数据做模2操作,显示字段名为New_Price
db.dev.aggregate([
{
$project: {
_id: 0,
title: 1,
New_Price: { $mod: [‘$price‘,2]}
}
}
])
返回结果:
{ "title": "Linux运维班", "New_Price": 0 }
{ "title": "Linux架构师", "New_Price": 0 }
{ "title": "Python自动化运维", "New_Price": 0 }
{ "title": "Python全栈", "New_Price": 0 }
{ "title": "Golang全栈", "New_Price": 0 }
{ "title": "AWS架构师", "New_Price": 0 }
3.9 多表关联-lookup
MongoDB很难像关系型数据库一样擅长多表关联,MongoDB提供了$lookup
来实现多表关联
模拟数据:比如我们有一个product表和一个orders表,我们orders集合中的文档通过pid
关联到对应的product文档的_id
字段
db.product.insert({_id: 1, name: ‘商品1‘, price: 15})
db.product.insert({_id: 2, name: ‘商品2‘, price: 23})
db.orders.insert({_id: 1, pid: 1, name: ‘订单1‘})
db.orders.insert({_id: 2, pid: 2, name: ‘订单2‘})
db.orders.insert({_id: 3, pid: 2, name: ‘订单3‘})
db.orders.insert({_id: 4, pid: 1, name: ‘订单4‘})
db.product.find()
db.orders.find()
1.在orders表中,找到price > 20的订单
1)我们orders表中是没有field的,第一步应该执行:
db.product.aggregate([
{
$lookup:
{
from: "orders",
localField: "_id",
foreignField: "pid",
as: "inventory_docs"
}
}
])
返回结果:
{ "_id": 1, "name": "商品1", "price": 15, "inventory_docs": [ { "_id": 1, "pid": 1, "name": "订单1" }, { "_id": 4, "pid": 1, "name": "订单4" } ] }
{ "_id": 2, "name": "商品2", "price": 23, "inventory_docs": [ { "_id": 2, "pid": 2, "name": "订单2" }, { "_id": 3, "pid": 2, "name": "订单3" } ] }
简单介绍$lookup
中的参数:
form:需要关联的表(orders)
localField:orders被product的关联的键
foreignField:orders和product有关联的键
as:对应的外键集合数据(可能存在一对多的情况)
2)$match筛选
db.product.aggregate([
{
$lookup:
{
from: "orders",
localField: "_id",
foreignField: "pid",
as: "inventory_docs"
}
},
{ $match: {price: {$gt: 20 } }}
])
返回结果:
{ "_id": 2, "name": "商品2", "price": 23, "inventory_docs": [ { "_id": 2, "pid": 2, "name": "订单2" }, { "_id": 3, "pid": 2, "name": "订单3" } ] }
3)$project挑选字段
我们只需要inventory_docs
字段即可
db.product.aggregate([
{
$lookup:
{
from: "orders",
localField: "_id",
foreignField: "pid",
as: "inventory_docs"
}
},
{ $match: {price: {$gt: 20 } }},
{ $project: {"inventory_docs": 1, "_id": 0} }
])
返回结果:
{ "inventory_docs": [ { "_id": 2, "pid": 2, "name": "订单2" }, { "_id": 3, "pid": 2, "name": "订单3" } ] }