the answer provided in below link works for 2 levels of Hierarchy, i am trying to achieve multiple levels of Hierarchy but not working
The result should be like,
10 ->>> Manager
/\
/ \
8 6 ---->> 8 & 6 reporting to manager 10
/\ /\
/ \ / \
4 5 2 1 ---->> 4 & 5 reporting to manager 8 ...
/ /
/ /
3 7 ---->> 3 reporting to manager 4 ...
/
/
11 ---->> 11 reporting to manager 7 ...
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" });
db.getCollection("employees").insert({"_id":"3","empId": "3","name":"Employee3","managerId":"4" });
db.getCollection("employees").insert({"_id":"7","empId": "7","name":"Employee7","managerId":"2" });
db.getCollection("employees").insert({"_id":"11","empId": "11","name":"Employee11","managerId":"7" });
Query i have tried from the given answer by some one the below query which i have tried from the above link but only able to achieve three level after that not able to add more graphlookup if i try to add another level, i am getting not nested data
db.employees.aggregate([
{
$match: {
empId : "10"
}
},
// level 0
{
$graphLookup: {
from: "employees",
startWith: "$empId",
connectFromField: "empId",
connectToField: "managerId",
as: "reportees",
maxDepth: 0
}
},
{
$unwind: "$reportees" // flatten
},
{
$addFields: {
"reportees.level": 0 // add level field
}
},
// level 1
{
$graphLookup: {
from: "employees",
startWith: "$reportees.empId",
connectFromField: "reportees.empId",
connectToField: "managerId",
as: "reportees.reportees",
maxDepth: 0
}
},
// **if i add another graphLookup i am getting all are flattened** // need to add multiple level
{
$group: { // group previously flattened documents back together
_id: "$_id",
empId: { $first: "$empId" },
name: { $first: "$name" },
managerId: { $first: "$managerId" },
reportees: { $push: "$reportees" },
}
},
{
$addFields: {
"reportees.reportees.level": 1 // add level field
}
}
])