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 update.

path

Required. A text array of object keys and array indexes identifying the value from the top level down. Every path step before the last must already exist in target.

new_value

Required argument. A SQL NULL invokes null_value_treatment; the JSONB value 'null'::jsonb is a non-NULL value and is stored as JSON null.

create_if_missing

Optional. If true, create the final path item when it is missing. All earlier path steps must exist. The default is true.

null_value_treatment

Optional. Selects how to handle SQL NULL in new_value. The default is 'use_json_null'. Valid values are:

• '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

When new_value is not SQL NULL, jsonb_set_lax() behaves like jsonb_set(): it replaces the value at path, or creates the final path item when create_if_missing is true.

If new_value is SQL NULL, the function applies null_value_treatment: raise an error, store JSON null, delete the item at the path, or return target unchanged. A JSONB value of 'null'::jsonb is not SQL NULL, so it is stored as JSON null regardless of null_value_treatment.

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

jsonb_set_lax() Examples

array

The following example updates the array element at index 1. PostgreSQL array indexes in JSON paths are zero-based.

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

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

Here, path {1} selects the second element in [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, path {1, 1} selects the second element of the nested array at outer index 1.

Object

The following example uses jsonb_set_lax() to update a field in a JSONB object.

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

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

The following example inserts a new field when its final path item is missing.

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

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

Here, path {y} selects the new field. The final path item is created because create_if_missing defaults to true. Set it to false to leave the object unchanged when that item is missing:

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, the third argument is SQL NULL, so the default null_value_treatment ('use_json_null') stores JSON null in y.

By contrast, an explicit JSONB null is a non-NULL value. It is stored as JSON null even when null_value_treatment is set to 'delete_key':

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

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

To apply another behavior to SQL NULL, pass a different 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.

Further reading

See the PostgreSQL documentation for jsonb_set_lax and jsonb_set() for the complete function behavior.