CODEWITHSUNDEEP
sqljsonpostgresqljsonb
Skorpeo
Skorpeo

Reputation: 2552

Add Nested Key Value to JSONB in Postgres

I am trying to append a key to a nested jsonb in postgres but I get errors. Essentially I start with a json as:

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

and I want to append {"b", "baz"} into "new_info" such that resulting jsonb is:

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

I am using the following commands to get to the original 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"}' ;

and trying to use the following to update "new_info" which doesn't work:

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"}';

Any pointers greatly appreciated!

UPDATE #1:

Per klins answer the following works:

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

However how can one avoid overwriting existing keys using something like jsonb_insert. In other words why don't the following examples work?:

#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 *;

In other words klin's answer only considers the keys at the top level data jsonb as opposed to the keys of the nested "new_info" json that is inside of data.

UPDATE #2:

Per klins updated answer the following works:

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

However if "new_info" doesn't exist in data the update completes successfully without saving. Thus the following commands complete successfully but the data is not saved:

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 *;

So this to me is a little surprising as it gives the impression that it saved although it didn't. I want to avoid case statements as most of the time it will be a unnecessary check and would rather it fail if "new_info" doesn't exist (or just create it if it doesn't add overhead to situations where "new_info" does already exist). I.e I want to avoid what these answers do:

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

Update or create nested jsonb value using single update command

Upvotes: 13

Views: 14369

Answers (1)

klin
klin

Reputation: 121604

Use || (concatenation operator):

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

The function jsonb_set() was introduced in Postgres 9.5. In Postgres 9.6+ you can also use jsonb_insert(), which may be more straightforward:

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

From 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.

Hence path must point to non-existing key (the key you want to insert).

Upvotes: 26

Related Questions