Redshift SQL中拆分places列数组值生成新行的实现方法

针对你的需求，可通过Redshift的字符串处理与数组函数实现，具体SQL语句如下：

SELECT
    t.userid,
    t.username,
    t.display_name,
    t.bio,
    COALESCE(unnest_item, '') AS places,
    t.email
FROM mytable t
LEFT JOIN UNNEST(
    CASE
        -- 处理空数组，生成包含空字符串的数组以保留原行
        WHEN t.places = '[]' THEN ARRAY['']::VARCHAR[]
        -- 清理格式后转换为数组：替换双单引号为单引号、去除前后方括号、按逗号分割
        ELSE STRING_TO_ARRAY(TRIM(BOTH '[]' FROM REPLACE(t.places, '''''', '''')), ', ')
    END
) AS unnest_item ON TRUE

语句说明 #

1. 格式清理：
   • REPLACE(t.places, '''''', '''')：将原字段中的双单引号（''）替换为单个单引号，还原正常字符串格式。
   • TRIM(BOTH '[]' FROM ...)：去除字符串首尾的方括号[]。
2. 数组转换与拆分：
   • 空数组场景：强制生成含空字符串的数组，确保原数据行被保留，places字段为空。
   • 多地点场景：将清理后的字符串按, 分割为数组，通过UNNEST拆分为多行，每行对应单个地点。
3. 空值处理：COALESCE(unnest_item, '')确保空数组场景下places字段显示为空字符串而非NULL。

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