mongoDB Join on multiple fields

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

This may be a good starting point: https://www.mongodb.com/blog/post/joins-and-other-aggregation-enhancements-coming-in-mongodb-3-2-part-1-of-3-introduction — Barney, Jul 06 '16 at 14:05
have already the referred the post which has helped me to build part of the above query. The post doesn't speak about joining with multiple keys which I am looking for now. — N Raghu, Jul 06 '16 at 15:54

Anish Agarwal · Accepted Answer · 2016-11-21T09:56:53.430

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.

sbharti · Answer 2 · 2017-04-19T03:16:07.060

With right combination of $lookup, $project and $match, you can join mutiple tables on parameters. This is because they can be chained multiple times.

Step 1: Link all tables

$lookup - one for each table in query

$unwind - because data is denormalised correctly, else wrapped in arrays

Python code..

db.LeftTable.aggregate([
                        # connect all tables

                        {"$lookup": {
                          "from": "RightTable",
                          "localField": "ID",
                          "foreignField": "ID",
                          "as": "R"
                        }},
                        {"$unwind": "R"}

                       # {"$lookup": {
                       #   "from": "TopTable",
                       #   "localField": "ID",
                       #   "foreignField": "ID",
                       #   "as": "T"
                       # }},
                       # {"$unwind": "T"},

                        ])

Step 2: Define all conditionals

$project : define all conditional statements here, plus all the variables you'd like to select.

Python Code..

db.LeftTable.aggregate([
                        # connect all tables

                        {"$lookup": {
                          "from": "RightTable",
                          "localField": "ID",
                          "foreignField": "ID",
                          "as": "R"
                        }},
                        {"$unwind": "R"},

                       # {"$lookup": {
                       #   "from": "TopTable",
                       #   "localField": "ID",
                       #   "foreignField": "ID",
                       #   "as": "T"
                       # }},
                       # {"$unwind": "T"},

                        # define conditionals + variables

                        {"$project": {
                          "midEq": {"$eq": ["$MID", "$R.MID"]},
                         # "midGt": {"$gt": ["$MID", "$T.MID"]},
                          "ID": 1, "MOB": 1, "MID": 1
                        }}
                        ])

Step 3: Join all the conditionals

$match - join all conditions using OR or AND etc. There can be multiples of these.

$project: undefine all conditionals

Python Code..

db.LeftTable.aggregate([
                        # connect all tables

                        {"$lookup": {
                          "from": "RightTable",
                          "localField": "ID",
                          "foreignField": "ID",
                          "as": "R"
                        }},
                        {"$unwind": "$R"},

                       # {"$lookup": {
                        #  "from": "TopTable",
                        #  "localField": "ID",
                        #  "foreignField": "ID",
                        #  "as": "T"
                        #}},
                        #{"$unwind": "$T"},

                        # define conditionals + variables

                        {"$project": {
                          "midEq": {"$eq": ["$MID", "$R.MID"]},
                          # "midGt": {"$gt": ["$MID", "$T.MID"]},
                          "ID": 1, "MOB": 1, "MID": 1
                        }},

                        # join all conditionals

                        {"$match": {
                          "$and": [
                            {"R.TIM": {"$gt": 0}}, 
                            {"MOB": {"$exists": True}},
                            {"midEq": {"$eq": True}},]
                        }},

                        # undefine conditionals

                        {"$project": {
                          "midEq": 0,
                          # "midGt": 0
                        }}

                        ])

Pretty much any combination of tables, conditionals and joins can be done in this manner.

mongoDB Join on multiple fields

2 Answers2

Linked