SQL/JSON path issues/questions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
33 messages Options
12
Reply | Threaded
Open this post in threaded view
|

SQL/JSON path issues/questions

Thom Brown-2
Hi,

I've been reading through the documentation regarding jsonpath and
jsonb_path_query etc., and I have found it lacking explanation for
some functionality, and I've also had some confusion when using the
feature.

? operator
==========
The first mention of '?' is in section 9.15, where it says:

"Suppose you would like to retrieve all heart rate values higher than
130. You can achieve this using the following expression:
'$.track.segments[*].HR ? (@ > 130)'"

So what is the ? operator doing here?  Sure, there's the regular ?
operator, which is given as an example further down the page:

'{"a":1, "b":2}'::jsonb ? 'b'

But this doesn't appear to have the same purpose.


like_regex
==========
Then there's like_regex, which shows an example that uses the keyword
"flag", but that is the only instance of that keyword being mentioned,
and the flags available to this expression aren't anywhere to be seen.


is unknown
==========
"is unknown" suggests a boolean output, but the example shows an
output of "infinity".  While I understand what it does, this appears
inconsistent with all other "is..." functions (e.g. is_valid(lsn),
pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
pg_is_in_backup() etc.).


$varname
==========
The jsonpath variable, $varname, has an incomplete description: "A
named variable. Its value must be set in the PASSING clause of an
SQL/JSON query function. for details."


Binary operation error
==========
I get an error when I run this query:

postgres=# SELECT jsonb_path_query('[2]', '2 + $[1]');
psql: ERROR:  right operand of jsonpath operator + is not a single numeric value

While I know it's correct to get an error in this scenario as there is
no element beyond 0, the message I get is confusing.  I'd expect this
if it encountered another array in that position, but not for
exceeding the upper bound of the array.


Cryptic error
==========
postgres=# SELECT jsonb_path_query('[1, "2",
{},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].type()');
psql: ERROR:  syntax error, unexpected ANY_P at or near "**" of jsonpath input
LINE 1: ...},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].typ...
                                                             ^
Again, I expect an error, but the message produced doesn't help me.
I'll remove the ANY_P if I can find it.


Can't use nested arrays with jsonpath
==========

I encounter an error in this scenario:

postgres=# select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == [1,2])');
psql: ERROR:  syntax error, unexpected '[' at or near "[" of jsonpath input
LINE 1: select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == ...

So these filter operators only work with scalars?


Thanks

Thom


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Kyotaro Horiguchi-4
Hi, Thom.

At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <[hidden email]> wrote
in <CAA-aLv4VVX=[hidden email]>

> Hi,
>
> I've been reading through the documentation regarding jsonpath and
> jsonb_path_query etc., and I have found it lacking explanation for
> some functionality, and I've also had some confusion when using the
> feature.
>
> ? operator
> ==========
> The first mention of '?' is in section 9.15, where it says:
>
> "Suppose you would like to retrieve all heart rate values higher than
> 130. You can achieve this using the following expression:
> '$.track.segments[*].HR ? (@ > 130)'"
>
> So what is the ? operator doing here?  Sure, there's the regular ?
It is described just above as:

| Each filter expression must be enclosed in parentheses and
| preceded by a question mark.

> operator, which is given as an example further down the page:
>
> '{"a":1, "b":2}'::jsonb ? 'b'
>
> But this doesn't appear to have the same purpose.

The section is mentioning path expressions and the '?' is a jsonb
operator. It's somewhat confusing but not so much comparing with
around..

> like_regex
> ==========
> Then there's like_regex, which shows an example that uses the keyword
> "flag", but that is the only instance of that keyword being mentioned,
> and the flags available to this expression aren't anywhere to be seen.

It is described as POSIX regular expressions. So '9.7.3 POSIX
Regular Expressions' is that. But linking it would
helpful. (attached 0001)

> is unknown
> ==========
> "is unknown" suggests a boolean output, but the example shows an
> output of "infinity".  While I understand what it does, this appears
> inconsistent with all other "is..." functions (e.g. is_valid(lsn),
> pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
> pg_is_in_backup() etc.).

It's the right behavior. Among them, only "infinity" gives
"unknown' for the test (@ > 0). -1 gives false, 2 and 3 true.

> $varname
> ==========
> The jsonpath variable, $varname, has an incomplete description: "A
> named variable. Its value must be set in the PASSING clause of an
> SQL/JSON query function. for details."

Yeah, it is apparently chopped amid. In the sgml source, the
missing part is "<!-- TBD: See <xref
linkend="sqljson-input-clause"/> -->", and the PASSING clause is
not implemented yet. On the other hand a similar stuff is
currently implemented as vas parameter in some jsonb
functions. Linking it to there might be helpful (Attached 0002).


> Binary operation error
> ==========
> I get an error when I run this query:
>
> postgres=# SELECT jsonb_path_query('[2]', '2 + $[1]');
> psql: ERROR:  right operand of jsonpath operator + is not a single numeric value
>
> While I know it's correct to get an error in this scenario as there is
> no element beyond 0, the message I get is confusing.  I'd expect this
> if it encountered another array in that position, but not for
> exceeding the upper bound of the array.
Something like attached makes it clerer? (Attached 0003)

| ERROR:  right operand of jsonpath operator + is not a single numeric value
| DETAIL:  It was an array with 0 elements.

> Cryptic error
> ==========
> postgres=# SELECT jsonb_path_query('[1, "2",
> {},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].type()');
> psql: ERROR:  syntax error, unexpected ANY_P at or near "**" of jsonpath input
> LINE 1: ...},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].typ...
>                                                              ^
> Again, I expect an error, but the message produced doesn't help me.
> I'll remove the ANY_P if I can find it.

Yeah, I had a similar error:

=# select jsonb_path_query('[-1,2,7, "infinity"]', '$[*] ? (($hoge) is
unknown)', '{"hoge": (@ > 0)}');
ERROR:  syntax error, unexpected IS_P at or near " " of jsonpath input

When the errors are issued, the caller side is commented as:

jsonpath_scan.l:481
> jsonpath_yyerror(NULL, "bogus input"); /* shouldn't happen */

The error message is reasonable if it were really shouldn't
happen, but it quite easily happen. I don't have an idea of how
to fix it for the present..

> Can't use nested arrays with jsonpath
> ==========
>
> I encounter an error in this scenario:
>
> postgres=# select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == [1,2])');
> psql: ERROR:  syntax error, unexpected '[' at or near "[" of jsonpath input
> LINE 1: select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == ...
>
> So these filter operators only work with scalars?
Perhaps true. It seems that SQL/JSON is saying so. Array is not
comparable with anything. (See 6.13.5 Comparison predicates in
[1])

