I belive that having level field we can build hierarchical structure from an array using $reduce. To achieve that we need to get reportees
ordered by level descending after $graphLookup
. Unfortunately the only way to do it currently is to use $unwind + $sort + $group which makes the aggregation quite long.
Then we can process that ordered array using $reduce
. In each step we just have to add an employee to the result set including his reportees
from previous level. Additionally we need to detect when level
changes during our processing and rearrange helper arrays in that case.
$addFields simply replaces existing reportees
field in this case. $concatArrays allows us to append current employee ($$this
) to the result. Using $filter we can get reportees
from lower level.
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
}
},
{
$unwind: "$reportees"
},
{
$sort: { "reportees.level": -1 }
},
{
$group: {
_id: "$_id",
empId: { $first: "$empId" },
managerId: { $first: "$managerId" },
reportees: { $push: "$reportees" }
}
},
{
$addFields: {
reportees: {
$reduce: {
input: "$reportees",
initialValue: {
currentLevel: -1,
currentLevelEmployees: [],
previousLevelEmployees: []
},
in: {
$let: {
vars: {
prev: {
$cond: [
{ $eq: [ "$$value.currentLevel", "$$this.level" ] },
"$$value.previousLevelEmployees",
"$$value.currentLevelEmployees"
]
},
current: {
$cond: [
{ $eq: [ "$$value.currentLevel", "$$this.level" ] },
"$$value.currentLevelEmployees",
[]
]
}
},
in: {
currentLevel: "$$this.level",
previousLevelEmployees: "$$prev",
currentLevelEmployees: {
$concatArrays: [
"$$current",
[
{ $mergeObjects: [
"$$this",
{ reportees: { $filter: { input: "$$prev", as: "e", cond: { $eq: [ "$$e.managerId", "$$this.empId" ] } } } }
] }
]
]
}
}
}
}
}
}
}
},
{
$addFields: { reportees: "$reportees.currentLevelEmployees" }
}
]).pretty()
Above solution should work for multiple levels. Outputs:
{
"_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),
"reportees" : [ ]
},
{
"empId" : "2",
"name" : "Employee2",
"managerId" : "6",
"level" : NumberLong(1),
"reportees" : [ ]
}
]
},
{
"empId" : "8",
"name" : "Employee8",
"managerId" : "10",
"level" : NumberLong(0),
"reportees" : [
{
"empId" : "5",
"name" : "Employee5",
"managerId" : "8",
"level" : NumberLong(1),
"reportees" : [ ]
},
{
"empId" : "4",
"name" : "Employee4",
"managerId" : "8",
"level" : NumberLong(1),
"reportees" : [ ]
}
]
}
]
}