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) →jsonbDeletes 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
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 18 Jan 2025, at 11:51 AM, Florents Tselai <florents.tselai@gmail.com> wrote:Thanks for having a Look, Andrew;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.
if there aren’t any other objections, I’ll come back with a json implementation too.
Attachment
On 18 Jan 2025, at 11:51 AM, Florents Tselai <florents.tselai@gmail.com> wrote:Thanks for having a Look, Andrew;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.
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> wrote:Thanks for having a Look, Andrew;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.
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/
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
Attachment
> 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' parameteris 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$
Attachment
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' parameteris 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