On 4/2/23 06:21, Erik Wienhold wrote:
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)
That is a long way from:
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.
--
Erik
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
