Snowflake中API返回JSON数组的扁平化与值解析方案问询

问题背景 #

我有一个Snowflake表，其中VARCHAR列存储API输入数据，需要处理300+列的扁平化操作。其中某字段包含JSON数组（元素数量1个或多个，键顺序固定），要求将数组中每个元素的键值（含嵌套urls的所有值）解析为对应行。现有查询存在两个问题：所有urls值被合并且仅返回第一组其他字段值；处理300+列时需将所有字段加入GROUP BY，操作繁琐。需要不受数组元素数量影响的通用方案。

输入示例 #

[{"active":false,"urls":{"6x6":"https://url.url.com/secure/?size=xsmall&ownerId=B12345&id=123","4x4":"https://url.url.com/secure/?size=small&ownerId=B12345&id=123","22x22":"https://url.url.com/secure/?size=medium&ownerId=B12345&id=123","44x44":"https://url.url.com/secure/?ownerId=B12345&id=123"},"displayName":"Name1,Lname1","emailAddress":"","key":"B12345","name":"B12345","self":"https://url.url.com/rest/api/2/user?username=B12345","timeZone":"India/Mumbai"},{"active":true,"urls":{"6x6":"https://url.url.com/secure/?size=xsmall&ownerId=A12345&id=456","4x4":"https://url.url.com/secure/?size=small&ownerId=A12345&id=456","22x22":"https://url.url.com/secure/?size=medium&ownerId=A12345&id=456","44x44":"https://url.url.com/secure/?ownerId=A12345&id=456"},"displayName":"Name1,Lname2.","emailAddress":"name.lname@abcdef.com","key":"A12345","name":"A12345","self":"https://url.url.com/rest/api/2/user?username=A12345","timeZone":"India/Mumbai"},{"active":true,"urls":{"6x6":"https://url.url.com/secure/?size=xsmall&ownerId=C12345&id=456","4x4":"https://url.url.com/secure/?size=small&ownerId=C12345&id=456","22x22":"https://url.url.com/secure/?size=medium&ownerId=C12345&id=456","44x44":"https://url.url.com/secure/?ownerId=C12345&id=456"},"displayName":"Name1,Lname3.","emailAddress":"name.lname@abcdef.com","key":"C12345","name":"C12345","self":"https://url.url.com/rest/api/2/user?username=C12345","timeZone":"India/Mumbai"}]

期望输出 #

