PostgreSQL jsonb_insert() Function

The PostgreSQL jsonb_insert() function inserts a new value into a given JSONB value at the specified path.

jsonb_insert() Syntax

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

jsonb_insert(
  target JSONB, path TEXT[], new_value JSONB[, insert_after 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 be inserted.

insert_after

Optional. It indicates whether to insert after the specified position. The default is false.

Return value

The PostgreSQL jsonb_insert() function returns the given JSONB value with the specified new value inserted at the specified path.

If the path points to an object field, the new value will only be inserted if the field does not exist in the object.

If the path points to an array element, if insert_after is false (the default), the new value is inserted before the specified path, otherwise the new value is inserted after the specified path.

If the specified path is not found in the JSONB value, the JSON value is not modified.

jsonb_insert() Examples

The following example shows how to use the PostgreSQL jsonb_insert() function to insert a key into a JSON object.

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

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

The following example shows how to use the PostgreSQL jsonb_insert() function to insert a new element into a JSON array.

SELECT jsonb_insert('{"x": 1, "y": [1, 2]}', '{y, 0}', '0');

       jsonb_insert
--------------------------
 {"x": 1, "y": [0, 1, 2]}

Here, the path array {y, 0} means to find the y field first in the object, then find the position in the y field value with an index of 0, and then insert a new element at this 0 position.

Of course, we can insert the new element after the specified position:

SELECT jsonb_insert('{"x": 1, "y": [1, 2]}', '{y, 0}', '0', true);

       jsonb_insert
--------------------------
 {"x": 1, "y": [1, 0, 2]}

You can’t replace a key that already exists in an object, like:

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

This will throw an error:

Error: cannot replace existing key, hint: try to use jsonb_set to replace key value.