Snowflake中如何将存储为varchar的数组字符串列拆分为多行

直接调用flatten不生效的核心原因是目标字段实际为VARCHAR类型，存储的是多个独立数组字符串直接拼接的非标准格式，并非Snowflake原生ARRAY类型，无法被flatten直接识别处理。

可直接运行的实现代码 #

WITH sample_data AS (
    -- 模拟原始表数据，实际使用时替换为你自己的表即可
    SELECT 
        1 AS id,
        '[10001,100000][10003,100000000][10004,10000][10006,100]' AS arr_str
)
SELECT 
    t.id,
    f.value AS single_arr_str
FROM sample_data t,
LATERAL FLATTEN(
    INPUT => REGEXP_EXTRACT_ALL(t.arr_str, '\\[[^]]+\\]')
) f;

逻辑说明 #

• REGEXP_EXTRACT_ALL函数会遍历整个字符串，提取所有符合[任意非]字符]格式的子串，返回标准的Snowflake ARRAY类型结果
• LATERAL FLATTEN将上述ARRAY的每个元素拆分为独立行，全程保留原id与拆分后内容的关联关系
• 如果后续需要对拆分后的数组做元素读取、计算等操作，可通过TRY_PARSE_JSON(f.value)将字符串格式的数组转为Snowflake原生ARRAY类型使用

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