如何在Redshift中将字符串格式ID映射为名称并拼接结果？

要实现将users表pets列中的ID转换为对应的宠物名称并拼接成"dog, cat, lizard"格式的字符串，可按以下步骤编写查询语句：

1. 清洗拆分pets字段 #

首先去除pets字段的方括号，将字符串拆分为单个ID并展开为多行数据：

SELECT 
    user_id, -- 替换为users表实际主键字段
    CAST(UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(pets, '^\[|\]$', ''), ',')) AS INT) AS pet_id
FROM users

• REGEXP_REPLACE(pets, '^\[|\]$', '')：移除字符串首尾的方括号，将"[1,2,4]"转换为"1,2,4"
• STRING_TO_ARRAY(..., ',')：按逗号拆分字符串为数组
• UNNEST(...)：将数组展开为多行记录
• CAST(...) AS INT：将字符串类型的ID转换为整数，用于后续关联

2. 关联宠物名称表并聚合拼接 #

将拆分后的ID与pet_names表关联，获取对应名称后，使用LISTAGG函数按用户分组拼接名称：

SELECT 
    u.user_id,
    LISTAGG(pn.name, ', ') WITHIN GROUP (ORDER BY pn.id) AS pet_names
FROM (
    SELECT 
        user_id,
        CAST(UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(pets, '^\[|\]$', ''), ',')) AS INT) AS pet_id
    FROM users
) u
JOIN pet_names pn ON u.pet_id = pn.id
GROUP BY u.user_id

• LISTAGG(pn.name, ', ') WITHIN GROUP (ORDER BY pn.id)：按pet_id顺序将名称拼接为指定格式的字符串

处理异常场景 #

如果pets字段存在空值或格式错误，可使用TRY_CAST避免转换失败，并过滤无效ID：

SELECT 
    u.user_id,
    LISTAGG(pn.name, ', ') WITHIN GROUP (ORDER BY pn.id) AS pet_names
FROM (
    SELECT 
        user_id,
        TRY_CAST(UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(pets, '^\[|\]$', ''), ',')) AS INT) AS pet_id
    FROM users
) u
JOIN pet_names pn ON u.pet_id = pn.id
WHERE u.pet_id IS NOT NULL
GROUP BY u.user_id

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