On Thu, Oct 17, 2024 at 02:07:00PM +0800, jian he wrote:
> On Thu, Oct 17, 2024 at 7:59 AM Bruce Momjian <br...@momjian.us> wrote:
> >
> >
> > Where are we on this? I still see this behavior.
> >
> > ---------------------------------------------------------------------------
>
> > > but I found following two examples returning different results,
> > > i think they should return the same value.
> > > select json_value('1', '$ == "1"' returning jsonb error on error);
> > > select json_query('1', '$ == "1"' returning jsonb error on error);
>
> This part has been resolved.
> see section Note section in
> https://www.postgresql.org/docs/current/functions-json.html#SQLJSON-QUERY-FUNCTIONS
Okay, good.
> > > >> the predicate: true, false, or unknown."
> > > >> "unknown" is wrong, because `select 'unknown'::jsonb;` will fail.
> > > >> here "unknown" should be "null"? see jsonb_path_query doc entry also.
> > > >>
>
> doc
> (https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-CHECK-EXPRESSIONS)
> <<QUOTE>>
> While SQL-standard path expressions return the relevant element(s) of
> the queried JSON value, predicate check expressions return the single
> three-valued result of the predicate: true, false, or unknown.
> <<END OF QUOTE>>
>
> https://www.postgresql.org/docs/current/datatype-boolean.html
> says
> "The boolean type can have several states: “true”, “false”, and a
> third state, “unknown”, which is represented by the SQL null value."
>
> but here
> select jsonb_path_query('1', '$ == "a"');
> return JSON null value, not SQL null value.
>
> however.
> select jsonb_path_match('1', '$ == "a"');
> return SQL null value.
>
>
> maybe we can change to
> "predicate check expressions return the single three-valued result of
> the predicate: true, false, or null"
Yes, done in the attached patch.
> Then in the <note> section mention that
> when Predicate check expressions cannot be applied, it returns JSON
> null for function jsonb_path_query,
> return SQL NULL for function jsonb_path_match or @@ operator.
The section is titled, "9.16.2.1. Deviations from the SQL Standard". Is
this a deviation from the standard? If not, I think we have to
distinguish SQL null and JSON null somewhere else. Is the "Note" text
also a deviation?
--
Bruce Momjian <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f8a0d76d12b..bf35145de89 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17648,7 +17648,7 @@ SELECT '{
element(s) of the queried JSON value, predicate check expressions
return the single three-valued result of the
predicate: <literal>true</literal>,
- <literal>false</literal>, or <literal>unknown</literal>.
+ <literal>false</literal>, or <literal>NULL</literal>.
For example, we could write this SQL-standard filter expression:
<screen>
<prompt>=></prompt> <userinput>select jsonb_path_query(:'json', '$.track.segments ?(@[*].HR > 130)');</userinput>
