Horton

Re: SQL/JSON path issues/questions

Alexander Korotkov <a.korotkov@postgrespro.ru>

View thread View on postgr.es
From: Alexander Korotkov <a.korotkov@postgrespro.ru>
To: Thom Brown <thom@linux.com>
Cc: Liudmila Mantrova <l.mantrova@postgrespro.ru>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2019-07-16T18:44:39Z
Lists: pgsql-hackers 
On Tue, Jul 16, 2019 at 9:22 PM Thom Brown <thom@linux.com> wrote:
> Now I'm looking at the @? and @@ operators, and getting a bit
> confused.  This following query returns true, but I can't determine
> why:
>
> # SELECT '{"a":[1,2,3,4,5]}'::jsonb @? '$.b == "hello"'::jsonpath;
>  ?column?
> ----------
>  t
> (1 row)
>
> "b" is not a valid item, so there should be no match.  Perhaps it's my
> misunderstanding of how these operators are supposed to work, but the
> documentation is quite terse on the behaviour.

So, the result of jsonpath evaluation is single value "false".

# SELECT jsonb_path_query_array('{"a":[1,2,3,4,5]}'::jsonb, '$.b == "hello"');
 jsonb_path_query_array
------------------------
 [false]
(1 row)

@@ operator checks that result is "true".  This is why it returns "false".

@? operator checks if result is not empty.  So, it's single "false"
value, not empty list.  This is why it returns "true".

Perhaps, we need to clarify this in docs providing more explanation.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Commits

  1. Fixes for jsonpath filter expression elements table in docs

  2. Assorted fixes for jsonpath documentation

  3. Fix description for $varname jsonpath variable

  4. Improve documentation for jsonpath like_regex predicate

  5. Support 'q' flag in jsonpath 'like_regex' predicate