minor doc issue in 9.16.2.1.1. Boolean Predicate Check Expressions

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

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');

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 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.

in [1]https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-CHECK-EXPRESSIONS 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 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.

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 02:07:00PM +0800, jian he wrote:

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

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 <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;

On Thu, Oct 17, 2024 at 10:37:46PM +0800, jian he wrote:

-     <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;

Oh, okay, but I think we need to say JSON null so we are clear --- patch
attached.

--
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?"

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 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.

--
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 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 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.

--
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 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?"