如何在PostgreSQL中移除数组中指定元素的单次出现？

针对attributes表中tokenIds（带重复值的text数组）的回滚需求——从数组中删除指定列表内每个元素的仅一次出现，以下是可行的SQL实现方案：

核心思路 #

利用unnest展开数组并保留原顺序，通过窗口函数标记每个元素的出现次数，过滤掉目标元素的首次出现后重新聚合数组，避免array_remove删除所有匹配项的问题。

直接UPDATE语句示例 #

假设需要移除tokenIds中的{"2","3"}各一次，更新指定行：

UPDATE attributes
SET tokenIds = (
  SELECT array_agg(element ORDER BY rn)
  FROM (
    SELECT 
      element,
      row_number() OVER (PARTITION BY element ORDER BY rn) AS elem_rn,
      rn
    FROM (
      SELECT unnest(tokenIds) AS element, generate_subscripts(tokenIds, 1) AS rn
    ) AS t1
  ) AS t2
  WHERE NOT (element = ANY('{"2","3"}'::text[]) AND elem_rn = 1)
)
WHERE Id = 1; -- 替换为实际筛选条件

封装可复用函数 #

如果需要多次使用该逻辑，可创建自定义函数：

CREATE OR REPLACE FUNCTION remove_elements_once(arr text[], remove_arr text[])
RETURNS text[] AS $$
BEGIN
  RETURN (
    SELECT array_agg(element ORDER BY rn)
    FROM (
      SELECT 
        element,
        row_number() OVER (PARTITION BY element ORDER BY rn) AS elem_rn,
        rn
      FROM (
        SELECT unnest(arr) AS element, generate_subscripts(arr, 1) AS rn
      ) AS t1
    ) AS t2
    WHERE NOT (element = ANY(remove_arr) AND elem_rn = 1)
  );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

函数调用示例 #

回滚场景中，移除{"1"}各一次：

UPDATE attributes
SET tokenIds = remove_elements_once(tokenIds, '{"1"}'::text[])
WHERE type = 'headscarf' AND value = 'blue ribbon' AND contract = '0x01';

效果验证 #

原数组：[1,2,3,3,2,3,4,2,6]
执行移除{"2","3"}后，结果为：[1,2,3,3,4,2,6]，符合仅删除每个目标元素一次的需求。

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