Re: ON ERROR in json_query and the like

> On 11.06.2024, at 03:58, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> 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.

This is not how it is meant. Your example is not subject to the ON ERROR
clause because the error happens in a sub-expression. My point is that
ON ERROR includes the String to JSON conversion (the JSON parsing) that
— in the way the standard describes these functions — inside of them.

In the standard, JSON_VALUE & co accept string types as well as the type JSON:

10.14 SR 1: The declared type of the <value expression> simply contained in the <JSON input expression> immediately contained in the <JSON context item> shall be a string type or a JSON type.

It might be best to think of it as two separate functions, overloaded:

JSON_VALUE(context_item JSONB, path_expression …)
JSON_VALUE(context_item TEXT, path_expression …)

Now if you do this:
create function test2(text) returns text as $$ begin
return $1; end $$ language plpgsql;
create function test3(text) returns jsonb as $$ begin
return $1::jsonb; end $$ language plpgsql;

SELECT JSON_VALUE(test2('invalid'), '$' null on error);
SELECT JSON_VALUE(test3('invalid'), '$' null on error);

The first query should return NULL, while the second should (and does) fail.

This is how I understand it.

-markus