Redshift中SUPER类型字段提取JSON值并拼接的实现问题

问题场景 #

现有Redshift表包含SUPER类型字段mycol，数据示例如下：

id  mycol
---------------------------------
1   [{"Title":"first"},{"Title":"Second"},{"Title":"Third"},{"Title":"fourth"}]
2   [{"Title":"some"},{"Title":"thing"}]
3   [{"Title":"else"}]

需要提取每个id对应的所有Title值，并用逗号拼接成一行。当前展开数组的查询能得到每行单个Title，但使用LISTAGG时因SUPER类型不支持报错：LISTAGG(Super) is not available。

解决方案 #

核心思路是先将SUPER类型的Title值转换为varchar类型，再进行聚合拼接。以下提供两种可行写法：

方法一：CAST转换SUPER字段后聚合

WITH cte AS (
    SELECT 
        id,
        CAST(item."Title" AS VARCHAR(255)) AS title_str
    FROM mytable t, t.mycol AS item AT index
)
SELECT 
    id,
    LISTAGG(title_str, ',') WITHIN GROUP (ORDER BY title_str) AS mycol
FROM cte
GROUP BY id;

• 先通过CTE展开SUPER数组中的每个元素，同时用CAST将SUPER类型的Title转为varchar，消除LISTAGG的类型限制。
• WITHIN GROUP (ORDER BY title_str)可控制拼接顺序，不需要的话可以省略。

方法二：JSON函数提取并转换

WITH cte AS (
    SELECT 
        id,
        JSON_EXTRACT_PATH_TEXT(item::VARCHAR, 'Title') AS title_str
    FROM mytable t, t.mycol AS item AT index
)
SELECT 
    id,
    LISTAGG(title_str, ',') AS mycol
FROM cte
GROUP BY id;

• 先将SUPER元素转为varchar字符串，再用JSON_EXTRACT_PATH_TEXT提取Title值，同样得到可用于LISTAGG的字符串类型。

两种方法都能输出预期结果：

id  mycol
-------------
1   first,Second,Third,fourth
2   some,thing
3   else

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