Multiple JOIN conditions with different operators in $lookup stage sub-pipeline

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:

  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}},
          ])
    

It would be great help …!!

Thanks in advance.

Hello @Vaibhav_Dalvi, welcome to the MongoDB Community forum!

The query condition

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

is the same as:

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

Note the SQL AND operator has precedence over the OR operator.

Then the $lookup pipeline’s $match stage can be constructed as follows:

 $expr:{ 
	 $or:[
		 { $and: [ 
			   { $eq: [ "$old",  "$$age_field" ] },
			   { $eq: [ "$alias", "$$name_field" ] }
		 ] },
		 { $eq: [ 24, "$$age_field" ] }
	 ]
 }
1 Like

Interesting…This works.

Thanks @Prasad_Saya for quick response.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.