PostgreSQL如何查找JSONB列跨数组存在重复ID值的行

问题原因 #

你原有逻辑中的EXISTS子查询仅遍历当前行的tertiary_ids数组元素，统计的是当前行内同一元素的出现次数，而非跨所有行的出现次数，因此无法识别跨行重复的数组元素。

修正后的查询语句 #

SELECT
  DISTINCT ON (o.id) -- 避免同一行因包含多个重复tertiary_id返回多条记录
  o.identifiers->>'primary_id' AS primary_id,
  o.identifiers->>'secondary_id' AS secondary_id,
  o.id
FROM objects o
WHERE
  -- 原有primary_id重复校验逻辑
  o.identifiers->>'primary_id' IN (
    SELECT identifiers->>'primary_id'
    FROM objects
    GROUP BY identifiers->>'primary_id'
    HAVING COUNT(identifiers->>'primary_id') > 1
  )
  -- 原有secondary_id重复校验逻辑
  OR o.identifiers->>'secondary_id' IN (
    SELECT identifiers->>'secondary_id'
    FROM objects
    GROUP BY identifiers->>'secondary_id'
    HAVING COUNT(identifiers->>'secondary_id') > 1
  )
  -- 新增tertiary_ids元素跨行重复校验逻辑
  OR EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(o.identifiers->'tertiary_ids') t(val)
    WHERE t.val IN (
      -- 先提取所有行的tertiary_ids元素，筛选出跨行重复的值
      SELECT t_inner.val
      FROM objects o_inner,
           jsonb_array_elements_text(o_inner.identifiers->'tertiary_ids') t_inner(val)
      GROUP BY t_inner.val
      HAVING COUNT(DISTINCT o_inner.id) > 1 -- 排除同一行内元素重复的误判
    )
  )
ORDER BY
  o.identifiers->>'primary_id' NULLS LAST,
  o.identifiers->>'secondary_id' NULLS LAST,
  o.id DESC;

可选：查看具体重复ID与重复类型 #

如果你需要明确看到每行是因为哪个ID重复被命中，可以使用以下版本：

SELECT
  o.identifiers->>'primary_id' AS primary_id,
  o.identifiers->>'secondary_id' AS secondary_id,
  t.val AS duplicate_tertiary_id,
  o.id,
  CASE
    WHEN o.identifiers->>'primary_id' IN (SELECT identifiers->>'primary_id' FROM objects GROUP BY identifiers->>'primary_id' HAVING COUNT(*) > 1) THEN 'primary_id重复'
    WHEN o.identifiers->>'secondary_id' IN (SELECT identifiers->>'secondary_id' FROM objects GROUP BY identifiers->>'secondary_id' HAVING COUNT(*) > 1) THEN 'secondary_id重复'
    ELSE 'tertiary_id重复'
  END AS duplicate_type
FROM objects o
LEFT JOIN jsonb_array_elements_text(o.identifiers->'tertiary_ids') t(val) ON TRUE
WHERE
  o.identifiers->>'primary_id' IN (
    SELECT identifiers->>'primary_id' FROM objects GROUP BY identifiers->>'primary_id' HAVING COUNT(*) > 1
  )
  OR o.identifiers->>'secondary_id' IN (
    SELECT identifiers->>'secondary_id' FROM objects GROUP BY identifiers->>'secondary_id' HAVING COUNT(*) > 1
  )
  OR t.val IN (
    SELECT t_inner.val FROM objects o_inner, jsonb_array_elements_text(o_inner.identifiers->'tertiary_ids') t_inner(val) GROUP BY t_inner.val HAVING COUNT(DISTINCT o_inner.id) > 1
  )
ORDER BY duplicate_type, o.id;

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