Menu

PostgreSQL jsonb_strip_nulls() Function

Updated on

The PostgreSQL jsonb_strip_nulls() function recursively removes object fields whose JSON value is null. By default, it leaves null elements in arrays unchanged.

This variant accepts and returns jsonb. For the json equivalent, see json_strip_nulls().

jsonb_strip_nulls() Syntax

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

jsonb_strip_nulls(json_value JSONB [, strip_in_arrays BOOLEAN]) -> JSONB

Parameters

json_value

Required. The JSONB value to process.

strip_in_arrays

Optional in PostgreSQL 18 and later. When true, also removes JSON null elements from arrays; the default is false. In earlier versions, omit this argument.

Return value

The PostgreSQL jsonb_strip_nulls() function recursively removes object fields whose JSON value is null. By default, null array elements remain. In PostgreSQL 18 and later, pass true for strip_in_arrays to remove those array elements too.

If you provide a NULL parameter, the jsonb_strip_nulls() function will return NULL.

jsonb_strip_nulls() Examples

The following example shows how to use the PostgreSQL jsonb_strip_nulls() function to remove a null object field from a given JSONB value.

SELECT jsonb_strip_nulls('[1, null, 3, {"x": 1, "y": null}]');

   jsonb_strip_nulls
------------------------
 [1, null, 3, {"x": 1}]

Here, the function removes the object field y but leaves the array’s null element in place, which is the default behavior.

Remove null array elements (PostgreSQL 18+)

Pass true as the second argument to remove JSON nulls from arrays as well:

SELECT jsonb_strip_nulls('[1, null, 3, {"x": 1, "y": null}]', true);

 jsonb_strip_nulls
-------------------
 [1, 3, {"x": 1}]