如何使用3个集合之间的关系进行mongoDB查询

Question

I have these 3 mongoDB collections :

client_results

[
 'resultId':1,
 'clientId':'client00',
],
[
 'resultId':2,
 'clientId':'client00',
],

results

[
'id':1,
'siteID':5
],
[
'id':2,
'siteID':6
]

sites

[
'id':5,
'language':'ar'
],
[
'id':6,
'language':'en'
]

how I can get client_results where results.site.language = 'en' ? in MYSQL query will like this :

select cr.id, cr.clientId from client_results cr
left join results r
  on r.id = cr.resultId 
left join site s
  on s.id = r.siteID
where s.language = 'en';

but in mongoDB how I can make this query ?

thanks

Answer 1

In mongodb it is little hectic to do joins,as mongodb was not meant to do joins,the main idea behind mongodb was to put all you need in one document,but there are some limitation,we cant put whole other document in another,anyways here is your answer,$look is like left outer join,But problem in this solution is there i have unwind the first table to join another,in this case if there is no first join there would be no second join as well, You can use a $project stage and put a _id variable in it,play with it you will get the desired result

db.client_results.aggregate([
                            {"$lookup" : 
                             {
                              from : results,
                              localField : resultId,
                              foreignField: id,
                              as : "resultTable"
                            }
                          },
                      {"$unwind" : "$resultTable"},
                      {"$lookup" : 
                         {
                          from : sites,
                          localField : "resultTable.siteID",
                          foreignField: id,
                          as : "siteTable"
                         }
                       },
                      {"$unwind" : "$siteTable"},
                      {$match : {"siteTable.language" : "en"}} // <-- only en language
                        ])