Clash Royale CLAN TAG#URR8PPP

Hierarchical queries with Mongo using $graphLookup

I have an employee collection with half a million records. Each record will have manager path from immediate manager to the top level. We do not just store the managerId alone.

The mongo document is as follows.


"_id": "234463456453643563456",
"name": "Mike",
"empId": "10",
"location" : "London",
"managerId": "8",
"projects" : [ "123", "456", "789"]



Here, I am retrieving records and doing depth-first search to construct the record in the Java layer and it is very slow. Just came across $graphLookup and thought of using this. But, Not exactly sure how to use for my scenario.

a. filter on
location

b. filter on projects

Not sure if mongodb is the best fit for this, but have no other option for now.

The result will be like,

10 ->>> Manager
/
/
8 6 ---->> 8 & 6 reporting to manager 10
/ /
/ /
4 5 2 1 ---->> 4 & 5 reporting to manager 8 ...


Any help will be appreciated for getting the hierarchial results with level?

I tried with the below query.

db.getCollection('employee').aggregate([

$match:
empId : "10"

,

$graphLookup:
from: "employee",
startWith: "$empId",
connectFromField: "empId",
connectToField: "managerId",
as: "reportees",
maxDepth: 5,
depthField: "level"


]);


It returns all the levels under reportees of first level and returns level correctly as 0 for immediate reportees and 1 for the following, ...

But, I want the result in hierarchical formats like in the above chart.

Sample Data :-

db.getCollection("employees").insert("_id":"10","empId": "10","name":"Employee10","managerId":"15" );

db.getCollection("employees").insert("_id":"8","empId": "8","name":"Employee8","managerId":"10" );

db.getCollection("employees").insert("_id":"6","empId": "6","name":"Employee6","managerId":"10" );

db.getCollection("employees").insert("_id":"4","empId": "4","name":"Employee4","managerId":"8" );

db.getCollection("employees").insert("_id":"5","empId": "5","name":"Employee5","managerId":"8" );

db.getCollection("employees").insert("_id":"2","empId": "2","name":"Employee2","managerId":"6" );

db.getCollection("employees").insert("_id":"1","empId": "1","name":"Employee1","managerId":"6" );


Query :-

db.getCollection('employees').aggregate([

$match:
empId : "10"

,

$graphLookup:
from: "employees",
startWith: "$empId",
connectFromField: "empId",
connectToField: "managerId",
as: "reportees",
maxDepth: 4,
depthField: "level"

,

$project:
"empId":1,
"managerId":1,
"reportees.empId":1,
"reportees.name":1,
"reportees.managerId":1,
"reportees.level":1


]);


Actual Result :-


"_id" : "10",
"empId" : "10",
"managerId" : "15",
"reportees" : [

"empId" : "1",
"name" : "Employee1",
"managerId" : "6",
"level" : NumberLong(1)
,

"empId" : "4",
"name" : "Employee4",
"managerId" : "8",
"level" : NumberLong(1)
,

"empId" : "2",
"name" : "Employee2",
"managerId" : "6",
"level" : NumberLong(1)
,

"empId" : "5",
"name" : "Employee5",
"managerId" : "8",
"level" : NumberLong(1)
,

"empId" : "6",
"name" : "Employee6",
"managerId" : "10",
"level" : NumberLong(0)
,

"empId" : "8",
"name" : "Employee8",
"managerId" : "10",
"level" : NumberLong(0)

]



Expected Result :-


"_id" : "10",
"empId" : "10",
"managerId" : "15",
"reportees" : [

"empId" : "6",
"name" : "Employee6",
"managerId" : "10",
"level" : NumberLong(0),
"reportees" : [

"empId" : "1",
"name" : "Employee1",
"managerId" : "6",
"level" : NumberLong(1)
,

"empId" : "2",
"name" : "Employee2",
"managerId" : "6",
"level" : NumberLong(1)

]
,

"empId" : "8",
"name" : "Employee8",
"managerId" : "10",
"level" : NumberLong(0),
"reportees" : [

"empId" : "5",
"name" : "Employee5",
"managerId" : "8",
"level" : NumberLong(1)
,

"empId" : "4",
"name" : "Employee4",
"managerId" : "8",
"level" : NumberLong(1)

]

]



Questions :-

Thanks

2 Answers
2

That's precicsely what you would $graphLookup for (the traversal bit at least). For the filtering part you could simply use $filter or $match depending on how exactly you want to filter.

Have a look at the results of this query:

db.collection.aggregate(
$graphLookup:
from: "pets",
startWith: "$managerIds",
connectFromField: "managerId",
connectToField: "number",
as: "managers",

)


$match

The official documentation on $graphLookup may provide help more or less.

https://docs.mongodb.com/manual/reference/operator/aggregation/graphLookup/

Just a kind remind.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.