Thread: SQL/JSON path issues/questions

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

Hi, Thom.

At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <thom@linux.com> wrote
in <CAA-aLv4VVX=b9RK5hkfPXJczqaiTdqO04teW9i0wiQVhdKcqzw@mail.gmail.com>
> 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.

Hi!

On Fri, Jun 14, 2019 at 10:16 AM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <thom@linux.com> wrote
> in <CAA-aLv4VVX=b9RK5hkfPXJczqaiTdqO04teW9i0wiQVhdKcqzw@mail.gmail.com>
> > 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

On Thu, Jun 13, 2019 at 5:00 PM Thom Brown <thom@linux.com> 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

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

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

On Fri, 14 Jun 2019 at 08:16, Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:
>
> Hi, Thom.
>
> At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <thom@linux.com> wrote
> in <CAA-aLv4VVX=b9RK5hkfPXJczqaiTdqO04teW9i0wiQVhdKcqzw@mail.gmail.com>
> > 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.

On Mon, Jun 17, 2019 at 8:40 PM Thom Brown <thom@linux.com> wrote:
> On Fri, 14 Jun 2019 at 08:16, Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:
> >
> > Hi, Thom.
> >
> > At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <thom@linux.com> wrote
> > in <CAA-aLv4VVX=b9RK5hkfPXJczqaiTdqO04teW9i0wiQVhdKcqzw@mail.gmail.com>
> > > 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

On 6/17/19 4:13 PM, Alexander Korotkov wrote:
> On Mon, Jun 17, 2019 at 8:40 PM Thom Brown <thom@linux.com> 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

On Thu, 13 Jun 2019 at 14:59, Thom Brown <thom@linux.com> 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

On Mon, Jun 17, 2019 at 8:40 PM Thom Brown <thom@linux.com> wrote:
> On Fri, 14 Jun 2019 at 08:16, Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:
> >
> > Hi, Thom.
> >
> > At Thu, 13 Jun 2019 14:59:51 +0100, Thom Brown <thom@linux.com> wrote
> > in <CAA-aLv4VVX=b9RK5hkfPXJczqaiTdqO04teW9i0wiQVhdKcqzw@mail.gmail.com>
> > > 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

On Wed, Jun 19, 2019 at 7:07 PM Thom Brown <thom@linux.com> wrote:
> On Thu, 13 Jun 2019 at 14:59, Thom Brown <thom@linux.com> 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

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

On Wed, Jun 19, 2019 at 10:14 PM Alexander Korotkov
<a.korotkov@postgrespro.ru> 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

On 6/21/19 8:04 PM, Alexander Korotkov wrote:
> On Wed, Jun 19, 2019 at 10:14 PM Alexander Korotkov
> <a.korotkov@postgrespro.ru> 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

On Wed, 19 Jun 2019 at 20:04, Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
>
> On Wed, Jun 19, 2019 at 7:07 PM Thom Brown <thom@linux.com> wrote:
> > On Thu, 13 Jun 2019 at 14:59, Thom Brown <thom@linux.com> 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

On Thu, Jun 27, 2019 at 4:57 PM Thom Brown <thom@linux.com> wrote:
> On Wed, 19 Jun 2019 at 20:04, Alexander Korotkov
> <a.korotkov@postgrespro.ru> wrote:
> > On Wed, Jun 19, 2019 at 7:07 PM Thom Brown <thom@linux.com> wrote:
> > > On Thu, 13 Jun 2019 at 14:59, Thom Brown <thom@linux.com> 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

On Tue, Jun 25, 2019 at 6:38 PM Liudmila Mantrova
<l.mantrova@postgrespro.ru> 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

On 2019-Jun-28, Alexander Korotkov wrote:

> On Tue, Jun 25, 2019 at 6:38 PM Liudmila Mantrova
> <l.mantrova@postgrespro.ru> 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

On Fri, Jun 28, 2019 at 8:10 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> On 2019-Jun-28, Alexander Korotkov wrote:
>
> > On Tue, Jun 25, 2019 at 6:38 PM Liudmila Mantrova
> > <l.mantrova@postgrespro.ru> 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

