jsonb_strip_nulls with arrays?

On 2024-09-17 Tu 5:26 AM, Florents Tselai wrote:

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

On Tue, Sep 17, 2024 at 5:11 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On 2024-09-17 Tu 5:26 AM, Florents Tselai wrote:

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.

Here's a patch that adds that argument (only for jsonb; no json
implementation yet)

That's how I imagined & implemented it,
but there may be non-obvious pitfalls in the semantics.

as-is version

select jsonb_strip_nulls('[1,2,null,3,4]');
jsonb_strip_nulls
--------------------
[1, 2, null, 3, 4]
(1 row)

select
jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
jsonb_strip_nulls
--------------------------------------------
{"a": 1, "c": [2, null, 3], "d": {"e": 4}}
(1 row)

with the additional boolean flag added

select jsonb_strip_nulls('[1,2,null,3,4]', *true*);
jsonb_strip_nulls
-------------------
[1, 2, 3, 4]
(1 row)

select
jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}',
*true*);
jsonb_strip_nulls
--------------------------------------
{"a": 1, "c": [2, 3], "d": {"e": 4}}
(1 row)

GH PR view: https://github.com/Florents-Tselai/postgres/pull/6/files

On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:

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.

Here's a patch that adds that argument (only for jsonb; no json
implementation yet)

I think it looks sane. We're not stripping a top level null, which is
one thing I looked out for.

I think we need a json implementation as well, though.

cheers

andrew

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

On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:

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.

Here's a patch that adds that argument (only for jsonb; no json implementation yet)

I think it looks sane. We're not stripping a top level null, which is one thing I looked out for.

I think we need a json implementation as well, though.

Thanks for having a Look, Andrew;
if there aren’t any other objections, I’ll come back with a json implementation too.

On 18 Jan 2025, at 11:51 AM, Florents Tselai <florents.tselai@gmail.com> wrote:

On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:

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.

Here's a patch that adds that argument (only for jsonb; no json implementation yet)

I think it looks sane. We're not stripping a top level null, which is one thing I looked out for.

I think we need a json implementation as well, though.

Thanks for having a Look, Andrew;
if there aren’t any other objections, I’ll come back with a json implementation too.

Attached is a v2 patch with the missing json implementation.
jsonb one remains the same.



On 2025-02-19 We 4:23 PM, Florents Tselai wrote:

On 18 Jan 2025, at 11:51 AM, Florents Tselai
<florents.tselai@gmail.com> wrote:

On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:

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.

Here's a patch that adds that argument (only for jsonb; no json
implementation yet)

I think it looks sane. We're not stripping a top level null, which
is one thing I looked out for.

I think we need a json implementation as well, though.

Thanks for having a Look, Andrew;
if there aren’t any other objections, I’ll come back with a json
implementation too.

Attached is a v2 patch with the missing json implementation.
jsonb one remains the same.

Please add this to the next Commitfest at
https://commitfest.postgresql.org/52/

cheers

andrew

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

On 20 Feb 2025, at 12:18 AM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 2025-02-19 We 4:23 PM, Florents Tselai wrote:

On 18 Jan 2025, at 11:51 AM, Florents Tselai <florents.tselai@gmail.com> <mailto:florents.tselai@gmail.com> wrote:

On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew@dunslane.net> <mailto:andrew@dunslane.net> wrote:

On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:

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.

Here's a patch that adds that argument (only for jsonb; no json implementation yet)

I think it looks sane. We're not stripping a top level null, which is one thing I looked out for.

I think we need a json implementation as well, though.

Thanks for having a Look, Andrew;
if there aren’t any other objections, I’ll come back with a json implementation too.

Attached is a v2 patch with the missing json implementation.
jsonb one remains the same.

Please add this to the next Commitfest at https://commitfest.postgresql.org/52/

Added ; thanks
https://commitfest.postgresql.org/patch/5260/

Hi

