How to Get hierarchical nested object of mongoose data

Question

I have data like this:

[{ "id": 80, "Category": "1", "sub_category": null},
 { "id": 81, "Category": "1.1", "sub_category": 80},
{ "id": 82, "Category": "1.1.1", "sub_category": 81},
{ "id": 83, "Category": "1.2", "sub_category": 80},
{ "id": 84, "Category": "1.1.1.1", "sub_category": 82}]

i am using express js with mongoose. node version 10.19.0 and npm version 6.14.9 and express js version 4.17.1. i have a category table and i have to get data with hierarchical nested data. like this

 { "id": 80, 
   "Category": "1", 
   "sub_category": null, 
   "SubCategories": [{ "id": 81, 
                       "Category": "1.1", 
                       "sub_category": 80,
                       "SubCategories": [{ "id": 82, 
                                            "Category": "1.1.1", 
                                            "sub_category": 81,
                                            "SubCategories": [{ "id": 84, 
                                                                "Category": "1.1.1.1", 
                                                                "sub_category": 82,
                                                                "SubCategories": [...]
                                                              },]
                                           },]
                     },
                     { "id": 83, 
                       "Category": "1.2", 
                       "sub_category": 80,
                       "SubCategories": [...]
                     },]
 }

I tried graphLookup query for that,

CategoryModel.aggregate([{
  $graphLookup: {
    from: "examcategories", 
    startWith: "$id", 
    connectFromField: "id", 
    connectToField: "sub_category", 
    as: "SubCategoies" 
  } 
}])

and get output like this

{ 
  "id": 80, 
  "Category": "1", 
  "sub_category": null,
  "SubCategories": [{ 
    "id": 81,
    "Category": "1.1",
    "sub_category": 80 
   },{ 
     "id": 82,
     "Category":"1.1.1",
     "sub_category": 81 
   },{ 
     "id": 84,
     "Category": "1.1.1.1",
     "sub_category": 82 
   },{ 
     "id": 83,
     "Category": "1.2",
     "sub_category": 80,
   }
]}

Answer 1

The $graphLookup performs a recursive search on a collection, with options for restricting the search by recursion depth and query filter, but just $groupLookup not enough for this, need to use more operators

• $match filter that records only have sub_category is null
• $graphLookup to get sub category records and depth number in defthField level
• $unwind deconstruct SubCategories array and allow to not remove empty sub categories
• $sort by depth level field level in descending order
• $group by id field and reconstruct SubCategories array

CategoryModel.aggregate([
  { $match: { sub_category: null } },
  {
    $graphLookup: {
      from: "examcategories",
      startWith: "$id",
      connectFromField: "id",
      connectToField: "sub_category",
      depthField: "level",
      as: "SubCategories"
    }
  },
  {
    $unwind: {
      path: "$SubCategories",
      preserveNullAndEmptyArrays: true
    }
  },
  { $sort: { "SubCategories.level": -1 } },
  {
    $group: {
      _id: "$id",
      sub_category: { $first: "$sub_category" },
      Category: { $first: "$Category" },
      SubCategories: { $push: "$SubCategories" }
    }
  },

• $addFields now find the nested level sub categories and allocate to its level,
  • $reduce to iterate loop of SubCategories array.
  • initialize default field level default value is -1, presentChild is [], prevChild is [] for the conditions purpose
  • $let to initialize fields:
    • prev as per condition if both level are equal then return prevChild otherwise return presentChild
    • current as per condition if both level are equal then return presentChild otherwise []
  • in to return level field and prevChild field from initialized fields
    • presentChild $filter SubCategories from prev array and return, merge current objects with SubCategories array using $mergeObjects and concat with current array of let using $concatArrays
• $addFields to return only presentChild array because we only required that processed array

  {
    $addFields: {
      SubCategories: {
        $reduce: {
          input: "$SubCategories",
          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",
                          {
                            SubCategories: {
                              $filter: {
                                input: "$$prev",
                                as: "e",
                                cond: { $eq: ["$$e.sub_category", "$$this.id"] }
                              }
                            }
                          }
                        ]
                      }
                    ]
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  { $addFields: { SubCategories: "$SubCategories.presentChild" } }
])

Playground

---

Without $mergeObjects: Playground