filtering by unknown property names

Question

Given documents such as:

{
    "id": 1,
    "types":
    {
        "type1": { "visible": true },
        "type2": { "visible": false }
    }
},
{
    "id": 2,
    "types":
    {
        "type3": { "visible": true }
    }
},
{
    "id": 3,
    "types":
    {
        "type4": { "visible": false }
    }
}

What SQL query should I write to return documents which have at least one type with "visible" == false?

Notice that "types" is an object, not an array. Actually it is a dictionary where property name is key. The property names of "types" object are not predictable, so I can't hardcode them in a query.

Answer 1

As far as I am known, there are no way to deal with it by using SQL directly.

There are two workaround. First one, change your schema of your document and using known key. Another one, create UDF to deal with this.

you can refer to Querying complex nested object in cosmosdb using sql Api