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