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)

Answer 2

I just faced the same issue and created a simple postgres-function for it. It may not be the fastest solution and might especially be slow for deep inserts, but so far works fine for me!

CREATE OR REPLACE FUNCTION jsonb_deep_set(curjson jsonb, globalpath text[], newval jsonb) RETURNS jsonb AS
$$
  BEGIN
    IF curjson is null THEN
      curjson := '{}'::jsonb;
    END IF;
    FOR index IN 1..ARRAY_LENGTH(globalpath, 1) LOOP
      IF curjson #> globalpath[1:index] is null THEN
        curjson := jsonb_set(curjson, globalpath[1:index], '{}');
      END IF;
    END LOOP;
    curjson := jsonb_set(curjson, globalpath, newval);
    RETURN curjson;
  END;
$$
LANGUAGE 'plpgsql';

Relacing jsonb with json makes it also work for json objects.

Answer 3

To achieve "safe value-setting" of deeply-nested fields (without having to create a new PostgreSQL function in the database), I use the following pattern: (as first mentioned here)

update "myTable" set "myField" =
  jsonb_set(COALESCE("myField", '{}'), '{"depth1"}',
  jsonb_set(COALESCE("myField"->'depth1', '{}'), '{"depth2"}',
  jsonb_set(COALESCE("myField"->'depth1'->'depth2', '{}'), '{"depth3"}',
  jsonb_set(COALESCE("myField"->'depth1'->'depth2'->'depth3', '{}'), '{"depth4"}',
  '"newValue"'
)))) where "id" = 'myRowID' returning *

And for those who may dislike the deeply-nested nature of the calls above, here is an alternative:

update "myTable" set "myField" = jsonb_set(
  CASE
    WHEN "myField" IS NULL THEN '{"depth1": {"depth2": {"depth3": {}}}}'
    WHEN "myField"->'depth1' IS NULL THEN jsonb_set("myField", array['depth1'], '{"depth2": {"depth3": {}}}')
    WHEN "myField"->'depth1'->'depth2' IS NULL THEN jsonb_set("myField", array['depth1','depth2'], '{"depth3": {}}')
    WHEN "myField"->'depth1'->'depth2'->'depth3' IS NULL THEN jsonb_set("myField", array['depth1','depth2','depth3'], '{}')
    ELSE "myField"
  END,
  array['depth1','depth2','depth3','depth4'],
  '"newValue"'
) where "id" = 'myRowID' returning *