CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
gopi nath
gopi nath

Reputation: 196

Cosmos DB Query Array value using SQL

I have several JSON files with below structure in my cosmos DB.

[
{
    "USA":  {
        "Applicable": "Yes",
        "Location": {
            "City": [
                "San Jose",
                "San Diego"
                ]
        }
    }
}]

I want to query all the results/files that has the array value of city = "San Diego".

I've tried the below sql queries

SELECT DISTINCT *
FROM c["USA"]["Location"]
WHERE ["City"] IN ('San Diego')

SELECT DISTINCT *
FROM c["USA"]["Location"]
WHERE ["City"] = 'San Diego'

SELECT c 
FROM c JOIN d IN c["USA"]["Location"]
WHERE d["City"] = 'San Diego'

I'm getting the results as 0 - 0

Upvotes: 1

Views: 2585

Answers (1)

David Makogon
David Makogon

Reputation: 71030

You need to query data from your entire document, where your USA.Location.City array contains an item. For example:

SELECT *
FROM c
WHERE ARRAY_CONTAINS (c.USA.Location.City, "San Jose")

This will give you what you're trying to achieve.

Note: You have a slight anti-pattern in your schema, using "USA" as the key, which means you can't easily query all the location names. You should replace this with something like:

{
    "Country": "USA",
    "CountryMetadata":  {
        "Applicable": "Yes",
        "Location": {
            "City": [
                "San Jose",
                "San Diego"
                ]
        }
    }
}

This lets you query all the different countries. And the query above would then need only a slight change:

SELECT *
FROM c
WHERE c.Country = "USA
AND ARRAY_CONTAINS (c.CountryMetadata.Location.City, "San Jose")

Note that the query now works for any country, and you can pass in country value as a parameter (vs needing to hardcode the country name into the query because it's an actual key name).

Tl;dr don't put values as your keys.

Upvotes: 3

Related Questions