Thread
-
More jsonpath methods: translate, split, join
Florents Tselai <florents.tselai@gmail.com> — 2026-04-13T09:56:56Z
Hello hackers, This is a follow-up to the work recently merged in bd4f879. In hindsight, I regret not pushing these through for the previous cycle, as they represent the "missing pieces" for users trying to perform data cleaning entirely within the JSONPath engine. With these we can significantly reduce the need for users to "drop out" of JSONPath into standard SQL for basic string-to-string-or-array-and-back workflows. select jsonb_path_query('" A,b,C "', '$.btrim().lower().split(",").join("-").replace("a","x").upper() starts with "X-B"'); jsonb_path_query ------------------ true (1 row) This patch series adds three new methods to the jsonpath engine: $.translate(from, to) A straightforward wrapper around the standard translate() function. It handles character-by-character mapping and is a natural companion to the recently merged .replace(). $.split(delimiter [, null_string]) A wrapper around string_to_array(). While we already have .split_part(), that only returns a single token. .split() allows for a full "explosion" of a string into a JSON array. $.join(delimiter [, null_string]) The inverse of .split(), wrapping array_to_string(). The input must be an array of strings or nulls. No implicit casting of numbers or booleans is attempted. This is consistent with how other jsonpath string methods handle type mismatches, and can always be relaxed in a follow-up if there's appetite for it. A Note on Lax vs. Strict Semantics: In this implementation, I have kept .join() behavior consistent between lax and strict modes regarding type mismatches (i.e., both will currently error on non-string elements). While lax mode traditionally handles auto-unwrapping of sequences, .join() is unique in that it operates on the array as a collective unit rather than iterating through it to produce multiple results. I've left the behavior as "strict-equivalent" for now to remain conservative, but I am open to discussion on whether lax should instead skip non-string elements or attempt to "auto-wrap" scalars into single-element arrays. The .split() and .join() methods introduce a shift in how we handle item methods. Historically, most string methods in our engine are scalar-to-scalar, with keyvalue() being the only exception so far. -
Re: More jsonpath methods: translate, split, join
Corey Huinker <corey.huinker@gmail.com> — 2026-06-16T19:53:34Z
On Mon, Apr 13, 2026 at 5:57 AM Florents Tselai <florents.tselai@gmail.com> wrote: > Hello hackers, > > This is a follow-up to the work recently merged in bd4f879. > In hindsight, I regret not pushing these through for the previous cycle, > as they represent the "missing pieces" for users trying to perform data > cleaning entirely within the JSONPath engine. > With these we can significantly reduce the need for users to "drop out" of > JSONPath > into standard SQL for basic string-to-string-or-array-and-back workflows. > > Seems this one got overlooked, so I took a look. The first patch applies, but the second needs a rebase.
-
Re: More jsonpath methods: translate, split, join
Florents Tselai <florents.tselai@gmail.com> — 2026-06-19T07:19:11Z
On Tue, Jun 16, 2026 at 10:53 PM Corey Huinker <corey.huinker@gmail.com> wrote: > On Mon, Apr 13, 2026 at 5:57 AM Florents Tselai <florents.tselai@gmail.com> > wrote: > >> Hello hackers, >> >> This is a follow-up to the work recently merged in bd4f879. >> In hindsight, I regret not pushing these through for the previous cycle, >> as they represent the "missing pieces" for users trying to perform data >> cleaning entirely within the JSONPath engine. >> With these we can significantly reduce the need for users to "drop out" >> of JSONPath >> into standard SQL for basic string-to-string-or-array-and-back workflows. >> >> > Seems this one got overlooked, so I took a look. The first patch applies, > but the second needs a rebase. > Thanks for having a look Corey, here's a consolidated v2 .
-
Re: More jsonpath methods: translate, split, join
Zsolt Parragi <zsolt.parragi@percona.com> — 2026-06-19T20:42:56Z
Hello + /* Validate target is an array */ + if (JsonbType(jb) != jbvArray) + { + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("jsonpath item method .join() can only be applied to an array")))); Other methods seem to use ERRCODE_SQL_JSON_ARRAY_NOT_FOUND + else + { + /* Recursive Tree (jbvArray) */ + for (int i = 0; i < jb->val.array.nElems; i++) Isn't this branch unreachable? -
Re: More jsonpath methods: translate, split, join
Florents Tselai <florents.tselai@gmail.com> — 2026-06-20T08:34:57Z
On Fri, Jun 19, 2026 at 11:43 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote: > Hello > > + /* Validate target is an array */ > + if (JsonbType(jb) != jbvArray) > + { > + RETURN_ERROR(ereport(ERROR, > + > (errcode(ERRCODE_INVALID_PARAMETER_VALUE), > + > errmsg("jsonpath item method .join() can only be applied > to an array")))); > > Other methods seem to use ERRCODE_SQL_JSON_ARRAY_NOT_FOUND > > + else > + { > + /* Recursive Tree (jbvArray) */ > + for (int i = 0; i < > jb->val.array.nElems; i++) > > Isn't this branch unreachable? both of your points are correct. Here's a v3 that address them . While at it, I've added some additional edge cases about these 3 methods. The semantics look good to me, and they pass without additional change in the underlying src code in jsonpath_exec.c https://commitfest.postgresql.org/patch/6673/ - Cheers, Flo tselai.com <http://tselai.com/?utm_source=email_signature&utm_medium=email&utm_campaign=Email_Signature> Schedule a chat ☕ <https://calendly.com/florents-tselai/30min> -
Re: More jsonpath methods: translate, split, join
Zsolt Parragi <zsolt.parragi@percona.com> — 2026-06-22T18:51:36Z
Hello! Thanks, I can confirm that the previous issues are fixed, I have only one minor comment for v3: +-- Empty string delimiter (splits into individual characters) +select jsonb_path_query('"abc"', '$.split("")'); + jsonb_path_query +------------------ + ["abc"] That comments seems incorrect, it doesn't split it into characters. -
Re: More jsonpath methods: translate, split, join
Corey Huinker <corey.huinker@gmail.com> — 2026-06-23T15:16:30Z
On Fri, Jun 19, 2026 at 3:19 AM Florents Tselai <florents.tselai@gmail.com> wrote: > > On Tue, Jun 16, 2026 at 10:53 PM Corey Huinker <corey.huinker@gmail.com> > wrote: > >> On Mon, Apr 13, 2026 at 5:57 AM Florents Tselai < >> florents.tselai@gmail.com> wrote: >> >>> Hello hackers, >>> >>> This is a follow-up to the work recently merged in bd4f879. >>> In hindsight, I regret not pushing these through for the previous cycle, >>> as they represent the "missing pieces" for users trying to perform data >>> cleaning entirely within the JSONPath engine. >>> With these we can significantly reduce the need for users to "drop out" >>> of JSONPath >>> into standard SQL for basic string-to-string-or-array-and-back workflows. >>> >>> >> Seems this one got overlooked, so I took a look. The first patch applies, >> but the second needs a rebase. >> > > Thanks for having a look Corey, > > here's a consolidated v2 . > My familiarity with the the internal json stuff is limited to my experience in reworking the I/O format of pg_ndistinct and pg_dependencies, plus some other misadventures in statistics export, so I apologize if some of these questions are naive. jsonpath.h: jpiStrJoin is added in the middle of the JsonPathItemType, but jpiStrSplit and jpiStrTranslate were added to the end of the enum. The comment specifically states that "the order must not be changed and new values must be added to the end" - is there some exception to this rule? If there is an exception should we modify the comment and have TAP test to show that it doesn't break pg_upgrade? jsonpath.c: In jspInitBuffer(), you add 3 new cases, but they aren't in a row, aren't organized alphabetically, or any other guiding organization that I can derive. The order of new types in the Assert() in jspGetLeftArg() is different than the order in jspGetRightArg(). Is there a reason they're different? jsonpath_exec.c: Everything here checks out. There were some patterns that initially concerned me, but they all have precedent elsewhere in the file. jsonpath_gram.y and jsonpath_scan.l: LGTM jsonb_jsonpath.out/sql +-- Empty string delimiter (splits into individual characters) +select jsonb_path_query('"abc"', '$.split("")'); + jsonb_path_query +------------------ + ["abc"] Like Zsolt, I am confused by this test. The description does not match the result. Based on the description, I would expect ["a", "b", "c"], but the output here looks like it was from a '$.split(",")' or any delimiter that isn't one of the letters in the string. the underlying function text_to_array is accessible via SQL string_to_array, and there we get: corey=# select string_to_array('foo', ''); string_to_array ----------------- {foo} (1 row) corey=# select string_to_array('foo', NULL); string_to_array ----------------- {f,o,o} (1 row) Assuming we don't find that behavior to be a bug, we should change the description of the test, or change the second parameter from '' to NULL. Moving along, the first "silent => true" query could use a comment. In general a comment above every test is nice for telling future developers what specifically is being tested. func-json.sgml: The entry for split() makes no mention of the proper behavior when the delimiter is empty Summary: The things I'm noticing seem like very random coding choices, which makes me think that they weren't choices, they're just randomness introduced by squashing and rebasing on top of bd4f879a9cdd. Could you check into that? -
Re: More jsonpath methods: translate, split, join
Florents Tselai <florents.tselai@gmail.com> — 2026-06-23T15:30:47Z
On Tue, Jun 23, 2026 at 6:16 PM Corey Huinker <corey.huinker@gmail.com> wrote: > On Fri, Jun 19, 2026 at 3:19 AM Florents Tselai <florents.tselai@gmail.com> > wrote: > >> >> On Tue, Jun 16, 2026 at 10:53 PM Corey Huinker <corey.huinker@gmail.com> >> wrote: >> >>> On Mon, Apr 13, 2026 at 5:57 AM Florents Tselai < >>> florents.tselai@gmail.com> wrote: >>> >>>> Hello hackers, >>>> >>>> This is a follow-up to the work recently merged in bd4f879. >>>> In hindsight, I regret not pushing these through for the previous cycle, >>>> as they represent the "missing pieces" for users trying to perform data >>>> cleaning entirely within the JSONPath engine. >>>> With these we can significantly reduce the need for users to "drop out" >>>> of JSONPath >>>> into standard SQL for basic string-to-string-or-array-and-back >>>> workflows. >>>> >>>> >>> Seems this one got overlooked, so I took a look. The first patch >>> applies, but the second needs a rebase. >>> >> >> Thanks for having a look Corey, >> >> here's a consolidated v2 . >> > > My familiarity with the the internal json stuff is limited to my > experience in reworking the I/O format of pg_ndistinct and pg_dependencies, > plus some other misadventures in statistics export, so I apologize if some > of these questions are naive. > > jsonpath.h: > > jpiStrJoin is added in the middle of the JsonPathItemType, but jpiStrSplit > and jpiStrTranslate were added to the end of the enum. The comment > specifically states that "the order must not be changed and new values must > be added to the end" - is there some exception to this rule? If there is an > exception should we modify the comment and have TAP test to show that it > doesn't break pg_upgrade? > > > jsonpath.c: > > In jspInitBuffer(), you add 3 new cases, but they aren't in a row, aren't > organized alphabetically, or any other guiding organization that I can > derive. > > The order of new types in the Assert() in jspGetLeftArg() is different > than the order in jspGetRightArg(). Is there a reason they're different? > > jsonpath_exec.c: > > Everything here checks out. There were some patterns that initially > concerned me, but they all have precedent elsewhere in the file. > > jsonpath_gram.y and jsonpath_scan.l: > LGTM > > jsonb_jsonpath.out/sql > > +-- Empty string delimiter (splits into individual characters) > +select jsonb_path_query('"abc"', '$.split("")'); > + jsonb_path_query > +------------------ > + ["abc"] > > Like Zsolt, I am confused by this test. The description does not match the > result. Based on the description, I would expect ["a", "b", "c"], but the > output here looks like it was from a '$.split(",")' or any delimiter that > isn't one of the letters in the string. > I have this planned, that comment is wrong and should read ; +-- Empty string delimiter (returns the original string as a single element) > > the underlying function text_to_array is accessible via SQL > string_to_array, and there we get: > > corey=# select string_to_array('foo', ''); > string_to_array > ----------------- > {foo} > (1 row) > > corey=# select string_to_array('foo', NULL); > string_to_array > ----------------- > {f,o,o} > (1 row) > > Assuming we don't find that behavior to be a bug, we should change the > description of the test, or change the second parameter from '' to NULL. > > Moving along, the first "silent => true" query could use a comment. In > general a comment above every test is nice for telling future developers > what specifically is being tested. > > func-json.sgml: > > The entry for split() makes no mention of the proper behavior when the > delimiter is empty > > Summary: > > The things I'm noticing seem like very random coding choices, which makes > me think that they weren't choices, they're just randomness introduced by > squashing and rebasing on top of bd4f879a9cdd. Could you check into that? > Thanks for your thoroughness. From a quick review I think most of your comments are spot on, and the enum ordering especially should be considered a bit critical as it could break backwards compatibility and pg_upgrade. I'll incorporate these in an upcoming v4. -
Re: More jsonpath methods: translate, split, join
Florents Tselai <florents.tselai@gmail.com> — 2026-06-24T17:17:00Z
On Tue, Jun 23, 2026 at 6:30 PM Florents Tselai <florents.tselai@gmail.com> wrote: > > > > On Tue, Jun 23, 2026 at 6:16 PM Corey Huinker <corey.huinker@gmail.com> > wrote: > >> On Fri, Jun 19, 2026 at 3:19 AM Florents Tselai < >> florents.tselai@gmail.com> wrote: >> >>> >>> On Tue, Jun 16, 2026 at 10:53 PM Corey Huinker <corey.huinker@gmail.com> >>> wrote: >>> >>>> On Mon, Apr 13, 2026 at 5:57 AM Florents Tselai < >>>> florents.tselai@gmail.com> wrote: >>>> >>>>> Hello hackers, >>>>> >>>>> This is a follow-up to the work recently merged in bd4f879. >>>>> In hindsight, I regret not pushing these through for the previous >>>>> cycle, >>>>> as they represent the "missing pieces" for users trying to perform >>>>> data cleaning entirely within the JSONPath engine. >>>>> With these we can significantly reduce the need for users to "drop >>>>> out" of JSONPath >>>>> into standard SQL for basic string-to-string-or-array-and-back >>>>> workflows. >>>>> >>>>> >>>> Seems this one got overlooked, so I took a look. The first patch >>>> applies, but the second needs a rebase. >>>> >>> >>> Thanks for having a look Corey, >>> >>> here's a consolidated v2 . >>> >> >> My familiarity with the the internal json stuff is limited to my >> experience in reworking the I/O format of pg_ndistinct and pg_dependencies, >> plus some other misadventures in statistics export, so I apologize if some >> of these questions are naive. >> >> jsonpath.h: >> >> jpiStrJoin is added in the middle of the JsonPathItemType, but >> jpiStrSplit and jpiStrTranslate were added to the end of the enum. The >> comment specifically states that "the order must not be changed and new >> values must be added to the end" - is there some exception to this rule? If >> there is an exception should we modify the comment and have TAP test to >> show that it doesn't break pg_upgrade? >> > > > >> >> jsonpath.c: >> >> In jspInitBuffer(), you add 3 new cases, but they aren't in a row, aren't >> organized alphabetically, or any other guiding organization that I can >> derive. >> >> The order of new types in the Assert() in jspGetLeftArg() is different >> than the order in jspGetRightArg(). Is there a reason they're different? >> >> jsonpath_exec.c: >> >> Everything here checks out. There were some patterns that initially >> concerned me, but they all have precedent elsewhere in the file. >> >> jsonpath_gram.y and jsonpath_scan.l: >> LGTM >> >> jsonb_jsonpath.out/sql >> >> +-- Empty string delimiter (splits into individual characters) >> +select jsonb_path_query('"abc"', '$.split("")'); >> + jsonb_path_query >> +------------------ >> + ["abc"] >> >> Like Zsolt, I am confused by this test. The description does not match >> the result. Based on the description, I would expect ["a", "b", "c"], but >> the output here looks like it was from a '$.split(",")' or any delimiter >> that isn't one of the letters in the string. >> > > I have this planned, that comment is wrong and should read ; > > +-- Empty string delimiter (returns the original string as a single > element) > > >> >> the underlying function text_to_array is accessible via SQL >> string_to_array, and there we get: >> >> corey=# select string_to_array('foo', ''); >> string_to_array >> ----------------- >> {foo} >> (1 row) >> >> corey=# select string_to_array('foo', NULL); >> string_to_array >> ----------------- >> {f,o,o} >> (1 row) >> >> Assuming we don't find that behavior to be a bug, we should change the >> description of the test, or change the second parameter from '' to NULL. >> >> Moving along, the first "silent => true" query could use a comment. In >> general a comment above every test is nice for telling future developers >> what specifically is being tested. >> >> func-json.sgml: >> >> The entry for split() makes no mention of the proper behavior when the >> delimiter is empty >> >> Summary: >> >> The things I'm noticing seem like very random coding choices, which makes >> me think that they weren't choices, they're just randomness introduced by >> squashing and rebasing on top of bd4f879a9cdd. Could you check into that? >> > > Thanks for your thoroughness. > > From a quick review I think most of your comments are spot on, and the > enum ordering especially should be considered a bit critical as it could > break backwards compatibility and pg_upgrade. > > I'll incorporate these in an upcoming v4. > Here's that v4 that addresses your points. -
Re: More jsonpath methods: translate, split, join
Corey Huinker <corey.huinker@gmail.com> — 2026-06-25T18:00:02Z
On Wed, Jun 24, 2026 at 1:17 PM Florents Tselai <florents.tselai@gmail.com> wrote: > > > On Tue, Jun 23, 2026 at 6:30 PM Florents Tselai <florents.tselai@gmail.com> > wrote: > >> >> >> >> On Tue, Jun 23, 2026 at 6:16 PM Corey Huinker <corey.huinker@gmail.com> >> wrote: >> >>> On Fri, Jun 19, 2026 at 3:19 AM Florents Tselai < >>> florents.tselai@gmail.com> wrote: >>> >>>> >>>> On Tue, Jun 16, 2026 at 10:53 PM Corey Huinker <corey.huinker@gmail.com> >>>> wrote: >>>> >>>>> On Mon, Apr 13, 2026 at 5:57 AM Florents Tselai < >>>>> florents.tselai@gmail.com> wrote: >>>>> >>>>>> Hello hackers, >>>>>> >>>>>> This is a follow-up to the work recently merged in bd4f879. >>>>>> In hindsight, I regret not pushing these through for the previous >>>>>> cycle, >>>>>> as they represent the "missing pieces" for users trying to perform >>>>>> data cleaning entirely within the JSONPath engine. >>>>>> With these we can significantly reduce the need for users to "drop >>>>>> out" of JSONPath >>>>>> into standard SQL for basic string-to-string-or-array-and-back >>>>>> workflows. >>>>>> >>>>>> >>>>> Seems this one got overlooked, so I took a look. The first patch >>>>> applies, but the second needs a rebase. >>>>> >>>> >>>> Thanks for having a look Corey, >>>> >>>> here's a consolidated v2 . >>>> >>> >>> My familiarity with the the internal json stuff is limited to my >>> experience in reworking the I/O format of pg_ndistinct and pg_dependencies, >>> plus some other misadventures in statistics export, so I apologize if some >>> of these questions are naive. >>> >>> jsonpath.h: >>> >>> jpiStrJoin is added in the middle of the JsonPathItemType, but >>> jpiStrSplit and jpiStrTranslate were added to the end of the enum. The >>> comment specifically states that "the order must not be changed and new >>> values must be added to the end" - is there some exception to this rule? If >>> there is an exception should we modify the comment and have TAP test to >>> show that it doesn't break pg_upgrade? >>> >> >> >> >>> >>> jsonpath.c: >>> >>> In jspInitBuffer(), you add 3 new cases, but they aren't in a row, >>> aren't organized alphabetically, or any other guiding organization that I >>> can derive. >>> >>> The order of new types in the Assert() in jspGetLeftArg() is different >>> than the order in jspGetRightArg(). Is there a reason they're different? >>> >>> jsonpath_exec.c: >>> >>> Everything here checks out. There were some patterns that initially >>> concerned me, but they all have precedent elsewhere in the file. >>> >>> jsonpath_gram.y and jsonpath_scan.l: >>> LGTM >>> >>> jsonb_jsonpath.out/sql >>> >>> +-- Empty string delimiter (splits into individual characters) >>> +select jsonb_path_query('"abc"', '$.split("")'); >>> + jsonb_path_query >>> +------------------ >>> + ["abc"] >>> >>> Like Zsolt, I am confused by this test. The description does not match >>> the result. Based on the description, I would expect ["a", "b", "c"], but >>> the output here looks like it was from a '$.split(",")' or any delimiter >>> that isn't one of the letters in the string. >>> >> >> I have this planned, that comment is wrong and should read ; >> >> +-- Empty string delimiter (returns the original string as a single >> element) >> >> >>> >>> the underlying function text_to_array is accessible via SQL >>> string_to_array, and there we get: >>> >>> corey=# select string_to_array('foo', ''); >>> string_to_array >>> ----------------- >>> {foo} >>> (1 row) >>> >>> corey=# select string_to_array('foo', NULL); >>> string_to_array >>> ----------------- >>> {f,o,o} >>> (1 row) >>> >>> Assuming we don't find that behavior to be a bug, we should change the >>> description of the test, or change the second parameter from '' to NULL. >>> >>> Moving along, the first "silent => true" query could use a comment. In >>> general a comment above every test is nice for telling future developers >>> what specifically is being tested. >>> >>> func-json.sgml: >>> >>> The entry for split() makes no mention of the proper behavior when the >>> delimiter is empty >>> >>> Summary: >>> >>> The things I'm noticing seem like very random coding choices, which >>> makes me think that they weren't choices, they're just randomness >>> introduced by squashing and rebasing on top of bd4f879a9cdd. Could you >>> check into that? >>> >> >> Thanks for your thoroughness. >> >> From a quick review I think most of your comments are spot on, and the >> enum ordering especially should be considered a bit critical as it could >> break backwards compatibility and pg_upgrade. >> >> I'll incorporate these in an upcoming v4. >> > > Here's that v4 that addresses your points. > I think you may have posted the wrong file. The v4 patch only seems to address my concern about the test case comment on split. Of particular concern is the change to the enum in jsonpath.h, which will break binary upgrades. diff ~/Downloads/v4-0001-Add-more-jsonpath-string-methods-.translate-.spli.patch ~/Downloads/v3-0001-Add-more-jsonpath-string-methods-.translate-.spli.patch 1c1 < From 2a85e7b8833f0d683d988ceb5a4ddd87a4042c86 Mon Sep 17 00:00:00 2001 --- > From a3d0abf3f43d71128c0fef031b074438ca0c5394 Mon Sep 17 00:00:00 2001 3,4c3,4 < Date: Tue, 23 Jun 2026 12:51:26 +0300 < Subject: [PATCH v4] Add more jsonpath string methods (.translate, .split, --- > Date: Sat, 20 Jun 2026 10:36:56 +0300 > Subject: [PATCH v3] Add more jsonpath string methods (.translate, .split, 610c610 < index 81efebc3d0f..26a8325f8f6 100644 --- > index 81efebc3d0f..5430fc083fa 100644 707c707 < +-- Empty string delimiter (returns the original string as a single element) --- > +-- Empty string delimiter (splits into individual characters) 974c974 < index c1f4ab5422e..3c4d775ee44 100644 --- > index c1f4ab5422e..a492e22f8b1 100644 1010c1010 < +-- Empty string delimiter (returns the original string as a single element) --- > +-- Empty string delimiter (splits into individual characters) -
Re: More jsonpath methods: translate, split, join
Florents Tselai <florents.tselai@gmail.com> — 2026-06-26T18:28:42Z
On Thu, Jun 25, 2026 at 9:00 PM Corey Huinker <corey.huinker@gmail.com> wrote: > On Wed, Jun 24, 2026 at 1:17 PM Florents Tselai <florents.tselai@gmail.com> > wrote: > >> >> >> On Tue, Jun 23, 2026 at 6:30 PM Florents Tselai < >> florents.tselai@gmail.com> wrote: >> >>> >>> >>> >>> On Tue, Jun 23, 2026 at 6:16 PM Corey Huinker <corey.huinker@gmail.com> >>> wrote: >>> >>>> On Fri, Jun 19, 2026 at 3:19 AM Florents Tselai < >>>> florents.tselai@gmail.com> wrote: >>>> >>>>> >>>>> On Tue, Jun 16, 2026 at 10:53 PM Corey Huinker < >>>>> corey.huinker@gmail.com> wrote: >>>>> >>>>>> On Mon, Apr 13, 2026 at 5:57 AM Florents Tselai < >>>>>> florents.tselai@gmail.com> wrote: >>>>>> >>>>>>> Hello hackers, >>>>>>> >>>>>>> This is a follow-up to the work recently merged in bd4f879. >>>>>>> In hindsight, I regret not pushing these through for the previous >>>>>>> cycle, >>>>>>> as they represent the "missing pieces" for users trying to perform >>>>>>> data cleaning entirely within the JSONPath engine. >>>>>>> With these we can significantly reduce the need for users to "drop >>>>>>> out" of JSONPath >>>>>>> into standard SQL for basic string-to-string-or-array-and-back >>>>>>> workflows. >>>>>>> >>>>>>> >>>>>> Seems this one got overlooked, so I took a look. The first patch >>>>>> applies, but the second needs a rebase. >>>>>> >>>>> >>>>> Thanks for having a look Corey, >>>>> >>>>> here's a consolidated v2 . >>>>> >>>> >>>> My familiarity with the the internal json stuff is limited to my >>>> experience in reworking the I/O format of pg_ndistinct and pg_dependencies, >>>> plus some other misadventures in statistics export, so I apologize if some >>>> of these questions are naive. >>>> >>>> jsonpath.h: >>>> >>>> jpiStrJoin is added in the middle of the JsonPathItemType, but >>>> jpiStrSplit and jpiStrTranslate were added to the end of the enum. The >>>> comment specifically states that "the order must not be changed and new >>>> values must be added to the end" - is there some exception to this rule? If >>>> there is an exception should we modify the comment and have TAP test to >>>> show that it doesn't break pg_upgrade? >>>> >>> >>> >>> >>>> >>>> jsonpath.c: >>>> >>>> In jspInitBuffer(), you add 3 new cases, but they aren't in a row, >>>> aren't organized alphabetically, or any other guiding organization that I >>>> can derive. >>>> >>>> The order of new types in the Assert() in jspGetLeftArg() is different >>>> than the order in jspGetRightArg(). Is there a reason they're different? >>>> >>>> jsonpath_exec.c: >>>> >>>> Everything here checks out. There were some patterns that initially >>>> concerned me, but they all have precedent elsewhere in the file. >>>> >>>> jsonpath_gram.y and jsonpath_scan.l: >>>> LGTM >>>> >>>> jsonb_jsonpath.out/sql >>>> >>>> +-- Empty string delimiter (splits into individual characters) >>>> +select jsonb_path_query('"abc"', '$.split("")'); >>>> + jsonb_path_query >>>> +------------------ >>>> + ["abc"] >>>> >>>> Like Zsolt, I am confused by this test. The description does not match >>>> the result. Based on the description, I would expect ["a", "b", "c"], but >>>> the output here looks like it was from a '$.split(",")' or any delimiter >>>> that isn't one of the letters in the string. >>>> >>> >>> I have this planned, that comment is wrong and should read ; >>> >>> +-- Empty string delimiter (returns the original string as a single >>> element) >>> >>> >>>> >>>> the underlying function text_to_array is accessible via SQL >>>> string_to_array, and there we get: >>>> >>>> corey=# select string_to_array('foo', ''); >>>> string_to_array >>>> ----------------- >>>> {foo} >>>> (1 row) >>>> >>>> corey=# select string_to_array('foo', NULL); >>>> string_to_array >>>> ----------------- >>>> {f,o,o} >>>> (1 row) >>>> >>>> Assuming we don't find that behavior to be a bug, we should change the >>>> description of the test, or change the second parameter from '' to NULL. >>>> >>>> Moving along, the first "silent => true" query could use a comment. In >>>> general a comment above every test is nice for telling future developers >>>> what specifically is being tested. >>>> >>>> func-json.sgml: >>>> >>>> The entry for split() makes no mention of the proper behavior when the >>>> delimiter is empty >>>> >>>> Summary: >>>> >>>> The things I'm noticing seem like very random coding choices, which >>>> makes me think that they weren't choices, they're just randomness >>>> introduced by squashing and rebasing on top of bd4f879a9cdd. Could you >>>> check into that? >>>> >>> >>> Thanks for your thoroughness. >>> >>> From a quick review I think most of your comments are spot on, and the >>> enum ordering especially should be considered a bit critical as it could >>> break backwards compatibility and pg_upgrade. >>> >>> I'll incorporate these in an upcoming v4. >>> >> >> Here's that v4 that addresses your points. >> > > I think you may have posted the wrong file. The v4 patch only seems to > address my concern about the test case comment on split. Of particular > concern is the change to the enum in jsonpath.h, which will break binary > upgrades. > Argh, you're right . Apologies for the noise. -
Re: More jsonpath methods: translate, split, join
Zsolt Parragi <zsolt.parragi@percona.com> — 2026-06-26T20:19:12Z
+ binary_jbv.type = jbvBinary; + binary_jbv.val.binary.data = &jb_result->root; + binary_jbv.val.binary.len = VARSIZE(jb_result); Shouldn't that use VARSIZE_ANY_EXHDR or VARSIZE(jb_result) - VARHDRSZ?
-
Re: More jsonpath methods: translate, split, join
Florents Tselai <florents.tselai@gmail.com> — 2026-06-27T05:40:51Z
On Fri, Jun 26, 2026 at 11:19 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote: > + binary_jbv.type = jbvBinary; > + binary_jbv.val.binary.data = > &jb_result->root; > + binary_jbv.val.binary.len = > VARSIZE(jb_result); > > Shouldn't that use VARSIZE_ANY_EXHDR or VARSIZE(jb_result) - VARHDRSZ? > I think you're right; let's see if Corey comes back with anything else before I send out a new version.
-
Re: More jsonpath methods: translate, split, join
Florents Tselai <florents.tselai@gmail.com> — 2026-07-04T08:59:09Z
On Sat, Jun 27, 2026 at 8:40 AM Florents Tselai <florents.tselai@gmail.com> wrote: > > > On Fri, Jun 26, 2026 at 11:19 PM Zsolt Parragi <zsolt.parragi@percona.com> > wrote: > >> + binary_jbv.type = jbvBinary; >> + binary_jbv.val.binary.data = >> &jb_result->root; >> + binary_jbv.val.binary.len = >> VARSIZE(jb_result); >> >> Shouldn't that use VARSIZE_ANY_EXHDR or VARSIZE(jb_result) - VARHDRSZ? >> > > I think you're right; let's see if Corey comes back with anything else > before I send out a new version. > Here's a v5 that fixes the VARSIZE_ANY_EXHDR Zsolt mentioned above I've also rearranged the test cases in jsonpath.sql and jsonb_jsonpath.sql Also added parser error test cases that were missing for translate() in jsonpath.sql