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

Kristo Mägi · Accepted Answer · 2017-10-17 21:00:43Z

8

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)

answered Oct 17, 2017 at 21:00

Kristo Mägi

1,70413 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user9645 Over a year ago

Thanks - I think I can use the 2nd option. The value v2 is coming from another query, but I can json-ize it with JSON_BUILD_OBJECT('value', (QUERY)), right?

Kristo Mägi Over a year ago

Yes if the query results only that single value preferably as json.

tuxSlayer Over a year ago

|| doesn't really work if there are other values except k2.value, i.e. lets imagine we want to modify a value in a nested object with might or might not exist. If it exists and create other values we are gonna remove them with this approach

Julian · Accepted Answer · 2020-09-23 08:56:56Z

6

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.

answered Sep 23, 2020 at 8:56

Julian

1161 silver badge2 bronze badges

1 Comment

Venryx Over a year ago

A similar Gist for this logic can be seen here. (as well as my alternative, which avoids the need to create a new function)

Venryx · Accepted Answer · 2022-12-17 04:47:57Z

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", '{}'), array['depth1'],
  jsonb_set(COALESCE("myField"->'depth1', '{}'), array['depth2'],
  jsonb_set(COALESCE("myField"->'depth1'->'depth2', '{}'), array['depth3'],
  jsonb_set(COALESCE("myField"->'depth1'->'depth2'->'depth3', '{}'), array['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 *

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related