Re: ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account

> On 01/04/2023 08:02 CEST jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> Hi,
> https://www.postgresql.org/docs/current/functions-json.html
> > jsonb @@ jsonpath → boolean
> > Returns the result of a JSON path predicate check for the specified JSON
> > value. Only the first item of the result is taken into account. If the
> > result is not Boolean, then NULL is returned.
> > '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' → t
>
> select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*]');
> return
>
> > jsonb_path_query
> > ------------------
> >  1
> >  2
> >  3
> >  4
> >  5
> > (5 rows)
>
> I don't understand:"Only the first item of the result is taken into account.".
>
> Here,JSON path predicate check for the specified JSON valuereturn true, some
> return false. (1 > 2 is false, 2 > 2 is false).

The result is true if any array element matches the predicate because predicates
are evaluated on sequences. The documentation for executePredicate in
src/backend/utils/adt/jsonpath_exec.c explains it:

> Predicates have existence semantics, because their operands are item
> sequences. Pairs of items from the left and right operand's sequences are
> checked. TRUE returned only if any pair satisfying the condition is found.
> In strict mode, even if the desired pair has already been found, all pairs
> still need to be examined to check the absence of errors. If any error
> occurs, UNKNOWN (analogous to SQL NULL) is returned.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/jsonpath_exec.c;h=b561f0e7e803f0e5a546ad118a47f625225b9708;hb=HEAD#l1461

Difference between using a predicate as path expression vs filter expression:

=# select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] > 2');
jsonb_path_query
------------------
true
(1 row)

=# select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ > 2)');
jsonb_path_query
------------------
3
4
5
(3 rows)

If you want the predicate result for each element, you must apply the predicate
to the rows returned from jsonb_path_query:

=# select elem, elem::float > 2 as pred from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*]') elem;
elem | pred
------+------
1 | f
2 | f
3 | t
4 | t
5 | t
(5 rows)

--
Erik