From: | "David E(dot) Wheeler" <david(at)justatheory(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Erik Wienhold <ewie(at)ewie(dot)name>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Patch: Improve Boolean Predicate JSON Path Docs |
Date: | 2024-01-21 19:58:10 |
Message-ID: | 6AC76368-7119-4FFE-84F8-F519AF9819E8@justatheory.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Jan 21, 2024, at 14:52, David E. Wheeler <david(at)justatheory(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David E. Wheeler | 2024-01-21 20:02:37 | Re: Patch: Improve Boolean Predicate JSON Path Docs |
Previous Message | Konstantin Knizhnik | 2024-01-21 19:56:36 | Re: index prefetching |