2025年3月1日(土) 2:58 Florents Tselai <florents.tselai@gmail.com>:

Please add this to the next Commitfest at https://commitfest.postgresql.org/52/

Added ; thanks
https://commitfest.postgresql.org/patch/5260/

I see this was committed, but there's a small formatting error in the docs
(extra comma in the parameter list); patch attached.

Regards

Ian Barwick

On 6 Mar 2025, at 2:10 AM, Ian Lawrence Barwick <barwick@gmail.com> wrote:

Hi

2025年3月1日(土) 2:58 Florents Tselai <florents.tselai@gmail.com>:

Please add this to the next Commitfest at https://commitfest.postgresql.org/52/

Added ; thanks
https://commitfest.postgresql.org/patch/5260/

I see this was committed, but there's a small formatting error in the docs
(extra comma in the parameter list); patch attached.

Regards

Ian Barwick
<json_strip_nulls-doc-fix.patch>

You’re corrrect.

On 2025-03-05 We 7:10 PM, Ian Lawrence Barwick wrote:

Hi

2025年3月1日(土) 2:58 Florents Tselai <florents.tselai@gmail.com>:

Please add this to the next Commitfest at https://commitfest.postgresql.org/52/

Added ; thanks
https://commitfest.postgresql.org/patch/5260/

I see this was committed, but there's a small formatting error in the docs
(extra comma in the parameter list); patch attached.

Thanks, pushed.

cheers

andrew

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

Hi,
Thanks for developing the good feature.
I've attached a small patch for the documentation of the json_strip_nulls function. The data type of the 'target' parameter is different between the implementation and the documentation. The implementation is json_stripe_nulls (target JSON, ...), but the current documentation says json_stripe_nulls(target JSONB, ...).

Regards,
Noriyoshi Shinoda

-----Original Message-----
From: Andrew Dunstan <andrew@dunslane.net>
Sent: Thursday, March 6, 2025 10:51 PM
To: Ian Lawrence Barwick <barwick@gmail.com>; Florents Tselai <florents.tselai@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: jsonb_strip_nulls with arrays?

On 2025-03-05 We 7:10 PM, Ian Lawrence Barwick wrote:

Hi

2025年3月1日(土) 2:58 Florents Tselai <florents.tselai@gmail.com>:

Please add this to the next Commitfest at
https://urldefense.com/v3/__https://commitfest.postgresql.org/52/__;!
!NpxR!moyYqgSNJn8nLYGjlKKZDuERAzIwnFY8Ge_C5MHDuoPRpTJI9Ee0gsyF4IeybO-
t--xu2idPkLF240-sxqpR$

Added ; thanks

https://urldefense.com/v3/__https://commitfest.postgresql.org/patch/5
260/__;!!NpxR!moyYqgSNJn8nLYGjlKKZDuERAzIwnFY8Ge_C5MHDuoPRpTJI9Ee0gsy
F4IeybO-t--xu2idPkLF24zKVAj03$

I see this was committed, but there's a small formatting error in the
docs (extra comma in the parameter list); patch attached.

Thanks, pushed.

cheers

andrew

--
Andrew Dunstan
EDB: https://urldefense.com/v3/__https://www.enterprisedb.com__;!!NpxR!moyYqgSNJn8nLYGjlKKZDuERAzIwnFY8Ge_C5MHDuoPRpTJI9Ee0gsyF4IeybO-t--xu2idPkLF24-WSxzSS$

On 2025-03-06 Th 9:17 AM, Shinoda, Noriyoshi (SXD Japan FSI) wrote:

Hi,
Thanks for developing the good feature.
I've attached a small patch for the documentation of the json_strip_nulls function. The data type of the 'target' parameter is different between the implementation and the documentation. The implementation is json_stripe_nulls (target JSON, ...), but the current documentation says json_stripe_nulls(target JSONB, ...).

Argh! My glasses must have been fogged up yesterday.

pushed, thanks

cheers

andrew

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