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]>
Subject: Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
Date: Fri, 27 Feb 2026 23:44:20 +0900
Message-ID: <CAMbWs4_4Zc7O4pCU_nJU_8=Y2bOS3sEXJR=WH39Kc__UuaCW3w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

On Thu, Feb 26, 2026 at 11:20 PM Vik Fearing <[email protected]> wrote:
> On 26/02/2026 10:57, PG Bug reporting form wrote:
> > Try this:
> >
> >    select json_array(select 1 where false);
> >
> > It produces NULL, not []

> I can confirm that postgres violates the standard here.

It looks like postgres rewrites JSON_ARRAY(query) into JSON_ARRAYAGG()
internally:

explain (verbose, costs off)
select json_array(select 1 where false);
                    QUERY PLAN
---------------------------------------------------
 Result
   Output: (InitPlan expr_1).col1
   InitPlan expr_1
     ->  Aggregate
           Output: JSON_ARRAYAGG(1 RETURNING json)
           ->  Result
                 One-Time Filter: false
(7 rows)

The comment above transformJsonArrayQueryConstructor() says:

/*
 * Transform JSON_ARRAY(query [FORMAT] [RETURNING] [ON NULL]) into
 *  (SELECT  JSON_ARRAYAGG(a  [FORMAT] [RETURNING] [ON NULL]) FROM (query) q(a))
 */

Because of this transformation, we inherit standard aggregate
behavior: evaluating an aggregate over an empty set without a GROUP BY
yields NULL instead of the expected [].

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)

- 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]
  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_4Zc7O4pCU_nJU_8=Y2bOS3sEXJR=WH39Kc__UuaCW3w@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