Re: SQL/JSON path issues/questions

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.

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