On Fri, Jun 28, 2019 at 9:01 AM Oleg Bartunov <obartunov@postgrespro.ru> wrote:
> On Fri, Jun 28, 2019 at 8:10 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> >
> > On 2019-Jun-28, Alexander Korotkov wrote:
> >
> > > On Tue, Jun 25, 2019 at 6:38 PM Liudmila Mantrova
> > > <l.mantrova@postgrespro.ru> 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.

+1

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

It would be great if you manage to do this.

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

On 6/28/19 6:47 AM, Alexander Korotkov wrote:
> On Tue, Jun 25, 2019 at 6:38 PM Liudmila Mantrova
> <l.mantrova@postgrespro.ru> 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

Hi,

I have rechecked the standard and I agree that we should use "filter 
expression" whenever possible.
"A filter expression must be enclosed in parentheses..." looks like an 
oversight, so I fixed it. As for what's actually enclosed, I believe we 
can still use the word "condition" here as it's easy to understand and 
is already used in our docs, e.g. in description of the WHERE clause 
that serves a similar purpose.
The new version of the patch fixes the terminology, tweaks the examples, 
and provides some grammar and style fixes in the jsonpath-related chapters.


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

Hi!

On Wed, Jul 3, 2019 at 5:27 PM Liudmila Mantrova
<l.mantrova@postgrespro.ru> wrote:
>
> I have rechecked the standard and I agree that we should use "filter
> expression" whenever possible.
> "A filter expression must be enclosed in parentheses..." looks like an
> oversight, so I fixed it. As for what's actually enclosed, I believe we
> can still use the word "condition" here as it's easy to understand and
> is already used in our docs, e.g. in description of the WHERE clause
> that serves a similar purpose.
> The new version of the patch fixes the terminology, tweaks the examples,
> and provides some grammar and style fixes in the jsonpath-related chapters.


It looks good to me.  But this sentence looks a bit too complicated.

"It can be followed by one or more accessor operators to define the
JSON element on a lower nesting level by which to filter the result."

Could we phrase this as following?

"In order to filter the result by values lying on lower nesting level,
@ operator can be followed by one or more accessor operators."

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

On 7/3/19 11:59 PM, Alexander Korotkov wrote:
> Hi!
>
> On Wed, Jul 3, 2019 at 5:27 PM Liudmila Mantrova
> <l.mantrova@postgrespro.ru> wrote:
>> I have rechecked the standard and I agree that we should use "filter
>> expression" whenever possible.
>> "A filter expression must be enclosed in parentheses..." looks like an
>> oversight, so I fixed it. As for what's actually enclosed, I believe we
>> can still use the word "condition" here as it's easy to understand and
>> is already used in our docs, e.g. in description of the WHERE clause
>> that serves a similar purpose.
>> The new version of the patch fixes the terminology, tweaks the examples,
>> and provides some grammar and style fixes in the jsonpath-related chapters.
>
> It looks good to me.  But this sentence looks a bit too complicated.
>
> "It can be followed by one or more accessor operators to define the
> JSON element on a lower nesting level by which to filter the result."
>
> Could we phrase this as following?
>
> "In order to filter the result by values lying on lower nesting level,
> @ operator can be followed by one or more accessor operators."
>
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company

Thank  you!

I think we can make this sentence even shorter, the fix is attached:

"To refer to a JSON element stored at a lower nesting level, add one or 
more accessor operators after <literal>@</literal>."


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

On Thu, Jul 4, 2019 at 4:38 PM Liudmila Mantrova
<l.mantrova@postgrespro.ru> wrote:
> Thank  you!
>
> I think we can make this sentence even shorter, the fix is attached:
>
> "To refer to a JSON element stored at a lower nesting level, add one or
> more accessor operators after <literal>@</literal>."

Thanks, looks good to me.  Attached revision of patch contains commit
message.  I'm going to commit this on no objections.

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

On Mon, Jul 8, 2019 at 12:30 AM Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
> On Thu, Jul 4, 2019 at 4:38 PM Liudmila Mantrova
> <l.mantrova@postgrespro.ru> wrote:
> > Thank  you!
> >
> > I think we can make this sentence even shorter, the fix is attached:
> >
> > "To refer to a JSON element stored at a lower nesting level, add one or
> > more accessor operators after <literal>@</literal>."
>
> Thanks, looks good to me.  Attached revision of patch contains commit
> message.  I'm going to commit this on no objections.

So, pushed!

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

