$lookup阶段子管道MongoDB中具有不同运算符的多个JOIN条件

Question

我想把下面用postgresql编写的查询写到MongoDB：

SELECT * FROM t1 
LEFT JOIN t2 ON (t1.age = t2.old AND t1.name = t2.alias OR t1.age = 24);

上面的查询有"AND"one_answers"OR"操作。

具有相同运算符和多个联接条件的查询在MongoDB中具有等效语法。

Postgresql:

SELECT * FROM t1 
LEFT JOIN t2 ON (t1.age = t2.old AND t1.name = t2.alias AND t1.age = 24);

MongoDB：

db.t1.aggregate([ {$lookup: { 
from: "t2",
let: {age_field: "$age", name_field: "$name"},
pipeline: [ { $match:{ $expr:{ $and:[
{$eq: [ "$old",  "$$age_field" ]},
{$eq: [ "$alias", "$$name_field"]},
{$eq: [ 24, "$$age_field"]}
]}}}
], 
as: "joined_result" }},
{$unwind: {path: "$joined_result", preserveNullAndEmptyArrays: true}}
])

我尝试了以下由AND和OR组成的查询语法，但这些语法并没有按预期工作。请建议正确的语法：

1. Used Multiple $match stages:
db.t1.aggregate([ {$lookup: { from: "t2", let: {age_field: "$age", name_field: "$name"},
pipeline: [
{ $match:{ $expr:{ $and:[{ $eq: [ 25,  "$$age_field" ] },{ $eq: [ "arun", "$$name_field" ] }]}} }
,{ $match:{ $expr:{ $or:[{ $eq: [ 24,  "$$age_field" ] }]}} }
],

as: "joined_result" }},
{$unwind: {path: "$joined_result",
preserveNullAndEmptyArrays: true}},
])   
2. OR is part of AND:
db.t1.aggregate([ {$lookup: {  from: "t2",
let: {age_field: "$age", name_field: "$name"},
pipeline:[{ $match:{ $expr: [ {$and:[
{$eq:["$old","$$age_field" ]},
{$eq:["$alias","$$name_field"]},
{$or: {$eq: [24, "$$age_field"]}}
]}
]  }}
] ,
as: "joined_result" }},
{$unwind: {path: "$joined_result",
preserveNullAndEmptyArrays: true}},
])

3. Array of expression:
db.t1.aggregate([ {$lookup: {  from: "t2",
let: {age_field: "$age", name_field: "$name"},
pipeline:[{ $match:{ $expr:[
{$and:[{$eq:["$old","$$age_field" ]},
{$eq: ["$alias","$$name_field"]}]},
{$or: {$eq: [24, "$old"]}}]}}
],
as: "joined_result" }},
{$unwind: {path: "$joined_result",
preserveNullAndEmptyArrays: true}},
])

Answer 1

查询条件

(t1.age=t2.old AND t1.name=t2.alias OR t1.age=24(

与相同

((t1.age=t2.old AND t1.name=t2.alias(OR t1.age=24(

SQL AND运算符的优先级高于OR运算符。

然后$lookup管道的$match阶段可以构造如下：

db.t1.aggregate([ {$lookup: { 
from: "t2",
let: {age_field: "$age", name_field: "$name"},
pipeline: [ { $match:{ $expr:{ 
$or: [
{ $and: [
{ $eq: [ "$old",  "$$age_field" ] },
{ $eq: [ "$alias", "$$name_field" ]}
]},
{ $eq: [ 24, "$$age_field"]}
]
}
}
}],
as: "joined_result"
}},
{$unwind: {path: "$joined_result", preserveNullAndEmptyArrays: true}}
])