Redshift中将Varchar格式数组转为行的实现问题

你的原代码无法得到正确结果，核心错误有两个：

1. array(department) 无法直接解析带{}的字符串为数组：Redshift的array()构造函数需要传入明确的元素列表（如array('admin','accounts')），不能自动识别带包裹符号的字符串格式。
2. 错误使用UNPIVOT：UNPIVOT的作用是列转行（将多列数据转为多行），并非拆分数组或字符串为多行，工具用错了场景。

针对Redshift中这种存储为{元素1,元素2,...}格式的Varchar字段，提供两种可行的解决方案：

方案一：使用split_to_array + unnest（Redshift 1.0.3594及以上版本支持） #

Redshift较新版本已经支持unnest函数，搭配split_to_array可以快速拆分：

SELECT 
    name,
    unnest(split_to_array(trim(department, '{}'), ',')) AS dep_unpivot
FROM table_emp;

步骤说明： #

• trim(department, '{}')：去掉字段首尾的{}，将{admin,accounts}转为admin,accounts
• split_to_array(..., ',')：按逗号分割字符串为数组
• unnest()：将数组元素拆分为多行

方案二：递归CTE（兼容所有Redshift版本） #

如果你的Redshift版本不支持unnest，可以用递归CTE处理不定长度的元素：

WITH recursive emp_deps AS (
    -- 基础查询：提取第一个部门元素
    SELECT 
        name,
        trim(department, '{}') AS dep_str,
        1 AS pos,
        split_part(trim(department, '{}'), ',', 1) AS dep_unpivot
    FROM table_emp
    WHERE trim(department, '{}') <> '' -- 过滤空值
    
    UNION ALL
    
    -- 递归查询：依次提取后续部门元素
    SELECT 
        name,
        dep_str,
        pos + 1,
        split_part(dep_str, ',', pos + 1) AS dep_unpivot
    FROM emp_deps
    WHERE split_part(dep_str, ',', pos + 1) <> '' -- 直到没有元素为止
)
SELECT name, dep_unpivot
FROM emp_deps
ORDER BY name, pos;

步骤说明： #

• 基础部分：清洗字符串后，提取第一个分隔后的元素
• 递归部分：逐步递增位置，提取下一个元素，直到取不到有效元素时停止递归

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