使用SQL的Cosmos DB查询数组值

Question

我的cosmos DB中有几个结构如下的JSON文件。

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

我想查询所有数组值为city = "San Diego“的结果/文件。

我尝试了以下sql查询

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'

我得到的结果是0 - 0

Answer 1

您需要从整个文档中查询数据，其中USA.Location.City数组包含一个项。例如：

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

这会给你你想要达到的目标。

注意:您的模式中有一个轻微的反模式，使用"USA“作为键，这意味着您无法轻松地查询所有的位置名称。您应该将其替换为如下所示：

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

这使您可以查询所有不同的国家。然后，上面的查询只需要稍微修改一下：

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

请注意，该查询现在适用于任何国家，您可以将country值作为参数传递(vs需要将国家名称硬编码到查询中，因为它是一个实际的键名)。

不要把价值作为你的钥匙。