Introduction to the PostgreSQL jsonb_insert() function

Thejsonb_insert() function allows you to insert new values into aJSON document of the JSONB type.

More specifically, thejsonb_insert() function allows you to add a new element into an array or a new key/value pair into an object, or nested combinations of them.

Here’s the syntax of thejsonb_insert() function:

jsonb_insert(   target jsonb,   path text[],   new_value jsonb,   [insert_after boolean]) → jsonb

In this syntax:

• target: The JSON document of the JSONB type into which you want to insert a new value.
• path: This is an array of text elements that specifies the path where you want to insert the new value.
• new_value is the new value that you want to insert into the JSON document.
• insert_after: This is an optional boolean parameter indicating whether you want to insert the new value after the specified path instead of before. It defaults tofalse, meaning that the function will insert a new value before the specified path.

Thejsonb_insert() function returns a new JSON document with thenew_value inserted before/after the specifiedpath.

PostgreSQL jsonb_insert() function examples

Let’s take some examples of using the PostgreSQLjsonb_insert() function

1) Inserting a new element into a JSON array

The following example uses thejsonb_insert() function to insert a new element into a JSON array:

SELECT jsonb_insert('[1,2,3]', '{0}', '0');

Output:

jsonb_insert-------------- [0, 1, 2, 3](1 row)

In this example:

• The original array is[1,2,3].
• The path\{0\} indicates the first element of the array.
• The number 0 is the new value.

Thejsonb_insert() function inserts the number 0 before the first element of the array.

To insert the number 0 after the first position, you set theinsert_after parameter to true as follows:

SELECT jsonb_insert('[1,2,3]','{0}','0', true);

Output:

jsonb_insert-------------- [1, 0, 2, 3](1 row)

2) Inserting a new element into a nested JSON array

The following example uses thejsonb_insert() function to insert a new element into a nested array:

SELECT  jsonb_insert(    '[1,2,[4,5],6]','{2,0}','3'  );

Output:

jsonb_insert---------------------- [1, 2, [3, 4, 5], 6](1 row)

In this example:

• The original array is\[1,2,\[3,4],6].
• The path\{2, 0\},2 specifies the second element of the array which is the nested array\[3,4], and0 specifies the first element of the nested array.
• 3 is the new value.

Therefore thejsonb_insert() function inserts the new value 3 before the first element of the nested array.

3) Inserting a new element into a JSON object

The following example uses thejsonb_insert() to add a new key/value pair to a JSON object:

SELECT  jsonb_insert('{"name": "John"}','{age}','2');

Output:

jsonb_insert---------------------------- {"age": 2, "name": "John"}(1 row)

In this example:

• \{“name”: “John”\} is the original object.
• \{age\} is the path that indicates the age property (or key).
• 2 is the new value to insert.

Therefore, thejsonb_insert() inserts the age property with value 2 into the JSON object.

Note that if you attempt to insert a key that already exists, you’ll get an error. For example:

SELECT jsonb_insert('{"name": "John"}','{name}','"Jane"');

Output:

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

In this case, you need to use thejsonb_set() function to replace the key value.

4) Inserting a new element into a nested JSON object

The following example uses thejsonb_insert() to add a new key/value pair to a nested JSON object:

SELECT  jsonb_insert(    '{"name":"John Doe", "address" : { "city": "San Francisco"}}',    '{address,state}',    '"California"'  );

Output:

jsonb_insert----------------------------------------------------------------------------------- {"name": "John Doe", "address": {"city": "San Francisco", "state": "California"}}(1 row)

In this example:

• \{“name”:”John Doe”, “address” : \{ “city”: “San Francisco”\}\} is the original JSON object.
• \{address, state\} is a path that specifies the address key whose value is an object and state is the new key of the address object.
• “California” is the value of the state key.

Therefore, thejsonb_insert() function inserts the state with the value California to the address object of the JSON document.

Note that to beautify the output, you can use thejsonb_pretty() function:

SELECT  jsonb_pretty(jsonb_insert(    '{"name":"John Doe", "address" : { "city": "San Francisco"}}',    '{address,state}',    '"California"'  ));

Output:

jsonb_pretty---------------------------------- {                               +     "name": "John Doe",         +     "address": {                +         "city": "San Francisco",+         "state": "California"   +     }                           + }(1 row)

5) Inserting a new element into an array of a nested object

The following example uses thejsonb_insert() to add a new element into an array of a nested object

SELECT  jsonb_insert(    '{"name": "John", "skills" : ["PostgreSQL", "API"]}',    '{skills,1}','"Web Dev"'  );

Output:

jsonb_insert-------------------------------------------------------------- {"name": "John", "skills": ["PostgreSQL", "Web Dev", "API"]}(1 row)

In this example:

• \{“name”: “John”, “skills” : \[“PostgreSQL”, “API”\]\} is the original JSON object.
• \{skills,1\} is a path that specifies the skills key, which is an array, and 1 specifies the second element of the array.
• “Web Dev” is the new value to insert.

Thejsonb_insert() function inserts the “Web Dev” before the second element of the skills array of the JSON object.

6) Using the PostgreSQL jsonb_insert() function with table data

We’ll show you how to use thejsonb_insert() function to insert a new value into a JSON document and update it back to a table.

First,create a new table calledemployee_profiles:

CREATE TABLE employee_profiles(    idINT PRIMARY KEY,    profiles JSONB);

Second,insert rows into theemployee_profiles table:

INSERT INTO employee_profiles(id, profiles)VALUES   (1,'{"name": "John", "skills" : ["PostgreSQL", "API"]}'),   (2,'{"name": "Jane", "skills" : ["SQL","Java"]}')RETURNING*;

Output:

id |                     profiles----+---------------------------------------------------  1 | {"name": "John", "skills": ["PostgreSQL", "API"]}  2 | {"name": "Jane", "skills": ["SQL", "Java"]}(2 rows)

Third, add the “Web Dev” skill to the employee with the id 1:

UPDATE  employee_profilesSET  profiles= jsonb_insert(    profiles,'{skills,0}','"Web Dev"'  )WHERE  id= 1RETURNING*;

Output:

id |                           profiles----+--------------------------------------------------------------  1 | {"name": "John", "skills": ["Web Dev", "PostgreSQL", "API"]}(1 row)

Summary

• Use thejsonb_insert() function to insert a new value into a JSON document of the type JSONB.

Last updated onFebruary 25, 2024

On this page

• Introduction to the PostgreSQL jsonb_insert() function
• PostgreSQL jsonb_insert() function examples
• Summary