On Fri, Jun 21, 2024 at 11:11 AM David G. Johnston <david.g.johns...@gmail.com> wrote: > > On Thu, Jun 20, 2024 at 7:30 PM jian he <jian.universal...@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