Simply way to append a key-value pair in Postgres JSONB

Question

I'm trying to write an UPDATE statement to add key-values pairs to a nested JSON path.

Right now my method is to use a CASE statement. For example, if I wanted to add the key-value pair {key: val} into the path a->b1...

UPDATE orders SET info = jsonb_set(info, '{a}',                                                -- adds to the a->b1 key (chain created if not exists)
    CASE
    WHEN info->'a' IS NULL THEN '{"b1": {"key": "val"}}'
    WHEN info->'a'->'b1' IS NULL THEN jsonb_set(info->'a', '{b1}', '{"key": "val"}', true)
    ELSE jsonb_set(info->'a', '{b1}', info->'a'->'b1' || '{"key": "val"}', true)
    END,
    true);

The statement above works...

• If a doesn't exist, it'll create a with {b1: {key: val}}.
• If a->b1 doesn't exist, it'll create a->b1 and set it to {key: val}.
• If a->b1 does exist, it'll grab a->b1 and append {key: val} within it.

, but it's tedious to write and understand. Does Postgres provide an easier way to do what I'm trying to do? Given a path, I want to add one or more key-value pairs into it (creating the objects in the path if they don't yet exist).

Answer 1

You can use the function described in Merging JSONB values in PostgreSQL?

update orders 
set info = jsonb_recursive_merge(info, '{"a": {"b1": {"key": "val"}}}');

Test it in rextester.