Snowflake中如何将JSON列的周时间数据转换为时间戳字段

核心处理步骤 #

• 用LATERAL FLATTEN打平JSON数组，将数组中每个周度时间配置拆分为独立行，解决数组嵌套无法逐行解析的问题
• 提取时间分量（时、分、秒）时，用ZEROIFNULL将所有null值统一替换为0
• 按目标格式拼接星期标识与补零后的时分字符串，生成start_time、end_time两个字段
• 输入示例最后一行Tuesday 23:为笔误，实际输出可按需调整时分秒显示精度，不需要秒位直接删除对应拼接逻辑即可

实现SQL #

假设存储源数据的表名为weekly_schedule，JSON类型列名为schedule_json，可直接使用如下代码：

SELECT
    -- 拼接开始时间字段
    CONCAT(
        elem.value:start_time_of_day_of_week.day_of_week::VARCHAR,
        ' ',
        LPAD(ZEROIFNULL(elem.value:start_time_of_day_of_week.time_of_day.hours::INT)::VARCHAR, 2, '0'),
        ':',
        LPAD(ZEROIFNULL(elem.value:start_time_of_day_of_week.time_of_day.minutes::INT)::VARCHAR, 2, '0')
    ) AS start_time,
    -- 拼接结束时间字段
    CONCAT(
        elem.value:end_time_of_day_of_week.day_of_week::VARCHAR,
        ' ',
        LPAD(ZEROIFNULL(elem.value:end_time_of_day_of_week.time_of_day.hours::INT)::VARCHAR, 2, '0'),
        ':',
        LPAD(ZEROIFNULL(elem.value:end_time_of_day_of_week.time_of_day.minutes::INT)::VARCHAR, 2, '0')
    ) AS end_time
FROM weekly_schedule,
LATERAL FLATTEN(input => schedule_json) elem

输出效果 #

运行后返回结果完全匹配预期格式：

若需要保留秒级精度，只需要在时分拼接段后追加冒号和补零后的秒值即可，nanos字段值全为null可直接忽略，不满足常规时间戳精度要求。

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