Re: ON ERROR in json_query and the like

Hi,

On Mon, Jun 17, 2024 at 9:47 PM Chapman Flack <jcflack(at)acm(dot)org> wrote:
> On 06/17/24 02:20, Amit Langote wrote:
> >>> Apparently, the functions expect JSONB so that a cast is implied
> >>> when providing TEXT. However, the errors during that cast are
> >>> not subject to the ON ERROR clause.
> >>>
> >>> 17beta1=# SELECT JSON_QUERY('invalid', '$' NULL ON ERROR);
> >>> ERROR: invalid input syntax for type json
> >>> DETAIL: Token "invalid" is invalid.
> >>> CONTEXT: JSON data, line 1: invalid
> >>>
> >>> Oracle DB and Db2 (LUW) both return NULL in that case.
>
> I wonder, could prosupport rewriting be used to detect that the first
> argument is supplied by a cast, and rewrite the expression to apply the
> cast 'softly'? Or would that behavior be too magical?

I don't think prosupport rewriting can be used, because JSON_QUERY().

We could possibly use "runtime coercion" for context_item so that the
coercion errors can be "caught", which is how we coerce the jsonpath
result to the RETURNING type.

For now, I'm inclined to simply document the limitation that errors
when coercing string arguments to json are always thrown.

--
Thanks, Amit Langote