> On 01/04/2023 08:02 CEST jian he <jian.universality@gmail.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