Re: Patch: Improve Boolean Predicate JSON Path Docs

On Jan 21, 2024, at 14:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I don't entirely buy this argument --- if that is the interpretation,
> of what use are predicate check expressions? It seems to me that we
> have to consider them as being a shorthand notation for filter
> expressions, or else they simply do not make sense as jsonpath.

I believe it becomes pretty apparent when using jsonb_path_query(). The filter expression returns a set (using the previous \gset example):

david=# select jsonb_path_query(:'json', '$.track.segments[*].HR ? (@ > 10)');
jsonb_path_query
------------------
73
135
(2 rows)

The predicate check returns a boolean:

david=# select jsonb_path_query(:'json', '$.track.segments[*].HR > 10');
jsonb_path_query
------------------
true
(1 row)

This is the only way the different behaviors make sense to me. @? expects a set, not a boolean, sees there is an item in the set, so returns true:

david=# select jsonb_path_query(:'json', '$.track.segments[*].HR > 1000');
jsonb_path_query
------------------
false
(1 row)

david=# select :'json'::jsonb @? '$.track.segments[*].HR > 1000';
?column?
----------
t
(1 row)

Best,

David