Graphlookup to fetch a tree structure

[Ashwin_Ramamurthy]

I have a tree structure.

node collection.

{
_id: ‘123’,
}
{
_id: ‘444’,
}
{
_id: ‘555’,
}
{
_id: ‘666’,
}

hierarchy collection.

{
_id: ‘333’,
parent: ‘123’,
child: ‘444’,
}
{
_id: ‘344’,
parent: ‘123’,
child: ‘555’,
}
{
_id: ‘344’,
parent: ‘555’,
child: ‘666’,
}

Something similar to the above. Now i need to recursively fetch the entire tree.
I want the result to be a single nested json.

{
 _id: '123',
 subTasks: [
      {
        _id: '444',
     },
     {
        _id: '555',
        subTasks: [
            {
              _id: '666',
            }
         ]
   }
 ]
 }

I tried using graph lookup. But i’m not getting the desired result

{
from: ‘hierarchies’,
startWith: ‘$_id’ ,
connectFromField: ‘parent’,
connectToField: ‘child’,
as: ‘subTasks’,
maxDepth: 3,
}

Given the root id of the tree. I need to fetch the entire tree(ie. stack all the children).

- Root node 1
    - Child 1.1
        - Subchild 1.1.1
    - Child 1.2
- Root node 2

I don’t need the hierarchy collection’s data to be in the result. I need the nodes to be stacked.

Thanks in advance

[turivishal (Vishal Turi)]

Hello @Ashwin_Ramamurthy Welcome to MongoDB Community Forum

I am not sure but there will be good and easy approach to do this, I am sharing one hack this may cause performance issues in huge documents because this is lengthy but for the knowledge you can try,

Aggregation Query

db.col1.aggregate([
  { $sort: { _id: 1 } },
  { $limit: 1 },
  {
    $graphLookup: {
      from: "col2",
      startWith: "$_id",
      connectFromField: "child",
      connectToField: "parent",
      depthField: "level",
      as: "subTasks"
    }
  },
  {
    $unwind: {
      path: "$subTasks",
      preserveNullAndEmptyArrays: true
    }
  },
  { $sort: { "subTasks.level": -1 } },
  {
    $group: {
      _id: "$_id",
      parent: { $first: "$subTasks.parent" },
      subTasks: {
        $push: {
          _id: "$subTasks.child",
          level: "$subTasks.level",
          parent: "$subTasks.parent"
        }
      }
    }
  },
  {
    $addFields: {
      subTasks: {
        $reduce: {
          input: "$subTasks",
          initialValue: {
            level: -1,
            presentChild: [],
            prevChild: []
          },
          in: {
            $let: {
              vars: {
                prev: {
                  $cond: [
                    { $eq: ["$$value.level", "$$this.level"] },
                    "$$value.prevChild",
                    "$$value.presentChild"
                  ]
                },
                current: {
                  $cond: [
                    { $eq: ["$$value.level", "$$this.level"] },
                    "$$value.presentChild",
                    []
                  ]
                }
              },
              in: {
                level: "$$this.level",
                prevChild: "$$prev",
                presentChild: {
                  $concatArrays: [
                    "$$current",
                    [
                      {
                        _id: "$$this._id",
                        parent: "$$this.parent",
                        subTasks: {
                          $filter: {
                            input: "$$prev",
                            as: "e",
                            cond: { $eq: ["$$e.parent", "$$this._id"] }
                          }
                        }
                      }
                    ]
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  { $addFields: { subTasks: "$subTasks.presentChild" } }
])

Playground

I have answered this in details here