PostgreSQL12触发器WHEN子句中jsonb对象值比较的实现方法

可以直接使用PG12内置的jsonb_path_query_array标量函数完成值提取与比较，全程不需要调用集合返回函数，完全兼容触发器WHEN条件的语法要求。

为什么之前的写法会报错 #

jsonb_each属于集合返回函数（SRF），会将输入拆解为多行结果输出，而触发器WHEN子句要求所有判断逻辑必须是返回单值的标量表达式，不允许出现多行返回的函数调用。
而jsonb_path_query_array是标准标量函数，会将路径匹配到的所有结果打包为一个jsonb数组作为单值返回，从语法层面完全符合WHEN条件的调用要求。

核心实现逻辑 #

1. 通过jsonpath路径$.*直接提取jsonb对象下所有顶层属性的值，完全不需要提前获知具体的键名。
2. 配合jsonpath内置的sort()方法对提取到的值数组做固定顺序排序，彻底消除数组元素顺序不一致带来的比较偏差，保证只要值集合完全一致，不管键名如何变更、键的存储顺序如何，比较结果都为相等。

可直接使用的WHEN条件写法 #

WHEN (
  jsonb_path_query_array(OLD.some_jsonb_object_column, '$.* .sort()')
  IS DISTINCT FROM
  jsonb_path_query_array(NEW.some_jsonb_object_column, '$.* .sort()')
)

场景适配验证 #

• 仅键名变更、值无变化场景：OLD列值为{"user_age": 30, "user_name": "alice"}，NEW列值为{"user_age": 30, "real_name": "alice"}。两边提取排序后的数组均为[30, "alice"]，比较结果相等，不会触发触发器，符合需求。
• 值发生变化场景：OLD列值为{"user_age": 30, "user_name": "alice"}，NEW列值为{"user_age": 31, "real_name": "alice"}。两边提取排序后的数组分别为[30, "alice"]和[31, "alice"]，比较结果不等，正常触发触发器逻辑。

注意事项 #

• 该方法完全适配PG12版本，无额外扩展依赖，不需要编写自定义函数。
• 结合业务上“不会同时新增、删除键”的前提，该方案不存在值集合长度不一致的异常问题，可完全覆盖需求。
• 如果jsonb中存在嵌套的对象或数组类型的值，可将路径调整为$..* ? (@.type() != "object" && @.type() != "array") .sort()，递归提取所有非容器类型的最终值做比较，逻辑同样成立。

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