# Understanding Postgres jsonb_insert() Function With Examples

> The jsonb_insert() function inserts a new JSONB value into an already existing JSONB object at a specific location. It takes in 3 parameters essentially and 1 …

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()**](<https://www.commandprompt.com/education/postgresql-jsonb_set-function/>) [function](<https://www.commandprompt.com/education/postgresql-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.

---
[View this page online](https://www.commandprompt.com/education/understanding-postgres-jsonb_insert-function-with-examples/)