With Postgres, how can I use UPDATE to insert a new jsonb value if NULL, or amend the json if a value already exists?

user_11317673 0
Viewed 1248

I have a table in Postgres (version 10) that contains a nullable jsonb column:

CREATE TABLE j_play (
id                  serial PRIMARY KEY,
created_ts          TIMESTAMPTZ,
data                JSONB
);

It is possible that records may initially be inserted with no json data, and so the column will be null.

| id | created_ts                   | data
| 1  | 2020-09-11 18:18:37.47755+00 | [null]

I want to update this record so that if 'null' a json object is added, and if a json object already exists then it is amended.

UPDATE j_play SET data = '{"a": "12345"}'
| id | created_ts                   | data
| 1  | 2020-09-11 18:18:37.47755+00 | {"a": "12345"}

UPDATE j_play SET data = '{"b": "54321"}'   -- This is obviously bogus but the intention is to amend existing json
| id | created_ts                   | data
| 1  | 2020-09-11 18:18:37.47755+00 | {"a": "12345", "b": "54321"}

If the record starts with an empty json document then I can use jsonb_set:

| id | created_ts                   | data
| 1  | 2020-09-11 18:18:37.47755+00 | {}
UPDATE j_play SET data = jsonb_set(rec_data, '{a}', '"12345"', TRUE) WHERE id = 1

But I can't work out how to do this when the initial value is NULL. I can probably live with initialising the column to {} but I wondered if there was an elegant way to update it from NULL.

jsonb sql-update postgresql sql json
1 Answers

I want to update this record so that if 'null' a json object is added, and if a json object already exists then it is amended.

Use coalesce():

update j_play set data = coalesce(data, '{}') || '{"a": "12345"}';

Demo on DB Fiddle:

insert into j_play (data) values(null);

update j_play set data = coalesce(data, '{}') || '{"a": "12345"}';
update j_play set data = coalesce(data, '{}') || '{"b": "54321"}';

select * from j_play;

id | created_ts | data                        
-: | :--------- | :---------------------------
 1 | null       | {"a": "12345", "b": "54321"}
user_106767160
Related