How to write mongo query to combine data from two document

Question

0.00/5 (No votes)

See more:

i have two document one is userRole(Role Master) and another is user(Role Asssociated with user),user can have multiple role and there is some permissions applied on user level and role level both , i want to fetch distinct permission from user and userRole for particular user and on associate multiple role on this user.

 "userRole":{
    "_id" : ObjectId("58902f6b5d474004b4034026"),
    "roleName" : "Filler",
    "permission" : {
        "ActionPermissions" : {
            "Incidence Management" : {
                "Trouble Ticketing" : {
                    "Add Ticket" : {}
                }
            }
        }
    },
    "associatePermission" : {
        "ActionPermissions" : {
            "Incidence Management" : {
                "Trouble Ticketing" : {
                    "Add Ticket" : {},
                    "Bulk Closure" : true
                },
                "Ageing Report" : {
                    "Filter Report" : true
                }
            },
            "Preventive Maintenance" : {
                "Edit Ticket" : {
                    "Severity" : true
                }
            }
        },
        "DataPermissions" : {
            "Incidence Management" : {
                "Trouble Ticketing" : {
                    "Add Ticket" : {}
                }
            }
        }
    }
}

What I have tried:

"user": {
    "_id" : ObjectId("58a29743f7d4180a4cae827e"),
    "firstName" : "dileep",
    "roles" : [ 
        {
            "roleName" : {
                "_id" : "5890336b5d474004b403402c",
                "roleName" : "Ceo"
            },
            "effectiveFrom" : "2017-02-16T18:30:00.000Z",
            "effectiveTo" : "2017-02-28T18:30:00.000Z",
            "id" : {}
        }, 
        {
            "effectiveFrom" : "2017-02-16T18:30:00.000Z",
            "effectiveTo" : ISODate("2017-02-15T18:30:00.000Z"),
            "roleName" : "{\"_id\":\"5890335d5d474004b4034029\",\"roleName\":\"Engineer\"}"
        }, 
        {
            "roleName" : {
                "_id" : "5890336b5d474004b403402c",
                "roleName" : "Ceo"
            },
            "effectiveFrom" : "2017-02-16T18:30:00.000Z",
            "effectiveTo" : ISODate("2017-02-28T18:30:00.000Z"),
            "id" : {}
        }, 
        {
            "roleName" : {
                "_id" : "589033795d474004b403402f",
                "roleName" : "WebUser"
            },
            "id" : {}
        }, 
        {
            "roleName" : {
                "_id" : "58a69b9a93e8e20854282001",
                "roleName" : "surbhi",
                "roleDescription" : "surbhi"
            },
            "effectiveFrom" : "2017-02-23T18:30:00.000Z",
            "id" : 5
        }, 
        {
            "roleName" : {
                "_id" : "58aef573d90ffd185c96bb65",
                "roleName" : "ty",
                "roleDescription" : ""
            },
            "effectiveFrom" : "2017-02-23T18:30:00.000Z",
            "id" : 6
        }
    ],
    "groups" : [ 
        {
            "groupName" : {
                "_id" : "5891d9e75d4740205401bd68",
                "groupName" : "Power Vendor"
            },
            "effectiveFrom" : "2017-02-17T18:30:00.000Z",
            "id" : 1,
            "effectiveTo" : ISODate("2017-02-15T18:30:00.000Z")
        }, 
        {
            "groupName" : {
                "_id" : "5891d9f15d4740205401bd6b",
                "groupName" : "Tower Co"
            },
            "effectiveFrom" : "2017-02-21T18:30:00.000Z",
            "effectiveTo" : ISODate("2017-02-28T18:30:00.000Z"),
            "id" : 2
        }, 
        {
            "groupName" : {
                "_id" : "5891d9f15d4740205401bd6b",
                "groupName" : "Tower Co"
            },
            "effectiveFrom" : "2017-02-21T18:30:00.000Z",
            "effectiveTo" : ISODate("2017-02-21T18:30:00.000Z"),
            "id" : 2
        }, 
        {
            "groupName" : {
                "_id" : "58a6bfa3ea436926ac06a234",
                "groupName" : "asdasd",
                "groupDescription" : "asd"
            },
            "effectiveFrom" : "2017-02-27T18:30:00.000Z",
            "id" : 4
        }, 
        {
            "groupName" : {
                "_id" : "58a6958e93e8e20854282000",
                "groupName" : "anas",
                "groupDescription" : "this group is made for testing purpose."
            },
            "effectiveFrom" : "2017-02-20T18:30:00.000Z",
            "id" : 5,
            "effectiveTo" : ISODate("2017-02-21T18:30:00.000Z")
        }, 
        {
            "groupName" : {
                "_id" : "58a69dbb93e8e20854282002",
                "groupName" : "GP",
                "groupDescription" : "gp is for test"
            },
            "effectiveFrom" : "2017-02-23T18:30:00.000Z",
            "effectiveTo" : "2017-03-01T18:30:00.000Z",
            "id" : 6
        }, 
        {
            "effectiveFrom" : ISODate("2017-02-26T18:30:00.000Z"),
            "groupName" : {
                "groupName" : "Tower CoMPANY",
                "_id" : "5891d9f15d4740205401bd6b"
            },
            "effectiveTo" : null,
            "id" : 7
        }
    ],
    "profilePic" : "UserProfilePic-1487050584447.jpg",
    "loginId" : "dileep123",
    "password" : null,
    "confirmPassword" : null,
    "imeiNumber1" : "222222222222222222",
    "middleName" : "kumara",
    "lastName" : "saxenaaaaaa",
    "primaryEmail" : "ok@gmail.com",
    "primaryPhoneNumber" : "1111111111",
    "associatePermission" : {
        "ActionPermissions" : {
            "Incidence Management" : {},
            "User Management" : {
                "Users" : {
                    "View" : true,
                    "Add" : true
                }
            }
        },
        "DataPermissions" : {}
    }
}

