Re: SQL/JSON path issues/questions

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