Removing nulls from JSON arrays - coming in PostgreSQL 18
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 null
s 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.