How do I use SQL kind join on Case Condition success query execution in MongoDB?

Working with Data
, ,
1

I have a use case where I need to join two collections in MongoDB but the join must happen only if one of the two conditions are met.
The SQL equivalent query is given below. How can I translate this in MongoDB?

SELECT *
FROM MainTble i
Left JOIN SecondTable p
ON CASE
WHEN i.myPropert="SMS" and i.phoneNumber = p.phoneNumber THEN 1
WHEN i.myProperty="Email" and i.email = p.email THEN 1
ELSE 0
END = 1
2

@MWD_Wajih_N_A ,

The following pipline does the trick:

db. MainTble.aggregate([{$lookup: {
  from: 'SecondTable',
  let: {"phone" : "$phoneNumber", "email" : "$email" , "property" : "$myProperty"},
  pipeline : [{ $match: {$expr: { 
     $or : [ { $and : [{$eq : ["$$property" , "SMS"]},{$eq : ["$$phone" , "$phoneNumber"]}]

          }
    ,{ $and : [{$eq : ["$$property" , "email"]},{$eq : ["$$email" , "$email"]}]

          }] 
    }
   }
  }
  ],
  as: 'messages'
}}])

Thanks
Pavel

1 Like
3

Thank you. It was helpful