On Jan 21, 2024, at 14:52, David E. Wheeler <david@justatheory.com> wrote:
> This is the only way the different behaviors make sense to me. @? expects a set, not a boolean, sees there is an item
inthe set, so returns true:
I make this interpretation based on this bit of the docs:
<para>
<productname>PostgreSQL</productname>'s implementation of the SQL/JSON path
language has the following deviations from the SQL/JSON standard.
</para>
<sect4 id="boolean-predicate-check-expressions">
<title>Boolean Predicate Check Expressions</title>
<para>
As an extension to the SQL standard, a <productname>PostgreSQL</productname>
path expression can be a Boolean predicate, whereas the SQL standard allows
predicates only in filters. Where SQL standard path expressions return the
relevant contents of the queried JSON value, predicate check expressions
return the three-valued result of the predicate: <literal>true</literal>,
<literal>false</literal>, or <literal>unknown</literal>. Compare this
filter <type>jsonpath</type> expression:
<screen>
<prompt>=></prompt> <userinput>select jsonb_path_query(:'json', '$.track.segments ?(@[*].HR > 130)');</userinput>
jsonb_path_query
---------------------------------------------------------------------------------
{"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}
</screen>
To a predicate expression, which returns <literal>true</literal>
<screen>
<prompt>=></prompt> <userinput>select jsonb_path_query(:'json', '$.track.segments[*].HR > 130');</userinput>
jsonb_path_query
------------------
true
</screen>
</para>
Best,
David