POSTGRESQL: update jsonb if key:value doesn't exsits-CodePudding

I want to update a jsonb column in table (sample):

CREATE TEMPORARY TABLE tmp_1 AS
SELECT 1 as id,'{"foo":1,"bar":2,"foo_bar":3}'::jsonb as jb_value;

in a way that this update:

UPDATE tmp_1 
   SET jb_value = jb_value || '{"foo_bar":4, "foo_foo_bar":4}'
 WHERE id = 1;

would return something similar to :

Result:
{"foo":1,"bar":2,"foo_bar":[3,4],"foo_foo_bar":4}

|| operator is not working for me, because according do docs:

Note: The || operator concatenates two JSON objects by generating an object containing the union of their keys, taking the second object's value when there are duplicate keys. All other cases produce a JSON array: first, any non-array input is converted into a single-element array, and then the two arrays are concatenated. It does not operate recursively; only the top-level array or object structure is merged.

and this would return replaced value for key "foo_bar", which I don't want:

Result:
{"foo":1,"bar":2,"foo_bar":4,"foo_foo_bar":4}

I also took jsonb_insert and jsonb_set under considaration, but I won't know path to the key that I want to update or insert.

Thanks in advance !

CodePudding user response：

Try this :

SELECT jsonb_object_agg( COALESCE(a.key, b.key)
                       , CASE
                             WHEN a.key IS NULL THEN b.value
                             WHEN b.key IS NULL THEN a.value
                             ELSE '[]' :: jsonb || a.value || b.value
                         END
                       )
  FROM jsonb_each('{"foo":1,"bar":2,"foo_bar":3}'::jsonb) AS a
  FULL OUTER JOIN jsonb_each('{"foo_bar":4, "foo_foo_bar":4}' :: jsonb) AS b
     ON a.key = b.key

http://sqlfiddle.com/#!17/9eecb/84825

You can insert this query into your UPDATE statement.