如何在Amazon Redshift中合并JSON数组元素并使用自定义分隔符转换文本格式

我来帮你搞定Redshift里这个JSON数组转分隔字符串的需求，两种实用方法供你选，完美匹配你的需求：

方法1：UNNEST + LISTAGG（兼容绝大多数Redshift版本） #

这个思路和PostgreSQL里用json_array_elements_text+string_agg的逻辑类似，先拆分数组元素再聚合拼接：

假设你的表叫your_table，存JSON数组的text列是genre_json，执行下面的SQL就能得到结果：

SELECT
  -- 记得加上表的主键/唯一标识列，确保每行对应自己的拼接结果
  your_primary_key,
  LISTAGG(genre_element, '|') WITHIN GROUP (ORDER BY genre_element) AS genres
FROM
  your_table,
  -- 把text类型的JSON字符串解析成Redshift超级数组，再拆成单行元素
  UNNEST(JSON_PARSE(genre_json)) AS t(genre_element)
GROUP BY
  your_primary_key;

• JSON_PARSE：把text格式的JSON字符串转成Redshift的**超级类型（Super Type）**数组，这样才能被UNNEST处理。
• UNNEST：将数组中的每个元素拆分为独立的行。
• LISTAGG：按原行分组，用|把拆分后的元素拼接成一个字符串，ORDER BY可选，用来控制元素的排列顺序。

方法2：REDUCE函数（更简洁高效，适合新版本Redshift） #

如果你的Redshift版本在1.0.23685及以上（支持超级类型的REDUCE函数），可以直接遍历数组拼接，不需要拆分行，性能更好：

SELECT
  REDUCE(
    JSON_PARSE(genre_json),
    -- 初始值设为空字符串
    '',
    -- 遍历每个元素，只有非初始值时才加分隔符|
    (accumulator, current_val) -> accumulator || CASE WHEN accumulator = '' THEN '' ELSE '|' END || current_val::VARCHAR,
    -- 返回最终拼接好的字符串
    accumulator -> accumulator
  ) AS genres
FROM your_table;

这个方法用REDUCE迭代数组元素，一步到位构建目标字符串，代码更紧凑。

测试验证 #

用你给出的测试数据：

执行任意一种方法后，都会得到每行对应的分隔字符串：

如果要得到你示例里genres drama|action|comedy drama|comedy|thriller drama|romance的格式，再套一层全局聚合即可：

SELECT 'genres ' || LISTAGG(genres, ' ') AS final_output
FROM (
  -- 这里放入上面方法1或方法2的查询语句
  SELECT
    REDUCE(
      JSON_PARSE(genre_json),
      '',
      (acc, val) -> acc || CASE WHEN acc = '' THEN '' ELSE '|' END || val::VARCHAR,
      acc -> acc
    ) AS genres
  FROM your_table
) AS subquery;

执行后就能得到你想要的最终格式。

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