Re: jsonpath: Missing regex_like && starts with Errors? - Mailing list pgsql-hackers

From David E. Wheeler
Subject Re: jsonpath: Missing regex_like && starts with Errors?
Date
Msg-id F789151A-85EF-4D72-81CB-49E5472B3A78@justatheory.com
Whole thread Raw
In response to Re: jsonpath: Missing regex_like && starts with Errors?  (Chapman Flack <jcflack@acm.org>)
Responses Re: jsonpath: Missing regex_like && starts with Errors?
List pgsql-hackers
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





pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: proposal: plpgsql, new check for extra_errors - strict_expr_check
Next
From: Tom Lane
Date:
Subject: Re: The content of the column_name field in the error response for a constraint violation