[1] http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip

regards.

0001-Add-link-to-description-for-like_regex.patch (1K) Download Attachment
0002-Fix-description-for-varname-of-jsonpath-variable.patch (2K) Download Attachment
0003-Separate-two-distinctive-json-errors.patch (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
Hi!

On Fri, Jun 14, 2019 at 10:16 AM Kyotaro Horiguchi
<[hidden email]> wrote:

> At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <[hidden email]> wrote
> in <CAA-aLv4VVX=[hidden email]>
> > Hi,
> >
> > I've been reading through the documentation regarding jsonpath and
> > jsonb_path_query etc., and I have found it lacking explanation for
> > some functionality, and I've also had some confusion when using the
> > feature.
> >
> > ? operator
> > ==========
> > The first mention of '?' is in section 9.15, where it says:
> >
> > "Suppose you would like to retrieve all heart rate values higher than
> > 130. You can achieve this using the following expression:
> > '$.track.segments[*].HR ? (@ > 130)'"
> >
> > So what is the ? operator doing here?  Sure, there's the regular ?
>
> It is described just above as:
>
> | Each filter expression must be enclosed in parentheses and
> | preceded by a question mark.
+1

> > operator, which is given as an example further down the page:
> >
> > '{"a":1, "b":2}'::jsonb ? 'b'
> >
> > But this doesn't appear to have the same purpose.
>
> The section is mentioning path expressions and the '?' is a jsonb
> operator. It's somewhat confusing but not so much comparing with
> around..

+1

> > like_regex
> > ==========
> > Then there's like_regex, which shows an example that uses the keyword
> > "flag", but that is the only instance of that keyword being mentioned,
> > and the flags available to this expression aren't anywhere to be seen.
>
> It is described as POSIX regular expressions. So '9.7.3 POSIX
> Regular Expressions' is that. But linking it would
> helpful. (attached 0001)

Actually, standard requires supporting the same regex flags as
XQuery/XPath does [1].  Perhaps, we found that we miss support for 'q'
flag, while it's trivial.  Attached patch fixes that.  Documentation
should contain description of flags.  That will be posted as separate
patch.

> > is unknown
> > ==========
> > "is unknown" suggests a boolean output, but the example shows an
> > output of "infinity".  While I understand what it does, this appears
> > inconsistent with all other "is..." functions (e.g. is_valid(lsn),
> > pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
> > pg_is_in_backup() etc.).
>
> It's the right behavior. Among them, only "infinity" gives
> "unknown' for the test (@ > 0). -1 gives false, 2 and 3 true.
+1
We follow here SQL standard for jsonpath language.  There is no direct
analogy with our SQL-level functions.

>
> > $varname
> > ==========
> > The jsonpath variable, $varname, has an incomplete description: "A
> > named variable. Its value must be set in the PASSING clause of an
> > SQL/JSON query function. for details."
>
> Yeah, it is apparently chopped amid. In the sgml source, the
> missing part is "<!-- TBD: See <xref
> linkend="sqljson-input-clause"/> -->", and the PASSING clause is
> not implemented yet. On the other hand a similar stuff is
> currently implemented as vas parameter in some jsonb
> functions. Linking it to there might be helpful (Attached 0002).
>
> > Binary operation error
> > ==========
> > I get an error when I run this query:
> >
> > postgres=# SELECT jsonb_path_query('[2]', '2 + $[1]');
> > psql: ERROR:  right operand of jsonpath operator + is not a single numeric value
> >
> > While I know it's correct to get an error in this scenario as there is
> > no element beyond 0, the message I get is confusing.  I'd expect this
> > if it encountered another array in that position, but not for
> > exceeding the upper bound of the array.
>
> Something like attached makes it clerer? (Attached 0003)
Thank you.  Will review these two and commit.

> | ERROR:  right operand of jsonpath operator + is not a single numeric value
> | DETAIL:  It was an array with 0 elements.
>
> > Cryptic error
> > ==========
> > postgres=# SELECT jsonb_path_query('[1, "2",
> > {},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].type()');
> > psql: ERROR:  syntax error, unexpected ANY_P at or near "**" of jsonpath input
> > LINE 1: ...},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].typ...
> >                                                              ^
> > Again, I expect an error, but the message produced doesn't help me.
> > I'll remove the ANY_P if I can find it.
>
> Yeah, I had a similar error:
>
> =# select jsonb_path_query('[-1,2,7, "infinity"]', '$[*] ? (($hoge) is
> unknown)', '{"hoge": (@ > 0)}');
> ERROR:  syntax error, unexpected IS_P at or near " " of jsonpath input
>
> When the errors are issued, the caller side is commented as:
>
> jsonpath_scan.l:481
> > jsonpath_yyerror(NULL, "bogus input"); /* shouldn't happen */
>
> The error message is reasonable if it were really shouldn't
> happen, but it quite easily happen. I don't have an idea of how
> to fix it for the present..
I'm also not sure.  Need further thinking about it.

> > Can't use nested arrays with jsonpath
> > ==========
> >
> > I encounter an error in this scenario:
> >
> > postgres=# select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == [1,2])');
> > psql: ERROR:  syntax error, unexpected '[' at or near "[" of jsonpath input
> > LINE 1: select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == ...
> >
> > So these filter operators only work with scalars?
>
> Perhaps true. It seems that SQL/JSON is saying so. Array is not
> comparable with anything. (See 6.13.5 Comparison predicates in
> [1])
That's true.  But we may we extended version of jsonpath having more
features than standard defined.  We can pick proposal [2] to evade
possible incompatibility with future standard updates.

Links.

1. https://www.w3.org/TR/xpath-functions/#func-matches
2. https://www.postgresql.org/message-id/5CF28EA0.80902%40anastigmatix.net

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

