Mongodb $graphLookup build hierarchy

Question

I have an output from mongodb $graphLookup aggregation:

db.getCollection('projects').aggregate([
    {
    $lookup: {
      from: "projects",
      localField: "_id",
      foreignField: "parent",
       as: "childrens"
     }
 }
])

{
    "_id" : "1",
    "name" : "Project1",
    "parent" : null,
    "childrens" : [ 
        {
            "_id" : "3",
            "name" : "ProjectForId1",
            "parent" : "1"
        }
    ]
},
{
    "_id" : "3",
    "name" : "ProjectForId1",
    "parent" : "1",
    "childrens" : [ 
        {
            "_id" : "6",
            "name" : "ProjectForId3",
            "parent" : "3"
        }, 
        {
            "_id" : "7",
            "name" : "ProjectForId3",
            "parent" : "3"
        }
    ]
}

I need to build hierarchy from this output in javascript or if is possible directly from query so the final output should look like:

{
    "_id" : "1",
    "name" : "Project1",
    "parent" : null,
    "childrens" : [ 
        {
            "_id" : "3",
            "name" : "ProjectForId1",
            "parent" : "1",
            "childrens" : [ 
                {
                    "_id" : "6",
                    "name" : "ProjectForId3",
                    "parent" : "3"
                }, 
                {
                    "_id" : "7",
                    "name" : "ProjectForId3",
                    "parent" : "3"
                }
            ]
        }
    ]
}

Also if someone have a brave heart to help in one more case where the hierarchy will be created by filtering _id:

ex: For _id = "1" the output will be same as above but if _id is 3 the final output should look like:

{
    "_id" : "3",
    "name" : "ProjectForId1",
    "parent" : "1",
    "childrens" : [ 
        {
            "_id" : "6",
            "name" : "ProjectForId3",
            "parent" : "3"
        }, 
        {
            "_id" : "7",
            "name" : "ProjectForId3",
            "parent" : "3"
        }
    ]
}

score 5 · Answer 1 · edited Jan 05 '19 at 14:36

Below solution is more or less the same as one of my past answers so you can get thorough explanation here

db.projects.aggregate([
    {
        $graphLookup: {
            from: "projects",
            startWith: "$_id",
            connectFromField: "_id",
            connectToField: "parent",
            as: "children",
            maxDepth: 4,
            depthField: "level"
        }
    },
    {
        $unwind: "$children"
    },
    {
        $sort: { "children.level": -1 }
    },
    {
        $group: {
            _id: "$_id",
            children: { $push: "$children" }
        }
    },
    {
        $addFields: {
            children: {
                $reduce: {
                    input: "$children",
                    initialValue: {
                        currentLevel: -1,
                        currentLevelProjects: [],
                        previousLevelProjects: []
                    },
                    in: {
                        $let: {
                            vars: {
                                prev: { 
                                    $cond: [ 
                                        { $eq: [ "$$value.currentLevel", "$$this.level" ] }, 
                                        "$$value.previousLevelProjects", 
                                        "$$value.currentLevelProjects" 
                                    ] 
                                },
                                current: { 
                                    $cond: [ 
                                        { $eq: [ "$$value.currentLevel", "$$this.level" ] }, 
                                        "$$value.currentLevelProjects", 
                                        [] 
                                    ] 
                                }
                            },
                            in: {
                                currentLevel: "$$this.level",
                                previousLevelProjects: "$$prev",
                                currentLevelProjects: {
                                    $concatArrays: [
                                        "$$current", 
                                        [
                                            { $mergeObjects: [ 
                                                "$$this", 
                                                { children: { $filter: { input: "$$prev", as: "e", cond: { $eq: [ "$$e.parent", "$$this._id"  ] } } } } 
                                            ] }
                                        ]
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        $addFields: { children: "$children.currentLevelProjects" }
    },
    {
        $match: {
            _id: "1"
        }
    }
])

Last stage is supposed to be the filtering so you can get the data for any level of depth here.

Thanks for your answer but unfortunately the result that you expected is not ok. The ouput is `{ "_id" : "1", "name" : "Project1", "parent" : null, "childrens" : [ { "_id" : "3", "name" : "ProjectForId1", "parent" : "1" } ] }` As you can see _id's "6" and "7" are not displayed as childrens for "3" — ricristian, Jan 05 '19 at 12:03
@durduliu2009 I assumed that JSON you posted is an input, probably you need to put your lookup in front of it — mickl, Jan 05 '19 at 12:29
@mikl please find my explanation above as comment to my post because here platform tells me that I exceeed with 407 characters... thank you — ricristian, Jan 05 '19 at 13:06
@durduliu2009 this changes a lot as you pasted your source data. Fortunately it's very similar to one of my previous answers so you can check my modified post. — mickl, Jan 05 '19 at 13:41
@mikl first of all I want to thank you for your help, i'm really thankfull for your last answer but unfortunately there is still a little problem for this aggregation ... I've updated my comment to question with a new document with _id = 8 and parent = 6 and there seems that this new document "_id": 8 appear in "_id" 6 and 7 but it should be only in "_id" = 6. So the new fully example is updated above. Thank you again for your efforts and if for this last problem you can help me I will be really happy — ricristian, Jan 05 '19 at 14:15
@mikl new update: I solved it by replacing `[ "$$e.managerId", "$$this.empId" ]` with `[ "$$e.parent", "$$this._id" ]` from "mergeObjects" Thank you for this great help — ricristian, Jan 05 '19 at 14:21
@durduliu2009 sorry for that, just a mistake when copy-pasting, accepted your edit, you can remove your second answer — mickl, Jan 05 '19 at 14:37
thanks for the sorting by depthLevel, it was the most confusing part for me to get from the documentation explanation. — Sunny Sharma, Apr 07 '20 at 17:22

Mongodb $graphLookup build hierarchy

1 Answers1

Linked