PostgreSQL jsonb_set() Function

The PostgreSQL jsonb_set() function replaces or inserts the value at the specified path.

`jsonb_set()` Syntax

This is the syntax of the PostgreSQL jsonb_set() function:

jsonb_set(
  target JSONB, path TEXT[], new_value JSONB[, create_if_missing BOOLEAN]
) -> JSONB

Parameters

target: Required. The JSONB value to insert the new value into.
path: Required. A text array indicating where new values are inserted. The first path in the array should contain the last path in the array.
new_value: Required. The new value to insert or update.
create_if_missing: Optional. It indicates whether to insert the specified new value if the specified path does not exist. The default value is true.

Return value

The PostgreSQL jsonb_set() function returns the given JSONB value with the specified path replaced with the new value, or insert the new value if create_if_missing is true (the default).

If any parameter new_value is NULL, the jsonb_set() function will return NULL.

`jsonb_set()` Examples

JSON Array

The following example shows how to use the PostgreSQL jsonb_set() function to update a element in a JSON array.

SELECT jsonb_set('[0, 1, 2]', '{1}', '"x"');

  jsonb_set
-------------
 [0, "x", 2]

Here, the path array {1} points to the element at index 1 in the array [0, 1, 2].

The following example shows how to use the PostgreSQL jsonb_set() function to update elements in an embedded JSON array.

SELECT jsonb_set('[0, [1, 2], 2]', '{1, 1}', '"x"');

    jsonb_set
------------------
 [0, [1, "x"], 2]

Here, the path array {1, 1} points to the element at index 1 in the nested array of the outer array [0, [1, 2], 2].

JSON Object

The following example shows how to use the PostgreSQL jsonb_set() function to update a field in a JSON object.

SELECT jsonb_set('{"x": 1}', '{x}', '"x"');

 jsonb_set
------------
 {"x": "x"}

The following example shows how to use the PostgreSQL jsonb_set() function to insert a new field in a JSON object.

SELECT jsonb_set('{"x": 1}', '{y}', '2');

    jsonb_set
------------------
 {"x": 1, "y": 2}

Here, since the path y does not exist in the JSON object {"x": 1}, so jsonb_set() inserted the field y with value 2 in the JSON object {"x": 1}. Of course, you can also disable this default insertion behavior by passing the parameter create_if_missing with false, for example:

SELECT jsonb_set('{"x": 1}', '{y}', '2', false);

 jsonb_set
-----------
 {"x": 1}

Here, the original JSON document is returned because the default insert behavior is disabled.

jsonb_set() Syntax