Re: ON ERROR in json_query and the like

On Tue, May 28, 2024 at 5:29 PM Markus Winand <markus(dot)winand(at)winand(dot)at> wrote:
>
> Hi!
>
> I’ve noticed two “surprising” (to me) behaviors related to
> the “ON ERROR” clause of the new JSON query functions in 17beta1.
>
> 1. JSON parsing errors are not subject to ON ERROR
> 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 had a look on the list archive to see if that is intentional but
> frankly speaking these functions came a long way. In case it is
> intentional it might be worth adding a note to the docs.
>

json_query ( context_item, path_expression);

`SELECT JSON_QUERY('invalid', '$' NULL ON ERROR);`
to make this return NULL, that means to catch all the errors that
happened while context_item evaluation.
otherwise, it would not be consistent?

Currently context_item expressions can be quite arbitrary.
considering the following examples.

create or replace function test(jsonb) returns jsonb as $$ begin raise
exception 'abort'; end $$ language plpgsql;
create or replace function test1(jsonb) returns jsonb as $$ begin
return $1; end $$ language plpgsql;
SELECT JSON_VALUE(test('1'), '$');
SELECT JSON_VALUE(test1('1'), '$');
SELECT JSON_VALUE((select '1'::jsonb), '$');
SELECT JSON_VALUE((with cte(s) as (select '1') select s::jsonb from cte), '$');
SELECT JSON_VALUE((with cte(s) as (select '1') select s::jsonb from
cte union all select s::jsonb from cte limit 1), '$');

Currently, I don't think we can make
SELECT JSON_VALUE(test('1'), '$' null on error);
return NULL.