Redshift中如何将存储为字符串的二维数组拆分展开为多行数据

方案1：字符串拆分实现（兼容性最好，无需依赖json/super类型支持） #

实现逻辑 #

• 第一步：清理嵌套数组字符串的外层括号，拆分得到所有子数组对应的字符串
• 第二步：通过序列生成函数将数组元素展开为独立行
• 第三步：拆分每个子数组字符串得到h、r两个字段值

完整SQL代码 #

WITH base_data AS (
    -- 替换为你的实际表名
    SELECT 
        id,
        col2,
        col_3,
        col_4,
        -- 移除字符串首尾的外层括号
        TRIM(BOTH '[]' FROM array_string) AS trimmed_arr_str
    FROM your_table_name
    -- 过滤空值避免报错
    WHERE array_string IS NOT NULL AND array_string <> ''
),
split_sub_arr AS (
    SELECT
        id,
        col2,
        col_3,
        col_4,
        -- 按"],["拆分得到所有子数组的字符串数组
        SPLIT_TO_ARRAY(trimmed_arr_str, '],[') AS sub_arr_list,
        -- 获取数组长度用于后续行展开
        ARRAY_LENGTH(SPLIT_TO_ARRAY(trimmed_arr_str, '],[')) AS arr_len
    FROM base_data
)
SELECT
    t.id,
    t.col2,
    t.col_3,
    t.col_4,
    -- 拆分每个子数组的第一个元素，同时清理残留的括号
    TRIM('[]' FROM SPLIT_PART(t.sub_arr_list[series.idx], ',', 1)) AS col_h,
    -- 拆分每个子数组的第二个元素，同时清理残留的括号
    TRIM('[]' FROM SPLIT_PART(t.sub_arr_list[series.idx], ',', 2)) AS col_r
FROM split_sub_arr t
-- 生成序列实现行展开，序列范围从1到数组长度
JOIN generate_series(1, 1000) AS series(idx) 
    ON series.idx <= t.arr_len
-- 可选：过滤掉空的子数组元素
WHERE col_h <> '' AND col_r <> '';

注：generate_series的最大参数（示例中为1000）可根据你实际业务中array_string的最大子数组数量调整。

方案2：转super类型实现（Redshift 最新版本支持，逻辑更简洁） #

如果你的Redshift集群版本支持json_parse函数和super类型，可以直接将字符串转成嵌套数组再展开，代码更简洁：

SELECT
    id,
    col2,
    col_3,
    col_4,
    arr_element[0] AS col_h,
    arr_element[1] AS col_r
FROM your_table_name,
     UNNEST(json_parse(array_string)::super) AS arr(arr_element)
WHERE array_string IS NOT NULL AND array_string <> '';

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