Thread
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Allow json{b}_strip_nulls to remove null array elements
- 4603903d294b 18.0 landed
-
jsonb_strip_nulls with arrays?
Florents Tselai <florents.tselai@gmail.com> — 2024-09-17T09:26:36Z
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
-
Re: jsonb_strip_nulls with arrays?
Andrew Dunstan <andrew@dunslane.net> — 2024-09-17T14:11:47Z
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 -
Re: jsonb_strip_nulls with arrays?
Florents Tselai <florents.tselai@gmail.com> — 2024-09-17T20:53:58Z
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 > cheers > > > andrew > > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com > > -
Re: jsonb_strip_nulls with arrays?
Andrew Dunstan <andrew@dunslane.net> — 2025-01-08T16:45:02Z
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 -
Re: jsonb_strip_nulls with arrays?
Florents Tselai <florents.tselai@gmail.com> — 2025-01-18T09:51:22Z
> 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. > cheers > > > > andrew > > > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com <https://www.enterprisedb.com/> -
Re: jsonb_strip_nulls with arrays?
Florents Tselai <florents.tselai@gmail.com> — 2025-02-19T21:23:51Z
> 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.  >> >> cheers >> >> >> >> andrew >> >> >> >> -- >> Andrew Dunstan >> EDB: https://www.enterprisedb.com <https://www.enterprisedb.com/> -
Re: jsonb_strip_nulls with arrays?
Andrew Dunstan <andrew@dunslane.net> — 2025-02-19T22:18:58Z
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 -
Re: jsonb_strip_nulls with arrays?
Florents Tselai <florents.tselai@gmail.com> — 2025-02-28T17:57:27Z
> 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/ > > cheers > > > > andrew > > > >> >> >> > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com <https://www.enterprisedb.com/> -
Re: jsonb_strip_nulls with arrays?
Ian Lawrence Barwick <barwick@gmail.com> — 2025-03-06T00:10:04Z
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
-
Re: jsonb_strip_nulls with arrays?
Florents Tselai <florents.tselai@gmail.com> — 2025-03-06T06:30:25Z
> 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.
-
Re: jsonb_strip_nulls with arrays?
Andrew Dunstan <andrew@dunslane.net> — 2025-03-06T13:51:02Z
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
-
RE: jsonb_strip_nulls with arrays?
Shinoda, Noriyoshi <noriyoshi.shinoda@hpe.com> — 2025-03-06T14:17:44Z
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$
-
Re: jsonb_strip_nulls with arrays?
Andrew Dunstan <andrew@dunslane.net> — 2025-03-06T15:57:32Z
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