I have this data as below in a collection of MongoDB.

{ "_id" : 1, "name" : "Dev" }
{ "_id" : 2, "name" : "Eliot", "parentId" : 1 }
{ "_id" : 3, "name" : "Ron", "parentId" : 2 }
{ "_id" : 4, "name" : "Andrew", "parentId" : 2 }
{ "_id" : 5, "name" : "Asya", "parentId" : 3 }
{ "_id" : 6, "name" : "Dan", "parentId" : 4 }

And I can use "$graphLookup" to get children and parents.

db.xmantree.aggregate( [
   {
      name: "Eliot"
   },
   {
      $graphLookup: {
          from: 'xmantree',
          startWith: '$_id',
          connectFromField: 'parentId',
          connectToField: 'parentId',
          as: 'children'
       }
   },
  {
      $graphLookup: {
          from: 'xmantree',
          startWith: '$parentId',
          connectFromField: 'parentId',
          connectToField: '_id',
          as: 'parents'
       }
   }
] )

The result is this.

{
    _id:2,
    name: "Eliot",
    parentId: 1,
    children: [
        {
            _id: 3,
            name: "Ron",
            parentId: 2,
        },
        {
            _id: 4,
            name: "Andrew",
            parentId: 2,
        },
    ],
    parents: [
        {
            _id: 1,
            name: "Dev"
        }
    ]
}

But Each of Ron and Andrew has more children in this case. So I want to get this recursively by one query of aggregate.
For example, this kind of result.

{
    _id:2,
    name: "Eliot",
    parentId: 1,
    children: [
        {
            _id: 3,
            name: "Ron",
            parentId: 2,
            children: [...] // <- I want to get this.
        },
        {
            _id: 4,
            name: "Andrew",
            parentId: 2,
            children: [...] // <- I want to get this.
        },
    ],
    parents: [
        {
            _id: 1,
            name: "Dev"
        }
    ]
}

Is my goal possible in MongoDB?

CodePudding user response：

You can set your $graphLookup like this to get an array of all children nodes:

{
    $graphLookup: {
      from: "xmantree",
      startWith: "$_id",
      connectFromField: "_id",
      connectToField: "parentId",
      as: "children"
    }
  }

Here is the Mongo playground for your reference.

However, for your expected layered form of output, you will need to know the recursion level. If you have the information, you can do something like below:

db.xmantree.aggregate([
  {
    "$match": {
      name: "Eliot"
    }
  },
  {
    "$lookup": {
      "from": "xmantree",
      let: {
        parentId: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$parentId",
                "$parentId"
              ]
            }
          }
        },
        {
          "$lookup": {
            "from": "xmantree",
            "localField": "_id",
            "foreignField": "parentId",
            "as": "children"
          }
        }
      ],
      "as": "children"
    }
  }
])

Here is the Mongo playground for your reference.