On 5th of March 2025, Andrew Dunstan committed patch:
Allow json{b}_strip_nulls to remove null array elements
An additional paramater ("strip_in_arrays") is added to these functions.
It defaults to false. If true, then null array elements are removed as
well as null valued object fields. JSON that just consists of a single
null is not affected.
Author: Florents Tselai <florents.tselai@gmail.com>
Discussion: https://postgr.es/m/4BCECCD5-4F40-4313-9E98-9E16BEB0B01D@gmail.com
So, we have pair of function to remove null elements from json objects, documented here.
You can see how they work in docs, but for completeness:
=$ select jsonb_strip_nulls('{"a":1, "b": { "c":null, "d":2}, "e":null}'); jsonb_strip_nulls ───────────────────────── {"a": 1, "b": {"d": 2}} (1 row)
As you can see both e and b.c elements were removed.
But now, you can also provide optional argument, and have it strip more nulls:
=$ select jsonb_strip_nulls('[1,2,null,3]', true); jsonb_strip_nulls ------------------- [1, 2, 3] (1 row)
If you'd pass array with just null, it will become empty array:
=$ select jsonb_strip_nulls('[null]', true); jsonb_strip_nulls ------------------- [] (1 row)
and, of course, it will also work for nested arrays:
=$ 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)
I think it's pretty helpful. Thanks, as usual, to everyone that made it happen.