Thread: minor doc issue in 9.16.2.1.1. Boolean Predicate Check Expressions
hi. ------------- 9.16.2.1.1. Boolean Predicate Check Expressions As an extension to the SQL standard, a PostgreSQL path expression can be a Boolean predicate, whereas the SQL standard allows predicates only within filters. 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. For example, we could write this SQL-standard filter expression: ------------- slight inconsistency, "SQL-standard" versus "SQL standard" "path expression can be a Boolean predicate", why capital "Boolean"? "predicate check expressions return the single three-valued result of 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.
Re: minor doc issue in 9.16.2.1.1. Boolean Predicate Check Expressions
From
"David G. Johnston"
Date:
On Thu, Jun 20, 2024 at 7:30 PM jian he <jian.universality@gmail.com> wrote:
"predicate check expressions return the single three-valued result of
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.
The syntax for json_exists belies this claim (assuming our docs are accurate there). Its "on error" options are true/false/unknown. Additionally, the predicate test operator is named "is unknown" not "is null".
The result of the predicate test, which is never produced as a value, only a concept, is indeed "unknown" - which then devolves to false when it is practically applied to determining whether to output the path item being tested. As it does also when used in a parth expression.
postgres=# select json_value('[null]','$[0] < 1');
json_value
------------
f
postgres=# select json_value('[null]','$[0] == null');
json_value
------------
t
json_value
------------
f
postgres=# select json_value('[null]','$[0] == null');
json_value
------------
t
Not sure how to peek inside the jsonpath system here though...
postgres=# select json_value('[null]','($[0] < 1) == null');
ERROR: syntax error at or near "==" of jsonpath input
LINE 1: select json_value('[null]','($[0] < 1) == null');
ERROR: syntax error at or near "==" of jsonpath input
LINE 1: select json_value('[null]','($[0] < 1) == null');
I am curious if that produces true (the unknown is left as null) or false (the unknown becomes false immediately).
David J.
On Fri, Jun 21, 2024 at 11:11 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Thu, Jun 20, 2024 at 7:30 PM jian he <jian.universality@gmail.com> wrote:
>>
>> "predicate check expressions return the single three-valued result of
>>
>> 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.
>>
>
> The syntax for json_exists belies this claim (assuming our docs are accurate there). Its "on error" options are
true/false/unknown. Additionally, the predicate test operator is named "is unknown" not "is null".
>
> The result of the predicate test, which is never produced as a value, only a concept, is indeed "unknown" - which
thendevolves to false when it is practically applied to determining whether to output the path item being tested. As
itdoes also when used in a parth expression.
>
in [1] says
The similar predicate check expression simply returns true, indicating
that a match exists:
=> select jsonb_path_query(:'json', '$.track.segments[*].HR > 130');
jsonb_path_query
------------------
true
----------------------------------------
but in this example
select jsonb_path_query('1', '$ == "1"');
return null.
I guess here, the match evaluation cannot be applied, thus returning null.
So summary:
if the boolean predicate check expressions are applicable, return true or false.
the boolean predicate check expressions are not applicable, return null.
example: select jsonb_path_query('1', '$ == "a"');
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);
[1] https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-CHECK-EXPRESSIONS
Where are we on this? I still see this behavior.
---------------------------------------------------------------------------
On Fri, Jun 21, 2024 at 04:53:55PM +0800, jian he wrote:
> On Fri, Jun 21, 2024 at 11:11 AM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> >
> > On Thu, Jun 20, 2024 at 7:30 PM jian he <jian.universality@gmail.com> wrote:
> >>
> >> "predicate check expressions return the single three-valued result of
> >>
> >> 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.
> >>
> >
> > The syntax for json_exists belies this claim (assuming our docs are accurate there). Its "on error" options are
true/false/unknown. Additionally, the predicate test operator is named "is unknown" not "is null".
> >
> > The result of the predicate test, which is never produced as a value, only a concept, is indeed "unknown" - which
thendevolves to false when it is practically applied to determining whether to output the path item being tested. As
itdoes also when used in a parth expression.
> >
>
> in [1] says
> The similar predicate check expression simply returns true, indicating
> that a match exists:
>
> => select jsonb_path_query(:'json', '$.track.segments[*].HR > 130');
> jsonb_path_query
> ------------------
> true
>
>
> ----------------------------------------
> but in this example
> select jsonb_path_query('1', '$ == "1"');
> return null.
>
> I guess here, the match evaluation cannot be applied, thus returning null.
>
>
> So summary:
> if the boolean predicate check expressions are applicable, return true or false.
>
> the boolean predicate check expressions are not applicable, return null.
> example: select jsonb_path_query('1', '$ == "a"');
>
>
> 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);
>
> [1] https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-CHECK-EXPRESSIONS
>
>
--
Bruce Momjian <bruce@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?"
On Thu, Oct 17, 2024 at 7:59 AM Bruce Momjian <bruce@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
>
> On Fri, Jun 21, 2024 at 04:53:55PM +0800, jian he wrote:
> > On Fri, Jun 21, 2024 at 11:11 AM David G. Johnston
> > <david.g.johnston@gmail.com> wrote:
> > >
> > > On Thu, Jun 20, 2024 at 7:30 PM jian he <jian.universality@gmail.com> wrote:
> > >>
> > >> "predicate check expressions return the single three-valued result of
> > >>
> > >> 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"
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.
On Fri, Jun 21, 2024 at 10:30:08AM +0800, jian he wrote: > hi. > ------------- > 9.16.2.1.1. Boolean Predicate Check Expressions > As an extension to the SQL standard, a PostgreSQL path expression can > be a Boolean predicate, whereas the SQL standard allows predicates > only within filters. 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. For example, we could write this SQL-standard > filter expression: > > ------------- > slight inconsistency, "SQL-standard" versus "SQL standard" > "path expression can be a Boolean predicate", why capital "Boolean"? I think "SQL-standard" is used with the dash above because it is an adjective, and without the dash, it might be understood as "SQL standard-path" vs. "SQL-standard path". There aren't clear rules on when to add the dash, but when it can be misread, a dash is often added. -- Bruce Momjian <bruce@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?"
On Thu, Oct 17, 2024 at 9:59 PM Bruce Momjian <bruce@momjian.us> wrote: > > > > > > >> 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. > - <literal>false</literal>, or <literal>unknown</literal>. + <literal>false</literal>, or <literal>NULL</literal>. nearby are all examples related to jsonb_path_query. As mentioned before, jsonb_path_query returns JSON null. so change to + <literal>false</literal>, or <literal>null</literal> would be better. since we can select 'null'::jsonb; but cannot select 'NULL'::jsonb;
Em qui., 17 de out. de 2024 às 13:31, Bruce Momjian <bruce@momjian.us> escreveu:
Oh, okay, but I think we need to say JSON null so we are clear --- patch
But true, false and null are all JSON, since you cannot do
select jsonb_path_query('{}'::jsonb,'$.track.segments[*].HR > 130') = true;
So, it would be better to be clear that all possible returned values are JSON, no ?
regards
Marcos
On Fri, Oct 18, 2024 at 2:05 AM Bruce Momjian <bruce@momjian.us> wrote:
>
> On Thu, Oct 17, 2024 at 02:47:57PM -0300, Marcos Pegoraro wrote:
> > Em qui., 17 de out. de 2024 às 13:31, Bruce Momjian <bruce@momjian.us>
> > escreveu:
> >
> > Oh, okay, but I think we need to say JSON null so we are clear --- patch
> >
> >
> > But true, false and null are all JSON, since you cannot do
> > select jsonb_path_query('{}'::jsonb,'$.track.segments[*].HR > 130') = true;
>
> Obviously, I was confused then. This confirms the result is JSONB:
>
> SELECT pg_typeof(jsonb_path_query('{}'::jsonb,'$.track.segments[*].HR > 130'));
> pg_typeof
> -----------
> jsonb
>
> > So, it would be better to be clear that all possible returned values are JSON,
> > no ?
>
> Yes, updated patch attached.
>
looks good.
in the meantime, do you think it's necessary to slightly rephrase
jsonb_path_match doc entry:
currently doc entry:
jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent
boolean ]] ) → boolean
Returns the result of a JSON path predicate check for the specified JSON value.
"the result of a JSON path predicate check for the specified JSON
value." is a jsonb boolean.
but jsonb_path_match returns sql boolean.
maybe add something to describe case like: "if JSON path predicate
check return jsonb null, jsonb_path_match will return SQL null".
On Thu, Oct 31, 2024 at 11:51 PM Bruce Momjian <bruce@momjian.us> wrote: > > On Fri, Oct 18, 2024 at 10:00:54AM +0800, jian he wrote: > > On Fri, Oct 18, 2024 at 2:05 AM Bruce Momjian <bruce@momjian.us> wrote: > > > Yes, updated patch attached. > > > > > looks good. > > > > in the meantime, do you think it's necessary to slightly rephrase > > jsonb_path_match doc entry: > > > > currently doc entry: > > jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent > > boolean ]] ) → boolean > > Returns the result of a JSON path predicate check for the specified JSON value. > > > > > > "the result of a JSON path predicate check for the specified JSON > > value." is a jsonb boolean. > > but jsonb_path_match returns sql boolean. > > maybe add something to describe case like: "if JSON path predicate > > check return jsonb null, jsonb_path_match will return SQL null". > > Yes, I think that is a good point, updated patch attached. > played with https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-FILTER-EX-TABLE The patch looks good to me.
On Tue, Nov 5, 2024 at 05:24:07PM +0800, jian he wrote: > On Thu, Oct 31, 2024 at 11:51 PM Bruce Momjian <bruce@momjian.us> wrote: > > > > On Fri, Oct 18, 2024 at 10:00:54AM +0800, jian he wrote: > > > On Fri, Oct 18, 2024 at 2:05 AM Bruce Momjian <bruce@momjian.us> wrote: > > > > Yes, updated patch attached. > > > > > > > looks good. > > > > > > in the meantime, do you think it's necessary to slightly rephrase > > > jsonb_path_match doc entry: > > > > > > currently doc entry: > > > jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent > > > boolean ]] ) → boolean > > > Returns the result of a JSON path predicate check for the specified JSON value. > > > > > > > > > "the result of a JSON path predicate check for the specified JSON > > > value." is a jsonb boolean. > > > but jsonb_path_match returns sql boolean. > > > maybe add something to describe case like: "if JSON path predicate > > > check return jsonb null, jsonb_path_match will return SQL null". > > > > Yes, I think that is a good point, updated patch attached. > > > > played with > https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-FILTER-EX-TABLE > > The patch looks good to me. Patch applied back to PG 17. -- Bruce Momjian <bruce@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?"