1. 语法分析
db.<collection>.aggregate(
[{
$lookup:
{
//同一数据库下等待被Join的集合
from: <collection to join>,
//源集合中的match值，如果在输入的集合中，某文档没有localField这个key（Field），在处理的过程中，会默认为此文档含有localField:null的键值对
localField: <field from the input documents>,
//待join的集合的match值，如果待Join的集合中，文档没有foreignField的值，在处理的过程中，会默认为此文档含有foreignField：null的键值对
foreignField: <field from the documents of the "from" collection>,
//为输出文档新增值命名，如果输入的集合中已经存在该值，则会被覆盖掉。
as: <output array field>
}
}]
)
该功能可以参考sql中的join操作。
<collection>：可以看做基表。
<collection to join>：可以看着连接表。
localField：基表的某字段。
foreignField：连接表的某字段。
as：若两字段相同，则将连接表作为基表数组输出。
2. 案例分析
$lookup的作用是将两个collection以某个条件进行合并。
- 订单集合，存储的数据如下：
注：item为商品名称
db.orders.insert([
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
{ "_id" : 3 },
{"_id" : 4, "item" : "Start", "price" : 2000, "quantity" : 1 }
])
- 商品库存集合，存储的数据如下：
注：sku字段为商品名称。
db.inventory2.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 }
])
需求：查看订单集合中商品的库存。
注意：库存和订单的关系时1：n，即多个订单对应着一个库存，一个库存对应多个订单。
db.orders.aggregate([
{
$lookup:{
from:"inventory2", //同一个数据库等待被join的集合
localField:"item", //源文件的match值。
foreignField:"sku", //待join集合的match值。
as:"inventory_docs" //输出文档新增值命名
}
}
]);
输出结果
/* 1 */
{
"_id" : 1,
"item" : "almonds",
"price" : 12,
"quantity" : 2,
"inventory_docs" : [{
"_id" : 1,
"sku" : "almonds",
"description" : "product 1",
"instock" : 120
}]
}
/* 2 */
{
"_id" : 2,
"item" : "pecans",
"price" : 20,
"quantity" : 1,
"inventory_docs" : [{
"_id" : 4,
"sku" : "pecans",
"description" : "product 4",
"instock" : 70
}]
}
/* 3 */
{
"_id" : 3,
"inventory_docs" : [{
"_id" : 5,
"sku" : null,
"description" : "Incomplete"
}, {
"_id" : 6
}]
}
/* 4 */
{
"_id" : 4,
"item" : "Start",
"price" : 2000,
"quantity" : 1,
"inventory_docs" : []
}
从集合order中逐个获取文档处理，拿到一个文档后，会根据localField值遍历被Join的inventory集合。判断inventory集合文档中的foreignField值是否与之相等。如果相等，则把符合条件的inventory文档整体内嵌到聚合框架新生成的文档中，并且新key统一命名为inventory_docs，考虑到符合条件的文档不唯一，这个key对用的value是一个数组的形式。
需要注意的是：若文档中不存在localField引用的key，在处理的过程中，会默认文档中key:null。具体可以看结果中_id:3的输出。
原集合中的key对应的值为null或者不存在时，需要特别小心。
3. 注意事项
为了描述方便：将待聚合的表称为基表、from的表称为连接表、localField和foreignField对应的Key定位为比较列。
-
$lookup在没有聚合$match的条件下，输出文档的数量是基表文档数量。 - 基表（orders）中
"_id" : 3中不含有localField列，默认会认为此文档含有localField:null的键值对。会与连接表foreignField:null进行连接。最终形成了输出的第三个文档。 - 基表（orders）中
"_id" : 4比较列的一个值，在连接表的所有文档下都不存在，那么得到的数组为[]。
4. 练习
案例1：寻找订单表存在而库存表不存在的文档
{
$lookup:{
from:"inventory2", //同一个数据库等待被join的集合
localField:"item", //源文件的match值。
foreignField:"sku", //待join集合的match值。
as:"inventory_docs" //输出文档新增值命名
}
},
{
$match:{"inventory_docs":[]}
}
]);
执行结果为：
/* 1 */
{
"_id" : 4,
"item" : "Start",
"price" : 2000,
"quantity" : 1,
"inventory_docs" : []
}
案例二：连接的是数组
订单集合中的数据为数组。
db.orders.remove({})
//导入数据
db.orders.insert({ "_id" : 1, "item" : "MON1003", "price" : 350, "quantity" : 2, "specs" :[ "27 inch", "Retina display", "1920x1080" ], "type" : "Monitor" })
db.inventory3.insert([
{ "_id" : 1, "sku" : "MON1003", "type" : "Monitor", "instock" : 120,"size" : "27 inch", "resolution" : "1920x1080" },
{ "_id" : 2, "sku" : "MON1012", "type" : "Monitor", "instock" : 85,"size" : "23 inch", "resolution" : "1280x800" },
{ "_id" : 3, "sku" : "MON1031", "type" : "Monitor", "instock" : 60,"size" : "23 inch", "display_type" : "LED" }
])
命令：
db.orders.aggregate([
{
$unwind:"$specs"
},
{
$lookup:{
from:"inventory3",
localField:"specs",
foreignField:"size",
as:"inventory_docs"
}
},
])
使用unwind将基表中的数组进行拆分，实际上将基表拆分为3条数据，每条数据在和连接表进行连接，最终得到的数据：
/* 1 */
{
"_id" : 1,
"item" : "MON1003",
"price" : 350,
"quantity" : 2,
"specs" : "27 inch",
"type" : "Monitor",
"inventory_docs" : [{
"_id" : 1,
"sku" : "MON1003",
"type" : "Monitor",
"instock" : 120,
"size" : "27 inch",
"resolution" : "1920x1080"
}]
}
/* 2 */
{
"_id" : 1,
"item" : "MON1003",
"price" : 350,
"quantity" : 2,
"specs" : "Retina display",
"type" : "Monitor",
"inventory_docs" : []
}
/* 3 */
{
"_id" : 1,
"item" : "MON1003",
"price" : 350,
"quantity" : 2,
"specs" : "1920x1080",
"type" : "Monitor",
"inventory_docs" : []
}
案例三：进行两表连接时，若每个表均存在条件
SELECT * FROM dbo.Rel_QQDetails AS D
JOIN dbo.Fct_QQStatements AS S ON D.OrderId=s.OrderStatementsId
WHERE D.ReconciliationId='te54test7-187e-4e38-85e7-88926000aa7a'
AND S.StatementsPriceException='false'
db.Rel_QQDetails.aggregate([
{ $match: {ReconciliationId:CSUUID("bb54bee7-187f-4d38-85d7-88926000ac7a")}}, //基表进行过滤
{ $lookup:
{
from: "Fct_QQStatements", //连接表
localField: "OrderId", //基表OrderId字段
foreignField: "OrderStatementsId", //连接表OrderStatementsId字段
as: "inventory_docs"
}
},
{ $match : {"inventory_docs.StatementsPriceException" :false} } //连接表进行过滤
])
案例四：数组+多条件查询
db.county.insert([
{
"Station_Id_C": "56172",
"name:" : "马尔康"
},
{
"Station_Id_C": "56180",
"name:" : "茂县"
}
]);
db.temp.insert([
{
"Station_Id_C": "56172",
"Mon:": 6,
"Day": 26,
"Temp": 11,
},
{
"Station_Id_C": "56172",
"Mon:": 6,
"Day": 27,
"Temp": 11,
},
{
"Station_Id_C": "56180",
"Mon:": 6,
"Day": 26,
"Temp": 15,
},
{
"Station_Id_C": "56180",
"Mon:": 6,
"Day": 17,
"Temp": 11,
}
]);
代码：
image.png
关键原因只是对文档数组的匹配，文档数组中一个元素满足所有条件或多个元素满足部分条件但加起来满足所有条件均会输出整个数组。这就是为什么连接表条件不生效的原因。
正确代码
db.county.aggregate([
{
$match:{"Station_Id_C":"56172"}
},
{
$lookup:{
from:"temp",
localField:"Station_Id_C",
foreignField:"Station_Id_C",
as:"docs"
}
},
{
$unwind:"$docs"
},
{
$match:{"docs.Day":27}
}
])
{
"_id" : ObjectId("5e6b56461ec58cc4b94b7a99"),
"Station_Id_C" : "56172",
"name:" : "马尔康",
"docs" : {
"_id" : ObjectId("5e6b56531ec58cc4b94b7a9c"),
"Station_Id_C" : "56172",
"Mon:" : 6,
"Day" : 27,
"Temp" : 11
}
}
