Re: SQL/JSON path issues/questions

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