Hi, 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? Regards, -Chap