Snowflake中Flatten无法展开行问题及数据拆分实现需求

输入数据表 #

期望输出表 #

当前编写的部分SQL #

with temp as (
    select  
        b.id,  
        (replace(get(rater_response:rate_breakdown:coverage_output_list,0):factor_breakdown:
     per_attorney_premium:lookup_values ,'"','') ) as arr1
     --b.*,c.insurance_application_id
    from rater b
)
SELECT 
    id,
    flattened_data.value
     /*substring(replace(split(flattened_data.value, '}')[0],'[{',''),1, REGEXP_INSTR(replace(split(flattened_data.value, '}')[0],'[{',''),'\\d')-2) as col1, */
FROM temp,

完整解决方案SQL #

由于原break_down字段是非标准JSON格式，需先做字符串清洗再拆分，以下是可直接运行的完整SQL：

WITH cleaned_data AS (
    SELECT 
        Policy,
        -- 清洗特殊格式：去除首尾引号、[]，替换{为分隔符，移除}
        TRIM(REPLACE(REPLACE(REPLACE(break_down, '"', ''), '[{', ''), '}', '')) AS cleaned_str
    FROM your_input_table -- 替换为实际表名
    WHERE break_down IS NOT NULL
),
split_rows AS (
    SELECT 
        Policy,
        -- 按条目规则拆分出每行数据
        REGEXP_SUBSTR(cleaned_str, '[^\\d]+\\s\\d+\\s\\d+\\.?\\d*', 1, seq) AS item
    FROM cleaned_data,
    -- 生成足够的行序列（假设最多10个条目，可按需调整）
    TABLE(GENERATOR(ROWCOUNT => 10))
    WHERE seq <= REGEXP_COUNT(cleaned_str, '\\{')
),
split_columns AS (
    SELECT 
        Policy,
        -- 提取带空格的描述字段
        TRIM(REGEXP_SUBSTR(item, '^[^\\d]+')) AS Description,
        -- 提取第一个数值字段
        TRIM(REGEXP_SUBSTR(item, '\\d+', 1, 1)) AS Pricing_Mod_Factor,
        -- 提取含小数的第二个数值字段
        TRIM(REGEXP_SUBSTR(item, '\\d+\\.?\\d*', 1, 2)) AS "Pricing Mod Premium"
    FROM split_rows
    WHERE item IS NOT NULL
)
-- 合并拆分结果与原表中break_down为null的记录
SELECT * FROM split_columns
UNION ALL
SELECT Policy, NULL, NULL, NULL FROM your_input_table WHERE break_down IS NULL;

关键步骤说明 #

1. 字符串清洗：将非标准格式的break_down转换为无冗余符号的纯内容字符串，方便后续拆分。
2. 行拆分：通过GENERATOR生成行序列，配合正则表达式将单条目中的多个子项拆分为独立行。
3. 列提取：利用正则匹配分别提取描述文本和两个数值字段，兼容带空格的描述内容。
4. 结果合并：保留原表中break_down为null的记录，确保所有Policy数据完整。

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