0001-Implement-like_regex-flag-q-in-jsonpath.patch (10K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
In reply to this post by Thom Brown-2
On Thu, Jun 13, 2019 at 5:00 PM Thom Brown <[hidden email]> wrote:
> I've been reading through the documentation regarding jsonpath and
> jsonb_path_query etc., and I have found it lacking explanation for
> some functionality, and I've also had some confusion when using the
> feature.

BTW, I've some general idea about jsonpath documentation structure.
Right now definition of jsonpath language is spread between sections
"JSON Types" [1] and "JSON Functions, Operators, and Expressions" [2].
Thank might be confusing.  I think it would be more readable if whole
jsonpath language definition would be given in a single place.  I
propose to move whole definition of jsonpath to section [1] leaving
section [2] just with SQL-level functions.  Any thoughts?

Links.

1. https://www.postgresql.org/docs/devel/datatype-json.html#DATATYPE-JSONPATH
2. https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
In reply to this post by Alexander Korotkov
I'm going to push attached 3 patches if no objections.

Regarding 0003-Separate-two-distinctive-json-errors.patch, I think it
requires more thoughts.

        RETURN_ERROR(ereport(ERROR,
                             (errcode(ERRCODE_SINGLETON_JSON_ITEM_REQUIRED),
                              errmsg("left operand of jsonpath
operator %s is not a single numeric value",
-                                    jspOperationName(jsp->type)))));
+                                    jspOperationName(jsp->type)),
+                             (llen != 1 ?
+                              errdetail("It was an array with %d
elements.", llen):
+                              errdetail("The only element was not a
numeric.")))));

When we have more than 1 value, it's no exactly array.  Jsonpath can
extract values from various parts of json document, which never
constitute and array.  Should we say something like "There are %d
values"?  Also, probably we should display the type of single element
if it's not numeric.  jsonb_path_match() also throws
ERRCODE_SINGLETON_JSON_ITEM_REQUIRED, should we add similar
errdetail() there?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

0001-implement-like_regex-flag-q-in-jsonpath-2.patch (10K) Download Attachment
0002-improve-jsonpath-like_regex-documentation-2.patch (1K) Download Attachment
0003-fix-jsonpath-varname-description-2.patch (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Liudmila Mantrova

On 6/17/19 11:36 AM, Alexander Korotkov wrote:

> I'm going to push attached 3 patches if no objections.
>
> Regarding 0003-Separate-two-distinctive-json-errors.patch, I think it
> requires more thoughts.
>
>          RETURN_ERROR(ereport(ERROR,
>                               (errcode(ERRCODE_SINGLETON_JSON_ITEM_REQUIRED),
>                                errmsg("left operand of jsonpath
> operator %s is not a single numeric value",
> -                                    jspOperationName(jsp->type)))));
> +                                    jspOperationName(jsp->type)),
> +                             (llen != 1 ?
> +                              errdetail("It was an array with %d
> elements.", llen):
> +                              errdetail("The only element was not a
> numeric.")))));
>
> When we have more than 1 value, it's no exactly array.  Jsonpath can
> extract values from various parts of json document, which never
> constitute and array.  Should we say something like "There are %d
> values"?  Also, probably we should display the type of single element
> if it's not numeric.  jsonb_path_match() also throws
> ERRCODE_SINGLETON_JSON_ITEM_REQUIRED, should we add similar
> errdetail() there?
>
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
Hi Alexander,

While I have no objections to the proposed fixes, I think we can further
improve patch 0003 and the text it refers to.
In attempt to clarify jsonpath docs and address the concern that ? is
hard to trace in the current text, I'd also like to propose patch 0004.
Please see both of them attached.

--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


0003-fix-jsonpath-varname-description-3.patch (3K) Download Attachment
0004-clarify-jsonpath-docs-1.patch (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Thom Brown-2
In reply to this post by Kyotaro Horiguchi-4
On Fri, 14 Jun 2019 at 08:16, Kyotaro Horiguchi <[hidden email]> wrote:

>
> Hi, Thom.
>
> At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <[hidden email]> wrote
> in <CAA-aLv4VVX=[hidden email]>
> > Hi,
> >
> > I've been reading through the documentation regarding jsonpath and
> > jsonb_path_query etc., and I have found it lacking explanation for
> > some functionality, and I've also had some confusion when using the
> > feature.
> >
> > ? operator
> > ==========
> > The first mention of '?' is in section 9.15, where it says:
> >
> > "Suppose you would like to retrieve all heart rate values higher than
> > 130. You can achieve this using the following expression:
> > '$.track.segments[*].HR ? (@ > 130)'"
> >
> > So what is the ? operator doing here?  Sure, there's the regular ?
>
> It is described just above as:
>
> | Each filter expression must be enclosed in parentheses and
> | preceded by a question mark.

Can I suggest that, rather than using "question mark", we use the "?"
symbol, or provide a syntax structure which shows something like:

<path expression> ? <filter expression>

This not only makes this key information clearer and more prominent,
but it also makes the "?" symbol searchable in a browser for anyone
wanting to find out what that symbol is doing.

> > operator, which is given as an example further down the page:
> >
> > '{"a":1, "b":2}'::jsonb ? 'b'
> >
> > But this doesn't appear to have the same purpose.
>
> The section is mentioning path expressions and the '?' is a jsonb
> operator. It's somewhat confusing but not so much comparing with
> around..
>
> > like_regex
> > ==========
> > Then there's like_regex, which shows an example that uses the keyword
> > "flag", but that is the only instance of that keyword being mentioned,
> > and the flags available to this expression aren't anywhere to be seen.
>
> It is described as POSIX regular expressions. So '9.7.3 POSIX
> Regular Expressions' is that. But linking it would
> helpful. (attached 0001)
>
> > is unknown
> > ==========
> > "is unknown" suggests a boolean output, but the example shows an
> > output of "infinity".  While I understand what it does, this appears
> > inconsistent with all other "is..." functions (e.g. is_valid(lsn),
> > pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
> > pg_is_in_backup() etc.).
>
> It's the right behavior. Among them, only "infinity" gives
> "unknown' for the test (@ > 0). -1 gives false, 2 and 3 true.

I still find it counter-intuitive.

>
> > $varname
> > ==========
> > The jsonpath variable, $varname, has an incomplete description: "A
> > named variable. Its value must be set in the PASSING clause of an
> > SQL/JSON query function. for details."
>
> Yeah, it is apparently chopped amid. In the sgml source, the
> missing part is "<!-- TBD: See <xref
> linkend="sqljson-input-clause"/> -->", and the PASSING clause is
> not implemented yet. On the other hand a similar stuff is
> currently implemented as vas parameter in some jsonb
> functions. Linking it to there might be helpful (Attached 0002).
>
>
> > Binary operation error
> > ==========
> > I get an error when I run this query:
> >
> > postgres=# SELECT jsonb_path_query('[2]', '2 + $[1]');
> > psql: ERROR:  right operand of jsonpath operator + is not a single numeric value
> >
> > While I know it's correct to get an error in this scenario as there is
> > no element beyond 0, the message I get is confusing.  I'd expect this
> > if it encountered another array in that position, but not for
> > exceeding the upper bound of the array.
>
> Something like attached makes it clerer? (Attached 0003)
>
> | ERROR:  right operand of jsonpath operator + is not a single numeric value
> | DETAIL:  It was an array with 0 elements.

My first thought upon seeing this error message would be, "I don't see
an array with 0 elements."

>
> > Cryptic error
> > ==========
> > postgres=# SELECT jsonb_path_query('[1, "2",
> > {},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].type()');
> > psql: ERROR:  syntax error, unexpected ANY_P at or near "**" of jsonpath input
> > LINE 1: ...},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].typ...
> >                                                              ^
> > Again, I expect an error, but the message produced doesn't help me.
> > I'll remove the ANY_P if I can find it.
>
> Yeah, I had a similar error:
>
> =# select jsonb_path_query('[-1,2,7, "infinity"]', '$[*] ? (($hoge) is
> unknown)', '{"hoge": (@ > 0)}');
> ERROR:  syntax error, unexpected IS_P at or near " " of jsonpath input
>
> When the errors are issued, the caller side is commented as:
>
> jsonpath_scan.l:481
> > jsonpath_yyerror(NULL, "bogus input"); /* shouldn't happen */
>
> The error message is reasonable if it were really shouldn't
> happen, but it quite easily happen. I don't have an idea of how
> to fix it for the present..
>
> > Can't use nested arrays with jsonpath
> > ==========
> >
> > I encounter an error in this scenario:
> >
> > postgres=# select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == [1,2])');
> > psql: ERROR:  syntax error, unexpected '[' at or near "[" of jsonpath input
> > LINE 1: select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == ...
> >
> > So these filter operators only work with scalars?
>
> Perhaps true. It seems that SQL/JSON is saying so. Array is not
> comparable with anything. (See 6.13.5 Comparison predicates in
> [1])
>
> [1] http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
>
> regards.


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
On Mon, Jun 17, 2019 at 8:40 PM Thom Brown <[hidden email]> wrote:

> On Fri, 14 Jun 2019 at 08:16, Kyotaro Horiguchi <[hidden email]> wrote:
> >
> > Hi, Thom.
> >
> > At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <[hidden email]> wrote
> > in <CAA-aLv4VVX=[hidden email]>
> > > Hi,
> > >
> > > I've been reading through the documentation regarding jsonpath and
> > > jsonb_path_query etc., and I have found it lacking explanation for
> > > some functionality, and I've also had some confusion when using the
> > > feature.
> > >
> > > ? operator
> > > ==========
> > > The first mention of '?' is in section 9.15, where it says:
> > >
> > > "Suppose you would like to retrieve all heart rate values higher than
> > > 130. You can achieve this using the following expression:
> > > '$.track.segments[*].HR ? (@ > 130)'"
> > >
> > > So what is the ? operator doing here?  Sure, there's the regular ?
> >
> > It is described just above as:
> >
> > | Each filter expression must be enclosed in parentheses and
> > | preceded by a question mark.
>
> Can I suggest that, rather than using "question mark", we use the "?"
> symbol, or provide a syntax structure which shows something like:
>
> <path expression> ? <filter expression>
>
> This not only makes this key information clearer and more prominent,
> but it also makes the "?" symbol searchable in a browser for anyone
> wanting to find out what that symbol is doing.

Sounds like a good point for me.

> > > operator, which is given as an example further down the page:
> > >
> > > '{"a":1, "b":2}'::jsonb ? 'b'
> > >
> > > But this doesn't appear to have the same purpose.
> >
> > The section is mentioning path expressions and the '?' is a jsonb
> > operator. It's somewhat confusing but not so much comparing with
> > around..
> >
> > > like_regex
> > > ==========
> > > Then there's like_regex, which shows an example that uses the keyword
> > > "flag", but that is the only instance of that keyword being mentioned,
> > > and the flags available to this expression aren't anywhere to be seen.
> >
> > It is described as POSIX regular expressions. So '9.7.3 POSIX
> > Regular Expressions' is that. But linking it would
> > helpful. (attached 0001)
> >
> > > is unknown
> > > ==========
> > > "is unknown" suggests a boolean output, but the example shows an
> > > output of "infinity".  While I understand what it does, this appears
> > > inconsistent with all other "is..." functions (e.g. is_valid(lsn),
> > > pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
> > > pg_is_in_backup() etc.).
> >
> > It's the right behavior. Among them, only "infinity" gives
> > "unknown' for the test (@ > 0). -1 gives false, 2 and 3 true.
>
> I still find it counter-intuitive.

It might be so.  But it's defined do in SQL Standard 2016.  Following
an SQL standard was always a project priority.  We unlikely going to
say: "We don't want to follow a standard, because it doesn't looks
similar to our home brew functions."

> > > $varname
> > > ==========
> > > The jsonpath variable, $varname, has an incomplete description: "A
> > > named variable. Its value must be set in the PASSING clause of an
> > > SQL/JSON query function. for details."
> >
> > Yeah, it is apparently chopped amid. In the sgml source, the
> > missing part is "<!-- TBD: See <xref
> > linkend="sqljson-input-clause"/> -->", and the PASSING clause is
> > not implemented yet. On the other hand a similar stuff is
> > currently implemented as vas parameter in some jsonb
> > functions. Linking it to there might be helpful (Attached 0002).
> >
> >
> > > Binary operation error
> > > ==========
> > > I get an error when I run this query:
> > >
> > > postgres=# SELECT jsonb_path_query('[2]', '2 + $[1]');
> > > psql: ERROR:  right operand of jsonpath operator + is not a single numeric value
> > >
> > > While I know it's correct to get an error in this scenario as there is
> > > no element beyond 0, the message I get is confusing.  I'd expect this
> > > if it encountered another array in that position, but not for
> > > exceeding the upper bound of the array.
> >
> > Something like attached makes it clerer? (Attached 0003)
> >
> > | ERROR:  right operand of jsonpath operator + is not a single numeric value
> > | DETAIL:  It was an array with 0 elements.
>
> My first thought upon seeing this error message would be, "I don't see
> an array with 0 elements."

Yes, it looks counter-intuitive for me too.  There is really no array
with 0 elements.  Actually, jsonpath subexpression selects no items.
We probably should adjust the message accordingly.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Chapman Flack
On 6/17/19 4:13 PM, Alexander Korotkov wrote:

> On Mon, Jun 17, 2019 at 8:40 PM Thom Brown <[hidden email]> wrote:
>>>> "is unknown" suggests a boolean output, but the example shows an
>>>> output of "infinity".  While I understand what it does, this appears
>>>> inconsistent with all other "is..." functions (e.g. is_valid(lsn),
>>>> pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
>>>> pg_is_in_backup() etc.).
>>>
>>> It's the right behavior. Among them, only "infinity" gives
>>> "unknown' for the test (@ > 0). -1 gives false, 2 and 3 true.
>>
>> I still find it counter-intuitive.
>
> It might be so.  But it's defined do in SQL Standard 2016.

IIUC, this comes about simply because the JSON data model for numeric
values does not have any infinity or NaN.

So the example given in our doc is sort of a trick example that does
double duty: it demonstrates that (@ > 0) is Unknown when @ is a string,
because numbers and strings are incomparable, and it *also* sort of
slyly reminds the reader that JSON numbers have no infinity, and
therefore "infinity" is nothing but a run-of-the-mill string.

But maybe it is just too brow-furrowingly clever to ask one example
to make both of those points. Maybe it would be clearer to use some
string other than "infinity" to make the first point:

[-1, 2, 7, "some string"] | $[*] ? ((@ > 0) is unknown) | "some string"

... and then if the reminder about infinity is worth making, repeat
the example:

[-1, 2, 7, "infinity"] | $[*] ? ((@ > 0) is unknown) | "infinity"

with a note that it's a trick example as a reminder that JSON numbers
don't have infinity or NaN and so it is no different from any other
string.

Regards,
-Chap


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Thom Brown-2
In reply to this post by Thom Brown-2
On Thu, 13 Jun 2019 at 14:59, Thom Brown <[hidden email]> wrote:

>
> Hi,
>
> I've been reading through the documentation regarding jsonpath and
> jsonb_path_query etc., and I have found it lacking explanation for
> some functionality, and I've also had some confusion when using the
> feature.
>
> ? operator
> ==========
> The first mention of '?' is in section 9.15, where it says:
>
> "Suppose you would like to retrieve all heart rate values higher than
> 130. You can achieve this using the following expression:
> '$.track.segments[*].HR ? (@ > 130)'"
>
> So what is the ? operator doing here?  Sure, there's the regular ?
> operator, which is given as an example further down the page:
>
> '{"a":1, "b":2}'::jsonb ? 'b'
>
> But this doesn't appear to have the same purpose.
>
>
> like_regex
> ==========
> Then there's like_regex, which shows an example that uses the keyword
> "flag", but that is the only instance of that keyword being mentioned,
> and the flags available to this expression aren't anywhere to be seen.
>
>
> is unknown
> ==========
> "is unknown" suggests a boolean output, but the example shows an
> output of "infinity".  While I understand what it does, this appears
> inconsistent with all other "is..." functions (e.g. is_valid(lsn),
> pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
> pg_is_in_backup() etc.).
>
>
> $varname
> ==========
> The jsonpath variable, $varname, has an incomplete description: "A
> named variable. Its value must be set in the PASSING clause of an
> SQL/JSON query function. for details."
>
>
> Binary operation error
> ==========
> I get an error when I run this query:
>
> postgres=# SELECT jsonb_path_query('[2]', '2 + $[1]');
> psql: ERROR:  right operand of jsonpath operator + is not a single numeric value
>
> While I know it's correct to get an error in this scenario as there is
> no element beyond 0, the message I get is confusing.  I'd expect this
> if it encountered another array in that position, but not for
> exceeding the upper bound of the array.
>
>
> Cryptic error
> ==========
> postgres=# SELECT jsonb_path_query('[1, "2",
> {},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].type()');
> psql: ERROR:  syntax error, unexpected ANY_P at or near "**" of jsonpath input
> LINE 1: ...},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].typ...
>                                                              ^
> Again, I expect an error, but the message produced doesn't help me.
> I'll remove the ANY_P if I can find it.
>
>
> Can't use nested arrays with jsonpath
> ==========
>
> I encounter an error in this scenario:
>
> postgres=# select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == [1,2])');
> psql: ERROR:  syntax error, unexpected '[' at or near "[" of jsonpath input
> LINE 1: select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == ...
>
> So these filter operators only work with scalars?
>
>

