On Jan 21, 2024, at 14:52, David E. Wheeler <da...@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 in the 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