1

I am trying to learn mongodb and wanted to know if it will support an inner join on multiple fields.

What I am expecting is a mongodb query equivalent of:

select * from tablea A inner join tableb B on a.id = b.id and a.name = b.name

I tried using $match but I am not able to see any results. These are my mongodb collections: CountryState:

{
    "countryCode" : "1",
    "stateCode" : "1"
}
{
    "countryCode" : "1",
    "stateCode" : "3"
}

CountryStateLookup:

{
    "countryCode" : "1",
    "stateCode" : "2",
    "countryName" : "TamilNadu"
}
{
    "countryCode" : "1",
    "stateCode" : "1",
    "countryName" : "Kerala"
}
{
    "countryCode" : "2",
    "stateCode" : "1",
    "countryName" : "Karnataka"
}
{
    "countryCode" : "2",
    "stateCode" : "2",
    "countryName" : "California"
}

Expected Output:

{
    "countryCode" : "1",
    "stateCode" : "1",
    "countryName" : "Kerala"
}

This is the following query I tried: (Dont know how to use the $match operator)

db.countryState.aggregate([  
   {  
      $lookup:{  
         from:"countryStateLookup",
         localField:"stateCode",
         foreignField:"stateCode",
         as:"stateCodeName"
      }
   },
   {  
      $unwind:"$stateCodeName"
   },
   {  
      $lookup:{  
         from:"countryStateLookup",
         localField:"countryCode",
         foreignField:"countryCode",
         as:"countryCodeName"
      }
   },
   {  
      $unwind:"$countryCodeName"
   },
   {  
      $match:{  
         "countryCode":"$stateCodeName.countryCode",
         "stateCode":"$stateCodeName.stateCode"
      }
   }
])

Any help would be appreciated, thank you!

Kavitha K
  • 11
  • 2
  • Basically one `$lookup` only on the `countryCode` values as the one with most matches, `$unwind` the array and then remove unmatched `stateCode` with a field comparison ( `$redact` or `$project/$match` ). Or use the `pipeline` expression where you have MongoDB 3.6 and specify both constraints to match. – Neil Lunn Apr 26 '18 at 13:51

0 Answers0