Re: SQL/JSON path issues/questions

On Thu, 13 Jun 2019 at 14:59, Thom Brown <thom(at)linux(dot)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