Tamilselvi thandapani Tamilselvi thandapani - 3 months ago 13
Node.js Question

Aggregate query for a single collection

My database contains a user

collection
in the form:

{
"userId": "12345",
"vertical": "BFS",
"Role": "Manager"
},
{
"userId": "12345",
"vertical": "Insurance",
"Role": "Manager"
},
{
"userId": "12367",
"vertical": "BFS",
"Role": "Associate"
}


I know
vertical
and
manager
and from this I have to find the
userId
. Then I have to find the
verticals
of the particular user.

Like in SQL:

select vertical
from user
where role="Manager"
and userid in (
select userid from user
where vertical="BFS" and role="Manager"
)


Kindly help on this. I am new to this technology.

Answer

Problem: We would like to get the data where a manager in BFS vertical is playing the Manager role in other verticals as well.

I would handle this problem with aggregate framework available in MongoDB.

Here is the explanation foe each pipeline in the below query.

1) $Match - Get all data where role is Manager because we are interested in manager role only

2) $project - Just an interim pipeline to project the fields to next pipeline

3) $group - Similar to SQL to group the data by userId and add their verticals to an array

4) $Match - To filter the userids where they are part of BFS

db.vertical.aggregate([
   {$match : { "Role": "Manager"}},
   {$project : { "userId" : 1, "vertical" : 1, "Role" : 1}},
   {$group : { _id : "$userId", "verticalsArray" : {$push : "$vertical"} } },
   {$match : { "verticalsArray": "BFS"}},
]);

Output:-

{
    "_id" : "12345",
    "verticalsArray" : [ 
        "BFS", 
        "Insurance"
    ]
}
Comments