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

我正在尝试将 key 附加到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"内部的嵌套data json的键。

更新2:

每位更新的克林斯回答以下作品:

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

最佳答案

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

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:

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

关于sql - 在Postgres中将嵌套键值添加到JSONB，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51014045/