How to combine more then one collection in MongoDB

Question

I have three collections ("role", "permission", "role_permission"). In this case I want to list all permissions (collection->permission) but I also wanto to add role info and add a state field it's coming from role_permission.state. If role_permission is not exist for the role the state value has must to be 0. How can I solve this case?

Collections:

/* "role" collection */
{
    "_id" : 1,
    "name" : "Admin"
}


/* "permission" collection */
{
    "_id" : 1,
    "name" : "Add User"
}
{
    "_id" : 2,
    "name" : "Update User"
}
{
    "_id" : 3,
    "name" : "Delete User"
}
{
    "_id" : 4,
    "name" : "List User"
}

/* "role_permission" collection */
{
    "_id" : 1,
    "role_id" : 1,
    "permission_id" : 1,
    "state": 1
}
{
    "_id" : 2,
    "role_id" : 1,
    "permission_id" : 2,
    "state": 1
}
{
    "_id" : 4,
    "role_id" : 1,
    "permission_id" : 4,
    "state": 1
}

Expecting Result:

/* expecting result */
{
    "role_id": 1,
    "permission_id": 1,
    "permission_name": "Add User",
    "permission_state": 1 // if role_permission is exist for the role: use role_permission.state else: 0
}
{
    "role_id": 1,
    "permission_id": 2,
    "permission_name": "Update User",
    "permission_state": 1 // if role_permission is exist for the role: use role_permission.state else: 0
}
{
    "role_id": 1,
    "permission_id": 3,
    "permission_name": "Delete User",
    "permission_state": 0 // if role_permission is exist for the role: use role_permission.state else: 0
}
{
    "role_id": 1,
    "permission_id": 4,
    "permission_name": "List User",
    "permission_state": 1 // if role_permission is exist for the role: use role_permission.state else: 0
}

can you add a sample output that you are expecting to the question — Sathish, Dec 03 '19 at 13:10
I added expecting result to the question. You can check out /* expecting result */ comment line. — Mustafa Alper KIZILGİL, Dec 03 '19 at 13:22
Does this answer your question? [How to join multiple collections with $lookup in mongodb](https://stackoverflow.com/questions/35813854/how-to-join-multiple-collections-with-lookup-in-mongodb) — Ayoub, Dec 03 '19 at 14:03

Ensar · Answer 1 · 2020-09-16T12:38:32.733

db.permission.aggregate
([
{ "$lookup" : { "from" : "role_permission", "localField" : "_id", "foreignField" : "permission_id", "as" : "JOIN_1" } }, 
{ "$lookup" : { "from" : "role", "localField" : "JOIN_1.role_id", "foreignField" : "_id", "as" : "JOIN_2" } }, 
{ "$unwind" : { "path" : "$JOIN_1", "preserveNullAndEmptyArrays" : true } }, 
{ "$unwind" : { "path" : "$JOIN_2", "preserveNullAndEmptyArrays" : true } }, 
{ "$project" : 
    {   "_id" : 0, 
        "p_id" : { "$ifNull" : ["$JOIN_1._id", 0] }, 
        "role_id" : { "$ifNull" : ["$JOIN_2._id", 1] }, 
        "permission_id" : "$_id", 
        "permission_name" : "$name", 
        "permission_state" : { "$ifNull" : ["$JOIN_1.state", 0] } 
    } 
}
])

How to combine more then one collection in MongoDB

1 Answers1