Tamilselvi thandapani Tamilselvi thandapani - 5 months ago 18
Node.js Question

Aggregate query for a single collection

My database contains a user

in the form:

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

I know
and from this I have to find the
. Then I have to find the
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.


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

   {$match : { "Role": "Manager"}},
   {$project : { "userId" : 1, "vertical" : 1, "Role" : 1}},
   {$group : { _id : "$userId", "verticalsArray" : {$push : "$vertical"} } },
   {$match : { "verticalsArray": "BFS"}},


    "_id" : "12345",
    "verticalsArray" : [