How to do inner joining in MongoDB?
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 populate on userschema you can also reduce the redundancy
As Tiramisu wrote this looks like schema issue.
You can make a manual inner join, by removing documents where $lookup returned empty array.
....
{$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"
},
I found answer my self it was
$unwind done the trick to me following query worked for me
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