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.

previous versions require SQL/JSON query function's context_item to
explicitly cast to jsonb,
if it is not it will error out.

previous version the following query will have a error
select json_value(text '"1"' , 'strict $[*]' DEFAULT 9 ON ERROR);

now it only requires that (context_item) casting to jsonb successfully.
I raise this issue separately at [1]

[1] https://www.postgresql.org/message-id/CACJufxGWJTa-b0WjNH15otih42PA7SF%2Be7LbkAb0gThs7ojT5Q%40mail.gmail.com

> 2. EMPTY [ARRAY|OBJECT] ON ERROR implies ERROR ON EMPTY
>
> 17beta1=# SELECT JSON_QUERY('[]', '$[*]' EMPTY ARRAY ON ERROR) a;
> a
> ----
> []
> (1 row)
>
> As NULL ON EMPTY is implied, it should give the same result as
> explicitly adding NULL ON EMPTY:
>

I vaguely remember, we stumbled on ON ERROR, ON EMPTY several times.
i don't have a standard, but the doc seems not explicit enough for the
above example.

in json_query, maybe we can rephrase like:
--------------------
The ON EMPTY clause specifies the behavior if evaluating
path_expression yields no value at all. The default when ON EMPTY is
not specified
and ON ERROR not specified is to return a null value.

The ON ERROR clause specifies the behavior if an error occurs when
evaluating path_expression, including evaluation yields no value at
all and ON EMPTY is not specified, the operation to coerce the result
value to the output type, or during the execution of ON EMPTY behavior
(that is caused by empty result of path_expression evaluation). The
default when ON ERROR is not specified is to return a null value.

> 17beta1=# SELECT JSON_QUERY('[]', '$[*]' NULL ON EMPTY EMPTY ARRAY ON ERROR) a;
> a
> ---
>
> (1 row)
>
> Interestingly, Oracle DB gives the same (wrong) results. Db2 (LUW)
> on the other hand returns NULL for both queries.
>
> I don’t think that PostgreSQL should follow Oracle DB's suit here
> but again, in case this is intentional it should be made explicit
> in the docs.

`
SELECT JSON_QUERY('[]', '$[*]' NULL ON EMPTY EMPTY ARRAY ON ERROR) a;
`
I think these sentences addressed the above query.
<<<
or during the execution of ON EMPTY behavior (that is caused by empty
result of path_expression evaluation).
<<<
As you can see, in this context, "execution of ON EMPTY behavior"
works fine, successfully returned null,
so `EMPTY ARRAY ON ERROR` part was ignored.