MongoDB中的聚合管道($lookup多表关联查询、$unwind、$match、$project)
你越是认真生活,你的生活就会越美好
——弗兰克·劳埃德·莱特
《人生果实》经典语录
管道的概念
管道
在Unix和Linux中一般用于将当前命令的输出结果
作为下一个命令的参数
。
MongoDB
的聚合管道
将MongoDB文档在一个管道处理完毕后
将结果传递给下一个管道
处理。管道操作是可以重复的。
聚合框架
MongoDB
中聚合(aggregate
)主要用于处理数据
(诸如统计平均值,求和等),并返回计算后的数据结果。
聚合框架
是MongoDB的高级查询语言
,它允许我们通过转换和合并多个文档中的数据
来生成新的单个文档中不存在的信息
。
聚合管道操作主要包含下面几个部分:
命令 | 功能描述 |
$project | 指定输出文档里的字段. |
$match | 选择要处理的文档,与fine()类似。 |
$limit | 限制传递给下一步的文档数量。 |
$skip | 跳过一定数量的文档。 |
$unwind | 扩展数组,为每个数组入口生成一个输出文档。 |
$group | 根据key来分组文档。 |
$sort | 排序文档。 |
$geoNear | 选择某个地理位置附近的的文档。 |
$out | 把管道的结果写入某个集合。 |
$redact | 控制特定数据的访问。 |
$lookup | 多表关联(3.2版本新增) |
$lookup的功能及语法
主要功能
是将每个输入待处理的文档,经过$lookup
阶段的处理,输出的新文档中会包含一个新生成的数组列
(户名可根据需要命名新key的名字 )。
数组列
存放的数据是来自被Join 集合的适配文档
,如果没有,集合为空(即 为[ ]
)
基本语法
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}
参数说明
- from:同一个数据库下等待被Join的集合。
- localField: 源集合中的match值,如果输入的集合中,某文档没有 localField,这个Key(Field),在处理的过程中,会默认为此文档含有 localField:null的键值对。
- foreignField:待Join的集合的match值,如果待Join的集合中,文档没有foreignField值,在处理的过程中,会默认为此文档含有 foreignField:null的键值对。
- as:为输出文档的新增值命名。如果输入的集合中已存在该值,则会覆盖掉
例子
假设 有 订单集合, 存储的测试数据 如下:
db.orders.insert(
[
{
"id": 1,
"item": "almonds",
"price": 12,
"quantity": 2
},
{
"id": 2,
"item": "pecans",
"price": 20,
"quantity": 1
},
{
"id": 3
}
]
)
其中item
对应 数据为 商品名称。
另外 一个 就是就是 商品库存集合 ,存储的测试数据 如下:
db.inventory.insert([
{
"id": 1,
"sku": "almonds",
description: "product 1",
"instock": 120
},
{
"id": 2,
"sku": "bread",
description: "product 2",
"instock": 80
},
{
"id": 3,
"sku": "cashews",
description: "product 3",
"instock": 60
},
{
"id": 4,
"sku": "pecans",
description: "product 4",
"instock": 70
},
{
"id": 5,
"sku": null,
description: "Incomplete"
},
{
"id": 6
}
])
此集合中的sku
数据等同于 订单 集合中的 商品名称。
在这种模式设计下,如果要查询订单表对应商品的库存情况
,应如何写代码呢?
很明显这需要两个集合Join
。
实现语句如下
db.getCollection('orders').aggregate([
{
$lookup:
{
from: "inventory",
localField: "item",
foreignField: "sku",
as: "inventory_docs"
}
}
])
返回的执行结果如下:
分析查询语句和结果,回扣$lookup
定义,可以将上面的处理过程,描述如下:
从集合order
中逐个获取文档处理,拿到一个文档后,会根据localField
值 遍历
被 Join的 inventory集合
(from: “inventory”),看inventory集合文档
中 foreignField
值是否与之相等
。
如果相等,就把符合条件的inventory文档
整体内嵌到聚合框架新生成的文档中
,并且新key 统一命名为 inventory_docs
。
考虑到符合条件的文档不唯一
,这个Key对应的Value是个数组形式
。
原集合中Key
对应的值为Null值
或不存在
时,需特别小心。
说明
在以下的说明中,为描述方便,将
from对应的集合
定义为 被join集合
;
待聚合的表
成为源表
;
将localField
和foreignField
对应的Key 定义为比较列
。
-
这个示例中,一共输出了三个文档,在没有再次聚合(
$match
)的条件下,这个输出文档数量
是以输入文档的数量
来决定的(由order来决定),而不是以被Join的集合(inventory)文档数量决定。 -
在此需要
特别强调
的是输出的第三个文档。在源库中原文档没有要比较的列(即item值不存在,既不是Null值,也不是值为空
),此时 和被Join 集合
比较,如果被Join集合中 比较列 也恰好 为NUll 或 不存在的值,此时,判断相等
,即会把 被Join集合中 比较列 为NUll 或 值不存在 文档 吸收进来。 -
假设 源表(order) 中比较列 为某一个值,而此值在待比较表(inventory)的所有文档中都不存在,那么查询结果会是什么样子呢?
order 集合
在现有数据的基础上,再被insert
进一笔测试数据,这个订单的商品为Start
,在库存商品中根本没有此数据。
db.orders.insert({
"id": 4,
"item": "Start",
"price": 2000,
"quantity": 1
})
order集合的文档数量由之前的3个增长为4个。
再次执行查询
db.getCollection('orders').aggregate([
{
$lookup:
{
from: "inventory",
localField: "item",
foreignField: "sku",
as: "inventory_docs"
}
}
])
此时查看结果
查询出的结果也由之前的3个变成了4个
。比较特别的是第四个文档 ,其新增列 为 "inventory_docs" : [ ] ,即值为空
。所以,此时,实现的功能非常像关系型数据库的 left join。
那么,可不可以只筛选出新增列为空的文档
呢?
即我们查询出,比较列的条件下,筛选出只在A集合中,而不在集合B的文档
呢? 就像关系数据库中量表Join的 left join on a.key =b.key where b.key is null .
MongoDB的聚合管道将MongoDB文档在一个管道处理完毕后将结果传递给下一个管道处理。管道操作是可以重复的。
实现如下
再次聚合一下就可以了,来一次$match
就可以了。
执行的语句调整一下就OK了。
db.getCollection('orders').aggregate([
{
$lookup:
{
from: "inventory",
localField: "item",
foreignField: "sku",
as: "inventory_docs"
}
},
{ $match : {"inventory_docs" : [ ]} }
])
执行结果如下
可以看出执行结果只有一个文档。这个文档表明的含义是:订单中有这个商品,但是库存中没有这个商品。
($lookup
只是聚合框架的一个stage
,在其前前后后,都可以嵌入到其他的聚合管道的命令,例如$match.$group
等。下面的说明,也可以说明一二)
- 以上的比较列都是单一的
Key/Value
,如果复杂一点,如果比较的列是数组,我们又该如何关联呢?
我们接下来再来测试一把。将之前集合order 、inventory
插入的数据清空。
插入此场景下的新数据,向集合order
中插入的数据,如下:
db.orders.insert(
{
"id" : 1,
"item" : "MON1003",
"price" : 350,
"quantity" : 2,
"specs" :[ "27 inch", "Retina display", "1920x1080" ],
"type" : "Monitor"
}
)
specs
对应的value是数组格式
。
向集合inventory
新插入的数据 如下:
db.inventory.insert({ "id" : 1, "sku" : "MON1003", "type" : "Monitor", "instock" : 120,"size" : "27 inch", "resolution" : "1920x1080" })
db.inventory.insert({ "id" : 2, "sku" : "MON1012", "type" : "Monitor", "instock" : 85,"size" : "23 inch", "resolution" : "1280x800" })
db.inventory.insert({ "id" : 3, "sku" : "MON1031", "type" : "Monitor", "instock" : 60,"size" : "23 inch", "display_type" : "LED" })
查询的语句如下:
db.getCollection('orders').aggregate([
{
$unwind: "$specs"
}
])
结果如下
db.getCollection('orders').aggregate([
{
$unwind: "$specs"
},
{
$lookup:
{
from: "inventory",
localField: "specs",
foreignField: "size",
as: "inventory_docs"
}
}
])
查询显示结果如下:
输出文档中的specs 对应的数据变成了字符串类型(原集合为数组)。 原因是 $unwind
{
$unwind: "$specs"
}
上面的结果前提下,只查询specs为"27 inch"的数据
db.getCollection('orders').aggregate([
{
$unwind: "$specs"
},
{
$lookup:
{
from: "inventory",
localField: "specs",
foreignField: "size",
as: "inventory_docs"
}
},
{
$match:{"specs": "27 inch"}
}
])
结果如下
再上面的前提下,只输出item,inventory_docs字段
db.getCollection('orders').aggregate([
{
$unwind: "$specs"
},
{
$lookup:
{
from: "inventory",
localField: "specs",
foreignField: "size",
as: "inventory_docs"
}
},
{
$match:{"specs": "27 inch"}
},
{
$project: {item:1, inventory_docs:1}
}
])
结果如下
最后一道题,在SQL中两表关联,每个表都有条件,那么在MongoDB中应该如何书写呢?
db.Rel_QQDetails.aggregate([
{ $match: {
ReconciliationId:CSUUID("bb54bee7-187f-4d38-85d7-88926000ac7a")
}
},
{ $lookup:
{
from: "Fct_QQStatements",
localField: "OrderId",
foreignField: "OrderStatementsId",
as: "inventory_docs"
}
},
{ $match : {
"inventory_docs.StatementsPriceException" :false
}
}
])
附加题,mongodb 集合间关联后更新,在MongoDB中应该如何书写呢?----借助 forEach 功能
由于篇幅限制(偷懒)
集合中的数据格式不再说明。
需求:集合QQ_OrderReturn 和 RelQQ_ReconciliationDetails 关联刷选,刷选符合条件,在更新QQ_OrderReturn的数据。
db.QQ_OrderReturn.aggregate([
{$match:{"Status" : 21}},
{$match:{"Disabled" : 0}},
{$match:{"JoinResponParty" : "合作方"}},
{$match:{ SupplierSellerName:"(合作营)ABC阳澄湖蟹"}},
{
$lookup:
{
from: "RelQQ_ReconciliationDetails",
localField: "OrderReturnId",
foreignField: "OrderId",
as: "inventory_docs"
}
},
{ $match : {"inventory_docs" : [ ]} }
]).forEach(function(item){
db.QQ_OrderReturn.update({"id":item.id},{$set:{"Status":NumberInt(0)}})
})
$unwind的功能及语法
$unwind:将文档中的某一个数组类型字段拆分成多条,每条包含数组中的一个值。
例子
db.orders.insert(
{
"id" : 1,
"item" : "MON1003",
"price" : 350,
"quantity" : 2,
"specs" :[ "27 inch", "Retina display", "1920x1080" ],
"type" : "Monitor"
}
)
对specs数组进行拆分:
db.orders.aggregate([
{$unwind:"$specs"}
])
拆分结果:
/* 1 */
{
"id" : 1,
"item" : "MON1003",
"price" : 350,
"quantity" : 2,
"specs" : "27 inch",
"type" : "Monitor"
}
/* 2 */
{
"id" : 1,
"item" : "MON1003",
"price" : 350,
"quantity" : 2,
"specs" :"Retina display",
"type" : "Monitor"
}
/* 3 */
{
"id" : 1,
"item" : "MON1003",
"price" : 350,
"quantity" : 2,
"specs" :"1920x1080",
"type" : "Monitor"
}
使用$unwind
可以将specs数组
中的每个数据都被分解成一个文档,并且除了specs
的值不同外,其他的值都是相同的.
评论区提问解答
问题一:
在表A中有一个字段,类型为数组,里面存放的表B的ID字段。所以一条表A的数据对应复数条表B的数据。现在我每次只查询单条表A的数据,想通过$unwind和$lookup
关联查询,但返回的结果是一个数组,数组里面除了插入的表B的数据不同之外其他的数据都是重复的。
我希望达成的效果
是最终只返回一条表A的数据,这条数据中的那个数组里是我查询并插入的所有表B的数据,这样结果就不会有太多的重复,但我不知道该如何实现这样的效果?
问题二
第二个问题是,使用$lookup
后,会将子表的所有数据全都插入到主表中,但我只希望获取子表中特定的某几项数据,其余全都不查询,或是不显示。但$project
好像只对主表有用,不能删选掉插入的子表中的字段,想请教下应该如何才能在聚合管道里删选掉子表里的特定字段?
解决思路
因为Mongo操作我也不熟悉,就我目前对Mongo操作的了解得到的 解决思路如下,熟悉聚合管道相关的操作后,只要能从表里拿到数据,不管数据是否有多余的,想怎么改造,交给js实现,比较灵活
下面是问题涉及到的聚合管道操作实例,可以看看
希望可以帮到你
// 表a
db.a.insert(
{
a1: 'hello',
a2: 'world',
a3: '!',
a4: ['001','002', '003']
}
)
// 表b
db.b.insert(
[
{
id: '001',
b1: 'do',
b2: 'better',
b3: '!',
},
{
id: '002',
b1: 'do',
b2: 'better',
b3: '!',
},
{
id: '003',
b1: 'do',
b2: 'better',
b3: '!',
}
]
)
执行下面命令
db.getCollection('a').aggregate([
{$unwind: "$a4"}
])
执行下面查询得到
db.getCollection('a').aggregate([
{$unwind: "$a4"}, // 这一行处理后的结果 给一行处理
{$match : {"a4" : "002"}}
])
执行下面的查询得到
db.getCollection('a').aggregate([
{$unwind: "$a4"}, // 这一行处理后的结果 给一行处理
{$match : {"a4" : "002"}}, // 这一行处理后的结果 给一行处理
{$project: {a2:1}}
])
执行下面的查询得到
db.getCollection('a').aggregate([
{$unwind: "$a4"},
{
$lookup:
{
from: "b",
localField: "a4",
foreignField: "id",
as: "new"
}
}
])
谢谢你阅读到了最后~
期待你关注、收藏、评论、点赞~
让我们一起 变得更强