Given an id of a document with recursive field `children`, find all documents that reference the document or any of its children

Question

I have a collection of product folders productfolders and a collection of products products.

const ProductFolderSchema = new Schema(
  {
    folderName: { type: String, required: true },
    parent: { type: Schema.Types.ObjectId, ref: 'ProductFolder' },
    children: [{ type: Schema.Types.ObjectId, ref: 'ProductFolder' }],
  }
);

const ProductSchema = new Schema<TProductSchema>(
  {
    productName: String,
    folder: { type: Schema.Types.ObjectId, ref: 'ProductFolder' },
  },
);

I have a backend that receives query parameter folderId and should return all products whose folder property is equal to folderId or is a descendant of folder with _id of folderId (meaning folder is one of the children of folder with _id of folderId - children can be nested deep inside children's children).

For example, consider collections productfolders and products that look like this:

const productfolders = [
  {
    "_id": "62e74dac78c13b738874e1a9",
    "folderName": "Weapons",
    "children": [
      {
        "_id": "62e74dd278c13b738874e1ac",
        "folderName": "Bows",
        "parent": "62e74dac78c13b738874e1a9",
        "children": [
          {
            "_id": "62e74ddb78c13b738874e1b1",
            "folderName": "Long Bows",
            "parent": "62e74dd278c13b738874e1ac",
            "children": [],
          },
          {
            "_id": "62e74de278c13b738874e1b7",
            "folderName": "Short Bows",
            "parent": "62e74dd278c13b738874e1ac",
            "children": [],
          }
        ],
      },
    ]
  }
];

const products = [
  {
    "productName": "Curved Bow",
    "folder": "62e74de278c13b738874e1b7",
    "_id": "62e237368fbde6ed77e3e489"
  }
];

When I pass folderId of 62e74dac78c13b738874e1a9 ("folderName": "Weapons"), I want "Curved Bow" product to be found because its folder is a deep children of "Weapons" folder.

I think you can only search something in recursive structures using $graphLookup but I couldn't figure out how to pass the variable folderId to its startsWith operator(sorry if I'm using the wrong naming of things)

Here's example db: https://mongoplayground.net/p/Yxps44cfG28

Here's my code that doesn't find anything:

const products = await ProductModel.aggregate([
  {
    $graphLookup: {
      from: 'productfolders',
      startWith: folderId, // can only pass mongo expressions here, not working with variables
      connectFromField: '_id',
      connectToField: 'children',
      as: 'output',
    },
  },
]);

How do I find all products whose folder property is equal to or is a deep children of folder with folderId?

Answer 1

Your search was quite close. I guess the confusion came from having both parent and children fields in your schema.

As I mentioned in the comment, I don't see how you keep children up to date for all parents when you add a new folder to one of the children, but I will leave it with you. For now I will just ignore the children array. parent is enough for $graphLookup:

db.products.aggregate([
  {
    "$graphLookup": {
      "from": "productfolders",
      "startWith": "$folder",
      "connectFromField": "parent",
      "connectToField": "_id",
      "as": "path"
    }
  },
  {
    "$match": {
      "path._id": "<Folder to search>"
    }
  }
])

Here $graphLookup builds a flat array of all parents for each product:

• startWith is the folder from products document
• connectToField is the corresponding field in productfolders collection
• productfolders is thee field of the productfolders document to use in the next recursive call instead of the startWith

So the path array for the Fireworks ("folder": "62e7bead91041bdddf25dd4b") will be:

[
  {
    "_id": "62e7bead91041bdddf25dd4b",
    "folderName": "Short swords\n",
    "parent": "62e79c6191041bdddf25dd1c"
  },
  {
    "_id":    "62e79c6191041bdddf25dd1c",
    "folderName": "Swords",
    "parent": "62e74dac78c13b738874e1a9"
  },
  {
    "_id":    "62e74dac78c13b738874e1a9",
    "folderName": "Weapons"
  }
]

Do you see the chain - parent of the document matches _id of the next in the chain, right?

So after the $graphLookup stage you have full folder path from the root to the product's folder for each product. Now you just $match products that do have the folder in question anywhere in the chain.

There is a simplified example on https://mongoplayground.net/p/Cy-_SzzcdNT

Answer 2

Based on this question by @rickhg12hs, you can use $function for this recursive search to get an array of nested folders. Then all is left is to use a regular $lookup:

db.productfolders.aggregate([
  {$project: {
      folders: {
        $function: {
          body: "function drill(t, n) {if (n.length > 0) {for (let elem of n) {t.push(elem._id); drill(t, elem.children)}} return t};",
          args: [["$_id"], "$children"],
          lang: "js"
        }
      }
    }
  },
  {$lookup: {
      from: "products",
      localField: "folders",
      foreignField: "folder",
      as: "products"
    }
  }
])

See how it works on the playground example