PostgreSQL：查找并删除JSONB重复数据，比对时排除指定键值对

我来帮你调整查询，实现排除updated_at键来判断重复，并保留最早记录的需求。核心思路是先清洗JSONB字段（移除不需要的键），再基于清洗后的数据找重复，最后删除重复项只留最早的一条。

第一步：查找所有重复记录（排除updated_at） #

首先修改原查询，用PostgreSQL的#-操作符移除JSONB中指定路径的updated_at键，再基于清洗后的数据分组统计重复：

SELECT 
  t1.id, -- 替换为你的表主键字段
  t1.jsonb_field,
  t1.created_at -- 替换为判断记录先后的字段（比如创建时间/自增ID）
FROM customers t1
INNER JOIN (
  SELECT 
    jsonb_field #- '{main,orders,updated_at}' AS cleaned_jsonb,
    COUNT(*) AS count_of
  FROM customers
  WHERE customer_id = 1
  GROUP BY cleaned_jsonb
  HAVING COUNT(*) > 1
) t2 ON t1.jsonb_field #- '{main,orders,updated_at}' = t2.cleaned_jsonb
WHERE t1.customer_id = 1
ORDER BY t2.cleaned_jsonb, t1.created_at;

这里jsonb_field #- '{main,orders,updated_at}'会精准删除main->orders下的updated_at键，确保你提供的两个示例JSONB清洗后完全一致，被判定为重复。

第二步：删除重复项，保留最早记录 #

用窗口函数给每个重复组的记录排序，标记出非最早的记录并删除：

WITH ranked_records AS (
  SELECT 
    id, -- 替换为你的表主键字段
    ROW_NUMBER() OVER (
      PARTITION BY jsonb_field #- '{main,orders,updated_at}'
      ORDER BY created_at ASC -- 按创建时间升序，最早的记录排第1
    ) AS rn
  FROM customers
  WHERE customer_id = 1
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked_records WHERE rn > 1);

注意事项 #

• 把created_at替换成你表中实际用来判断“最早记录”的字段，如果是自增主键id，也可以用ORDER BY id ASC（更早插入的记录ID更小）。
• 执行删除前，先运行第一步的查询确认哪些记录会被删除，避免误操作。
• 如果你的JSONB结构有变化，记得调整#-后面的路径（比如键名不同、层级变化）。

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