mongoDB Join on multiple fields [duplicate]

Question

I am rewriting SQL Queries into mongoDB. Can someone help how do we join two collections with multiple join keys and conditions like in below SQL Query.

SELECT S.* FROM LeftTable S
LEFT JOIN RightTable R ON S.ID =R.ID AND S.MID =R.MID WHERE R.TIM >0 AND S.MOB IS NOT NULL

I have the below code which does with single join key condition. I would be glad if someone can help with multiple join keys and where clause to complete query.

db.dim.aggregate([{$lookup:{from:"dimFactsVer11",localField:"Sub", foreignField:"Type", as:"EmbedUp"}}])

Answer 1

Currently mongodb $lookup only compare single local and foreign key.

But if you want to perform a query as like mysql left join with two or more filed then below is solution.

db.getCollection('LeftTable').aggregate([
{
    $lookup:
        {
          from: "RightTable",
          localField: "ID",
          foreignField: "ID",
          as: "RightTableData"
        }
},  
{$unwind :"$RightTableData" },
{ 
     $project: { 
            mid: { $cond: [ { $eq: [ '$MID', '$RightTableData.MID' ] }, 1, 0 ] } 
        } 
},
{$match : { mid : 1}}

])

Here $MID is LeftTable MID field.