Add Nested Key Value to JSONB in Postgres

Question

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

klin · Accepted Answer · 2018-06-25 11:22:49Z

26

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

edited Jun 25, 2018 at 11:22

answered Jun 24, 2018 at 22:08

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Skorpeo Over a year ago

Thank you this works but how can I avoid overwriting existing key using something like jsonb_insert. For example why doesn't this work: WITH orig_new_info AS (SELECT data#>'{new_info}' FROM mytable WHERE data @> '{"name": "foo"}') WITH updated_new_info AS(SELECT jsonb_set(orig_new_info, orig_new_info ||'{"b":"bazer"}',TRUE)) update mytable set data = jsonb_set(data, '{new_info}', updated_new_info, TRUE) where data @> '{"name": "foo"}' returning *;

Skorpeo Over a year ago

see updated question. Thanks very much for your answer

Skorpeo Over a year ago

thanks much again! do you know why my examples don't work? Is it not possible to manipulate data and new_info separately? I have updated the question, the issue i am encountering is that if "new_info" doesn't exist the commands succeed without saving/updating???

klin Over a year ago

The problem stems from that you want to do two different operations in a single function call. In the last your example the row is updated but the json value remains unchanged because data->'new_info' is null. Use coalesce(), it seems the simplest solution. See DbFiddle.

Skorpeo Over a year ago

Has anyone ever called you master? Thanks, this is cool! But it seems that to do this with jsonb_insert would not be possible in one statement? Do you see any way to do this without overwriting existing values but still inserting "new_info" if it doesn't exist? thank you again!

Collectives™ on Stack Overflow

Add Nested Key Value to JSONB in Postgres

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related