Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 - Mailing list pgsql-bugs

On Mon, Mar 2, 2026 at 2:09 PM Richard Guo <guofenglinux@gmail.com> wrote:
> On Fri, Feb 27, 2026 at 11:44 PM Richard Guo <guofenglinux@gmail.com> wrote:
> > I wonder if we can fix it by wrapping the JSON_ARRAYAGG in a COALESCE
> > to catch the NULL and convert it to an empty array; ie:
> >
> > SELECT COALESCE(
> >     JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]),
> >     '[]'::[RETURNING_TYPE]
> > ) FROM (query) q(a)

> The attached patch seems to fix it.

(cc-ing Álvaro who committed 7081ac46a)

Regarding back-patching, I believe this fix is safe to back-patch to
stable branches.  However, similar to a nearby bug fix, this will only
apply to newly created views.  Existing views will continue to exhibit
the old behavior until recreated.  Additionally, this changes the
user-facing output from NULL to [], so users may need to update any
application code that relied on the NULL behavior.

- Richard