On Wed, 10 Jul 2019 at 05:58, Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
>
> On Mon, Jul 8, 2019 at 12:30 AM Alexander Korotkov
> <a.korotkov@postgrespro.ru> wrote:
> > On Thu, Jul 4, 2019 at 4:38 PM Liudmila Mantrova
> > <l.mantrova@postgrespro.ru> wrote:
> > > Thank  you!
> > >
> > > I think we can make this sentence even shorter, the fix is attached:
> > >
> > > "To refer to a JSON element stored at a lower nesting level, add one or
> > > more accessor operators after <literal>@</literal>."
> >
> > Thanks, looks good to me.  Attached revision of patch contains commit
> > message.  I'm going to commit this on no objections.
>
> So, pushed!

I've just noticed the >= operator shows up as just > in the "jsonpath
Filter Expression Elements" table, and the <= example only shows <.

Thom

On Thu, Jul 11, 2019 at 5:10 PM Thom Brown <thom@linux.com> wrote:
> On Wed, 10 Jul 2019 at 05:58, Alexander Korotkov
> <a.korotkov@postgrespro.ru> wrote:
> >
> > On Mon, Jul 8, 2019 at 12:30 AM Alexander Korotkov
> > <a.korotkov@postgrespro.ru> wrote:
> > > On Thu, Jul 4, 2019 at 4:38 PM Liudmila Mantrova
> > > <l.mantrova@postgrespro.ru> wrote:
> > > > Thank  you!
> > > >
> > > > I think we can make this sentence even shorter, the fix is attached:
> > > >
> > > > "To refer to a JSON element stored at a lower nesting level, add one or
> > > > more accessor operators after <literal>@</literal>."
> > >
> > > Thanks, looks good to me.  Attached revision of patch contains commit
> > > message.  I'm going to commit this on no objections.
> >
> > So, pushed!
>
> I've just noticed the >= operator shows up as just > in the "jsonpath
> Filter Expression Elements" table, and the <= example only shows <.

Thank you for catching this!  Fix just pushed.

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

On Thu, 11 Jul 2019 at 16:23, Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
>
> On Thu, Jul 11, 2019 at 5:10 PM Thom Brown <thom@linux.com> wrote:
> > On Wed, 10 Jul 2019 at 05:58, Alexander Korotkov
> > <a.korotkov@postgrespro.ru> wrote:
> > >
> > > On Mon, Jul 8, 2019 at 12:30 AM Alexander Korotkov
> > > <a.korotkov@postgrespro.ru> wrote:
> > > > On Thu, Jul 4, 2019 at 4:38 PM Liudmila Mantrova
> > > > <l.mantrova@postgrespro.ru> wrote:
> > > > > Thank  you!
> > > > >
> > > > > I think we can make this sentence even shorter, the fix is attached:
> > > > >
> > > > > "To refer to a JSON element stored at a lower nesting level, add one or
> > > > > more accessor operators after <literal>@</literal>."
> > > >
> > > > Thanks, looks good to me.  Attached revision of patch contains commit
> > > > message.  I'm going to commit this on no objections.
> > >
> > > So, pushed!
> >
> > I've just noticed the >= operator shows up as just > in the "jsonpath
> > Filter Expression Elements" table, and the <= example only shows <.
>
> Thank you for catching this!  Fix just pushed.

Thanks.

Now I'm looking at the @? and @@ operators, and getting a bit
confused.  This following query returns true, but I can't determine
why:

# SELECT '{"a":[1,2,3,4,5]}'::jsonb @? '$.b == "hello"'::jsonpath;
 ?column?
----------
 t
(1 row)

"b" is not a valid item, so there should be no match.  Perhaps it's my
misunderstanding of how these operators are supposed to work, but the
documentation is quite terse on the behaviour.

Thom

On Tue, Jul 16, 2019 at 9:22 PM Thom Brown <thom@linux.com> wrote:
> Now I'm looking at the @? and @@ operators, and getting a bit
> confused.  This following query returns true, but I can't determine
> why:
>
> # SELECT '{"a":[1,2,3,4,5]}'::jsonb @? '$.b == "hello"'::jsonpath;
>  ?column?
> ----------
>  t
> (1 row)
>
> "b" is not a valid item, so there should be no match.  Perhaps it's my
> misunderstanding of how these operators are supposed to work, but the
> documentation is quite terse on the behaviour.

So, the result of jsonpath evaluation is single value "false".

# SELECT jsonb_path_query_array('{"a":[1,2,3,4,5]}'::jsonb, '$.b == "hello"');
 jsonb_path_query_array
------------------------
 [false]
(1 row)

@@ operator checks that result is "true".  This is why it returns "false".

@? operator checks if result is not empty.  So, it's single "false"
value, not empty list.  This is why it returns "true".

Perhaps, we need to clarify this in docs providing more explanation.

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

On Tue, 16 Jul 2019 at 19:44, Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
>
> On Tue, Jul 16, 2019 at 9:22 PM Thom Brown <thom@linux.com> wrote:
> > Now I'm looking at the @? and @@ operators, and getting a bit
> > confused.  This following query returns true, but I can't determine
> > why:
> >
> > # SELECT '{"a":[1,2,3,4,5]}'::jsonb @? '$.b == "hello"'::jsonpath;
> >  ?column?
> > ----------
> >  t
> > (1 row)
> >
> > "b" is not a valid item, so there should be no match.  Perhaps it's my
> > misunderstanding of how these operators are supposed to work, but the
> > documentation is quite terse on the behaviour.
>
> So, the result of jsonpath evaluation is single value "false".
>
> # SELECT jsonb_path_query_array('{"a":[1,2,3,4,5]}'::jsonb, '$.b == "hello"');
>  jsonb_path_query_array
> ------------------------
>  [false]
> (1 row)
>
> @@ operator checks that result is "true".  This is why it returns "false".
>
> @? operator checks if result is not empty.  So, it's single "false"
> value, not empty list.  This is why it returns "true".
>
> Perhaps, we need to clarify this in docs providing more explanation.

Understood.  Thanks.

Also, is there a reason why jsonb_path_query doesn't have an operator analog?

Thom

On Thu, Jul 18, 2019 at 5:08 PM Thom Brown <thom@linux.com> wrote:
> On Tue, 16 Jul 2019 at 19:44, Alexander Korotkov
> <a.korotkov@postgrespro.ru> wrote:
> >
> > On Tue, Jul 16, 2019 at 9:22 PM Thom Brown <thom@linux.com> wrote:
> > > Now I'm looking at the @? and @@ operators, and getting a bit
> > > confused.  This following query returns true, but I can't determine
> > > why:
> > >
> > > # SELECT '{"a":[1,2,3,4,5]}'::jsonb @? '$.b == "hello"'::jsonpath;
> > >  ?column?
> > > ----------
> > >  t
> > > (1 row)
> > >
> > > "b" is not a valid item, so there should be no match.  Perhaps it's my
> > > misunderstanding of how these operators are supposed to work, but the
> > > documentation is quite terse on the behaviour.
> >
> > So, the result of jsonpath evaluation is single value "false".
> >
> > # SELECT jsonb_path_query_array('{"a":[1,2,3,4,5]}'::jsonb, '$.b == "hello"');
> >  jsonb_path_query_array
> > ------------------------
> >  [false]
> > (1 row)
> >
> > @@ operator checks that result is "true".  This is why it returns "false".
> >
> > @? operator checks if result is not empty.  So, it's single "false"
> > value, not empty list.  This is why it returns "true".
> >
> > Perhaps, we need to clarify this in docs providing more explanation.
>
> Understood.  Thanks.
>
> Also, is there a reason why jsonb_path_query doesn't have an operator analog?

The point of existing operator analogues is index support.  We
introduced operators for searches we can accelerate using GIN indexes.

jsonb_path_query() doesn't return bool.  So, even if we have an
operator for that, it wouldn't get index support.

However, we can discuss introduction of operator analogues for other
functions as syntax sugar.

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

Hi Steven,

On Fri, Jul 19, 2019 at 9:53 PM Steven Pousty <steve.pousty@gmail.com> wrote:
> I would like to help review this documentation. Can you please point me in the right direction?

Thank you for your interest.  You're welcome to do review.

Please take a look at instruction for reviewing a patch [1] and
working with git [2].  Also, in order to build a doc you will need to
setup a toolset first [3].

Links

1. https://wiki.postgresql.org/wiki/Reviewing_a_Patch
2. https://wiki.postgresql.org/wiki/Working_with_git#Testing_a_patch
3. https://www.postgresql.org/docs/devel/docguide-toolsets.html


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