Re: SQL/JSON path issues/questions

On Tue, Jul 16, 2019 at 9:22 PM Thom Brown <thom(at)linux(dot)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