TL;DR Some details about a recent patch of mine to core PostgreSQL 18 (upcoming) commited @ 4603903d2

Postgres has excellent support for JSON, and one would think that there are not too many low-hanging fruits to be reaped. I found such a fruit, though and it’s the perfect remedy for a long-standing pain of mine (and others)

Here’s the task: Remove the nulls from the following JSON array

[ 5, 6, null, null, 9 ]

Currently (until Postgres 17), you can use a JSONPath expression like this

SELECT jsonb_path_query_array('[5, 6, null, null, 9]', '$[*] ? (@ != null)');

or, if you can’t get the JSONPath syntax right, you probably have written a utility function jsonb_remove_array_nulls(jsonb) function that looks a bit like this one.

SELECT jsonb_agg(e)
FROM jsonb_array_elements('[5, 6, null, null, 9]') e
WHERE e IS NOT NULL;

Apart from being ugly, this also kills performance because it unpacks a JSON object, traverses it and repacks it again.

PostgreSQL in fact has a jsonb_strip_nulls(jsonb) that deletes all object fields that have null values from the given JSON value, recursively! This, however, leaves arrays untouched. If you pass it a simple array it’ll have no effect

SELECT jsonb_strip_nulls('[ 5, 6, null, null, 9 ]')
   jsonb_strip_nulls   
-----------------------
 [5, 6, null, null, 9]
(1 row)

Most importantly, though, what if your nulls are part of a more complex hierarchical object like this one?

{
    "a": 1,
    "b": null,
    "c": [ 5, 6, null, null, 9 ],
    "d": {
        "e": null,
        "f": [ null, null, { "x": null } ]
    }
}

jsonb_strip_nulls is recursive so it will remove "b": null and the nested "e": null and "x": null". Again, though nulls inside arrays won’t be touched.

When working with complex JSON data though you may want to strip nulls from arrays too. This has been a very typical story for me and I decided to do something about it.

So here it is, in all its glory: Starting from PostgreSQL 18 4603903d2, an additional parameter (“strip_in_arrays”) is added to json{b}_strip_nulls functions. It defaults to false (so existing SQL code won’t change behavior) If true, though, then null array elements are removed as well as null valued object fields. JSON that just consists of a single null is not affected.

 select jsonb_strip_nulls('
{
    "a": 1,
    "b": null,
    "c": [ 5, 6, null, null, 9 ],
    "d": {
        "e": null,
        "f": [ null, null, { "x": null } ]
    }
}
', true );
             jsonb_strip_nulls
--------------------------------------------
 {"a": 1, "c": [5, 6, 9], "d": {"f": [{}]}}
(1 row)

And of course removing the nulls from a single array is now more straightforward

SELECT jsonb_strip_nulls('[ 5, 6, null, null, 9 ]', true)
           jsonb_strip_nulls
-------------------
    [5, 6, 9]
   (1 row)

Hopefully this will simplify your SQL queries a bit.