PostgreSQL 18 reference · JSON Functions

PostgreSQL JSONB_STRIP_NULLS() Function

Remove object fields whose value is JSON null, recursively. Since PostgreSQL 18 it can also drop nulls in arrays.

PostgreSQL 18 Since 9.5

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

jsonb_strip_nulls(target, strip_in_arrays)

Parameter	Description
`jsonb`	A `jsonb` (or `json`) value.
`strip_in_arrays` optional	Also remove null array elements (18+). Default: `false`

jsonb.

Drop null fields

SELECT jsonb_strip_nulls('{"name":"Ada","email":null,"tags":[1,null]}') AS cleaned;

Output

              cleaned
------------------------------------
 {"name": "Ada", "tags": [1, null]}
(1 row)

Run this on your computer — see “Get Started” for setup.

Arrays too (18+)

SELECT jsonb_strip_nulls('[1,null,{"a":null}]', true) AS cleaned_arrays;

Output

 cleaned_arrays
----------------
 [1, {}]
(1 row)

Run this on your computer — see “Get Started” for setup.

Version	Change
PostgreSQL 9.5	Added
PostgreSQL 18	Added the `strip_in_arrays` parameter.

JSONB_SET() · Return jsonb with the value at a path replaced or, by default, created. Combine with || to merge objects.
SQL String Functions · tutorial