如何在Snowflake中检查嵌套JSON的所有regions数组是否为空

核心思路 #

逐层展开嵌套的JSON数组（objects → clusters → regions），验证是否存在非空的regions数组，最终返回布尔值表示所有regions是否都为空。

实现方法 #

方法1：使用NOT EXISTS快速判断

通过FLATTEN逐层展开嵌套数组，利用NOT EXISTS检查是否存在非空的regions数组，一旦找到非空项直接返回false，性能更高效。

WITH test_data AS (
    -- 示例1：所有regions为空
    SELECT PARSE_JSON('{
        "objects": [
            {
                "zone": "zone_name",
                "clusters": [
                    { "cluster_name": "abc", "regions": [] },
                    { "cluster_name": "def", "regions": [] },
                    { "cluster_name": "ghi", "regions": [] }
                ]
            }
        ]
    }') AS json_col
    UNION ALL
    -- 示例2：存在非空regions
    SELECT PARSE_JSON('{
        "objects": [
            {
                "zone": "zone_name",
                "clusters": [
                    { "cluster_name": "abc", "regions": [] },
                    { "cluster_name": "def", "regions": [] },
                    { "cluster_name": "ghi", "regions": ["UK","london"] }
                ]
            }
        ]
    }') AS json_col
)
SELECT
    json_col,
    NOT EXISTS (
        SELECT 1
        FROM FLATTEN(input => json_col:objects) obj
        , FLATTEN(input => obj.value:clusters) clus
        WHERE ARRAY_SIZE(clus.value:regions) > 0
    ) AS all_regions_empty
FROM test_data;

方法2：使用BOOL_AND聚合判断

逐层展开所有嵌套数组后，用BOOL_AND聚合每个regions数组的空值判断结果，只有全部为空时返回true。

WITH test_data AS (
    SELECT PARSE_JSON('{
        "objects": [
            {
                "zone": "zone_name",
                "clusters": [
                    { "cluster_name": "abc", "regions": [] },
                    { "cluster_name": "def", "regions": [] },
                    { "cluster_name": "ghi", "regions": [] }
                ]
            }
        ]
    }') AS json_col
    UNION ALL
    SELECT PARSE_JSON('{
        "objects": [
            {
                "zone": "zone_name",
                "clusters": [
                    { "cluster_name": "abc", "regions": [] },
                    { "cluster_name": "def", "regions": [] },
                    { "cluster_name": "ghi", "regions": ["UK","london"] }
                ]
            }
        ]
    }') AS json_col
)
SELECT
    json_col,
    BOOL_AND(ARRAY_SIZE(clus.value:regions) = 0) AS all_regions_empty
FROM test_data
, LATERAL FLATTEN(input => json_col:objects) obj
, LATERAL FLATTEN(input => obj.value:clusters) clus
GROUP BY json_col;

关键函数说明 #

• FLATTEN：展开JSON数组，将数组中的每个元素转为单独的行。
• ARRAY_SIZE：返回数组的元素个数，0表示空数组。
• NOT EXISTS：判断子查询是否存在结果，不存在则返回true。
• BOOL_AND：聚合函数，仅当所有输入布尔值为true时，返回true。

内容的提问来源于stack exchange，提问作者u6765