SQL/JSON path issues/questions - Mailing list pgsql-hackers

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