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?

There is so much duplication in productfolders. Are you sure your collection looks like in the mongodbplayground link? How do you update folders in so many places to keep all these documents in sync? — Alex Blex, Aug 10 '22 at 23:37
All children is an array of ObjectIds so when I change a child in one place it changes everywhere — PYTHON DEVELOPER999, Aug 13 '22 at 01:32
Ah, that makes sense. For records `productfolders` "collection" in the question is not a mongodb collection but a model populated by mongoose. — Alex Blex, Aug 13 '22 at 21:46

score 1 · Accepted Answer · answered Aug 11 '22 at 00:16

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

score 0 · Answer 2 · answered Aug 06 '22 at 09:12

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

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

2 Answers2