Is it possible to query any sub node of a tree hierarchy, irresponsive of depth, using Cosmos' DB SQL?

Question

Given a hierarchical JSON structure:

{
  id = 1,
  text = "Root",
  children = [
    {
      id = 2,
      nodeType = "foo",
      children = [
        {
          {
            id = 3,
            nodeType = "foo",
            children = [
              {
              }
            ]
          },
          {
            id = 6,
            nodeType = "bar",
            children = [
              {
              }
            ]
          }
        }
      ]
    },
    {
      id = 4,
      nodeType = "foo",
      children = [
        {
        }
      ]
    }
  ]
}

The depth of the hierarchy is unknown. Using a Cosmos SQL query, is there a (performant) way of finding the IDs of all sub objects where nodeType == foo, which would result in IDs 2, 3, and 4?

I'm thinking the structure is just not right and I'd be better off saving sub objects as a flat array and keeping the tree information separate.

Answer 1

as @Mark Bwown in the comment section, there is no good or best way for your requirement. Posting the same to help other community members.

The only recommended approach is to just query for the root, deserialize it into a dynamic type, then traverse it in your code checking for non-null types or non-empty arrays.

Answer 2

Although it's not directly answering your question about doing it with Azure SQL on CosmosDB. I suggest you use the mongo api that cosmosdb supports. this gives you full feature filtering of subnode data. Examples here: https://docs.microsoft.com/en-us/azure/cosmos-db/mongodb/tutorial-query-mongodb