PostgreSQL中如何确保JSONB数组元素的唯一性？

当然有办法解决这个问题！你当前的UPDATE语句只是简单拼接两个JSONB数组，完全没做去重处理，所以才会出现重复值。下面我给你几个实用的方案，帮你彻底搞定emails列的唯一性问题：

方案1：拼接后拆分组装去重（直观易理解） #

这是最直接的思路：把现有数组和新数组合并，拆分成单个元素去重后，再重新组合成JSONB数组：

UPDATE students
SET emails = (
  SELECT jsonb_agg(DISTINCT elem ORDER BY elem)
  FROM jsonb_array_elements(emails || '["j.doe@email.com", "john@email.com"]'::jsonb) AS elem
)
WHERE student_id = 1
-- 只有当新数组里有现有数组没有的元素时才更新
AND NOT emails @> '["j.doe@email.com", "john@email.com"]'::jsonb;

关键说明： #

• jsonb_array_elements()会把合并后的数组拆分成一行行的单个元素
• DISTINCT关键字自动剔除重复的邮箱地址
• jsonb_agg()把去重后的元素重新组装成JSONB数组，ORDER BY elem是可选的，用来保持数组元素的有序性

方案2：用JSON路径查询去重（简洁高效） #

如果你用的是PostgreSQL 12及以上版本，可以利用JSON路径查询功能，一行代码完成去重：

UPDATE students
SET emails = jsonb_path_query_array(
  emails || '["j.doe@email.com", "john@email.com"]'::jsonb,
  '$[*] ? (!exists($[*] ? (@ == $current)))'
)
WHERE student_id = 1
AND NOT emails @> '["j.doe@email.com", "john@email.com"]'::jsonb;

关键说明： #

• 路径表达式$[*] ? (!exists($[*] ? (@ == $current)))的作用是：筛选出在整个数组中仅出现一次的元素，也就是自动去重后的结果
• 这个方法不需要拆分数组再聚合，语法更紧凑，性能也不错

方案3：创建触发器（一劳永逸） #

如果希望任何操作（包括INSERT、其他UPDATE）都自动保证emails数组无重复，推荐创建触发器，从根源上解决问题：

首先创建触发器函数：

CREATE OR REPLACE FUNCTION ensure_unique_jsonb_array()
RETURNS TRIGGER AS $$
BEGIN
  -- 对新的emails数组自动去重
  NEW.emails = (
    SELECT jsonb_agg(DISTINCT elem ORDER BY elem)
    FROM jsonb_array_elements(NEW.emails) AS elem
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

然后给students表绑定触发器：

CREATE TRIGGER trigger_students_unique_emails
BEFORE INSERT OR UPDATE OF emails ON students
FOR EACH ROW EXECUTE FUNCTION ensure_unique_jsonb_array();

之后不管你是插入新记录，还是更新emails列，触发器都会自动帮你完成去重，再也不用每次写复杂的UPDATE语句了。

小优化：调整更新条件 #

原查询的NOT emails @> new_emails条件，只有当新数组的所有元素都不在现有数组里时才会更新。如果想只要有新元素就更新（同时去重），可以把WHERE条件改成：

WHERE student_id = 1
AND (emails || '["j.doe@email.com", "john@email.com"]'::jsonb) <> emails;

这样只要合并后的数组和原数组有差异（也就是有新元素），就会执行更新操作。

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