Snowflake中如何将JSON多列月份字段转为单列？

方法1：改进UNPIVOT，过滤无效月份 #

你的UNPIVOT返回72行是因为把每条原始数据的12个月份全展开了，只要在结果里添加过滤条件，只保留值为1的月份即可，同时可以用DECODE快速映射月份缩写：

WITH test AS (
    SELECT 
        -- 保留原始业务字段，比如建筑面积、经纬度
        value['building_area']::FLOAT AS building_area,
        value['latitude']::FLOAT AS latitude,
        value['longitude']::FLOAT AS longitude,
        value['month_1.0']::INT AS January, 
        value['month_2.0']::INT AS February,
        value['month_3.0']::INT AS March,
        value['month_4.0']::INT AS April,
        value['month_5.0']::INT AS May,
        value['month_6.0']::INT AS June,
        value['month_7.0']::INT AS July,
        value['month_8.0']::INT AS August,
        value['month_9.0']::INT AS September,
        value['month_10.0']::INT AS October,
        value['month_11.0']::INT AS November,
        value['month_12.0']::INT AS December
    FROM logs, LATERAL FLATTEN(input => data) AS d
)
SELECT 
    building_area,
    latitude,
    longitude,
    DECODE(month,
        'January', 'JAN',
        'February', 'FEB',
        'March', 'MAR',
        'April', 'APR',
        'May', 'MAY',
        'June', 'JUN',
        'July', 'JUL',
        'August', 'AUG',
        'September', 'SEP',
        'October', 'OCT',
        'November', 'NOV',
        'December', 'DEC'
    ) AS month_abbr
FROM test
UNPIVOT (
    is_active FOR month IN (
        January, February, March, April, May, June,
        July, August, September, October, November, December
    )
)
WHERE is_active = 1; -- 仅保留值为1的有效月份

方法2：直接FLATTEN解析JSON月份字段（更高效） #

没必要先把每个月份转成结构化列，直接通过LATERAL FLATTEN解析JSON的键值对，筛选出目标月份后映射缩写，扩展性更强：

SELECT 
    value['building_area']::FLOAT AS building_area,
    value['latitude']::FLOAT AS latitude,
    value['longitude']::FLOAT AS longitude,
    DECODE(
        SPLIT_PART(month_kv.key, '_', 2)::INT,
        1, 'JAN', 2, 'FEB', 3, 'MAR', 4, 'APR',
        5, 'MAY', 6, 'JUN', 7, 'JUL', 8, 'AUG',
        9, 'SEP', 10, 'OCT', 11, 'NOV', 12, 'DEC'
    ) AS month_abbr
FROM logs,
    LATERAL FLATTEN(input => data) AS d,
    -- 展开JSON的所有键值对
    LATERAL FLATTEN(input => PARSE_JSON(d.value), mode => 'OBJECT') AS month_kv
WHERE 
    STARTS_WITH(month_kv.key, 'month_')
    AND month_kv.value::INT = 1;

说明 #

• 方法1适合已经完成JSON转结构化列的场景，仅需添加过滤即可得到预期的6行结果（假设每行仅一个月份为1）。
• 方法2更贴近原始JSON结构，无需提前定义12个月份列，后续字段变更时无需修改列映射逻辑。

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