PostgreSQL中移除JSONB数组重复元素的方法求助

基础查询去重（不保留原始顺序） #

如果只需要去除数组重复元素，不关心元素顺序，可以用以下SQL：
假设你的表名为your_table，目标列是web（jsonb类型）：

SELECT 
    id, -- 替换为你的表主键/唯一标识列，确保对应原数据行
    jsonb_agg(DISTINCT elem) AS unique_web
FROM 
    your_table,
    jsonb_array_elements(web) AS elem
GROUP BY 
    id;

直接更新表数据 #

如果要直接修改表中web列的值，替换为去重后的数组：

UPDATE your_table
SET web = (
    SELECT jsonb_agg(DISTINCT elem)
    FROM jsonb_array_elements(web) AS elem
);

保留元素原始出现顺序 #

如果需要保留元素在原数组中第一次出现的顺序，可以结合WITH ORDINALITY获取元素位置，用窗口函数筛选首次出现的元素：

SELECT 
    id,
    jsonb_agg(elem ORDER BY idx) AS unique_web
FROM (
    SELECT 
        id,
        elem,
        idx,
        -- 给每个分组下的元素按出现位置标记行号
        ROW_NUMBER() OVER (PARTITION BY id, elem ORDER BY idx) AS rn
    FROM 
        your_table,
        -- WITH ORDINALITY 获取元素在数组中的原始位置（PostgreSQL 9.4+支持）
        jsonb_array_elements(web) WITH ORDINALITY AS arr(elem, idx)
) AS sub_query
WHERE rn = 1 -- 只保留每个元素第一次出现的行
GROUP BY id;

关键函数说明 #

• jsonb_array_elements：将jsonb数组拆分为单行元素；
• DISTINCT：过滤重复元素；
• jsonb_agg：将单行元素重新聚合为jsonb数组；
• WITH ORDINALITY：用于获取元素在原数组中的索引位置，实现顺序保留。

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