PostgreSQL中JSON数组元素查询：运行时变量传值实现计数

你的核心问题是：data列存储的是JSON数组，原触发器里直接用data->>'contact'=OLD.name是错误的——因为数组本身没有contact键，只有数组里的每个对象才有，所以这个条件永远匹配不到任何行。而静态查询用data::jsonb @> '[{"contact": "0123456789"}]'能生效，是因为它检查的是JSON数组中是否存在包含指定contact值的元素。

下面给你两种可行的修改方案：

方案1：展开JSON数组后匹配元素 #

先把data列的JSON数组展开为行，再检查每个元素的contact字段是否等于变量值：

CREATE OR REPLACE FUNCTION trigger_validate() RETURNS trigger 
LANGUAGE plpgsql AS $$ 
DECLARE active_customer bigint; 
BEGIN 
   SELECT count(*) 
   FROM customer, jsonb_array_elements(data::jsonb) AS elem
   WHERE elem->>'contact' = OLD.name INTO active_customer; 
   IF active_customer > 0 THEN 
     RAISE EXCEPTION 'This is an entry found'; 
   END IF ;
RETURN OLD; 
END; 
$$;

方案2：构造动态JSONB条件（推荐，可复用索引） #

和你原来的静态查询逻辑对齐，用变量构造出对应的JSONB数组对象，再用@>操作符匹配，这样如果data列有JSONB索引的话，能直接利用索引提升效率：

CREATE OR REPLACE FUNCTION trigger_validate() RETURNS trigger 
LANGUAGE plpgsql AS $$ 
DECLARE active_customer bigint; 
BEGIN 
   SELECT count(*) 
   FROM customer 
   WHERE data::jsonb @> jsonb_build_array(jsonb_build_object('contact', OLD.name)) INTO active_customer; 
   IF active_customer > 0 THEN 
     RAISE EXCEPTION 'This is an entry found'; 
   END IF ;
RETURN OLD; 
END; 
$$;

说明 #

• 方案2里的jsonb_build_array(jsonb_build_object('contact', OLD.name))会动态生成和静态查询中'[{"contact": "0123456789"}]'结构完全一致的JSONB值，完美复用你原来的查询逻辑。
• 如果你的data列本身就是jsonb类型（不是json），可以去掉::jsonb的类型转换，进一步提升效率。

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