Hi @slava and experts,
I am using MongoDB 4.2.
I want to write below query written in postgresql to MongoDB:
SELECT * FROM t1 LEFT JOIN t2 ON (t1.age = t2.old AND t1.name = t2.alias OR t1.age = 24);
The above query has ‘AND’ and ‘OR’ operation.
The query below which has same operator with multiple join condition has equivalent syntax in 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}}
])
But I am looking for syntax for below query which has $AND and $OR operators
SELECT * FROM t1 LEFT JOIN t2 ON (t1.age = t2.old AND t1.name = t2.alias OR t1.age = 24);
I tried some syntaxes as below but unfortunately those are NOT working as expected:
-
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}}, ])
-
$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}}, ])
-
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}}, ])
It would be great help …!!
Thanks in advance.