Menu

PostgreSQL jsonb_set() Function

Updated on

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 update.

path

Required. A text array of object keys and array indexes identifying the value from the top level down. Every path step before the last must already exist in target.

new_value

Required. The new value to insert or update.

create_if_missing

Optional. If true, create the final path item when it is missing. All earlier path steps must exist. The default is true.

Return value

The PostgreSQL jsonb_set() function returns target with the value at path replaced by new_value. If the final path item is missing, it is created only when create_if_missing is true; missing earlier path steps leave target unchanged. To store JSON null, pass 'null'::jsonb as new_value.

For SQL NULL handling, see jsonb_set_lax().

jsonb_set() Examples

JSON Array

The following example updates the array element at index 1. PostgreSQL array indexes in JSON paths are zero-based.

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

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

Here, path {1} selects the second element in [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, path {1, 1} selects the second element of the nested array at outer index 1.

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, path {y} selects the new field. The final path item is created because create_if_missing defaults to true. Set it to false to leave the object unchanged when that item is missing:

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.

Further reading

See the PostgreSQL documentation for jsonb_set and jsonb_set_lax() for complete function behavior.