Recursively apply a projection to a nested object in Mongo DB

I have a data structure like this in MongoDB. It can have many levels:

{
    _id: ObjectId("123"),
    name: "Top level",
    someData: "hello world",
    parent: null,
    children: {
        "456": {
            _id: ObjectId("456"),
            name: "Nested Obj",
            parent: "123",
            someData: "lorem ipsum",
            children: {
                "963": {
                    _id: ObjectId("963"),
                    name: "2nd level nesting",
                    parent: "456",
                    someData: "some data",
                    children: {}
                }
        },
        "798": {
            _id: ObjectId("798"),
            parent: "123",
            name: "Another entry",
            someData: "more data here",
            children: {},
        }
    }
}

Is there a way to make a query that would allow me to:

1. Recursively apply a projection to all nested objects.
2. Create a calculated field that contains the path to get to each object.

For example, say that I am only interested in the _id and name fields, I would expect to see this as a result of the query:

{
    _id: ObjectId("123"),
    name: "Top level",
    path: []
    children: {
        "456": {
            _id: ObjectId("456"),
            name: "Nested Obj",
            path: ["123"]
            children: {
                "963": {
                    _id: ObjectId("963"),
                    name: "2nd level nesting",
                    path: ["123", "456"]
                    children: {}
                }
        },
        "798": {
            _id: ObjectId("798"),
            name: "Another entry",
            path: ["123"]
            children: {},
        }
    }
}

If you are definitely stuck with this nested data structure, here are two solutions. Before that, I will reiterate the points from my comments above:

• See the docs for Data models for Tree Structures
• You would want to be able to use $graphLookup for such queries.
• And Wernfried Domscheit's comment: you should not use dynamic values as field names.

1. Using a deeply nested expression in an aggregation

But it's limited by number of levels of nesting. You either need to copy-paste to the level you want or use code to generate the nesting to the max level you want. This won't work for infinite levels of nesting and you need to decide on how many.

You can't go beyond the 16 MB document limit. So even with the smallest children objects like this, the max nested level can't be "anything".

{ "1": { _id: "1", children: { "2": { _id: "2", children: { "3": { ... } } } } }

If each child-document was just 10 bytes, the max level is definitely less than 1.6 million . Not sure what the MongoDB max nesting level is for queries or documents. Realistically, your docs are probably nested to a max of 10-100 levels.

In the aggregation below:

• I have intentionally used the same variable names in vars for the inner levels, for ease of copy-pasting.
  • (The var is needed to keep track of the current value of path being generated.)
• Convert each child object with arbitrary field names to an array of {k: <key>, v: <value>} objects.
• $map those objects to create a new ones with the path.
  • add or remove fields as required in the in expression.
• In the map operation, initially set the path to the id of the parent. In the nested levels, concat the previous path and the current parent.
• Then convert that array of {k: ..., v: ...} objects back to an object, the way they were.

db.collection.aggregate({
  $set: {
    children: {
      $arrayToObject: {
        $map: {
          input: { $objectToArray: "$children" },
          as: "c",
          in: {
            $let: {
              vars: {
                path: [ "$$c.v.parent" ]
              },
              in: {
                k: "$$c.k",
                v: {
                  _id: "$$c.v._id",
                  name: "$$c.v.name",
                  path: "$$path",
                  children: {
                    // copy-paste this full { ... } object
                    // as value of `children: {}` below
                    $arrayToObject: {
                      $map: {
                        input: { $objectToArray: "$$c.v.children" },
                        as: "c",
                        in: {
                          $let: {
                            vars: {
                              path: {
                                $concatArrays: [
                                  "$$path",
                                  ["$$c.v.parent"]
                                ]
                              }
                            },
                            in: {
                              k: "$$c.k",
                              v: {
                                _id: "$$c.v._id",
                                name: "$$c.v.name",
                                path: "$$path",
                                // replace the `{}` value below
                                children: {}
                              }
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
})

Working example with 2-levels of nesting - top level, 1st level, 2nd level.

For further nesting, copy-paste the children: { $arrayToObject: { ... } } part into the inner-most/bottom-most children: {} and keep repeating till the max-level of nesting you have. Here is an example with the copy-paste done correctly to 5 levels along with more nested data.

2. Using $function and JS

If you are able to use Javascript functions and Map-Reduce on your MongoDB instance, then this recursive code will run for any arbitrary levels of nesting.

db.collection.aggregate({
  $set: {
    children: {
      $function: {
        body: function (childrenObj) {
          function processChildren(children, path) {
            for (const key in children) {
              if (children.hasOwnProperty(key)) {
                const child = children[key];
                child.path = [...path];
                if (child.children) {
                  const nextPath = [...path, child._id];
                  processChildren(child.children, nextPath);
                }
              }
            }
          }
          
          for (const key in childrenObj) {
            if (childrenObj.hasOwnProperty(key)) {
              const parent = childrenObj[key];
              if (parent && parent.children) {
                processChildren(parent.children, [parent._id]);
              }
            }
          }
          
          return childrenObj;
        },
        args: ["$children"],
        lang: "js"
      }
    }
  }
})

Working example in Mongo Playground. It is currently on v6.0.21 and requires converting the JS code into a single line string, the alternative syntax for the body.

Using JS functions is already deprecated in MongoDB and at some point it will become completely unavailable. You may already be using an instance which doesn't have JS enabled.

3. Do it in application code

If you can't use JS functions or don't want to statically or dynamically generate the nested aggregation then, as mentioned in the comments and by you, you will need to do this in application code (JS, Python, Java, Go, etc.). Either at query time or by updating all documents - by fetching each one, adding the nested path's, and saving to the DB.