如何使用SQL语言条件替换Spark SQL中的数组元素值？

可以通过「数组拆解 + 逐元素应用CASE逻辑」的方式实现需求，相比直接JSON替换没有误匹配风险，更推荐使用，不同数据库的实现方式如下：

方法1：逐元素安全转换（推荐） #

PostgreSQL 版本 #

SELECT 
  ARRAY_AGG(
    CASE elem
      WHEN 'red' THEN 1
      WHEN 'green' THEN 2
      ELSE elem -- 可自定义其他元素的处理规则
    END 
    ORDER BY ord -- 保证转换后数组元素顺序和原数组一致
  ) AS myColumn
FROM myTable,
     UNNEST(myColumn) WITH ORDINALITY AS t(elem, ord)
GROUP BY myTable.ctid, myColumn; -- ctid为PostgreSQL内置行唯一标识，也可以替换为你表中的主键字段

MySQL 8.0+ 版本（数组为JSON格式存储时适用） #

SELECT 
  JSON_ARRAYAGG(
    CASE elem
      WHEN 'red' THEN 1
      WHEN 'green' THEN 2
      ELSE elem
    END 
    ORDER BY ord
  ) AS myColumn
FROM myTable,
     JSON_TABLE(
       myColumn,
       '$[*]' COLUMNS (
         elem VARCHAR(20) PATH '$',
         ord FOR ORDINALITY
       )
     ) t
GROUP BY myTable.id, myColumn; -- id为表的主键字段

方法2：JSON快速替换（仅特定场景适用） #

你当前使用的JSON替换方案在数组元素不会出现red/green子串的场景下可以直接使用，调整后写法如下：

-- 以PostgreSQL为例，最终转换为整数数组
SELECT replace(replace(to_json(myColumn)::TEXT, 'red', '1'), 'green', '2')::JSONB::INT[] AS myColumn
FROM myTable;

注意：该方法仅适用于数组元素仅为red、green两类值的场景，若存在redxxx这类包含目标字符串的元素会出现误替换。

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