Another observation about the documentation is that the examples given
in 9.15. JSON Functions, Operators, and Expressions aren't all
functional.  Some example JSON is provided, followed by example
jsonpath queries which could be used against it.  These will produce
results for the reader wishing to test them out until this example:

'$.track.segments[*].HR ? (@ > 130)'

This is because there is no HR value greater than 130.  May I propose
setting this and all similar examples to (@ > 120) instead?

Also, this example doesn't work:

'$.track ? (@.segments[*] ? (@.HR > 130)).segments.size()'

This gives me:

psql: ERROR:  syntax error, unexpected $end at end of jsonpath input
LINE 13: }','$.track ? (@.segments[*]');
            ^

Thanks

Thom


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
In reply to this post by Thom Brown-2
On Mon, Jun 17, 2019 at 8:40 PM Thom Brown <[hidden email]> wrote:

> On Fri, 14 Jun 2019 at 08:16, Kyotaro Horiguchi <[hidden email]> wrote:
> >
> > Hi, Thom.
> >
> > At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <[hidden email]> wrote
> > in <CAA-aLv4VVX=[hidden email]>
> > > Hi,
> > >
> > > I've been reading through the documentation regarding jsonpath and
> > > jsonb_path_query etc., and I have found it lacking explanation for
> > > some functionality, and I've also had some confusion when using the
> > > feature.
> > >
> > > ? operator
> > > ==========
> > > The first mention of '?' is in section 9.15, where it says:
> > >
> > > "Suppose you would like to retrieve all heart rate values higher than
> > > 130. You can achieve this using the following expression:
> > > '$.track.segments[*].HR ? (@ > 130)'"
> > >
> > > So what is the ? operator doing here?  Sure, there's the regular ?
> >
> > It is described just above as:
> >
> > | Each filter expression must be enclosed in parentheses and
> > | preceded by a question mark.
>
> Can I suggest that, rather than using "question mark", we use the "?"
> symbol, or provide a syntax structure which shows something like:
>
> <path expression> ? <filter expression>
>
> This not only makes this key information clearer and more prominent,
> but it also makes the "?" symbol searchable in a browser for anyone
> wanting to find out what that symbol is doing.

Sounds good for me.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
In reply to this post by Thom Brown-2
On Wed, Jun 19, 2019 at 7:07 PM Thom Brown <[hidden email]> wrote:

> On Thu, 13 Jun 2019 at 14:59, Thom Brown <[hidden email]> wrote:
> >
> > Hi,
> >
> > I've been reading through the documentation regarding jsonpath and
> > jsonb_path_query etc., and I have found it lacking explanation for
> > some functionality, and I've also had some confusion when using the
> > feature.
> >
> > ? operator
> > ==========
> > The first mention of '?' is in section 9.15, where it says:
> >
> > "Suppose you would like to retrieve all heart rate values higher than
> > 130. You can achieve this using the following expression:
> > '$.track.segments[*].HR ? (@ > 130)'"
> >
> > So what is the ? operator doing here?  Sure, there's the regular ?
> > operator, which is given as an example further down the page:
> >
> > '{"a":1, "b":2}'::jsonb ? 'b'
> >
> > But this doesn't appear to have the same purpose.
> >
> >
> > like_regex
> > ==========
> > Then there's like_regex, which shows an example that uses the keyword
> > "flag", but that is the only instance of that keyword being mentioned,
> > and the flags available to this expression aren't anywhere to be seen.
> >
> >
> > is unknown
> > ==========
> > "is unknown" suggests a boolean output, but the example shows an
> > output of "infinity".  While I understand what it does, this appears
> > inconsistent with all other "is..." functions (e.g. is_valid(lsn),
> > pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
> > pg_is_in_backup() etc.).
> >
> >
> > $varname
> > ==========
> > The jsonpath variable, $varname, has an incomplete description: "A
> > named variable. Its value must be set in the PASSING clause of an
> > SQL/JSON query function. for details."
> >
> >
> > Binary operation error
> > ==========
> > I get an error when I run this query:
> >
> > postgres=# SELECT jsonb_path_query('[2]', '2 + $[1]');
> > psql: ERROR:  right operand of jsonpath operator + is not a single numeric value
> >
> > While I know it's correct to get an error in this scenario as there is
> > no element beyond 0, the message I get is confusing.  I'd expect this
> > if it encountered another array in that position, but not for
> > exceeding the upper bound of the array.
> >
> >
> > Cryptic error
> > ==========
> > postgres=# SELECT jsonb_path_query('[1, "2",
> > {},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].type()');
> > psql: ERROR:  syntax error, unexpected ANY_P at or near "**" of jsonpath input
> > LINE 1: ...},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].typ...
> >                                                              ^
> > Again, I expect an error, but the message produced doesn't help me.
> > I'll remove the ANY_P if I can find it.
> >
> >
> > Can't use nested arrays with jsonpath
> > ==========
> >
> > I encounter an error in this scenario:
> >
> > postgres=# select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == [1,2])');
> > psql: ERROR:  syntax error, unexpected '[' at or near "[" of jsonpath input
> > LINE 1: select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == ...
> >
> > So these filter operators only work with scalars?
> >
> >
>
> Another observation about the documentation is that the examples given
> in 9.15. JSON Functions, Operators, and Expressions aren't all
> functional.  Some example JSON is provided, followed by example
> jsonpath queries which could be used against it.  These will produce
> results for the reader wishing to test them out until this example:
>
> '$.track.segments[*].HR ? (@ > 130)'
>
> This is because there is no HR value greater than 130.  May I propose
> setting this and all similar examples to (@ > 120) instead?

