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

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(at)pgmasters(dot)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.

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 3/4/20 3:18 PM, Nikita Glukhov wrote:
> On 04.03.2020 19:13, David Steele wrote:
>> 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.
Another year has passed without any comment or review on this patch set.

I'm not sure why the feature is not generating any interest, but you
might want to ask people who have been involved in JSON path before if
they are interested in reviewing.

Since this is still essentially a new feature with no review before this
CF I still don't think it is a good candidate for v14 but let's see if
it gets some review.

Regards,
--
-David
david(at)pgmasters(dot)net

On 3/3/21 9:44 AM, David Steele wrote:
> On 3/4/20 3:18 PM, Nikita Glukhov wrote:
>> On 04.03.2020 19:13, David Steele wrote:
>>> 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.
> Another year has passed without any comment or review on this patch set.
>
> I'm not sure why the feature is not generating any interest, but you
> might want to ask people who have been involved in JSON path before if
> they are interested in reviewing.
>
> Since this is still essentially a new feature with no review before this
> CF I still don't think it is a good candidate for v14 but let's see if
> it gets some review.
Target version updated to 15.

Regards,
--
-David
david(at)pgmasters(dot)net

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(dot)gluhov(at)postgrespro(dot)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(at)mit(dot)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(dot)gluhov(at)postgrespro(dot)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(at)mit(dot)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(dot)gluhov(at)postgrespro(dot)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(at)mit(dot)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 :(
>
>
>

--
Regards,
Nikita Malakhov
Postgres Professional
https://postgrespro.ru/

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 people would 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 lot of users who could use this feature aren't using it because of the lack of documentation on all of postgres' amazing jsonpath features. 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