postgresql: Adding/updating a key with array value to json column

Question

I have a column "data" with datatype json that may be empty { } or may contain some keys already: { "category": "alpha", "verified": true } I want to have a notes key which will be a text array. If it doesn't exist, an UPDATE query should create it with the text provided, otherwise it shall add to it (index position doesn't matter while appending).

There will always be a single note to be added to the notes key.

Currently, I am doing it as follows:

SELECT data::jsonb ? 'notes' FROM my_table WHERE id = 1;

If it is false, I create the key and provide the first value:

UPDATE my_table
SET data = jsonb_set(coalesce(data::jsonb,'{}'), '{notes}', '["Add"]'::jsonb)
WHERE id = 1;

If it exists, I append to the same array:

UPDATE my_table
SET data = jsonb_set(data::jsonb, array['notes'], (data->'notes')::jsonb || '["Update"]'::jsonb)
WHERE id = 1;

Can this be done in a single operation? I eventually will be writing a function that will update other columns of this table and adding/updating notes as required.

score 0 · Accepted Answer · answered May 05 '23 at 17:43

Yes it is easily as done single operation. Basically just use your first query as a case clause. Insert or append notes: based on the result. (see [demo here][1]).

update my_table 
   set data = case when data::jsonb ? 'notes' 
                   then jsonb_set(data::jsonb, array['notes'], (data->'notes')::jsonb || '["Update"]'::jsonb)
                   else jsonb_set(coalesce(data::jsonb,'{}'), '{notes}', '["Add"]'::jsonb)
              end   
 where id ...;

This of course presupposes the id(s) in question exist and the data column is itself not null (that is different that from an empty Json).
[1]: https://dbfiddle.uk/_TVKAm9a

postgresql: Adding/updating a key with array value to json column

1 Answers1