PostgreSQL中jsonb_set函数报错不存在及jsonb_insert使用问题咨询

一、jsonb_set报错原因 #

你调用的jsonb_set参数完全不符合函数签名：
PostgreSQL中jsonb_set的正确签名是：

jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean DEFAULT true)

你的语句jsonb_set('[{"a":"foo"}]'::jsonb, '{}'::jsonb, true)有两个问题：

1. 第二个参数要求是text数组类型的路径，你传的是jsonb类型的'{}'，应该改成'{}'::text[]；
2. 参数顺序错误：第三个参数应该是要设置的新jsonb值，第四个才是布尔值（可选），你把布尔值true放在了第三个参数的位置，完全不符合要求。

二、jsonb_insert报错原因 #

你的jsonb_insert调用同样存在两个致命问题：

1. 第一个参数错误地写成了jsonb[]（PostgreSQL的数组类型），但jsonb_insert要求第一个参数是单个jsonb值（哪怕它内部是json数组），所以应该去掉末尾的[]，写成'[{"a":"foo"}]'::jsonb；
2. 参数顺序完全颠倒：jsonb_insert的正确签名是：

jsonb_insert(target jsonb, path text[], new_value jsonb, insert_after boolean DEFAULT false)

你把新值'{"b": "hello"}'::jsonb放在了第二个参数的位置，而路径'0'（应该是'{0}'::text[]）放在了第三个参数，顺序完全错了。

三、jsonb相关函数的正确使用方式 #

1. jsonb_set：修改/新增jsonb中的值 #

用于修改指定路径的现有值，或在路径不存在时新增（由第四个布尔参数控制，默认true）。

• 示例1：修改json对象的某个字段

-- 把logs中的a字段改成"new_foo"
UPDATE holidays SET logs = jsonb_set(logs, '{a}', '"new_foo"'::jsonb) WHERE id = 1;

• 示例2：修改json数组中指定元素的字段

-- 把logs数组第一个元素的a字段改成"updated"
UPDATE holidays SET logs = jsonb_set(logs, '{0,a}', '"updated"'::jsonb) WHERE id = 1;

2. jsonb_insert：向jsonb数组/对象插入新元素 #

用于向json数组的指定位置插入元素，或向json对象插入新键值对。

• 示例1：向json数组头部插入元素

-- 在logs数组的索引0位置插入新元素，原元素后移
UPDATE holidays SET logs = jsonb_insert(logs, '{0}', '{"b": "hello"}'::jsonb) WHERE id = 1;

• 示例2：向json数组末尾插入元素（用{#}表示数组末尾位置）

-- 在logs数组最后追加新元素
UPDATE holidays SET logs = jsonb_insert(logs, '{#}', '{"b": "hello"}'::jsonb) WHERE id = 1;

3. ||操作符：简洁的jsonb拼接 #

你最终用的logs = logs || '{"a": "foo"}'::jsonb属于jsonb的拼接操作，规则是：

• 如果左右都是json数组：将右边数组的元素追加到左边数组末尾；
• 如果左右都是json对象：合并两个对象，右边的键会覆盖左边同名的键；
• 如果一边是数组一边是单个json值：将单个值追加到数组末尾（这就是你的场景）。
  这种方式在简单追加数组元素时，比jsonb_insert更简洁直观。

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