public inbox for [email protected]  

help / color / mirror / Atom feed

From: Richard Guo <[email protected]>
To: Vik Fearing <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: PG Bug reporting form <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Subject: Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
Date: Mon, 2 Mar 2026 14:45:03 +0900
Message-ID: <CAMbWs4-y4CEy66yA-Hh+6Snqbok3XTMFj5h87gUk_15Hhjd3qw@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs4_f-4BqvfKTt0YbkOky+Gf0Mpi2kCT3CAE8brZFnK275Q@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAMbWs4_4Zc7O4pCU_nJU_8=Y2bOS3sEXJR=WH39Kc__UuaCW3w@mail.gmail.com>
	<CAMbWs4_f-4BqvfKTt0YbkOky+Gf0Mpi2kCT3CAE8brZFnK275Q@mail.gmail.com>

On Mon, Mar 2, 2026 at 2:09 PM Richard Guo <[email protected]> wrote:
> On Fri, Feb 27, 2026 at 11:44 PM Richard Guo <[email protected]> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
  In-Reply-To: <CAMbWs4-y4CEy66yA-Hh+6Snqbok3XTMFj5h87gUk_15Hhjd3qw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox