如何用Snowflake SQL从特定JSON数组提取数据并转为指定格式

解决方案SQL #

假设你的表名为your_table，存储JSON数组的varchar列名为json_col，可以用以下SQL实现需求：

-- 先将JSON数组拆分行，再转置为列
WITH parsed_data AS (
    SELECT
        REPLACE(JSON_EXTRACT_PATH_TEXT(value, '@id'), ' ', '') AS month_col,
        JSON_EXTRACT_PATH_TEXT(value, '$') AS metric_value
    FROM your_table,
         LATERAL FLATTEN(input => PARSE_JSON(json_col))
)
SELECT *
FROM parsed_data
PIVOT (
    MAX(metric_value) FOR month_col IN ('Month1', 'Month2', 'Month3', 'Month4', 'Month5', 'Month6')
) AS p;

如果需要输出为逗号分隔的字符串格式，可以再套一层处理：

-- 输出为逗号分隔的表头和数值行
WITH parsed_data AS (
    SELECT
        REPLACE(JSON_EXTRACT_PATH_TEXT(value, '@id'), ' ', '') AS month_col,
        JSON_EXTRACT_PATH_TEXT(value, '$') AS metric_value
    FROM your_table,
         LATERAL FLATTEN(input => PARSE_JSON(json_col))
),
pivoted_data AS (
    SELECT *
    FROM parsed_data
    PIVOT (
        MAX(metric_value) FOR month_col IN ('Month1', 'Month2', 'Month3', 'Month4', 'Month5', 'Month6')
    ) AS p
)
SELECT
    -- 生成表头行
    '''Month1,Month2,Month3,Month4,Month5,Month6''' AS result
UNION ALL
SELECT
    -- 生成数值行
    CONCAT_WS(',', "Month1", "Month2", "Month3", "Month4", "Month5", "Month6") AS result
FROM pivoted_data;

关键步骤说明 #

• PARSE_JSON(json_col)：把varchar类型的JSON字符串转换成Snowflake可识别的JSON数据类型
• LATERAL FLATTEN(...)：将JSON数组拆分成单独的行，每个数组元素对应一行
• JSON_EXTRACT_PATH_TEXT(value, '@id')：提取JSON元素里带特殊符号的键值，因为键名包含@、$这类特殊字符，必须用该函数指定
• REPLACE(..., ' ', '')：把Month 1里的空格去掉，转换成Month1，匹配目标格式的表头
• PIVOT：把行数据转置成列，将每个月份作为表头，对应数值作为列值
• CONCAT_WS：把各列数值用逗号连接成字符串，生成目标格式的数值行

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