Thread: jsonb_strip_nulls with arrays?

Currently: 

jsonb_strip_nulls ( jsonb ) → jsonb

Deletes all object fields that have null values from the given JSON value, recursively. Null values that are not object fields are untouched.

> Null values that are not object fields are untouched. 

Can we revisit this and make it work with arrays, too?

Tbh, at first sight that looked like the expected behavior for me.

That is strip nulls from arrays as well.

This has been available since 9.5 and iiuc predates lots of the jsonb array work.

In practice, though, whenever jsonb_build_array is used (especially with jsonpath),

a few nulls do appear in the resulting array most of the times,

Currently, there’s no expressive way to remove this.  

We could also have jsonb_array_strip_nulls(jsonb) as well

Currently: 

jsonb_strip_nulls ( jsonb ) → jsonb

Deletes all object fields that have null values from the given JSON value, recursively. Null values that are not object fields are untouched.

> Null values that are not object fields are untouched. 

Can we revisit this and make it work with arrays, too?

Tbh, at first sight that looked like the expected behavior for me.

That is strip nulls from arrays as well.

This has been available since 9.5 and iiuc predates lots of the jsonb array work.

I don't think that's a great idea. Removing an object field which has a null value shouldn't have any effect on the surrounding data, nor really any on other operations (If you try to get the value of the missing field it should give you back null). But removing a null array member isn't like that at all - unless it's the trailing member of the array it will renumber all the succeeding array members.

And I don't think we should be changing the behaviour of a function, that people might have been relying on for the better part of a decade.

In practice, though, whenever jsonb_build_array is used (especially with jsonpath),

a few nulls do appear in the resulting array most of the times,

Currently, there’s no expressive way to remove this.  

We could also have jsonb_array_strip_nulls(jsonb) as well

We could, if we're going to do anything at all in this area. Another possibility would be to provide a second optional parameter for json{b}_strip_nulls. That's probably a better way to go.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com