Posted 28-Feb-17 17:59pm

Rahul Gour

Updated 28-Feb-17 20:24pm

Add a Solution

Comments

Rahul Gour 1-Mar-17 0:01am

need distinct associate permission from both the collection basis on RoleID join..e.g. in user collection if user have 2 permission and user is associate with 3 roles then all roles permission should be come with this document,

select permission from role
where roleID in (SELECT roleID from user where userID = 123)
Union
Select permission from user where userID = 123;

i want to write above query in mongoDB

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sachin.vishwa90 · Answer 1 · 2017-02-28T20:24:00

Solution 1

Hi, you need to use aggregation framework here.you can check here

so your query will look like this
first you match the user with userid and pass the data to next operator.
next operator will join it with userrole document and you can get the user with their role in list.

JavaScript

db.users.aggregate([{$match:{"userId":"youruserid"}},{$lookup:{from:"userRoles",localField:"userId",foreignField:"userId",as:"userWithRoleList"}},{$project:{"userId":1,userWithRoleList:1}}])

this should work for you.

Posted 28-Feb-17 20:24pm

sachin.vishwa90

Comments

Rahul Gour 1-Mar-17 4:23am

@sachin.vishwa90, thanks for ur solution,
i tried using above shared query but not able to found desired output actually (it will return only exist role on input user) i want distinct associate permission from both the collection basis on RoleID join..e.g. in user collection if user have 2 permission and user is associate with 3 roles then all roles permission should be come with this document.

sachin.vishwa90 1-Mar-17 5:08am

hi, That query was just an skeleton to show you for join the two document and pass the result to next operator, may be you will have to use $unwind operator after join.
check the link which was shared in my solution. by the time i will check data posted by you.

sachin.vishwa90 1-Mar-17 5:34am

db.users.aggregate([{$match:{"_id":"yourUserId"}},{$unwind:"$roles"},{$project:{"$roleName._Id":1}},{$lookup:{from:"userRole",localField:"_id",foreignField:"_id",as:"userPermissions"}}])

can you try this query?