CosmosDB SQL to query "any" child field

Question

Given a document structure like below, where "variants" has N id based sub-entries, I would like to filter on the inner "sku" field. Something akin to this:

SELECT * FROM c WHERE c.variants.?.sku = "some_sku_1"

Here "some_id_1" and "some_id_2" are id values, data driven, and cannot be part of the query.

Is this possible with Cosmos DB and if so, how?

{
    "id": "45144",
    "variants": {
        "some_id_1": {
            "sku": "some_sku_1",
            "title": "some title 1"
        },
        "some_id_2": {
            "sku": "some_sku_2",
            "title": "some title 2"
        }
    }
}

Chris Anderson-MSFT Chris Anderson-MSFT · Accepted Answer · 2018-10-16T23:07:46

You can't do that with that schema without using a UDF/SPROC, but if you change the schema slightly, you can do it.

Schema:

{
    "id": "45144",
    "variants": [
        {
            "id": "some_id_1",
            "sku": "some_sku_1",
            "title": "some title 1"
        },
        {
            "id": "some_id_2"
            "sku": "some_sku_2",
            "title": "some title 2"
        }
    ]
}

Query:

SELECT * FROM c IN Item.variants WHERE c.sku == "some_sku_1"

Check out this article to get a good idea of what's possible with that "IN' statement, which allows you to iterate over objects. https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-sql-query#Advanced

CosmosDB SQL to query "any" child field

1 Answers