Re: ON ERROR in json_query and the like - Mailing list pgsql-hackers

On Mon, Jun 17, 2024 at 10:07 PM Markus Winand <markus.winand@winand.at> wrote:
> > On 17.06.2024, at 08:20, Amit Langote <amitlangote09@gmail.com> 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.
> >
> > This behavior is a bug and result of an unintentional change that I
> > made at some point after getting involved with this patch set.  So I'm
> > going to fix this so that the empty results of jsonpath evaluation use
> > NULL ON EMPTY by default, ie, when the ON EMPTY clause is not present.
> > Attached a patch to do so.
> >
>
> Tested: works.

Pushed, thanks for testing.

I'll work on the documentation updates that may be needed based on
this and nearby discussion(s).

--
Thanks, Amit Langote