mongoDB在多个字段上联接 [英] mongoDB Join on multiple fields
问题描述
我正在将SQL查询重写为mongoDB.有人可以帮助我们如何使用多个联接键和条件联接两个集合,例如下面的SQL查询.
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
我有以下与单连接键条件有关的代码.如果有人可以帮助使用多个连接键和where子句来完成查询,我将感到很高兴.
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"}}])
推荐答案
当前mongodb $ lookup 仅比较单个 local 和外国键.
Currently mongodb $lookup only compare single local and foreign key.
但是,如果您要执行像mysql左连接两个或多个字段那样的查询,则下面是解决方法.
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}}
])
此处$ MID是 LeftTable MID字段.
Here $MID is LeftTable MID field.
这篇关于mongoDB在多个字段上联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!