PostgreSQL触发器函数修改JSON的id字段报错原因咨询

问题场景 #

通过row_to_json(NEW)生成的原始JSON结构如下：

{
  "id": 123,
  "country": {
     "code": "US"
  }
}

需要修改为将国家码追加到id并转为字符串的目标结构：

{
  "id": "US123",
  "country": {
    "code": "US"
  }
}

尝试代码及报错 #

执行以下SQL代码时触发错误：

new_json := new_json || json_build_object('id', new_json -> 'country' ->> 'code' || new_json ->> 'id')::jsonb; 

报错信息：Operator does not exist text ->> unknown

报错原因分析 #

核心问题是数据类型不匹配：

• row_to_json(NEW)生成的是json类型数据，但你使用了仅适用于jsonb类型的||拼接运算符。
• 对json类型的new_json执行->>操作后，字符串拼接结果为text类型，此时试图将text与json类型用||拼接，PostgreSQL无法识别该操作，因此抛出"运算符不存在"的错误。

另外代码还存在隐性问题：即便类型匹配，json/jsonb的||是合并对象，原id会被新值覆盖，但前提是参与拼接的两边必须是同类型的JSON对象。

正确实现方式 #

先将row_to_json(NEW)生成的json转为jsonb类型，再执行后续操作，示例代码如下：

-- 先转换为jsonb类型
new_json := row_to_json(NEW)::jsonb;
-- 合并新构造的id字段
new_json := new_json || jsonb_build_object('id', (new_json -> 'country' ->> 'code') || (new_json ->> 'id'));

或者使用jsonb_set更精准地修改指定字段：

new_json := jsonb_set(
  row_to_json(NEW)::jsonb,
  '{id}',
  to_jsonb((row_to_json(NEW) -> 'country' ->> 'code') || (row_to_json(NEW) ->> 'id'))
);

内容的提问来源于stack exchange，提问作者Mr.D