Cosmos DB Query Array value using SQL

Question

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

I'm not sure why azure-data-explorer tag was removed. I am using the Azure Data Explorer to query the cosmos db. can i add it back @Yoni L. — gopi nath
– gopi nath, Commented Apr 22, 2022 at 17:53
to my understanding, this question has nothing to do with the Big Data analytics platform named "Azure Data Explorer", rather it discusses a query run against an entirely different Azure service, named "Cosmos DB". — Yoni L.
– Yoni L., Commented Apr 22, 2022 at 17:57
Correct - Azure Data Explorer has nothing to do with the "Data Explorer" tab in Cosmos DB; it's related to Kusto. It's been correctly removed. However, the other tags are problematic too - this shouldn't be tagged with sql - I fixed this. Final note: I believe you meant to say you have some documents in a Cosmos DB collection, not "JSON files," correct? — David Makogon
– David Makogon, Commented Apr 22, 2022 at 23:10

David Makogon · Accepted Answer · 2022-04-22 23:18:41Z

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.

Collectives™ on Stack Overflow

Cosmos DB Query Array value using SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related