Makes sense to me.

> Also, this example doesn't work:
>
> '$.track ? (@.segments[*] ? (@.HR > 130)).segments.size()'
>
> This gives me:
>
> psql: ERROR:  syntax error, unexpected $end at end of jsonpath input
> LINE 13: }','$.track ? (@.segments[*]');
>             ^

Perhaps it should be following:

'$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()'

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
In reply to this post by Liudmila Mantrova
Hi, Liudmila!

> While I have no objections to the proposed fixes, I think we can further
> improve patch 0003 and the text it refers to.
> In attempt to clarify jsonpath docs and address the concern that ? is
> hard to trace in the current text, I'd also like to propose patch 0004.
> Please see both of them attached.

Thank you for your editing.  I'm going to commit them as well.

But I'm going to commit your changes separately from 0003 I've posted
before.  Because 0003 fixes factual error, while you're proposing set
of grammar/style fixes.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
On Wed, Jun 19, 2019 at 10:14 PM Alexander Korotkov
<[hidden email]> wrote:

> > While I have no objections to the proposed fixes, I think we can further
> > improve patch 0003 and the text it refers to.
> > In attempt to clarify jsonpath docs and address the concern that ? is
> > hard to trace in the current text, I'd also like to propose patch 0004.
> > Please see both of them attached.
>
> Thank you for your editing.  I'm going to commit them as well.
>
> But I'm going to commit your changes separately from 0003 I've posted
> before.  Because 0003 fixes factual error, while you're proposing set
> of grammar/style fixes.

I made some review of these patches.  My notes are following:

   <para>
-    See also <xref linkend="functions-aggregate"/> for the aggregate
-    function <function>json_agg</function> which aggregates record
-    values as JSON, and the aggregate function
-    <function>json_object_agg</function> which aggregates pairs of values
-    into a JSON object, and their <type>jsonb</type> equivalents,
+    See also <xref linkend="functions-aggregate"/> for details on
+    <function>json_agg</function> function that aggregates record
+    values as JSON, <function>json_object_agg</function> function
+    that aggregates pairs of values into a JSON object, and their
<type>jsonb</type> equivalents,
     <function>jsonb_agg</function> and <function>jsonb_object_agg</function>.
   </para>

This part is not directly related to jsonpath, and it has been there
for a long time.  I'd like some native english speaker to review this
change before committing this.

        <para>
-        Expression inside subscript may consititue an integer,
-        numeric expression or any other <literal>jsonpath</literal> expression
-        returning single numeric value.  The <literal>last</literal> keyword
-        can be used in the expression denoting the last subscript in an array.
-        That's helpful for handling arrays of unknown length.
+        The specified <replaceable>index</replaceable> can be an integer,
+        as well as a numeric or <literal>jsonpath</literal> expression that
+        returns a single integer value. Zero index corresponds to the first
+        array element. To access the last element in an array, you can use
+        the <literal>last</literal> keyword, which is useful for handling
+        arrays of unknown length.
        </para>

I think this part requires more work.  Let's see what cases do we have
with examples:

1) Integer: '$.ar[1]'
2) Numeric: '$.ar[1.5]' (converted to integer)
3) Some numeric expression: '$.ar[last - 1]'
4) Arbitrary jsonpath expression: '$.ar[$.ar2.size() + $.num - 2]'

