sql - 将嵌套键值添加到 Postgres 中的 JSONB

Question

我试图将一个键附加到 postgres 中的嵌套 jsonb，但出现错误。本质上，我从一个 json 开始:

{"tel": "123", "name": "foo", "new_info": {"a": "bar"}}

我想将 {"b", "baz"} 附加到 "new_info"中，这样生成的 jsonb 是:

{"tel": "123", "name": "foo", "new_info": {"a": "bar", "b":"baz"}}

我正在使用以下命令获取原始 jsonb:

CREATE TABLE mytable (
 ID serial NOT NULL PRIMARY KEY,
 data jsonb NOT NULL
);


INSERT INTO mytable (data)
VALUES
 (
 '{ "name": "foo", "tel": "123"}'
 );

UPDATE mytable SET data = jsonb_set(data, '{new_info}', '{"a":"bar"}', TRUE) WHERE data @> '{"name": "foo"}' ;

并尝试使用以下内容更新不起作用的“new_info”:

WITH orig_new_info AS (SELECT data#>'{new_info}' FROM mytable WHERE data @> '{"name": "foo"}')
WITH updated_new_info AS (jsonb_set(orig_new_info, '{"b":"baz"}',TRUE ))
UPDATE mytable SET data = jsonb_set(data, '{new_info}', updated_new_info, TRUE) WHERE data @> '{"name": "foo"}';

非常感谢任何指点!

更新 #1:

Per klins 回答了以下工作:

update mytable 
set data = jsonb_insert(data, '{new_info}', data->'new_info' || '{"b":"baz"}', TRUE)
where data @> '{"name": "foo"}'
returning *;

但是如何避免使用 jsonb_insert 之类的方法覆盖现有 key 。换句话说，为什么以下示例不起作用？:

#ex 1
update mytable 
set data = jsonb_insert(data, '{new_info}', jsonb_insert(SELECT data->'new_info' FROM mytable WHERE data @> '{"name": "foo"}'), '{"b":"baz"}'),true)
where data @> '{"name": "foo"}'
returning *;

#ex2
WITH orig_new_info AS (SELECT data#>'{new_info}' FROM mytable WHERE data @> '{"name": "foo"}')
WITH updated_new_info AS(SELECT jsonb_insert(orig_new_info, orig_new_info ||'{"b":"bazer"}'))
update mytable 
set data = jsonb_set(data, '{new_info}', updated_new_info, TRUE)
where data @> '{"name": "foo"}'
returning *; 

换句话说，klin 的答案只考虑顶级 data jsonb 中的键，而不是 中嵌套的 "new_info" json 的键>数据。

更新#2:

Per klins 更新了以下工作的答案:

update mytable 
set data = jsonb_insert(data, '{new_info, b}', '"baz"')
where data @> '{"name": "foo"}'

但是，如果 "new_info" 不存在于 data 中，更新将成功完成而不保存。因此以下命令成功完成但数据未保存:

DROP TABLE mytable;

CREATE TABLE mytable (
 ID serial NOT NULL PRIMARY KEY,
 data jsonb NOT NULL
);


INSERT INTO mytable (data)
VALUES
 (
 '{ "name": "foo", "tel": "123"}'
 );

update mytable 
set data = jsonb_insert(data, '{new_info, b}', '"baz"')
where data @> '{"name": "foo"}'
returning *;

所以这对我来说有点令人惊讶，因为它给人的印象是它保存了，但实际上并没有。我想避免使用 case 语句，因为大多数时候这将是不必要的检查，如果“new_info”不存在则宁愿它失败(或者如果它不增加开销到“new_info”已经存在的情况下则创建它存在)。即我想避免这些答案的作用:

Check if key exists in a JSON with PL/pgSQL?

Update or create nested jsonb value using single update command

Answer 1

使用||(串联运算符):

update mytable 
set data = jsonb_set(data, '{new_info}', data->'new_info' || '{"b":"baz"}')
where data @> '{"name": "foo"}'
returning *

 id |                                data                                 
----+---------------------------------------------------------------------
  1 | {"tel": "123", "name": "foo", "new_info": {"a": "bar", "b": "baz"}}
(1 row)

UPDATE 1    

---

函数 jsonb_set() 是在 Postgres 9.5 中引入的。在 Postgres 9.6+ 中，您还可以使用 jsonb_insert(), 这可能更直接:

update mytable 
set data = jsonb_insert(data, '{new_info, b}', '"baz"')
where data @> '{"name": "foo"}'

来自 the documentation:

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

(...) If target section designated by path is in JSONB object, new_value will be inserted only if target does not exist.

因此路径必须指向不存在的键(您要插入的键)。