On Tuesday, May 28, 2024, Markus Winand <
markus.winand@winand.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.