In principle, it not necessary to divide 3 and 4, or divide 1 and 2.
Or we may don't describe cases at all, but just say it's a jsonpath
expression returning numeric, which is converted to integer.

Also, note that we do not necessary *access* last array element with
"last" keyword.  "last" keyword denotes index of last element in
expression.  But completely different element might be actually
accessed.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Liudmila Mantrova
On 6/21/19 8:04 PM, Alexander Korotkov wrote:

> On Wed, Jun 19, 2019 at 10:14 PM Alexander Korotkov
> <[hidden email]> wrote:
>>> While I have no objections to the proposed fixes, I think we can further
>>> improve patch 0003 and the text it refers to.
>>> In attempt to clarify jsonpath docs and address the concern that ? is
>>> hard to trace in the current text, I'd also like to propose patch 0004.
>>> Please see both of them attached.
>> Thank you for your editing.  I'm going to commit them as well.
>>
>> But I'm going to commit your changes separately from 0003 I've posted
>> before.  Because 0003 fixes factual error, while you're proposing set
>> of grammar/style fixes.
> I made some review of these patches.  My notes are following:
>
>     <para>
> -    See also <xref linkend="functions-aggregate"/> for the aggregate
> -    function <function>json_agg</function> which aggregates record
> -    values as JSON, and the aggregate function
> -    <function>json_object_agg</function> which aggregates pairs of values
> -    into a JSON object, and their <type>jsonb</type> equivalents,
> +    See also <xref linkend="functions-aggregate"/> for details on
> +    <function>json_agg</function> function that aggregates record
> +    values as JSON, <function>json_object_agg</function> function
> +    that aggregates pairs of values into a JSON object, and their
> <type>jsonb</type> equivalents,
>       <function>jsonb_agg</function> and <function>jsonb_object_agg</function>.
>     </para>
>
> This part is not directly related to jsonpath, and it has been there
> for a long time.  I'd like some native english speaker to review this
> change before committing this.
>
>          <para>
> -        Expression inside subscript may consititue an integer,
> -        numeric expression or any other <literal>jsonpath</literal> expression
> -        returning single numeric value.  The <literal>last</literal> keyword
> -        can be used in the expression denoting the last subscript in an array.
> -        That's helpful for handling arrays of unknown length.
> +        The specified <replaceable>index</replaceable> can be an integer,
> +        as well as a numeric or <literal>jsonpath</literal> expression that
> +        returns a single integer value. Zero index corresponds to the first
> +        array element. To access the last element in an array, you can use
> +        the <literal>last</literal> keyword, which is useful for handling
> +        arrays of unknown length.
>          </para>
>
> I think this part requires more work.  Let's see what cases do we have
> with examples:
>
> 1) Integer: '$.ar[1]'
> 2) Numeric: '$.ar[1.5]' (converted to integer)
> 3) Some numeric expression: '$.ar[last - 1]'
> 4) Arbitrary jsonpath expression: '$.ar[$.ar2.size() + $.num - 2]'
>
> In principle, it not necessary to divide 3 and 4, or divide 1 and 2.
> Or we may don't describe cases at all, but just say it's a jsonpath
> expression returning numeric, which is converted to integer.
>
> Also, note that we do not necessary *access* last array element with
> "last" keyword.  "last" keyword denotes index of last element in
> expression.  But completely different element might be actually
> accessed.
>
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
Hi Alexander,