[[false,[https://url.url.com/secure/?size=xsmall&ownerId=B12345&id=123,https://url.url.com/secure/?size=small&ownerId=B12345&id=123,https://url.url.com/secure/?size=medium&ownerId=B12345&id=123,https://url.url.com/secure/?ownerId=B12345&id=123], Name1, Lname1, ,B12345, B12345, https://url.url.com/rest/api/2/user?username=B12345,India/Mumbai],[true,[https://url.url.com/secure/?size=xsmall&ownerId=A12345&id=456,https://url.url.com/secure/?size=small&ownerId=A12345&id=456,https://url.url.com/secure/?size=medium&ownerId=A12345&id=456,https://url.url.com/secure/?ownerId=A12345&id=456], Name1, Lname2.,name.lname@abcdef.com, A12345,A12345,https://url.url.com/rest/api/2/user?username=B12345,India/Mumbai],[true,[https://url.url.com/secure/?size=xsmall&ownerId=C12345&id=456,https://url.url.com/secure/?size=small&ownerId=C12345&id=456,https://url.url.com/secure/?size=medium&ownerId=C12345&id=456,https://url.url.com/secure/?ownerId=C12345&id=456], Name1, Lname3.,name.lname@abcdef.com, C12345,C12345,https://url.url.com/rest/api/2/user?username=B12345,India/Mumbai]]

尝试的查询 #

SELECT CONCAT('[[',REPLACE(GET(flattened.value,  'active'), '"', '') , ', ','[',listagg(CASE WHEN flattened_nested.value LIKE '%https%' THEN 'https:' || REPLACE(SPLIT_PART(flattened_nested.value, ':', 2), '"', '') ELSE NULL END,',')within group (order by null),']', ', ',REPLACE(GET(flattened.value,  'displayName'), '"', '') , ', ',REPLACE(GET(flattened.value,  'emailAddress'), '"', '') , ', ',REPLACE(GET(flattened.value,  'key'), '"', '') , ', ',REPLACE(GET(flattened.value,  'name'), '"', '')  , ', ',REPLACE(GET(flattened.value,  'self'), '"', '') , ', ',REPLACE(GET(flattened.value,  'timeZone'), '"', ''),   ']]')  as output_column 
FROM snowflake_table  SRC,
LATERAL FLATTEN(input=>SRC.json_values:"fields":"field_12345") AS flattened,
LATERAL FLATTEN(input=>flattened.value:urls) AS flattened_nested 
group by  flattened.value;

现有问题 #

• 返回结果将所有元素的urls值合并，仅返回第一组其他字段值；
• 处理300+列时，所有字段需加入GROUP BY子句，操作繁琐。

解决方案 #

核心思路 #

1. 先扁平化顶层JSON数组，得到每个独立的用户对象；
2. 对每个用户对象的urls字段，用OBJECT_VALUES提取所有URL值并保持顺序，避免二次扁平化后聚合的问题；
3. 直接引用JSON属性提取其他字段，无需手动逐个拼接，适配300+列的场景。

优化后的基础查询 #

SELECT 
    CONCAT(
        '[',
        flattened.value:active::STRING, ',',
        '[', ARRAY_TO_STRING(OBJECT_VALUES(flattened.value:urls), ','), '],',
        flattened.value:displayName::STRING, ',',
        flattened.value:emailAddress::STRING, ',',
        flattened.value:key::STRING, ',',
        flattened.value:name::STRING, ',',
        flattened.value:self::STRING, ',',
        flattened.value:timeZone::STRING,
        ']'
    ) AS output_row
FROM snowflake_table SRC,
LATERAL FLATTEN(input => PARSE_JSON(SRC.json_values:"fields":"field_12345")) AS flattened;

适配300+列的通用动态方案 #

如果需要自动处理所有字段（除了urls），可以结合OBJECT_KEYS和动态SQL生成字段列表，避免手动编写300+个字段：

-- 先获取所有字段名（排除urls）
WITH field_names AS (
    SELECT DISTINCT 
        KEY 
    FROM snowflake_table SRC,
    LATERAL FLATTEN(input => PARSE_JSON(SRC.json_values:"fields":"field_12345")[0]) -- 取第一个元素的键作为字段列表
    WHERE KEY != 'urls'
),
-- 生成动态SQL语句
dynamic_sql AS (
    SELECT 
        CONCAT(
            'SELECT CONCAT(''['',',
            STRING_AGG('flattened.value:' || KEY || '::STRING', ','), ',',
            ''',['', ARRAY_TO_STRING(OBJECT_VALUES(flattened.value:urls), '',''), ']'',',
            ''']'') AS output_row FROM snowflake_table SRC, LATERAL FLATTEN(input => PARSE_JSON(SRC.json_values:"fields":"field_12345")) AS flattened;'
        ) AS sql_stmt
    FROM field_names
)
-- 执行动态SQL
EXECUTE IMMEDIATE (SELECT sql_stmt FROM dynamic_sql);

方案说明 #

• 避免聚合错误：使用OBJECT_VALUES(flattened.value:urls)直接提取所有URL值为数组，无需二次扁平化和LISTAGG，确保每个用户的URL独立分组；
• 简化300+列处理：动态SQL自动生成所有字段的引用，无需手动编写每个字段和加入GROUP BY；
• 保持键顺序：OBJECT_VALUES在Snowflake中会保留JSON对象的键顺序（与输入一致），符合需求。

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