Re: minor doc issue in 9.16.2.1.1. Boolean Predicate Check Expressions
On Fri, Jun 21, 2024 at 11:11 AM David G. Johnston
wrote:
>
> On Thu, Jun 20, 2024 at 7:30 PM jian he 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
Re: minor doc issue in 9.16.2.1.1. Boolean Predicate Check Expressions
On Thu, Jun 20, 2024 at 7:30 PM jian he 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.
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.
