v2.25 Preview Preview release - not for production v2024.2 LTS Stable release with long-term support v2024.1 STS Stable release with standard-term support v2.20 LTS Stable release with long-term support Unsupported versions

jsonb_set() and jsonb_insert()

Purpose: Use jsonb_set() to change a JSON value that is the value of an existing key-value pair in a JSON object or the value at an existing index in a JSON array. Use jsonb_insert() to insert a value, either as the value for a key that doesn't yet exist in a JSON object or beyond the end or before the start of the index range, for a JSON array.

Signature: For jsonb_set():

jsonb_in:           jsonb
path:               text[]
replacement:        jsonb
create_if_missing:  boolean default true
return value:       jsonb

Signature: For jsonb_insert():

jsonb_in:           jsonb
path:               text[]
replacement:        jsonb
insert_after:       boolean default false
return value:       jsonb

Notes:

  • There is no json variant.

  • It turns out that the effect of the two functions is the same in some cases. This brings useful "upsert" functionality when the target is a JSON array.

  • The meaning of the defaulted boolean formal parameter is context dependent.

  • The input JSON value must be either an object or an array—in other words, it must have elements that can be addressed by a path.

Semantics when "jsonb_in" is a JSON object

parsed, or when two JSON values are concatenated, and if a key is repeated, then the last-mentioned in left-to-right order wins.) The functionality is sufficiently illustrated by a "json_in" value that has just primitive values. The result of each function invocation is the same.

do $body$
declare
  j constant jsonb := '{"a": 1, "b": 2, "c": 3}';
  path constant text[] := array['d'];
  new_number constant jsonb := '4';

  j_set constant jsonb := jsonb_set(
    jsonb_in          => j,
    path              => path,
    replacement       => new_number,
    create_if_missing => true);

  j_insert constant jsonb := jsonb_insert(
    jsonb_in          => j,
    path              => path,
    replacement       => new_number,
    insert_after      => false);

  expected_j constant jsonb := '{"a": 1, "b": 2, "c": 3, "d": 4}';

begin
  assert
    j_set = expected_j and
    j_insert = expected_j,
  'unexpected';
end;
$body$;

Notice that the specified path, the key "d" doesn't yet exist. Each function call asks to produce the result that the key "d" should exist with the value 4. So, as you see, the effect of each, as written above, is the same.

If jsonb_set() is invoked with "create_if_missing" set to FALSE, then its result is the same as the input. But if jsonb_insert() is invoked with "insert_after" set to TRUE, then its output is the same as when it's invoked with "insert_after" set to FALSE. This reflects the fact that the order of key-value pairs in an object is insignificant.

What if "path" specifies a key that does already exist? Now jsonb_insert() causes this error when it's invoked both with "insert_after" set to TRUE and with "insert_after" set to FALSE:

cannot replace existing key
Try using the function jsonb_set to replace key value.

And this DO block quietly succeeds, both when it's invoked with "create_if_missing" set to FALSE and when it's invoked with "create_if_missing" set to TRUE.

do $body$
declare
  j constant jsonb := '{"a": 1, "b": 2, "c": 3}';
  path constant text[] := array['c'];
  new_number constant jsonb := '4';

  j_set constant jsonb := jsonb_set(
    jsonb_in          => j,
    path              => path,
    replacement       => new_number,
    create_if_missing => true);

  expected_j constant jsonb := '{"a": 1, "b": 2, "c": 4}';

begin
  assert
    j_set = expected_j,
  'unexpected';
end;
$body$;

Semantics when "jsonb_in" is an JSON array

A JSON array is a list of index-addressable values—in other words, the order is defined and significant. Again, the functionality is sufficiently illustrated by a json_in value that has just primitive values. Now the result of jsonb_set() differs from that of jsonb_insert().

do $body$
declare
  j constant jsonb := '["a", "b", "c", "d"]';
  path constant text[] := array['3'];
  new_string constant jsonb := '"x"';

  j_set constant jsonb := jsonb_set(
    jsonb_in          => j,
    path              => path,
    replacement       => new_string,
    create_if_missing => true);

  j_insert constant jsonb := jsonb_insert(
    jsonb_in     => j,
    path         => path,
    replacement  => new_string,
    insert_after => true);

  expected_j_set    constant jsonb := '["a", "b", "c", "x"]';
  expected_j_insert constant jsonb := '["a", "b", "c", "d", "x"]';

begin
  assert
    (j_set = expected_j_set) and
    (j_insert = expected_j_insert),
  'unexpected';
end;
$body$;

Notice that the path denotes the fourth value and that this already exists.

Here, jsonb_set() located the fourth value and set it to "x" while jsonb_insert() located the fourth value and, as requested by "insert_after" set to TRUE, inserted "x" after it. Of course, with "insert_after" set to FALSE, "x" is inserted before "d". And (of course, again) the choice for "create_if_missing" has no effect on the result of jsonb_set().

What if the path denotes a value beyond the end of the array?

do $body$
declare
  j constant jsonb := '["a", "b", "c", "d"]';
  path constant text[] := array['42'];
  new_string constant jsonb := '"x"';

  j_set constant jsonb := jsonb_set(
    jsonb_in          => j,
    path              => path,
    replacement       => new_string,
    create_if_missing => true);

  j_insert constant jsonb := jsonb_insert(
    jsonb_in          => j,
    path              => path,
    replacement       => new_string,
    insert_after      => true);

  expected_j constant jsonb := '["a", "b", "c", "d", "x"]';

begin
  assert
    j_set = expected_j and
    j_insert = expected_j,
  'unexpected';
end;
$body$;

Here, each function had the same effect.

The path, for jsonb_set(), is taken to mean the as yet nonexistent fifth value. So, with "create_if_missing" set to FALSE, jsonb_set() has no effect.

The path, for jsonb_insert(), is also taken to mean the as yet nonexistent fifth value. But now, the choice of TRUE or FALSE for "insert_after" makes no difference because before, or after, a nonexistent element is taken to mean insert it.

Notice that if the path is specified as -42 (i.e. an impossible array index) the result is to establish the specified value at the start of the array. jsonb_set and jsonb_insert produce the same result, this:

do $body$
declare
  j constant jsonb := '["a", "b", "c", "d"]';
  path constant text[] := array['-42'];
  new_string constant jsonb := '"x"';

  j_set constant jsonb := jsonb_set(
    jsonb_in          => j,
    path              => path,
    replacement       => new_string,
    create_if_missing => true);

  j_insert constant jsonb := jsonb_insert(
    jsonb_in          => j,
    path              => path,
    replacement       => new_string,
    insert_after      => true);

  expected_j constant jsonb := '["x", "a", "b", "c", "d"]';

begin
  assert
    j_set = expected_j and
    j_insert = expected_j,
  'unexpected';
end;
$body$;

The path, for jsonb_set(), is taken to mean a new first value (implying that the existing values all move along one place). So, again, with "create_if_missing" set to FALSE, jsonb_set() has no effect.

The path, for jsonb_insert(), is also taken to mean a new first value. So again, the choice of TRUE or FALSE for "insert_after" makes no difference because before or after, a nonexistent element is taken to mean insert it.