Menu

PostgreSQL jsonb_insert() Function

Updated on

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

path

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

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 target with new_value inserted at path.

If the path points to an object field that already exists, the function leaves target unchanged; it does not replace the field or raise an error. Use jsonb_set() to replace an existing field.

If the path points to an array element, the function inserts before it by default or after it when insert_after is true. For an out-of-range final array index, a negative index inserts at the beginning and a positive index at the end. If an earlier path step is missing, target is returned unchanged.

If a final object key does not exist, it is added with new_value.

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, path {y, 0} selects the array at key y, then inserts before its element at index 0.

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]}

An existing object key is left unchanged; jsonb_insert() does not replace it:

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

 jsonb_insert
--------------
 {"x": 1}

Use jsonb_set() when you need to change the value of an existing key.

Further reading

See the PostgreSQL documentation for jsonb_insert for the complete function behavior.