Thank you for the catch! Please see the modified version of patch 0004
attached.

As for your comment on patch 0003, since I'm not a native speaker, I can
only refer to a recent discussion in pgsql-docs mailing list that seems
to support my view on a similar issue:

https://www.postgresql.org/message-id/9484.1558050957%40sss.pgh.pa.us


--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


0004-clarify-jsonpath-docs-2.patch (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Thom Brown-2
In reply to this post by Alexander Korotkov
On Wed, 19 Jun 2019 at 20:04, Alexander Korotkov
<[hidden email]> wrote:

>
> On Wed, Jun 19, 2019 at 7:07 PM Thom Brown <[hidden email]> wrote:
> > On Thu, 13 Jun 2019 at 14:59, Thom Brown <[hidden email]> wrote:
> > >
> > > Hi,
> > >
> > > I've been reading through the documentation regarding jsonpath and
> > > jsonb_path_query etc., and I have found it lacking explanation for
> > > some functionality, and I've also had some confusion when using the
> > > feature.
> > >
> > > ? operator
> > > ==========
> > > The first mention of '?' is in section 9.15, where it says:
> > >
> > > "Suppose you would like to retrieve all heart rate values higher than
> > > 130. You can achieve this using the following expression:
> > > '$.track.segments[*].HR ? (@ > 130)'"
> > >
> > > So what is the ? operator doing here?  Sure, there's the regular ?
> > > operator, which is given as an example further down the page:
> > >
> > > '{"a":1, "b":2}'::jsonb ? 'b'
> > >
> > > But this doesn't appear to have the same purpose.
> > >
> > >
> > > like_regex
> > > ==========
> > > Then there's like_regex, which shows an example that uses the keyword
> > > "flag", but that is the only instance of that keyword being mentioned,
> > > and the flags available to this expression aren't anywhere to be seen.
> > >
> > >
> > > is unknown
> > > ==========
> > > "is unknown" suggests a boolean output, but the example shows an
> > > output of "infinity".  While I understand what it does, this appears
> > > inconsistent with all other "is..." functions (e.g. is_valid(lsn),
> > > pg_is_other_temp_schema(oid), pg_opclass_is_visible(opclass_oid),
> > > pg_is_in_backup() etc.).
> > >
> > >
> > > $varname
> > > ==========
> > > The jsonpath variable, $varname, has an incomplete description: "A
> > > named variable. Its value must be set in the PASSING clause of an
> > > SQL/JSON query function. for details."
> > >
> > >
> > > Binary operation error
> > > ==========
> > > I get an error when I run this query:
> > >
> > > postgres=# SELECT jsonb_path_query('[2]', '2 + $[1]');
> > > psql: ERROR:  right operand of jsonpath operator + is not a single numeric value
> > >
> > > While I know it's correct to get an error in this scenario as there is
> > > no element beyond 0, the message I get is confusing.  I'd expect this
> > > if it encountered another array in that position, but not for
> > > exceeding the upper bound of the array.
> > >
> > >
> > > Cryptic error
> > > ==========
> > > postgres=# SELECT jsonb_path_query('[1, "2",
> > > {},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].type()');
> > > psql: ERROR:  syntax error, unexpected ANY_P at or near "**" of jsonpath input
> > > LINE 1: ...},[{"a":2}],2.3,null,"2019-06-05T13:25:43.511Z"]','$[**].typ...
> > >                                                              ^
> > > Again, I expect an error, but the message produced doesn't help me.
> > > I'll remove the ANY_P if I can find it.
> > >
> > >
> > > Can't use nested arrays with jsonpath
> > > ==========
> > >
> > > I encounter an error in this scenario:
> > >
> > > postgres=# select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == [1,2])');
> > > psql: ERROR:  syntax error, unexpected '[' at or near "[" of jsonpath input
> > > LINE 1: select jsonb_path_query('[1, 2, 1, [1,2], 3]','$[*] ? (@ == ...
> > >
> > > So these filter operators only work with scalars?
> > >
> > >
> >
> > Another observation about the documentation is that the examples given
> > in 9.15. JSON Functions, Operators, and Expressions aren't all
> > functional.  Some example JSON is provided, followed by example
> > jsonpath queries which could be used against it.  These will produce
> > results for the reader wishing to test them out until this example:
> >
> > '$.track.segments[*].HR ? (@ > 130)'
> >
> > This is because there is no HR value greater than 130.  May I propose
> > setting this and all similar examples to (@ > 120) instead?
>
> Makes sense to me.
>
> > Also, this example doesn't work:
> >
> > '$.track ? (@.segments[*] ? (@.HR > 130)).segments.size()'
> >
> > This gives me:
> >
> > psql: ERROR:  syntax error, unexpected $end at end of jsonpath input
> > LINE 13: }','$.track ? (@.segments[*]');
> >             ^
>
> Perhaps it should be following:
>
> '$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()'

I'm not clear on why the original example doesn't work here.

Thom


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
On Thu, Jun 27, 2019 at 4:57 PM Thom Brown <[hidden email]> wrote:

> On Wed, 19 Jun 2019 at 20:04, Alexander Korotkov
> <[hidden email]> wrote:
> > On Wed, Jun 19, 2019 at 7:07 PM Thom Brown <[hidden email]> wrote:
> > > On Thu, 13 Jun 2019 at 14:59, Thom Brown <[hidden email]> wrote:
> > > Also, this example doesn't work:
> > >
> > > '$.track ? (@.segments[*] ? (@.HR > 130)).segments.size()'
> > >
> > > This gives me:
> > >
> > > psql: ERROR:  syntax error, unexpected $end at end of jsonpath input
> > > LINE 13: }','$.track ? (@.segments[*]');
> > >             ^
> >
> > Perhaps it should be following:
> >
> > '$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()'
>
> I'm not clear on why the original example doesn't work here.

It doesn't work because filter expression should be predicate, i.e.
always return bool.  In the original example filter expression selects
some json elements.  My original idea was that it was accidentally
come from some of our extensions where we've allowed that.  But it
appears to be just plain wrong example, which never worked.  Sorry for
that.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alexander Korotkov
In reply to this post by Liudmila Mantrova
On Tue, Jun 25, 2019 at 6:38 PM Liudmila Mantrova
<[hidden email]> wrote:
> Thank you for the catch! Please see the modified version of patch 0004
> attached.

I tried to review and revise the part related to filters, but I failed
because I don't understand the notions used in the documentation.

What is the difference between filter expression and filter condition?
 I can guess that filter expression contains question mark,
parentheses and filter condition inside.  But this sentence is in
contradiction with my guess: "A filter expression must be enclosed in
parentheses and preceded by a question mark".  So, filter expression
is inside the parentheses.  Then what is filter condition?  The same?

>    Each filter expression can provide one or more filters
>    that are applied to the result of the path evaluation.


So additionally to filter condition and filter expression we introduce
the notion of just filter.  What is it?  Could we make it without
introduction of new notion?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Alvaro Herrera-9
On 2019-Jun-28, Alexander Korotkov wrote:

> On Tue, Jun 25, 2019 at 6:38 PM Liudmila Mantrova
> <[hidden email]> wrote:
> > Thank you for the catch! Please see the modified version of patch 0004
> > attached.
>
> I tried to review and revise the part related to filters, but I failed
> because I don't understand the notions used in the documentation.
>
> What is the difference between filter expression and filter condition?
>  I can guess that filter expression contains question mark,
> parentheses and filter condition inside.  But this sentence is in
> contradiction with my guess: "A filter expression must be enclosed in
> parentheses and preceded by a question mark".  So, filter expression
> is inside the parentheses.  Then what is filter condition?  The same?

The SQL standard defines "JSON filter expressions" (in 9.39 of the 2016
edition).  It does not use either term "filter condition" nor bare
"filter"; it uses "JSON path predicate" which is the part of the JSON
filter expression that is preceded by the question mark and enclosed by
parens.

Maybe we should stick with the standard terminology ...

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: SQL/JSON path issues/questions

Oleg Bartunov-3
On Fri, Jun 28, 2019 at 8:10 AM Alvaro Herrera <[hidden email]> wrote:

>
> On 2019-Jun-28, Alexander Korotkov wrote:
>
> > On Tue, Jun 25, 2019 at 6:38 PM Liudmila Mantrova
> > <[hidden email]> wrote:
> > > Thank you for the catch! Please see the modified version of patch 0004
> > > attached.
> >
> > I tried to review and revise the part related to filters, but I failed
> > because I don't understand the notions used in the documentation.
> >
> > What is the difference between filter expression and filter condition?
> >  I can guess that filter expression contains question mark,
> > parentheses and filter condition inside.  But this sentence is in
> > contradiction with my guess: "A filter expression must be enclosed in
> > parentheses and preceded by a question mark".  So, filter expression
> > is inside the parentheses.  Then what is filter condition?  The same?
>
> The SQL standard defines "JSON filter expressions" (in 9.39 of the 2016
> edition).  It does not use either term "filter condition" nor bare
> "filter"; it uses "JSON path predicate" which is the part of the JSON
> filter expression that is preceded by the question mark and enclosed by
> parens.

Yes, this is what I used in my talk
http://www.sai.msu.su/~megera/postgres/talks/jsonpath-ibiza-2019.pdf

>
> Maybe we should stick with the standard terminology ...

Sure.

As for the jsonpath documentation, I think we should remember, that
jsonpath is a part of SQL/JSON, and in the following releases we will
expand documentation to include SQL/JSON functions, so I suggest to
have one chapter SQL/JSON with following structure:
1.  Introduction
1.1 SQL/JSON data model
1.2 SQL/JSON path language
1.3 <SQL/JSON functions> -- to be added
2. PostgreSQL implementation
2.1  jsonpath data type                       -- link from json data types
2.2 jsonpath  functions and operators -- link from functions
2.3 Indexing

I plan to work on a separate chapter "JSON handling in PostgreSQL" for
PG13,  which includes
JSON(b) data types, functions, indexing and SQL/JSON.

>
> --
> Álvaro Herrera                https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>


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


12