PostgreSQL中如何高效为JSONB列的字符串列表去重追加元素？

方法1：合并后全局去重 #

先将原数组与待追加数组合并，再展开数组元素去重后重新聚合，适合需要对整个数组做去重的场景：

UPDATE employee
SET tags = (
    SELECT jsonb_agg(DISTINCT elem)
    FROM jsonb_array_elements(tags || '["TagD", "TagA"]'::jsonb) elem
)
WHERE emp_id = 'EMP-111';

原理：通过jsonb_array_elements将合并后的数组拆分为单行元素，用DISTINCT剔除重复项，再用jsonb_agg重新组合为无重复的JSONB数组。

方法2：仅追加不重复元素 #

先筛选出待追加数组中不存在于原数组的元素，再将这些元素追加到原数组，效率更高（无需处理原数组已有元素）：

UPDATE employee
SET tags = tags || (
    SELECT jsonb_agg(elem)
    FROM jsonb_array_elements('["TagD", "TagA"]'::jsonb) elem
    WHERE NOT elem <@ tags
)
WHERE emp_id = 'EMP-111';

原理：利用<@操作符判断元素是否存在于原数组，只保留不存在的元素进行追加，从源头避免重复。

方法3：封装自定义函数简化操作 #

如果频繁需要这类操作，可以封装一个可复用的函数，让代码更简洁：

CREATE OR REPLACE FUNCTION jsonb_array_append_unique(target jsonb, additions jsonb)
RETURNS jsonb AS $$
BEGIN
    RETURN target || (
        SELECT jsonb_agg(elem)
        FROM jsonb_array_elements(additions) elem
        WHERE NOT elem <@ target
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

调用时只需：

UPDATE employee
SET tags = jsonb_array_append_unique(tags, '["TagD", "TagA"]'::jsonb)
WHERE emp_id = 'EMP-111';

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