How to use PostgreSQL JSONB_SET() to create new deep object element

Question

I must be missing something... seems JSONB_SET() is not working as advertised?

SELECT JSONB_SET(
    '{"k1": {"value": "v1"}}',
    '{k2,value}',
    '"v2"',
    TRUE
);

Results in:

----+------------------------
    | jsonb_set
    | jsonb
----+------------------------
  1 | {"k1": {"value": "v1"}}
----+------------------------

I was expecting {"k1": {"value": "v1"}, "k2": {"value": "v2"}} I also tried FALSE as the fourth parameter in case it was reversed or something.

I am using PostgreSQL 9.6.4

Answer 1

The document says:

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

In your example, the second parameter - '{k2,value}' is the search path, but since the first path is k2 that does not exist, results to NULL before value could be added/replaced.

In simple terms – jsonb_set is not meant to build entire JSON document the way you tried using search path, but to add or replace single key/value.

If you want to add/replace entirely new set of JSON like that you can use || (concatenate) operator instead like that:

-- Add example:
SELECT $${"k1": {"value": "v1"}}$$::jsonb || $${ "k2": { "value": "v2"}}$$::jsonb;
                    ?column?
------------------------------------------------
 {"k1": {"value": "v1"}, "k2": {"value": "v2"}}
(1 row)

-- Replace example
SELECT $${"k1": {"value": "v1"}}$$::jsonb || $${ "k1": { "value": "v2"}}$$::jsonb;
        ?column?
-------------------------
 {"k1": {"value": "v2"}}
(1 row) 

OR

You can use jsonb_set() like that instead:

SELECT JSONB_SET(
    '{"k1": {"value": "v1"}}',
    '{k2}',
    '{"value": "v2"}',
    TRUE
);
                   jsonb_set
------------------------------------------------
 {"k1": {"value": "v1"}, "k2": {"value": "v2"}}
(1 row)