Subject: Re: jsonb_strip_nulls with arrays?

From: Florents Tselai <[email-masked]>
Date: Tue, 17 Sep 2024 23:53:58 +0300

On Tue, Sep 17, 2024 at 5:11 PM Andrew Dunstan <[email-masked]> 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
>
>

---

From: Andrew Dunstan <[email-masked]>
Date: Wed, 8 Jan 2025 11:45:02 -0500

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.
>

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

---

From: Florents Tselai <[email-masked]>
Date: Sat, 18 Jan 2025 11:51:22 +0200

> On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <[email-masked]> 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.
> 
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/>

---

From: Florents Tselai <[email-masked]>
Date: Wed, 19 Feb 2025 23:23:51 +0200

> On 18 Jan 2025, at 11:51 AM, Florents Tselai <[email-masked]> wrote:
> 
> 
> 

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

￼￼￼

---

From: Andrew Dunstan <[email-masked]>
Date: Wed, 19 Feb 2025 17:18:58 -0500

> jsonb one remains the same.

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

cheers

andrew

---

From: Florents Tselai <[email-masked]>
Date: Fri, 28 Feb 2025 19:57:27 +0200

> On 20 Feb 2025, at 12:18 AM, Andrew Dunstan <[email-masked]> wrote:
> 
> 
> 
>>> On 18 Jan 2025, at 11:51 AM, Florents Tselai <[email-masked]> <mailto:[email-masked]> wrote:
>>> 
>>> 
>>> 
>>>> On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <[email-masked]> <mailto:[email-masked]> wrote:
>>>> 
>>>> 
>>>> 
> Please add this to the next Commitfest at https://commitfest.postgresql.org/52/
> 

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

---

From: Ian Lawrence Barwick <[email-masked]>
Date: Thu, 6 Mar 2025 09:10:04 +0900

Hi

>  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
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f97f0ce570a..e5a3c3427ec 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17345,14 +17345,14 @@ ERROR:  value too long for type character(2)
         <indexterm>
          <primary>json_strip_nulls</primary>
         </indexterm>
-        <function>json_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
+        <function>json_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type> <optional>, <parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
         <returnvalue>json</returnvalue>
        </para>
        <para role="func_signature">
         <indexterm>
          <primary>jsonb_strip_nulls</primary>
         </indexterm>
-        <function>jsonb_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
+        <function>jsonb_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type> <optional>, <parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
         <returnvalue>jsonb</returnvalue>
        </para>
        <para>

---

From: Florents Tselai <[email-masked]>
Date: Thu, 6 Mar 2025 08:30:25 +0200

> On 6 Mar 2025, at 2:10 AM, Ian Lawrence Barwick <[email-masked]> wrote:
> 
> Hi
> 
> <json_strip_nulls-doc-fix.patch>

You’re corrrect.

---

From: Andrew Dunstan <[email-masked]>
Date: Thu, 6 Mar 2025 08:51:02 -0500

> (extra comma in the parameter list); patch attached.

Thanks, pushed.

cheers

andrew

---

From: "Shinoda, Noriyoshi (SXD Japan FSI)" <[email-masked]>
Date: Thu, 6 Mar 2025 14:17:44 +0000

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 <[email-masked]> 
Sent: Thursday, March 6, 2025 10:51 PM
To: Ian Lawrence Barwick <[email-masked]>; Florents Tselai <[email-masked]>
Cc: pgsql-hackers <[email-masked]>
Subject: Re: jsonb_strip_nulls with arrays?

On 2025-03-05 We 7:10 PM, Ian Lawrence Barwick wrote:
>> 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$

---

From: Andrew Dunstan <[email-masked]>
Date: Thu, 6 Mar 2025 10:57:32 -0500

> 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 implem…

Argh! My glasses must have been fogged up yesterday.

pushed, thanks

cheers

andrew