从mongodb 3.6开始支持为$lookup操作的来源表指定多个查询条件。实现类似如下sql的效果:
SELECT a.x1,a.x2...,a.xn, b.x1,b.x2...b.xn
FROM a
WHERE a.x1 IN (SELECT b.x1,b.x2...b.xn
FROM b
WHERE a.x1 = b.x1
AND b.x2 = 'xxxx' );
业务场景:zw_reboot_result和deterioration_task_sample两张表,需要zw_reboot_result表关联deterioration_task_sample表指定task_id的记录中PPPOEUser相同的记录,由于deterioration_task_sample表有大量不同task_id的记录,采用先全量lookup再再next stage中过滤的方式会导致在lookup过程中每条记录关联的数组较大,故采用mongodb中Specify Multiple Join Conditions with $lookup的操作特性。
代码如下:
db.getCollection('zw_reboot_result').aggregate(
[
{
"$match":{
"task_id":146
}
},
{
"$lookup":{
"from":"deterioration_task_sample",
//设置zw_reboot_result表中变量名,不可与deterioration_task_sample表中同名
"let":{
"zwPPPOEUser":"$PPPOEUser"
},
"pipeline":[
{
"$match":{
//条件一
"$expr":{
"$and":[
{
"$eq":[
"$PPPOEUser",
"$$zwPPPOEUser"
]
}
]
},
//条件二
"task_id":146
}
},
{
"$project":{
"is_deter_osgi":1,
"county_code":1,
"area_code":1,
"reboot_status":1,
"sn":1,
"fail_reason":1,
"install_addr":1,
"is_delete":1
}
}
],
//生成关联结果集别名
"as":"basicInfo"
}
},
{
"$unwind":{
"path":"$basicInfo",
"preserveNullAndEmptyArrays":true
}
},
{
"$project":{
"basicInfoManufacturer":"$basicInfo.manufacturer",
"basicInfoModel":"$basicInfo.model",
"task_id":1,
"mac":1,
"manufacturer":1,
"model":1,
"failure_reason":1,
"is_reboot_succ":1,
"send_time":1,
"receive_time":1,
"PPPOEUser":1,
"sn":"$basicInfo.sn",
"area_code":"$basicInfo.area_code",
"county_code":"$basicInfo.county_code",
"install_addr":"$basicInfo.install_addr",
"reboot_status":"$basicInfo.reboot_status",
"fail_reason":"$basicInfo.fail_reason",
"is_deter_osgi":"$basicInfo.is_deter_osgi",
"is_delete":"$basicInfo.is_delete"
}
},
{
"$match":{
"is_delete":0
}
}
]
)
Spring boot未提供$lookup pipeline抽象,需要通过原生的方式进行json的组装和拼接。