Index Object without recursion

Question

Index Object without recursion

Derek Scheller 1

As a CosmosDB (SQL API) user I would like to index all non object or array properties inside of an object.

We have a sizable data set that is very dynamic from object to object, however we currently restrict users to querying records by only top level properties inside the data object.

Give the data set below I would like to configure my indexing policy to not recurse beyond the properties inside of data. I have not found a way to support this based on what is in the docs and many attempts in cosmos have proved fruitless.

   [  
     {  
       "partitionKey": "f402a704-19bb-4f4d-93e6-801c50280cf6",  
       "id": "4a7a11e5-00b5-4def-8e80-132a8c083f24",  
       "data": {  
         "country": "Belgium",  
         "employees": 250,  
         "teammates": [  
           { "name": "Jake", "id": 123 ...},  
           { "name": "kyle", "id": 3252352 ...}  
         ],  
         "user": {  
           "name": "Brian",  
           "addresses": [{ "city": "Moscow" ...}, { "city": "Moscow" ...}]  
         }  
       }  
     },  
     
     {  
       "partitionKey": "f402a704-19bb-4f4d-93e6-801c50280cf6",  
       "id": "36b563cc-1db9-48cb-82cd-eba0253990de",  
       "data": {  
         "Co": 1,  
         "Mth": "2020-03-01T00:00:00",  
         "Vendor": 9702,  
         "address": { "city": "Moscow", "state": "ohio" ...},  
         "jobs": [{ "name": "UPS Store" ...}]  
       }  
     },  
     
     {  
       "partitionKey": "f402a704-19bb-4f4d-93e6-801c50280cf6",  
       "id": "368d6090-70f7-458e-9146-1cb1ad5b7f5c",  
       "data": {  
         "DocName": "new test doc",  
         "id": 234234,  
         "user_permissions": ["read", "write", "delete"]  
       }  
     }  
   ]

Policies like /data/* will not work because it would then index /data/teamates/name ... and so on.
/data/? => assumes data is a value type which it never will be so this doesn't work.
/data/ and /data/*/? and /data/*? are not accepted by cosmos as valid policies.

Only value types in the data object will ever be queried on and therefor those properties need to be indexed.
I have tried many iterations but it seems that options don't work for various reasons, is there a way to support what I am trying to do?

Anurag Sharma 17,636 Reputation points

2022-08-30T04:27:46.823+00:00
Hi @Derek Scheller , welcome to Microsoft Q&A forum.

From what I understand from the query, your document structure could change for different documents and you need to exclude some of the properties in the data object and include only those that have scaler values.

As much as I am aware there is no direct way to define a single include path for this kind of requirements. However what we can do is to create a exclude strategy to exclude everything from data object:

Excluded Path: /data/*

And then define include strategy for those specific properties that we need to include. We might need to create multiple include paths to include all those scaler values. One example below:

Included Path: /data/Vendor/*

Reference: Include/exclude strategy

Please let us know if this helps or else we can discuss further.
Derek Scheller 1 Reputation point

2022-08-30T19:50:18.2+00:00
I don't think this would work.
The data objects being dynamic and numbering in the 100ks means we would have to constantly update our indexing policy to exclude or include new paths depending on which way we went. Our indexing policy would end up being 1000s of lines long and we would be adding new paths to it many times a day. Just adding one path to the policy on a data set this large can take many hours, so doing that many times a day simply would not work.

Really all we are looking for is a way to declare a termination on recursion, essentially a depth variable on the path. For example I could see the definition looking like this

"IncludedPaths" : [ { "Path": "/data/*", "Depth" : 1 } ]

This would tell cosmos to index everything under the data path but don't recurse beyond the first level, similar to other features -1 could be the default to recurse all the way till the end of the tree structure.