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