Re: SQL/JSON path issues/questions

On Wed, 19 Jun 2019 at 20:04, Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
>
> On Wed, Jun 19, 2019 at 7:07 PM Thom Brown <thom(at)linux(dot)com> wrote:
> > 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?
>
> Makes sense to me.
>
> > 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[*]');
> > ^
>
> Perhaps it should be following:
>
> '$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()'

I'm not clear on why the original example doesn't work here.

Thom