MIME-Version: 1.0
References: <19418-591ba1f29862ef5b@postgresql.org> <2abdb464-27f5-4759-bb0b-f09ab5b5ceab@postgresfriends.org>
 <CAMbWs4_4Zc7O4pCU_nJU_8=Y2bOS3sEXJR=WH39Kc__UuaCW3w@mail.gmail.com> <CAMbWs4_f-4BqvfKTt0YbkOky+Gf0Mpi2kCT3CAE8brZFnK275Q@mail.gmail.com>
In-Reply-To: <CAMbWs4_f-4BqvfKTt0YbkOky+Gf0Mpi2kCT3CAE8brZFnK275Q@mail.gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 2 Mar 2026 14:45:03 +0900
Message-ID: <CAMbWs4-y4CEy66yA-Hh+6Snqbok3XTMFj5h87gUk_15Hhjd3qw@mail.gmail.com>
Subject: Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC
 9075-2:2023(E) 6.34 <JSON value constructor>
To: Vik Fearing <vik@postgresfriends.org>
Cc: lukas.eder@gmail.com, pgsql-bugs@lists.postgresql.org, 
	PG Bug reporting form <noreply@postgresql.org>, =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAMbWs4-y4CEy66yA-Hh%2B6Snqbok3XTMFj5h87gUk_15Hhjd3qw%40mail.gmail.com>
Precedence: bulk

On Mon, Mar 2, 2026 at 2:09=E2=80=AFPM Richard Guo <guofenglinux@gmail.com>=
 wrote:
> On Fri, Feb 27, 2026 at 11:44=E2=80=AFPM 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 =C3=81lvaro 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