PSQL中ARRAY_REPLACE仅替换数组首个值的问题排查

我在PostgreSQL中尝试通过另一张表的映射关系替换数组列中的值，但发现ARRAY_REPLACE只替换了数组里的首个匹配值，想知道我的语句哪里有问题？

执行的SQL语句：

UPDATE table1 t1 
  SET ids = ARRAY_REPLACE(t1.ids, t2.old_id, t2.new_id::text) 
FROM table2 t2;

表结构与数据 #

Table 1 结构 #

Column     |  Type  | Collation | Nullable | Default
---------------+--------+-----------+----------+---------
 group_id      | uuid   |           | not null |
 ids           | text[] |           | not null |

Table 1 更新前数据 #

group_id                             | ids
00000000-0000-4000-a000-00000000000a | {10002,10003,10000,10001}
00000000-0000-4000-a000-00000000000b | {20002,20003,20001,20000}

Table 2 结构 #

Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 old_id      | character varying        |           | not null |
 new_id      | uuid                     |           | not null |

Table 2 数据 #

old_id | new_id
10000  | 00000000-0000-4000-a000-000000000010
10001  | 00000000-0000-4000-a000-000000000011
10002  | 00000000-0000-4000-a000-000000000012
10003  | 00000000-0000-4000-a000-000000000013
20000  | 00000000-0000-4000-a000-000000000020
20001  | 00000000-0000-4000-a000-000000000021
20002  | 00000000-0000-4000-a000-000000000022
20003  | 00000000-0000-4000-a000-000000000023

Table 1 更新后数据 #

group_id                             | ids
00000000-0000-4000-a000-00000000000a | {10002,10003,00000000-0000-4000-a000-000000000010,10001}
00000000-0000-4000-a000-00000000000b | {20002,20003,20001,00000000-0000-4000-a000-000000000020}

问题原因 #

1. ARRAY_REPLACE的固有特性：ARRAY_REPLACE函数本身仅能替换数组中第一个匹配到的元素，不支持批量替换所有符合条件的元素。
2. UPDATE语句的逻辑缺陷：当前语句会让table1和table2做笛卡尔积关联，每一行table1数据会和table2的每一行配对执行更新。但PostgreSQL的UPDATE只会保留最后一次对同一行的修改结果，最终只有table2中最后一条匹配的替换生效，表现为只替换了一个元素。

解决方案 #

方法1：拆分数组+映射聚合 #

将数组拆分为单个元素，通过table2的映射关系替换后重新聚合为数组：

UPDATE table1 t1
SET ids = (
  SELECT array_agg(
    CASE WHEN t2.new_id IS NOT NULL THEN t2.new_id::text ELSE elem END
  )
  FROM unnest(t1.ids) AS elem
  LEFT JOIN table2 t2 ON elem = t2.old_id
);

方法2：自定义批量替换函数 #

如果需要频繁执行这类操作，可以创建一个自定义函数：

CREATE OR REPLACE FUNCTION replace_array_elements(arr text[], mapping jsonb)
RETURNS text[] AS $$
BEGIN
  FOR i IN 1..array_length(arr, 1) LOOP
    IF arr[i] = ANY(jsonb_object_keys(mapping)) THEN
      arr[i] := mapping->>arr[i];
    END IF;
  END LOOP;
  RETURN arr;
END;
$$ LANGUAGE plpgsql;

调用方式：

UPDATE table1 t1
SET ids = replace_array_elements(
  t1.ids,
  (SELECT jsonb_object_agg(old_id, new_id::text) FROM table2)
);

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