Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>

On Thu, Feb 26, 2026 at 11:20 PM Vik Fearing <vik(at)postgresfriends(dot)org> 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