In PostgreSQL, various built-in functions are used to deal with the JSON and JSONB data efficiently, such as TO_JSONB(), JSONB_ARRAY_ELEMENTS(), JSONB_BUILD_ARRAY(), and many more. Among them, the most frequently used function is JSONB_SET() which inserts or updates the given value at the specified path.

This write-up will guide you on using the JSONB_SET() function in PostgreSQL using practical examples.

PostgreSQL JSONB_SET() Function

JSONB_SET() is a predefined JSON function that accepts a new value, a JSONB value to insert/replace the new value into, and the targeted path. As a result, it inserts/replaces the “new value” in the targeted JSONB value at the specified path.

Syntax

Check out the below-stated syntax for the PostgreSQL’s JSONB_SET() function:

JSONB_SET(
target_val JSONB, 
path TEXT[], 
new_val JSONB[, create_if_missing BOOLEAN]
)

Parameters

Here is the parameter’s description that will help you understand JSONB_SET() function better:

- The “target_val” represents a JSONB value to insert/replace the new value into.

- The “path” parameter represents a text array where new values ​​will be inserted.

- The “new_val” represents a value to be inserted/replaced.

Let’s implement the JSONB_SET() function practically.

Example 1: Using JSONB_SET() Function on JSON Arrays

The following example illustrates the use of the JSONB_SET() function on JSON Arrays:

SELECT JSONB_SET('[100, 200, 125, 250, 120]', '{2}', '"Joseph"');

The above code will insert “Joseph” at the second index of the specified array:

The output demonstrates that the specified function successfully replaces the array element at the second index with "Joseph".

Example 2: Using JSONB_SET() Function on JSON Object

The following example illustrates the use of the JSONB_SET() function on JSON Object:

SELECT JSONB_SET('{"Joseph": 100}', '{Joseph}', '"250"');

The output demonstrates that the stated function successfully updated the object’s value to "250".

Example 3: Using JSONB_SET() Function to Insert New Field in JSON Object

The below example demonstrates the use of the JSONB_SET() function to insert a new field in JSON Object:

SELECT JSONB_SET('{"Joseph": 100}', '{Seth}', '250');

A path that does not already exist in the JSON object will be inserted as a new field:

A new field has been successfully inserted into the selected JSON object.

Example 4: Disable Default Insertion in JSON Object

You can disable the default insertion in JSON objects by using the "false" parameter value, as shown in the following example:

SELECT JSONB_SET('{"Joseph": 100}', '{Seth}', '250', FALSE);

From the output, it can be clearly seen that this time JSONB_SET() doesn't insert the specified value into the JSON object.

Conclusion

JSONB_SET() is a predefined JSON function that accepts a new value, a JSONB value to insert/replace the new value into, and the targeted path. As a result, it inserts/replaces the “new value” in the targeted JSONB value at the specified path. A path that does not already exist in the JSON object will be inserted as a new field. However, users can disable the default insertion in JSON objects by using the "false" parameter value. This write-up has demonstrated various use cases of the JSONB_SET() function.