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

From: PG Bug reporting form <[email-masked]>
Date: Thu, 26 Feb 2026 09:57:50 +0000

The following bug has been logged on the website:

Bug reference:      19418
Logged by:          Lukas Eder
Email address:      [email-masked]
PostgreSQL version: 18.2
Operating system:   Linux
Description:        

When using the ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor by query>
syntax, 6.34 GR 4) b) i) says that empty tables should produce a JSON array
with no elements (intuitively), not NULL.

Try this:

  select json_array(select 1 where false);

It produces NULL, not []

---

From: Vik Fearing <[email-masked]>
Date: Thu, 26 Feb 2026 15:20:08 +0100

>    select json_array(select 1 where false);

I can confirm that postgres violates the standard here.

-- 

Vik Fearing

---

From: Richard Guo <[email-masked]>
Date: Fri, 27 Feb 2026 23:44:20 +0900

> 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

---

From: Richard Guo <[email-masked]>
Date: Mon, 2 Mar 2026 14:09:46 +0900

>     JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]),

The attached patch seems to fix it.

- Richard

---

From: Richard Guo <[email-masked]>
Date: Mon, 2 Mar 2026 14:45:03 +0900

> 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

---

From: jian he <[email-masked]>
Date: Tue, 3 Mar 2026 10:41:47 +0800

> I can confirm that postgres violates the standard here.

Since the subject title mentioned JSON_VALUE.

SELECT JSON_VALUE(((select NULL where false)), '$');
SELECT JSON_QUERY(((select NULL where false)), '$');
SELECT JSON_EXISTS(((select NULL where false)), '$');

Should the above produce []?

--
jian
https://www.enterprisedb.com/

---

From: Amit Langote <[email-masked]>
Date: Mon, 20 Apr 2026 17:29:00 +0900

> Should the above produce []?

AFAIK about the standard, no. The empty-set -> '[]' rule is specific
to JSON_ARRAY(<query>), whose job is to collect rows into an array.
JSON_VALUE, JSON_QUERY, and JSON_EXISTS return a scalar, a JSON value,
and a boolean, respectively, not array-shaped values, so there's no
empty-array concept to invoke; empty-input behavior is governed by ON
EMPTY / ON ERROR.

-- 
Thanks, Amit Langote