如何在SQL中通过值映射将数组元素替换生成新数组

由于不同数据库对数组的处理语法存在差异，以下是主流数据库的实现方案：

PostgreSQL #

PostgreSQL原生支持数组类型，可通过unnest展开数组元素，映射后再用array_agg聚合为新数组：

SELECT 
    id,
    array_agg(
        CASE elem
            WHEN 'a' THEN 1
            WHEN 'b' THEN 2
            WHEN 'c' THEN 3
            ELSE elem  -- 保留未匹配的原始值，可根据需求删除
        END
    ) AS parsed_array
FROM 
    table_a,
    unnest(some_array) AS elem
GROUP BY 
    id
ORDER BY 
    id;

MySQL 8.0+ #

利用JSON_TABLE展开JSON数组，通过JSON_ARRAYAGG聚合映射后的元素：

SELECT 
    t.id,
    JSON_ARRAYAGG(
        CASE j.elem
            WHEN 'a' THEN 1
            WHEN 'b' THEN 2
            WHEN 'c' THEN 3
            ELSE j.elem
        END
    ) AS parsed_array
FROM 
    table_a t,
    JSON_TABLE(
        t.some_array,
        '$[*]' COLUMNS (elem VARCHAR(10) PATH '$')
    ) j
GROUP BY 
    t.id
ORDER BY 
    t.id;

SQL Server #

通过OPENJSON解析数组，用STRING_AGG拼接映射结果并转为JSON数组（兼容旧版本，2022+可直接用JSON_ARRAYAGG）：

SELECT 
    t.id,
    JSON_QUERY(
        '[' + STRING_AGG(
            CASE j.value
                WHEN 'a' THEN '1'
                WHEN 'b' THEN '2'
                WHEN 'c' THEN '3'
                ELSE j.value
            END, ','
        ) + ']'
    ) AS parsed_array
FROM 
    table_a t
CROSS APPLY 
    OPENJSON(t.some_array) j
GROUP BY 
    t.id
ORDER BY 
    t.id;

核心逻辑说明 #

1. 展开数组：通过数据库提供的函数将数组拆分为单行元素；
2. 值映射：用CASE语句根据预设规则转换每个元素；
3. 聚合重组：将映射后的元素重新聚合为数组类型输出。

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