PostgreSQL jsonb_set_lax() Function

The PostgreSQL jsonb_set_lax() function replaces or inserts the value at the specified path. This function differs from jsonb_set() in the method of handling NULL values.

`jsonb_set_lax()` Syntax

This is the syntax of the PostgreSQL jsonb_set_lax() function:

jsonb_set_lax(
    target JSONB
  , path TEXT[]
  , new_value JSONB
  [, create_if_missing BOOLEAN
  [, null_value_treatment TEXT]]
) -> JSONB

Parameters

target: Required. The JSONB value to insert the new value into.
path: Required. A text array indicating where new values are inserted. The first path in the array should contain the last path in the array.
new_value: Required. The new value to insert or update.
create_if_missing: Optional. It indicates whether to insert the specified new value if the specified path does not exist. The default value is true.
null_value_treatment: Optional. It indicates handling method when new_value is NULL. It is a string that can accept the following 4 values:

'raise_exception': Gives an error if new_value is NULL.
'use_json_null': Use JSON null value, if new_value is NULL.
'delete_key': Delete the corresponding key, if new_value is NULL.
'return_target': Return the original JSON value, if new_value is NULL.

Return value

The PostgreSQL jsonb_set_lax() function returns the given JSONB value with the specified path replaced with the new value, or insert the new value if create_if_missing is true (the default).

If the argument new_value is not NULL, this function likes jsonb_set() exactly.

If the parameter new_value is NULL, you can pass the parameter null_value_treatment to take different actions on the NULL value:

'raise_exception': gives an error
'use_json_null': use JSON null value
'delete_key': delete the corresponding key
'return_target': take no action, return the original JSON value

If the parameter target or path is NULL, the jsonb_set_lax() function will return NULL.

`jsonb_set_lax()` Examples

array

The following example shows how to use the PostgreSQL jsonb_set_lax() function to update a element in a JSON array.

SELECT jsonb_set_lax('[0, 1, 2]', '{1}', '"x"');

  jsonb_set_lax
-------------
 [0, "x", 2]

Here, the path array {1} points to the element at index 1 in the array [0, 1, 2].

The following example shows how to use the PostgreSQL jsonb_set_lax() function to update elements in an embedded JSON array.

SELECT jsonb_set_lax('[0, [1, 2], 2]', '{1, 1}', '"x"');

    jsonb_set_lax
------------------
 [0, [1, "x"], 2]

Here, the path array {1, 1} points to the element at index 1 in the nested array of the outer array [0, [1, 2], 2].

Object

The following example shows how to use the PostgreSQL jsonb_set() function to update a field in a JSON object.

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

 jsonb_set_lax
------------
 {"x": "x"}

The following example shows how to use the PostgreSQL jsonb_set() function to insert a new field in a JSON object.

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

    jsonb_set_lax
------------------
 {"x": 1, "y": 2}

Here, since the path y does not exist in the JSON object {"x": 1}, so jsonb_set() inserted the field y with value 2 in the JSON object {"x": 1}. Of course, you can also disable this default insertion behavior by passing the parameter create_if_missing with false, for example:

SELECT jsonb_set_lax('{"x": 1}', '{y}', '2', false);

 jsonb_set_lax
-----------
 {"x": 1}

Here, the original JSON document is returned because the default insert behavior is disabled.

NULL Values

The following example shows how to use the PostgreSQL jsonb_set_lax() function to insert a new field in a JSON object.

SELECT jsonb_set_lax('{"x": 1, "y": 2}', '{y}', NULL);

    jsonb_set_lax
---------------------
 {"x": 1, "y": null}

Here, we used a NULL value for the field y, and the jsonb_set_lax() function used null as the value of y. This is the default behavior. But we can change the default behavior by the parameter null_value_treatment, for example:

delete_key

SELECT jsonb_set_lax('{"x": 1, "y": 2}', '{y}', NULL, true, 'delete_key');

jsonb_set_lax
---------------
{"x": 1}

Here, we used 'delete_key' for the parameter null_value_treatment, and the field y was removed.

return_target

SELECT jsonb_set_lax('{"x": 1, "y": 2}', '{y}', NULL, true, 'return_target');

jsonb_set_lax
------------------
{"x": 1, "y": 2}

Here, we used 'return_target' for the parameter null_value_treatment, and jsonb_set_lax() returned the original JSON value.

raise_exception

SELECT jsonb_set_lax('{"x": 1, "y": 2}', '{y}', NULL, true, 'raise_exception');

Error:  JSON value must not be null
Description:  Exception was raised because null_value_treatment is "raise_exception".
Tips:  To avoid, either change the null_value_treatment argument or ensure that an SQL NULL is not passed.

Here, we used 'raise_exception' for the parameter null_value_treatment, and jsonb_set_lax() gave an error.

jsonb_set_lax() Syntax