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-25T11:22:49.873

21

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 '18 at 11:22

answered Jun 24 '18 at 22:08

klin

112,967
15
204
232

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 Jun 25 '18 at 10:41
see updated question. Thanks very much for your answer – Skorpeo Jun 25 '18 at 10:50
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??? – Skorpeo Jun 25 '18 at 12:50
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.](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=cd8a5b4bd5d1a0a2080f8c0b8ea01748) – klin Jun 25 '18 at 13:09
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! – Skorpeo Jun 25 '18 at 14:52

Add Nested Key Value to JSONB in Postgres

1 Answers1