Re: ON ERROR in json_query and the like

On Tuesday, May 28, 2024, Markus Winand <markus(dot)winand(at)winand(dot)at> wrote:

>
> 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:
>
> 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.
>

The docs here don’t seem to cover the on empty clause at all nor fully
cover all options.

Where do you find the claim that the one implies the other? Is it a typo
that your examples says “implies null on empty” but the subject line says
“implies error on empty”?

Without those clauses a result is either empty or an error - they are
mutually exclusive (ignoring matches). I would not expect one clause to
imply or affect the behavior of the other. There is no chaining. The
original result is transformed to the new result specified by the clause.

I’d need to figure out whether the example you show is actually producing
empty or error; but it seems correct if the result is empty. The first
query ignores the error clause - the empty array row seems to be the
representation of empty here; the second one matches the empty clause and
outputs null instead of the empty array.

David J.