MongoDB - get subdocuments from arbitrary depth and object structure

Question

I have a collection that has objects of various random structures/depths. Within them, there is one object that I want to get as my results, which has a specific key/value pair that I can use to find them.

An example from my collection:

{
    "_id": ObjectId("123"),
    "someKey": "blue",
    "children": [
        {
            "foo": "bar",
            "anotherKey": "anotherValue",
            "whateverA": 111
        }
    ]
}
{
    "_id": ObjectId("456"),
    "blahKey": "dog",
    "children": [
        {
            "anotherRandom": "randomValue",
            "children": [
                {
                    "moreRandom": "stuffValue",
                    "children": [
                        {
                            "foo": "bar",
                            "animalKey": "legsValue",
                            "whateverB": 222
                        }
                    ]
                }
            ]
        }
    ]
}

I would like to search for subdocuments that contain "foo: bar", and get a result that looks like the following:

{
    "foo": "bar",
    "anotherKey": "anotherValue",
    "whateverA": 111
}
{
    "foo": "bar",
    "animalKey": "legsValue",
    "whateverB": 222
}

Then I can paginate the results. Is this even possible in MongoDB 5?

Thank you.

Please see [this](https://stackoverflow.com/questions/71638088/mongodb-javascript-function-not-returning-expected-value-in-mongoplayground/71650522#71650522) — nimrod serok, May 09 '22 at 14:58

nimrod serok · Accepted Answer · 2022-05-19T15:51:21.167

2

If we use the solution of @rickhg12hs

You can do something like:

EDIT: fixed to handle case of not found (Thanks to a comment by @Jimba):

db.collection.aggregate([
  {
    $addFields: {
      res: {
        $function: {
                 body: "function drill(t, n) {if (n && n.length > 0){for (let elem of n){if(elem['foo'] && elem['foo'] === 'bar'){t.push(elem);}else {drill(t, elem.children)}}}return t}",
          args: [
            [],
            "$children"
          ],
          lang: "js"
        }
      }
    }
  },
  {
    $project: {
      res: {
        $cond: [
          {$gt: [{$size: "$res"}, 0]},
          {$arrayElemAt: ["$res", 0]},
          {res: null}
        ]
      },
      _id: 0
    }
  },
  {
    $match: {res: {$ne: null}}
  },
  {
    $replaceRoot: {newRoot: "$res"}
  }
])

As you can see on this playground example.

We can use $function to recursively look for this key named foo and return the object that contains it.

Edit according to a question in the comment:

You can use your code to manipulate it according to your needs: for example in js:

const key = 'foo';
const val = 'bar';
const body = `function drill(t, n) {if (n && n.length > 0){for (let elem of n){if(elem[${key}] && elem[${key}] === ${val}){t.push(elem);}else {drill(t, elem.children)}}}return t}`;


db.collection.aggregate([
  {
    $addFields: {res: {$function: {body, args: [[], "$children"], lang: "js"}}}
  },
  {
    $project: {
      res: {
        $cond: [
          {$gt: [{$size: "$res"}, 0]},
          {$arrayElemAt: ["$res", 0]},
          {res: null}
        ]
      },
      _id: 0
    }
  },
  {
    $match: {res: {$ne: null}}
  },
  {
    $replaceRoot: {newRoot: "$res"}
  }
])

edited May 19 '22 at 15:51

answered May 09 '22 at 15:21

nimrod serok

14,151
2
11
33

I have one quick question regarding the above code: - How do I incorporate my `db.collection.find( { ... } )` search parameters in the above aggragate command? Thank you. – Jimba May 10 '22 at 10:09
Your query is a `json` up until it is executed. You can edit this `json` in your code, according to your needs before you execute the query. – nimrod serok May 10 '22 at 10:13
Updated the answer to contain this. – nimrod serok May 10 '22 at 10:22
WARNING: the above javascript function (drill) does not work when the key is not found. It produces the following error: `'newRoot' expression must evaluate to an object, but resulting value was: MISSING. Type of resulting value: 'missing'`, Playground example: https://mongoplayground.net/p/jix1_LjIXv5 – Jimba May 17 '22 at 09:07
Updated the answer accordingly – nimrod serok May 17 '22 at 09:55
Thank you for the update. Now it produces a lot of: ``` { "res": { "res": null } } ``` Is there a way to completely remove empty/null subdocuments, so they are not counted in the pagination as results? – Jimba May 19 '22 at 07:19
I think I got it. I needed to change the above `$cond` else condition from `{res:null}` to just null, and add a `{$match: { "res": { $ne: null } }} ` to remove null elements from the results. – Jimba May 19 '22 at 07:28
Updated accordingly. Thanks – nimrod serok May 19 '22 at 15:59

MongoDB - get subdocuments from arbitrary depth and object structure

1 Answers1

Linked