使用”$literal”来避免解析字符串,直接当成字符串本身,而不是解析变量或者投影。
“$eq”: [“$tenant_virtual_account”, {“$literal”:”$-v2131231asdadsad2sadaasdasdass3″}]
谢谢指点,已经解决了
谢谢您的指点,按照您的方法解决了
当第二个lookup进行关联的时候,第二个关联的表的外键为第一个关联所查出的role_id的时候,以下面方式这样写无法生效(businessRolePermission.role_id指明外键),即查不出结果,但是以以前不采用pipeline方法以同样的businessRolePermission.role_id形式却可以查出,这是什么原因呢,一直没搞明白。
db.t_application_business.aggregate(
[{
“$lookup”: {
“from”: “t_user_application_business_role_permission”,
“let”: {
“main_business_id”: “$business_id”
},
“pipeline”: [{
“$match”: {
“$expr”: {
“$and”: [{
“$eq”: [“$business_id”, “$$main_business_id”]
}, {
“$eq”: [“$tenant_virtual_account”, “$ – v2131231asdadsad2sadaasdasdass3”]
}, {
“$eq”: [“$status”, 0]
}, {
“$eq”: [“$delete_flag”, 0]
}]
}
}
}, {
“$project”: {
“tenant_virtual_account”: 1,
“role_id”: 1,
“application_id”: 1,
“create_time”: 1,
“status”: 1
}
}],
“as”: “businessRolePermission”
}
}, {
“$lookup”: {
“from”: “t_business_role”,
“let”: {
“main_role_id”: “$businessRolePermission.role_id”
},
“pipeline”: [{
“$match”: {
“$expr”: {
“$and”: [{
“$eq”: [“$role_id”, “$$main_role_id”]
}]
}
}
}],
“as”: “applicationBusinessRole”
}
}, {
“$match”: {
“seatBusinessRolePermission”: {
“$ne”: []
},
“seat_application_id”: {
“$in”: [“63299bd172f24fe18924598cd5695a1a”]
},
“status”: 0,
“delete_flag”: {
“$in”: [0]
}
}
}, {
“$project”: {
“business_name”: 1,
“business_id”: 1,
“businessRolePermission”: 1,
“applicationBusinessRole”: 1,
“create_time”: 1
}
}, {
“$sort”: {
“businessRolePermission.create_time”: 1
}
}, {
“$skip”: 0
}, {
“$limit”: 20
}]
)
当在$lookup连续使用pipeline的时候,即第三个表根据第二个表查出的role_id进行关联的时候,无法关联上,查询脚本如下
db.t_application_business.aggregate(
[{
“$lookup”: {
“from”: “t_user_application_business_role_permission”,
“let”: {
“main_business_id”: “$business_id”
},
“pipeline”: [{
“$match”: {
“$expr”: {
“$and”: [{
“$eq”: [“$business_id”, “$$main_business_id”]
}, {
“$eq”: [“$tenant_virtual_account”, {
“$literal”: “$vtl-2b2cfb7272f94bc590bc81245a39e56d@75c8a797226046bfa303a1baedb84275#cmft.com”
}]
}, {
“$eq”: [“$status”, 0]
}, {
“$eq”: [“$delete_flag”, 0]
}]
}
}
}, {
“$project”: {
“tenant_virtual_account”: 1,
“role_id”: 1,
“application_id”: 1,
“create_time”: 1,
“status”: 1
}
}],
“as”: “businessRolePermission”
}
}, {
“$lookup”: {
“from”: “t_business_role”,
“let”: {
“main_role_id”: “$businessRolePermission.role_id”
},
“pipeline”: [{
“$match”: {
“$expr”: {
“$and”: [{
“$eq”: [“$role_id”, “$$main_role_id”]
}]
}
}
}],
“as”: “applicationBusinessRole”
}
}, {
“$match”: {
“seatBusinessRolePermission”: {
“$ne”: []
},
“seat_application_id”: {
“$in”: [“63299bd172f24fe18924598cd5695a1a”]
},
“status”: 0,
“delete_flag”: {
“$in”: [0]
}
}
}, {
“$project”: {
“business_name”: 1,
“business_id”: 1,
“businessRolePermission”: 1,
“applicationBusinessRole”: 1,
“create_time”: 1
}
}, {
“$sort”: {
“businessRolePermission.create_time”: 1
}
}, {
“$skip”: 0
}, {
“$limit”: 20
}]
)
比如下面这个例子在expr表达式,and里面第二个$eq,我想表达,我的列tenant_virtual_account等于这个id“$-v2131231asdadsad2sadaasdasdass3”,但是由于$是特殊字符,在程序中会被任务这是一个名为“-v2131231asdadsad2sadaasdasdass3”的列,这就与我所需要的不相符合了。
db.t_application_business.aggregate(
[{
“$lookup”: {
“from”: “t_user_application_business_role_permission”,
“let”: {
“main_business_id”: “$business_id”
},
“pipeline”: [{
“$match”: {
“$expr”: {
“$and”: [{
“$eq”: [“$business_id”, “$$main_business_id”]
}, {
“$eq”: [“$tenant_virtual_account”, “$-v2131231asdadsad2sadaasdasdass3”]
}, {
“$eq”: [“$status”, 0]
}, {
“$eq”: [“$delete_flag”, 0]
}]
}
}
}, {
“$project”: {
“tenant_virtual_account”: 1,
“role_id”: 1,
“application_id”: 1,
“create_time”: 1,
“status”: 1
}
}],
“as”: “businessRolePermission”
}
}, {
“$lookup”: {
“from”: “t_business_role”,
“localField”: “businessRolePermission.role_id”,
“foreignField”: “role_id”,
“as”: “applicationBusinessRole”
}
}, {
“$match”: {
“businessRolePermission”: {
“$ne”: []
},
“application_id”: {
“$in”: [“63299bd172f24fe18924598cd5695a1a”]
},
“status”: 0,
“delete_flag”: {
“$in”: [0]
}
}
}, {
“$project”: {
“business_name”: 1,
“business_id”: 1,
“businessRolePermission”: 1,
“applicationBusinessRole”: 1,
“create_time”: 1
}
}, {
“$sort”: {
“businessRolePermission.create_time”: 1
}
}, {
“$skip”: 0
}, {
“$limit”: 20
}]
)