The JSONB objects store a huge amount of textual data in the binary form. Sometimes we need to insert more data values into the existing PostgreSQL JSONB objects. This is possible if we use the PostgreSQL jsonb_insert() function. This function assists the user in adding more values to the existing JSONB objects, by taking that particular JSONB object and the path or location at which we want to insert the new JSONB value and the new JSONB object, as inputs.

The following article will demonstrate the methods that can insert a new JSONB object into the existing JSONB object using the jsonb_insert() function.

Understanding Postgres jsonb_insert() Function

The jsonb_insert() function takes the JSONB object as input, along with the path at which we wish to insert the new JSONB value and the new JSONB value (that is to be inserted). The basic structure of the jsonb_insert() function can be written as:

jsonb_insert(
target_jsonb_object JSONB,
location_of_insersion TEXT[],
new_jsonb_val JSONB
[, insert_after_val BOOLEAN]) ;

In the above syntax:

● The first parameter i.e.“target_jsonb_object” is the given/target JSONB object, in which we want to insert the new JSONB value.

● The second parameter i.e.“location_of_insersion” specifies the path or the location at which we want to insert the new JSONB value in the “target_jsonb_object”. This parameter is specified in the TEXT array format.

● The third parameter i.e. “new_jsonb_val” is the value that is to be inserted in the given “target_jsonb_object”.

● The fourth parameter is totally optional. The “insert_after_val” is a boolean value that illustrates if the “new_jsonb_val” is inserted after the specified “location_of_insersion” or not. The value is FALSE by default. This means that if we do not specify the “insert_after_val” parameter, the new JSONB value will be added before the specified location. Whereas, if the “insert_after_val” parameter is TRUE, the new JSONB value is inserted after the specified location.

● The jsonb_insert() function returns a JSONB object. That JSONB object is basically the new values inserted into the target JSONB object.

Basic Working

So how does the jsonb_insert() function basically work? The jsonb_insert() function first looks for the provided new JSONB object(that is to be inserted) in the provided target JSONB object, if the new JSONB value(to be inserted), already exists in the target JSONB object, the new value declared is simply added to that target JSONB object. If it is absent, then a new key-value pair is created in that JSONB object.

Let’s move forward into the details of the jsonb_insert() function with the help of examples.

Example 1: Understanding the jsonb_insert() Function

To understand the working of the jsonb_insert() function, consider the following query:

SELECT jsonb_insert('{"Roll numbers": [1,3,5,7,9]}', '{Roll numbers,0}', '100');

In the above query:

● The first parameter i.e. '{"Roll numbers": [1,3,5,7,9]}' is the target JSONB object in which the value is to be inserted.

● The second parameter here is '{Roll numbers,0}'. This specifies the location. The location is given as a TEXT array. This depicts that the new JSONB value is to be placed as “Roll numbers” at and before index “0”.

● The new JSONB value specified is 100.

● We have not specified the “insert_after val” parameter. This means that it will be FALSE by default and the new JSONB value will be inserted before the specified location.

So the function will first find the new JSONB value(to be inserted) in the given/target array. In this case, it is already present in the target JSONB object, so it will simply insert the specified value in the array at a specific location. The last boolean parameter is skipped so it will be FALSE by default. This means that the value will be inserted before the provided location i.e. “0” in this case.

The query results in the following output:

We can notice that the new JSON value that is “100” has been inserted into the given JSONB object before the index 0.

Let’s observe what will happen if we replace the location in the index with something else. The following query depicts the change.

SELECT jsonb_insert('{"Roll numbers": [1,3,5,7,9]}', '{Roll numbers,3}', '100');

We have specified the index “3” now. The query results are the following:

So this is how the jsonb_insert() function basically works.

Example 2: Understanding the jsonb_insert() Function

We will now see the case if the new JSONB value does not exist in the provided/target JSONB values. Consider the below given query:

SELECT jsonb_insert('{"Roll numbers": [1,3,5,7,9]}', '{test_scores}', '100');

We have specified a new JSONB object i.e. “test_scores”. In this case, a new value will be created rather than added to the existing one. Following is the output:

We can see that the returned JSONB object contains the previously contained JSONB values and the newly inserted value. Let’s now add some impact of the last “insert_after_val” parameter.

Example 3: Understanding the jsonb_insert() Function’s Last Parameter

As stated, the last parameter i.e. “insert_after_val” has a boolean value and is totally optional. The default value of this parameter is FALSE. The FALSE means that the value will be placed before the specified location as in the above cases. Let’s specify FALSE.

SELECT jsonb_insert('{"Roll numbers": [1,3,5,7,9]}', '{Roll numbers,0}', '100', FALSE);

So in this case, the query will add the value before the “0” index of the provided JSONB object array like this:

Now if we declare the “insert_after_val” as TRUE. This will insert the new JSONB value after the specified location.

SELECT jsonb_insert('{"Roll numbers": [1,3,5,7,9]}', '{Roll numbers,0}', '100', TRUE);

Now the value “100” will be added after the index “0” in the provided JSONB object “Roll numbers” array i.e. the element at index “0” is 1. So 100 will be placed after it.

The outcome of the query is as we expected. This is the impact of the last boolean parameter.

Example 4: Replacing Existing Key Error in the jsonb_insert() Function

We can insert some value in the existing JSONB array using the jsonb_insert() function but we can not replace any value. For example in the above-considered example, if we do not specify the index of the element in the array it would depict the whole “Roll numbers” JSONB object, and when we specify its new value it means that we want to replace the value of the already existing “Roll numbers” JSONB object. The following query implements these statements.

SELECT jsonb_insert('{"Roll numbers": [1,3,5,7,9]}', '{Roll numbers}', '100');

In this code, we have not specified the index position of the “Roll numbers” JSONB object array. So the function will observe that the “Roll numbers” key is already present and has a new declared value. This means that we are trying to replace the key which is not possible with the jsonb_insert() function and it throws an error like this:

The error states that we are trying to replace the key which is not possible by making use of this function. To do this use the jsonb_set() function instead.

Conclusion

The jsonb_insert() function inserts a new JSONB value into an already existing JSONB object at a specific location. The jsonb_insert() function takes in 3 parameters essentially and 1 parameter optionally. These 3 parameters include; the target JSONB array in which we want to insert a new value, the path/location of the JSONB array where the new value will inserted, and the new JSONB array that we wish to insert. The optional argument is a boolean value that shows if the new JSONB value is to be placed in the target JSONB object before or after the specified location. The jsonb_insert() function has been discussed in detail in this article using proper examples and use cases.