Nested self join in mongodb

I’am having document like following.

{
_id:ObjectId("3df61406-a65c-480d-b258-85786caa871e"),
"model":["nnn","mmm"]
childrens: ["2a865da0-fbe1-4842-954d-8c7b527f8bd1", "8d06e6af-5f68-4559-9712-706afed5c0db"]
},
{
_id:ObjectId("2a865da0-fbe1-4842-954d-8c7b527f8bd1"),
"model":["nnn","mmm"],
childrens: ["4f84d69a-94da-4219-a85d-0293a615da8d", "5e55c16f-1b39-48ca-8f79-7acc373dca13"]
},
{
_id:ObjectId("4f84d69a-94da-4219-a85d-0293a615da8d"),
"model":["nnn","mmm"],
childrens: []
},
{
_id:ObjectId("5e55c16f-1b39-48ca-8f79-7acc373dca13"),
"model":["nnn","mmm"],
childrens: ["fb47003b-8b9f-4c1c-bc6c-cf51c0dbb8bc"]
},
{
_id:ObjectId("8d06e6af-5f68-4559-9712-706afed5c0db"),
"model":["nnn","mmm"],
childrens:[]
},
{
_id:ObjectId("fb47003b-8b9f-4c1c-bc6c-cf51c0dbb8bc"),
"model":["nnn","mmm"],
childrens:[]
}

I need to fetch data from root (ObjectId(“3df61406-a65c-480d-b258-85786caa871e”)) to last child. I need a query to get the following output.

{
 _id:ObjectId("3df61406-a65c-480d-b258-85786caa871e"),
 "model":["nnn","mmm"]
 childrens: [
 {
     _id:ObjectId("2a865da0-fbe1-4842-954d-8c7b527f8bd1"),
     "model":["nnn","mmm"],
      childrens: [
      {
          _id:ObjectId("4f84d69a-94da-4219-a85d-0293a615da8d"),
          "model":["nnn","mmm"],
          childrens: []
      },
      {
          _id:ObjectId("5e55c16f-1b39-48ca-8f79-7acc373dca13"),
          "model":["nnn","mmm"],
          childrens: [
          {
              _id:ObjectId("fb47003b-8b9f-4c1c-bc6c-cf51c0dbb8bc"),
              "model":["nnn","mmm"],
              childrens:[]
          }
          ]
      }
      ]
 },
 {
     _id:ObjectId("8d06e6af-5f68-4559-9712-706afed5c0db"),
     "model":["nnn","mmm"],
     childrens:[]
 }]
 }

I have tried the following query, but that was also not giving exact output.

db.collection.aggregate([
  {
    "$match": {
      "_id": "3df61406-a65c-480d-b258-85786caa871e"
    }
  },
  {
    $unwind: "$childrens"
  },
  {
    $graphLookup: {
      from: "collection",
      startWith: "$childrens",
      connectFromField: "childrens",
      connectToField: "_id",
      as: "childrensList"
    }
  }
])
1 Like

Hi @Tamilselvan_95764 Welcome to MongoDB Community Forum,

See a topic this will help you,

Hai @turivishal i have checked that topic, but document structure was different, so that query not suits for me.
Can you please give your solutions here Mongo playground ? Thanks in advance.

Please see your solution Playground.

Aggregation Query
db.collection.aggregate([
  {
    $match: {
      _id: "3df61406-a65c-480d-b258-85786caa871e"
    }
  },
  {
    $graphLookup: {
      from: "collection",
      startWith: "$childrens",
      connectFromField: "childrens",
      connectToField: "_id",
      depthField: "level",
      as: "children"
    }
  },
  {
    $unwind: {
      path: "$children",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $sort: {
      "children.level": -1
    }
  },
  {
    $group: {
      _id: "$childrens",
      parent_id: {
        $first: "$_id"
      },
      model: {
        $first: "$model"
      },
      children: {
        $push: "$children"
      }
    }
  },
  {
    $addFields: {
      children: {
        $reduce: {
          input: "$children",
          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",
                    [
                      {
                        $mergeObjects: [
                          "$$this",
                          {
                            children: {
                              $filter: {
                                input: "$$prev",
                                as: "e",
                                cond: {
                                  $in: [
                                    "$$e._id",
                                    "$$this.childrens"
                                  ]
                                }
                              }
                            }
                          }
                        ]
                      }
                    ]
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $addFields: {
      children: "$children.presentChild"
    }
  }
])
1 Like