How to do inner joining in MongoDB?

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

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"},

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