如何在 MongoDB 中进行内部连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37575722/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do inner joining in MongoDB?
提问by Asantha Thilina
Is it possible to do SQL inner joins kind of stuff in MongoDB , well i know there is
是否可以在 MongoDB 中进行 SQL 内部连接之类的操作，我知道有
$lookup
$查找
attribute in aggregation pipeline and it is equivalent to outer joins in SQL but i want to do a similar kind of a task as in inner joins , i have two three collections ,which i need to merge together
聚合管道中的属性，它相当于 SQL 中的外连接，但我想做与内连接类似的任务，我有两个三个集合，我需要将它们合并在一起
----User Collection----
db.User.find({})
{
ID : 1,
USER_NAME : "John",
password : "pass"
}
{
ID : 2,
USER_NAME : "Andrew",
PASSWORD : "andrew"
}
---ROLE COLLECTION---
db.ROLE.find({})
{
ID : 1,
ROLE_NAME : "admin"
},
{
ID : 2,
ROLE_NAME : "staff"
}
---USER_ROLE COLLECTION---
db.USER_ROLE.find({})
{
ID : 1,
USER_ID : 1,
ROLE_ID : 1
}
i having above 3 collections and i want extract only the documents matched with users and their respective roles not all the documents, how can i manage it in MongoDB can anyone give me a suggestion?
我有以上 3 个集合，我只想提取与用户及其各自角色匹配的文档而不是所有文档，我如何在 MongoDB 中管理它，谁能给我一个建议？
回答by Asantha Thilina
I found answer my self it was
我找到了我自己的答案
$unwind done the trick to me following query worked for me
$unwind 为我完成了以下查询对我有用的技巧
db.USER.aggregate([{
$lookup: {
from: "USER_ROLE",
localField: "ID",
foreignField: "USER_ID",
as: "userRole"
}
}, {
$unwind: {
path: "$userRole",
preserveNullAndEmptyArrays: false
}
}, {
$lookup: {
from: "ROLE",
localField: "userRole.ROLE_ID",
foreignField: "ID",
as: "role"
}
}, {
$unwind: {
path: "$role",
preserveNullAndEmptyArrays: false
}
}, {
$match: {
"role.ROLE_NAME": "staff"
}, {
$project: {
USER_NAME: 1,
_id: 0
}
}
]).pretty()
Anyway thanks for the answers
无论如何感谢您的回答
回答by profesor79
As Tiramisu wrote this looks like schema issue.
正如提拉米苏所写，这看起来像是架构问题。
You can make a manual inner join, by removing documents where $lookup returned empty array.
您可以通过删除 $lookup 返回空数组的文档来进行手动内部联接。
....
{$lookup... as myArray},
{$match: {"myArray":{$ne:[]}}},
{$lookup... as myArray2},
{$match: {"myArray2":{$ne:[]}}},
schema change
模式改变
I personally will go for schema update, like this:
我个人将进行架构更新，如下所示：
db.User.find({})
{
ID : 1,
USER_NAME : "John",
password : "pass"
roles:[{ID : 1, ROLE_NAME : "admin"}]
}
db.ROLE.find({})
{
ID : 1,
ROLE_NAME : "admin"
},
回答by skyhavoc
Will this help
这会帮助吗
const RolesSchema = new Schema({
....
});
const Roles = mongoose.model('Roles', RolesSchema);
const UserSchema = new Schema({
...
roles: [{ type: mongoose.Schema.Types.ObjectId, ref: "Roles" }]
});
using the populateon userschema you can also reduce the redundancy
使用用户架构上的填充，您还可以减少冗余
回答by joshbaga wang
> show dbs
admin 0.000GB
config 0.000GB
local 0.002GB
> use local
switched to db local
> show collections
startup_log
test1
test2
> db.test2.aggregate([{
... $lookup: {
... from: "test1",
... localField: "id",
... foreignField: "id",
... as: "aggTest"
... }
... }])
