On Jun 15, 2024, at 12:23, Chapman Flack <jcflack@acm.org> wrote:
> I see. Yes, that documentation now says "predicate check expressions return
> the single three-valued result of the predicate: true, false, or unknown".
It has been there since jsonpath was introduced in v12[1]:
> A path expression can be a Boolean predicate, although the SQL/JSON standard allows predicates only in filters. This
isnecessary for implementation of the @@ operator. For example, the following jsonpath expression is valid in
PostgreSQL:
>
> '$.track.segments[*].HR < 70'
> (Aside: are all readers of the docs assumed to have learned the habit
> of calling SQL null "unknown" when speaking of a boolean? They can flip
> back to 8.6 Boolean Type and see 'a third state, “unknown”, which is
> represented by the SQL null value'. But would it save them some page
> flipping to add " (represented by SQL null)" to the sentence here?)
In 9.16.2[2] it says:
> The unknown value plays the same role as SQL NULL and can be tested for with the is unknown predicate.
> As Unknown is typically what the predicates return within a filter (where
> errors get trapped) when an error has occurred, the existing docs seem to
> suggest they behave the same way in a "predicate check expression", so a
> change to that behavior now would be a change to what we've documented.
It’s reasonable to ask, then, whether `starts with` and `like_regex` are correct and the others shouldn’t throw errors
inpredicate check expressions, yes. I don’t know the answer, but would like it to be consistent.
> Can't really overload jsonb_path_query's 'silent' parameter for that,
> because it is already false by default. If predicate check expressions
> were nonsilent by default, the existing 'silent' parameter would be a
> perfect way to silence them.
I think that’s how it should be; I prefer that it raises errors by default but you can silence them:
david=# select jsonb_path_query(target => '{"x": "hi"}', path => '$.integer()', silent => false);
ERROR: jsonpath item method .integer() can only be applied to a string or numeric value
david=# select jsonb_path_query(target => '{"x": "hi"}', path => '$.integer()', silent => true);
jsonb_path_query
------------------
(0 rows)
I suggest that the same behavior be adopted for `like_regex` and `starts with`.
> No appetite to add yet another optional boolean parameter to
> jsonb_path_query for the sole purpose of controlling the silence of
> our nonstandard syntax extension ....
You don’t need it IMO, the existing silent parameter already does it existing error-raising operators.
Best,
David
[1]: https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH
[2]: https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH