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

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(at)dunslane(dot)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

> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>

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(at)dunslane(dot)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.
> cheers
>
>
>
> andrew
>
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com <https://www.enterprisedb.com/>

> On 18 Jan 2025, at 11:51 AM, Florents Tselai <florents(dot)tselai(at)gmail(dot)com> wrote:
>
>
>
>> On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew(at)dunslane(dot)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.


>>
>> cheers
>>
>>
>>
>> andrew
>>
>>
>>
>> --
>> Andrew Dunstan
>> EDB: https://www.enterprisedb.com <https://www.enterprisedb.com/>

On 2025-02-19 We 4:23 PM, Florents Tselai wrote:
>
>
>> On 18 Jan 2025, at 11:51 AM, Florents Tselai
>> <florents(dot)tselai(at)gmail(dot)com> wrote:
>>
>>
>>
>>> On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew(at)dunslane(dot)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(at)dunslane(dot)net> wrote:
>
>
>
> On 2025-02-19 We 4:23 PM, Florents Tselai wrote:
>>
>>
>>> On 18 Jan 2025, at 11:51 AM, Florents Tselai <florents(dot)tselai(at)gmail(dot)com> <mailto:florents(dot)tselai(at)gmail(dot)com> wrote:
>>>
>>>
>>>
>>>> On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> <mailto:andrew(at)dunslane(dot)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/
>
> cheers
>
>
>
> andrew
>
>
>
>>
>>
>>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com <https://www.enterprisedb.com/>

Hi

2025年3月1日(土) 2:58 Florents Tselai <florents(dot)tselai(at)gmail(dot)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(at)gmail(dot)com> wrote:
>
> Hi
>
> 2025年3月1日(土) 2:58 Florents Tselai <florents(dot)tselai(at)gmail(dot)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(dot)tselai(at)gmail(dot)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(at)dunslane(dot)net>
Sent: Thursday, March 6, 2025 10:51 PM
To: Ian Lawrence Barwick <barwick(at)gmail(dot)com>; Florents Tselai <florents(dot)tselai(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)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(dot)tselai(at)gmail(dot)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