Query with dynamic Property name in document in Cosmos DB-CodePudding

I went through many post on SO. Not finding what I need.

I am having a collection document which is like below

    [
        {
           "Key1": "Value",
           "Key2": 1,
           "Key3": "Value",
           "Key4": 5,
           "Date": "2021-10-25T04:20:00",
           "Key5": {
              "a3": "2021-09-03T09:32:55",
              "a199": "2021-09-03T09:32:55"
           }
        },
        {
           "Key1": "Value",
           "Key2": 1,
           "Key3": "Value",
           "Key4": 5,
           "Date": "2021-10-25T04:20:00",
           "Key5": {
              "a500": "2021-09-03T09:32:55",
              "a39": "2021-09-03T09:32:55"
           }
        }
     ]

I am using this query

Select top 10 * from Collection c where c.Key5.a3 = '2021-09-03T09:32:55'

Now I need to filter the data from this collection where Key5.a3 or Key5.a199 is equals to something. The problem here is I don't know Which properties will be coming under Key5, Those are created by system randomly from UI and each document will have different properties in Key5.

I need to build a query for this collection which will not care about the Key5.a199/399/500 or anything but just compare the value associated with it.

CodePudding user response：

You cannot have dynamic queries that ignore a particular property's name.

The problem you have is created by storing data values as property names (both with Key1 through Key4 and your more complex Key5. Specifically:

"Key5": {
   "a500": "2021-09-03T09:32:55",
   "a39": "2021-09-03T09:32:55"
}

You've embedded metadata into your property names, which is the issue when attempting to index or search.

To avoid this, refactor your data model to have consistent property names. Something like this, where you have well-defined key-value pairs. You can then have multiple values, within an array. Something like this:

{
   "Key5": [
      { "property_key": "a500", "property_value": "2021-09-03T09:32:55" },
      { "property_key": "a39", "property_value" : "2021-09-03T09:32:55" }
   ]
}

At this point, you'd now have a consistent set of property names to query.

You'd need to custom-tailor this to your needs, but this shows how to avoid the problem of storing real data in property names.