Обсуждение: jsonpath syntax extensions
Hi, hackers! Attached patches implement several useful jsonpath syntax extensions. I already published them two years ago in the original SQL/JSON thread, but then after creation of separate threads for SQL/JSON functions and JSON_TABLE I forgot about them. A brief description of the patches: 1. Introduced new jsonpath modifier 'pg' which is used for enabling PostgreSQL-specific extensions. This feature was already proposed in the discussion of jsonpath's like_regex implementation. 2. Added support for raw jbvObject and jbvArray JsonbValues inside jsonpath engine. Now, jsonpath can operate with JSON arrays and objects only in jbvBinary form. But with introduction of array and object constructors in patches #4 and #5 raw in-memory jsonb containers can appear in jsonpath engine. In some places we can iterate through jbvArrays, in others we need to encode jbvArrays and jbvObjects into jbvBinay. 3. SQL/JSON sequence construction syntax. A simple comma-separated list can be used to concatenate single values or sequences into a single resulting sequence. SELECT jsonb_path_query('[1, 2, 3]', 'pg $[*], 4, 2 + 3'); jsonb_path_query------------------ 1 2 3 4 5 SELECT jsonb_path_query('{ "a": [1, 2, 3], "b": [4, 5] }', 'pg ($.a[*], $.b[*]) ? (@ % 2 == 1)'); jsonb_path_query------------------ 1 3 5 Patches #4-#6 implement ECMAScript-like syntax constructors and accessors: 4. Array construction syntax. This can also be considered as enclosing a sequence constructor into brackets.SELECT jsonb_path_query('[1, 2, 3]', 'pg [$[*], 4, 2 + 3]'); jsonb_path_query------------------ [1, 2, 3, 4, 5] Having this feature, jsonb_path_query_array() becomes somewhat redundant. 5. Object construction syntax. It is useful for constructing derived objects from the interesting parts of the original object. (But this is not sufficient to "project" each object in array, item method like '.map()' is needed here.) SELECT jsonb_path_query('{"b": 2}', 'pg { a : 1, b : $.b, "x y" : $.b + 3 }'); jsonb_path_query------------------------------- { "a" : 1, "b": 3, "x y": 5 } Fields with empty values are simply skipped regardless of lax/strict mode: SELECT jsonb_path_query('{"a": 1}', 'pg { b : $.b, a : $.a ? (@ > 1) }'); jsonb_path_query ------------------ {} 6. Object subscription syntax. This gives us ability to specify what key to extract on runtime. The syntax is the same as ordinary array subscription syntax. -- non-existent $.x is simply skipped in lax modeSELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$.a, "x", "a"]'); jsonb_path_query------------------ "c" "b" SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$fld]', '{"fld": "b"}'); jsonb_path_query ------------------ "c" -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Вложения
- v1-0001-Add-jsonpath-pg-modifier-for-enabling-extensions.patch
- v1-0002-Add-raw-jbvArray-and-jbvObject-support-to-jsonpat.patch
- v1-0003-Add-jsonpath-sequence-constructors.patch
- v1-0004-Add-jsonpath-array-constructors.patch
- v1-0005-Add-jsonpath-object-constructors.patch
- v1-0006-Add-jsonpath-object-subscripting.patch
Hi Nikita, On 2/27/20 10:57 AM, Nikita Glukhov wrote: > > Attached patches implement several useful jsonpath syntax extensions. > I already published them two years ago in the original SQL/JSON thread, > but then after creation of separate threads for SQL/JSON functions and > JSON_TABLE I forgot about them. Are these improvements targeted at PG13 or PG14? This seems to be a pretty big change for the last CF of PG13. I know these have been submitted before but that was a few years ago so I think they count as new. Regards, -- -David david@pgmasters.net
On 04.03.2020 19:13, David Steele wrote:
Hi Nikita,
On 2/27/20 10:57 AM, Nikita Glukhov wrote:
Attached patches implement several useful jsonpath syntax extensions.
I already published them two years ago in the original SQL/JSON thread,
but then after creation of separate threads for SQL/JSON functions and
JSON_TABLE I forgot about them.
Are these improvements targeted at PG13 or PG14? This seems to be a pretty big change for the last CF of PG13. I know these have been submitted before but that was a few years ago so I think they count as new.
I believe that some of these improvements can get into PG13. There is no need to review all of them, we can choose only the simplest ones. Most of code changes in #3-#5 consist of straightforward boilerplate jsonpath I/O code, and only changes in jsonpath_exec.c are interesting. Only the patch #1 is mandatory, patches #3-#6 depend on it. The patch #2 is not necessary, if jbvArray and jbvObject values would be wrapped into jbvBinary by JsonbValueToJsonb() call in #4 and #5. Patch #4 is the simplest one (only 20 new lines of code in jsonpath_exec.c). Patch #6 is the most complex one, and it affects only jsonpath execution.
This patch seems to be getting ignored. Like David I'm a bit puzzled because it doesn't seem like an especially obscure or difficult patch to review. Yet it's been multiple years without even a superficial "does it meet the coding requirements" review let alone a design review. Can we get a volunteer to at least give it a quick once-over? I don't think it's ideal to be doing this in the last CF but neither is it very appetizing to just shift it to the next CF without a review after two years... On Thu, 27 Feb 2020 at 10:58, Nikita Glukhov <n.gluhov@postgrespro.ru> wrote: > > Hi, hackers! > > Attached patches implement several useful jsonpath syntax extensions. > I already published them two years ago in the original SQL/JSON thread, > but then after creation of separate threads for SQL/JSON functions and > JSON_TABLE I forgot about them. > > A brief description of the patches: > > 1. Introduced new jsonpath modifier 'pg' which is used for enabling > PostgreSQL-specific extensions. This feature was already proposed in the > discussion of jsonpath's like_regex implementation. > > 2. Added support for raw jbvObject and jbvArray JsonbValues inside jsonpath > engine. Now, jsonpath can operate with JSON arrays and objects only in > jbvBinary form. But with introduction of array and object constructors in > patches #4 and #5 raw in-memory jsonb containers can appear in jsonpath engine. > In some places we can iterate through jbvArrays, in others we need to encode > jbvArrays and jbvObjects into jbvBinay. > > 3. SQL/JSON sequence construction syntax. A simple comma-separated list can be > used to concatenate single values or sequences into a single resulting sequence. > > SELECT jsonb_path_query('[1, 2, 3]', 'pg $[*], 4, 2 + 3'); > jsonb_path_query > ------------------ > 1 > 2 > 3 > 4 > 5 > > SELECT jsonb_path_query('{ "a": [1, 2, 3], "b": [4, 5] }', > 'pg ($.a[*], $.b[*]) ? (@ % 2 == 1)'); > jsonb_path_query > ------------------ > 1 > 3 > 5 > > > Patches #4-#6 implement ECMAScript-like syntax constructors and accessors: > > 4. Array construction syntax. > This can also be considered as enclosing a sequence constructor into brackets. > > SELECT jsonb_path_query('[1, 2, 3]', 'pg [$[*], 4, 2 + 3]'); > jsonb_path_query > ------------------ > [1, 2, 3, 4, 5] > > Having this feature, jsonb_path_query_array() becomes somewhat redundant. > > > 5. Object construction syntax. It is useful for constructing derived objects > from the interesting parts of the original object. (But this is not sufficient > to "project" each object in array, item method like '.map()' is needed here.) > > SELECT jsonb_path_query('{"b": 2}', 'pg { a : 1, b : $.b, "x y" : $.b + 3 }'); > jsonb_path_query > ------------------------------- > { "a" : 1, "b": 3, "x y": 5 } > > Fields with empty values are simply skipped regardless of lax/strict mode: > > SELECT jsonb_path_query('{"a": 1}', 'pg { b : $.b, a : $.a ? (@ > 1) }'); > jsonb_path_query > ------------------ > {} > > > 6. Object subscription syntax. This gives us ability to specify what key to > extract on runtime. The syntax is the same as ordinary array subscription > syntax. > > -- non-existent $.x is simply skipped in lax mode > SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$.a, "x", "a"]'); > jsonb_path_query > ------------------ > "c" > "b" > > SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$fld]', '{"fld": "b"}'); > jsonb_path_query > ------------------ > "c" > > -- > Nikita Glukhov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company -- greg
Hm. Actually... These changes were split off from the JSON_TABLE patches? Are they still separate or have they been merged into those other patches since? I see the JSON_TABLE thread is getting more comments do those reviews include these patches? On Mon, 21 Mar 2022 at 16:09, Greg Stark <stark@mit.edu> wrote: > > This patch seems to be getting ignored. Like David I'm a bit puzzled > because it doesn't seem like an especially obscure or difficult patch > to review. Yet it's been multiple years without even a superficial > "does it meet the coding requirements" review let alone a design > review. > > Can we get a volunteer to at least give it a quick once-over? I don't > think it's ideal to be doing this in the last CF but neither is it > very appetizing to just shift it to the next CF without a review after > two years... > > On Thu, 27 Feb 2020 at 10:58, Nikita Glukhov <n.gluhov@postgrespro.ru> wrote: > > > > Hi, hackers! > > > > Attached patches implement several useful jsonpath syntax extensions. > > I already published them two years ago in the original SQL/JSON thread, > > but then after creation of separate threads for SQL/JSON functions and > > JSON_TABLE I forgot about them. > > > > A brief description of the patches: > > > > 1. Introduced new jsonpath modifier 'pg' which is used for enabling > > PostgreSQL-specific extensions. This feature was already proposed in the > > discussion of jsonpath's like_regex implementation. > > > > 2. Added support for raw jbvObject and jbvArray JsonbValues inside jsonpath > > engine. Now, jsonpath can operate with JSON arrays and objects only in > > jbvBinary form. But with introduction of array and object constructors in > > patches #4 and #5 raw in-memory jsonb containers can appear in jsonpath engine. > > In some places we can iterate through jbvArrays, in others we need to encode > > jbvArrays and jbvObjects into jbvBinay. > > > > 3. SQL/JSON sequence construction syntax. A simple comma-separated list can be > > used to concatenate single values or sequences into a single resulting sequence. > > > > SELECT jsonb_path_query('[1, 2, 3]', 'pg $[*], 4, 2 + 3'); > > jsonb_path_query > > ------------------ > > 1 > > 2 > > 3 > > 4 > > 5 > > > > SELECT jsonb_path_query('{ "a": [1, 2, 3], "b": [4, 5] }', > > 'pg ($.a[*], $.b[*]) ? (@ % 2 == 1)'); > > jsonb_path_query > > ------------------ > > 1 > > 3 > > 5 > > > > > > Patches #4-#6 implement ECMAScript-like syntax constructors and accessors: > > > > 4. Array construction syntax. > > This can also be considered as enclosing a sequence constructor into brackets. > > > > SELECT jsonb_path_query('[1, 2, 3]', 'pg [$[*], 4, 2 + 3]'); > > jsonb_path_query > > ------------------ > > [1, 2, 3, 4, 5] > > > > Having this feature, jsonb_path_query_array() becomes somewhat redundant. > > > > > > 5. Object construction syntax. It is useful for constructing derived objects > > from the interesting parts of the original object. (But this is not sufficient > > to "project" each object in array, item method like '.map()' is needed here.) > > > > SELECT jsonb_path_query('{"b": 2}', 'pg { a : 1, b : $.b, "x y" : $.b + 3 }'); > > jsonb_path_query > > ------------------------------- > > { "a" : 1, "b": 3, "x y": 5 } > > > > Fields with empty values are simply skipped regardless of lax/strict mode: > > > > SELECT jsonb_path_query('{"a": 1}', 'pg { b : $.b, a : $.a ? (@ > 1) }'); > > jsonb_path_query > > ------------------ > > {} > > > > > > 6. Object subscription syntax. This gives us ability to specify what key to > > extract on runtime. The syntax is the same as ordinary array subscription > > syntax. > > > > -- non-existent $.x is simply skipped in lax mode > > SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$.a, "x", "a"]'); > > jsonb_path_query > > ------------------ > > "c" > > "b" > > > > SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$fld]', '{"fld": "b"}'); > > jsonb_path_query > > ------------------ > > "c" > > > > -- > > Nikita Glukhov > > Postgres Professional: http://www.postgrespro.com > > The Russian Postgres Company > > > > -- > greg -- greg
Op 21-03-2022 om 21:13 schreef Greg Stark: > Hm. Actually... These changes were split off from the JSON_TABLE > patches? Are they still separate or have they been merged into those > other patches since? I see the JSON_TABLE thread is getting more > comments do those reviews include these patches? > They are separate. FWIW, I've done all my JSON_PATH testing both without and with these syntax extensions (but I've done no code review.) I like these extensions but as you say -- there seems to be not much interest. Erik > On Mon, 21 Mar 2022 at 16:09, Greg Stark <stark@mit.edu> wrote: >> >> This patch seems to be getting ignored. Like David I'm a bit puzzled >> because it doesn't seem like an especially obscure or difficult patch >> to review. Yet it's been multiple years without even a superficial >> "does it meet the coding requirements" review let alone a design >> review. >> >> Can we get a volunteer to at least give it a quick once-over? I don't >> think it's ideal to be doing this in the last CF but neither is it >> very appetizing to just shift it to the next CF without a review after >> two years... >> >> On Thu, 27 Feb 2020 at 10:58, Nikita Glukhov <n.gluhov@postgrespro.ru> wrote: >>> >>> Hi, hackers! >>> >>> Attached patches implement several useful jsonpath syntax extensions. >>> I already published them two years ago in the original SQL/JSON thread, >>> but then after creation of separate threads for SQL/JSON functions and >>> JSON_TABLE I forgot about them. >>> >>> A brief description of the patches: >>> >>> 1. Introduced new jsonpath modifier 'pg' which is used for enabling >>> PostgreSQL-specific extensions. This feature was already proposed in the >>> discussion of jsonpath's like_regex implementation. >>> >>> 2. Added support for raw jbvObject and jbvArray JsonbValues inside jsonpath >>> engine. Now, jsonpath can operate with JSON arrays and objects only in >>> jbvBinary form. But with introduction of array and object constructors in >>> patches #4 and #5 raw in-memory jsonb containers can appear in jsonpath engine. >>> In some places we can iterate through jbvArrays, in others we need to encode >>> jbvArrays and jbvObjects into jbvBinay. >>> >>> 3. SQL/JSON sequence construction syntax. A simple comma-separated list can be >>> used to concatenate single values or sequences into a single resulting sequence. >>> >>> SELECT jsonb_path_query('[1, 2, 3]', 'pg $[*], 4, 2 + 3'); >>> jsonb_path_query >>> ------------------ >>> 1 >>> 2 >>> 3 >>> 4 >>> 5 >>> >>> SELECT jsonb_path_query('{ "a": [1, 2, 3], "b": [4, 5] }', >>> 'pg ($.a[*], $.b[*]) ? (@ % 2 == 1)'); >>> jsonb_path_query >>> ------------------ >>> 1 >>> 3 >>> 5 >>> >>> >>> Patches #4-#6 implement ECMAScript-like syntax constructors and accessors: >>> >>> 4. Array construction syntax. >>> This can also be considered as enclosing a sequence constructor into brackets. >>> >>> SELECT jsonb_path_query('[1, 2, 3]', 'pg [$[*], 4, 2 + 3]'); >>> jsonb_path_query >>> ------------------ >>> [1, 2, 3, 4, 5] >>> >>> Having this feature, jsonb_path_query_array() becomes somewhat redundant. >>> >>> >>> 5. Object construction syntax. It is useful for constructing derived objects >>> from the interesting parts of the original object. (But this is not sufficient >>> to "project" each object in array, item method like '.map()' is needed here.) >>> >>> SELECT jsonb_path_query('{"b": 2}', 'pg { a : 1, b : $.b, "x y" : $.b + 3 }'); >>> jsonb_path_query >>> ------------------------------- >>> { "a" : 1, "b": 3, "x y": 5 } >>> >>> Fields with empty values are simply skipped regardless of lax/strict mode: >>> >>> SELECT jsonb_path_query('{"a": 1}', 'pg { b : $.b, a : $.a ? (@ > 1) }'); >>> jsonb_path_query >>> ------------------ >>> {} >>> >>> >>> 6. Object subscription syntax. This gives us ability to specify what key to >>> extract on runtime. The syntax is the same as ordinary array subscription >>> syntax. >>> >>> -- non-existent $.x is simply skipped in lax mode >>> SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$.a, "x", "a"]'); >>> jsonb_path_query >>> ------------------ >>> "c" >>> "b" >>> >>> SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$fld]', '{"fld": "b"}'); >>> jsonb_path_query >>> ------------------ >>> "c" >>> >>> -- >>> Nikita Glukhov >>> Postgres Professional: http://www.postgrespro.com >>> The Russian Postgres Company >> >> >> >> -- >> greg > > >
Hi, On 2022-03-21 21:09, Greg Stark wrote: > This patch seems to be getting ignored. Like David I'm a bit puzzled > because it doesn't seem like an especially obscure or difficult patch > to review. Yet it's been multiple years without even a superficial > "does it meet the coding requirements" review let alone a design > review. > > Can we get a volunteer to at least give it a quick once-over? I don't > think it's ideal to be doing this in the last CF but neither is it > very appetizing to just shift it to the next CF without a review after > two years... I have just one suggestion: probably the object subscription syntax, as in '$["keyA","keyB"]', should not require 'pg ' prefix, as it is a part of the original JSONPath (https://goessner.net/articles/JsonPath/) and is supported in multiple other implementations. >> 6. Object subscription syntax. This gives us ability to specify what >> key to >> extract on runtime. The syntax is the same as ordinary array >> subscription >> syntax. >> >> -- non-existent $.x is simply skipped in lax mode >> SELECT jsonb_path_query('{"a": "b", "b": "c"}', 'pg $[$.a, "x", >> "a"]'); >> jsonb_path_query >> ------------------ >> "c" >> "b" The variable reference support ('pg $[$.a]') probably _is_ a PostgreSQL-specific extension, though. -- Ph.
Well I still think this would be a good candidate to get reviewed. But it currently needs a rebase and it's the last day of the CF so I guess it'll get moved forward again. I don't think "returned with feedback" is helpful given there's been basically no feedback :(
Hi,
Ok, we'll rebase it onto actual master for the next iteration.
Thank you!
On Thu, Mar 31, 2022 at 10:17 PM Greg Stark <stark@mit.edu> wrote:
Well I still think this would be a good candidate to get reviewed.
But it currently needs a rebase and it's the last day of the CF so I
guess it'll get moved forward again. I don't think "returned with
feedback" is helpful given there's been basically no feedback :(
As discussed in [1], we're taking this opportunity to return some patchsets that don't appear to be getting enough reviewer interest. This is not a rejection, since we don't necessarily think there's anything unacceptable about the entry, but it differs from a standard "Returned with Feedback" in that there's probably not much actionable feedback at all. Rather than code changes, what this patch needs is more community interest. You might - ask people for help with your approach, - see if there are similar patches that your code could supplement, - get interested parties to agree to review your patch in a CF, or - possibly present the functionality in a way that's easier to review overall. [For this patchset in particular, it's been suggested to split the extensions up into smaller independent pieces.] (Doing these things is no guarantee that there will be interest, but it's hopefully better than endlessly rebasing a patchset that is not receiving any feedback from the community.) Once you think you've built up some community support and the patchset is ready for review, you (or any interested party) can resurrect the patch entry by visiting https://commitfest.postgresql.org/38/2482/ and changing the status to "Needs Review", and then changing the status again to "Move to next CF". (Don't forget the second step; hopefully we will have streamlined this in the near future!) Thanks, --Jacob [1] https://postgr.es/m/f6344bbb-9141-e8c8-e655-d9baf40c4478%40timescale.com
These syntax extensions would make the jsonpath syntax a super powerful query language capable of most nosql workloads peoplewould have. Especially querying jsonpath with a variable key to look for is a sorely missed feature from the language.I would be open to reviewing the patches if need be, but if community support is all that's needed I believe a lotof users who could use this feature aren't using it because of the lack of documentation on all of postgres' amazing jsonpathfeatures. The best doc I've found on all the functionality is https://github.com/obartunov/sqljsondoc/blob/master/jsonpath.md Let me know how i can help! Alex The new status of this patch is: Needs review