Hierarchical tree structure ($graphlookup)

[Eric Cyliu]

Hi experts,

I am trying to understand how graphlookup works by playing a toy example modified from mongoDB example. It is related to my real application. Eventually what  I want is a hierarchical structure where each node have a summary of how many PASSEDs and FAILEDs aggregated from one level lower/deeper nodes (I am not there yet). Please tell me if you happen to know solution.

Example docs:

db.employees.insert(

[

             { "_id" : 1, "name" : "Dev", "status": "PASSED" , "age": 1 },

{ "_id" : 2, "name" : "Eliot", "reportsTo" : "Dev" , "status": "PASSED" },

{ "_id" : 3, "name" : "Ron", "reportsTo" : "Eliot" , "age": 3,"status": "PASSED" },

{ "_id" : 4, "name" : "Andrew", "reportsTo" : "Eliot" , "age": 4,"status": "PASSED" },

{ "_id" : 5, "name" : "Asya", "reportsTo" : "Ron" , "status": "FAILED" },

{ "_id" : 6, "name" : "Dan", "reportsTo" : "Andrew" , "age": 6 ,"status": "FAILED"}

             ])

Graphlookup aggregation:

db.employees.aggregate( [

{
      $graphLookup: {
         from: "employees",
         startWith: "$reportsTo",
         connectFromField: "reportsTo",
         connectToField: "name",

  maxDepth: 4,

   depthField: "depth",
     as: "reportingHierarchy"
   }

},

{

$project:

{

name:1,

reportsTo:1,

status:1,

"reportingHierarchy.name":1,

"reportingHierarchy.reportsTo":1,

"reportingHierarchy.status":1,

numPass:
{
        $cond: [{ $eq: ["$status", "PASSED"]},  1, 0 ]
},

"reportingHierarchy.numPass":
{
        $cond: [{ $eq: ["$status", "PASSED"]},  1, 0 ]
},

numFail:
{
        $cond: [{ $eq: ["$status", "FAILED"]},  1, 0 ]

},

"reportingHierarchy.numFail":
{
        $cond: [{ $eq: ["$reportingHierarchy.status", "FAILED"]},  1, 0 ]
},

}

}

] )

This aggregation did not work like I expected. The $cond does not work inside reportingHierarchy, especially id5 and id6. The numbers of pass and fail are always 0. In my real application, It always shows 0. 

Can someone help with this ?

{ "_id" : 1, "name" : "Dev", "status" : "PASSED", "reportingHierarchy" : [ ], "numPass" : 1, "numFail" : 0 }

{ "_id" : 2, "name" : "Eliot", "reportsTo" : "Dev", "status" : "PASSED", "reportingHierarchy" : [ { "name" : "Dev", "status" : "PASSED", "numPass" : 1, "numFail" : 0 } ], "numPass" : 1, "numFail" : 0 }

{ "_id" : 3, "name" : "Ron", "reportsTo" : "Eliot", "status" : "PASSED", "reportingHierarchy" : [ { "name" : "Dev", "status" : "PASSED", "numPass" : 1, "numFail" : 0 }, { "name" : "Eliot", "reportsTo" : "Dev", "status" : "PASSED", "numPass" : 1, "numFail" : 0 } ], "numPass" : 1, "numFail" : 0 }

{ "_id" : 4, "name" : "Andrew", "reportsTo" : "Eliot", "status" : "PASSED", "reportingHierarchy" : [ { "name" : "Dev", "status" : "PASSED", "numPass" : 1, "numFail" : 0 }, { "name" : "Eliot", "reportsTo" : "Dev", "status" : "PASSED", "numPass" : 1, "numFail" : 0 } ], "numPass" : 1, "numFail" : 0 }

{ "_id" : 5, "name" : "Asya", "reportsTo" : "Ron", "status" : "FAILED", "reportingHierarchy" : [ { "name" : "Dev", "status" : "PASSED", "numPass" : 0, "numFail" : 0 }, { "name" : "Eliot", "reportsTo" : "Dev", "status" : "PASSED", "numPass" : 0, "numFail" : 0 }, { "name" : "Ron", "reportsTo" : "Eliot", "status" : "PASSED", "numPass" : 0, "numFail" : 0 } ], "numPass" : 0, "numFail" : 1 }

{ "_id" : 6, "name" : "Dan", "reportsTo" : "Andrew", "status" : "FAILED", "reportingHierarchy" : [ { "name" : "Dev", "status" : "PASSED", "numPass" : 0, "numFail" : 0 }, { "name" : "Eliot", "reportsTo" : "Dev", "status" : "PASSED", "numPass" : 0, "numFail" : 0 }, { "name" : "Andrew", "reportsTo" : "Eliot", "status" : "PASSED", "numPass" : 0, "numFail" : 0 } ], "numPass" : 0, "numFail" : 1 }

[Ivan Grigolon]

Hi,

Could you please provide the expected output from the query, and the logic that leads to that output?

Also, can you please provide the MongoDB version that your are using?

Best Regards,
Ivan