azure - 使用 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")

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

Tl;dr 不要将值作为键。