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

On 26/02/2026 10:57, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 19418
Logged by: Lukas Eder
Email address: lukas.eder@gmail.com
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 []

I can confirm that postgres violates the standard here.

--

Vik Fearing

On Thu, Feb 26, 2026 at 11:20 PM Vik Fearing <vik@postgresfriends.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

On Fri, Feb 27, 2026 at 11:44 PM 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.

- Richard

On Mon, Mar 2, 2026 at 2:09 PM Richard Guo <guofenglinux@gmail.com> wrote:

On Fri, Feb 27, 2026 at 11:44 PM 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 Á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

Richard Guo <guofenglinux@gmail.com> writes:

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.

Okay, but ...

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.

... doesn't that point disqualify it from being back-patched?
People don't like unprompted behavioral changes in minor releases.
"This is what the standard says" is not strong enough to justify
changing behavior that was not obviously broken (like, say, crashing).

Another point is that the previous coding already failed to
be round-trippable, ie you wrote JSON_ARRAY() but what comes
out in view decompilation is JSON_ARRAYAGG(). This makes that
situation considerably worse. We should endeavor to not expose
implementation details like that. (To be clear, I don't object
if EXPLAIN shows that sort of thing. But it shouldn't creep
into view dumps. We've regretted doing that in the past.)

regards, tom lane

On Mon, Mar 2, 2026 at 3:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

... doesn't that point disqualify it from being back-patched?
People don't like unprompted behavioral changes in minor releases.
"This is what the standard says" is not strong enough to justify
changing behavior that was not obviously broken (like, say, crashing).

Fair point. Changing user-facing output is not something we want to
surprise users with in a minor release. So this will be a master-only
fix.

Another point is that the previous coding already failed to
be round-trippable, ie you wrote JSON_ARRAY() but what comes
out in view decompilation is JSON_ARRAYAGG(). This makes that
situation considerably worse. We should endeavor to not expose
implementation details like that. (To be clear, I don't object
if EXPLAIN shows that sort of thing. But it shouldn't creep
into view dumps. We've regretted doing that in the past.)

That is a good point I hadn't considered. So I think the ideal fix is
to have the parser preserve the user's original JSON_ARRAY(query)
syntax as much as possible, and then defer the JSON_ARRAYAGG rewrite
trick to the planner, perhaps during expression preprocessing.

- Richard

On Thu, Feb 26, 2026 at 10:20 PM Vik Fearing <vik@postgresfriends.org> wrote:

Try this:

select json_array(select 1 where false);

It produces NULL, not []

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/

That was a typo. I meant to refer to JSON_ARRAY, not JSON_VALUE in the
subject title. The issue is specifically about <JSON array constructor by
query> (I also made a typo there, sorry for that)

On Tue, Mar 3, 2026 at 3:42 AM jian he <jian.universality@gmail.com> wrote:

On Tue, Mar 3, 2026 at 10:03 AM Richard Guo <guofenglinux@gmail.com> wrote:

That is a good point I hadn't considered. So I think the ideal fix is
to have the parser preserve the user's original JSON_ARRAY(query)
syntax as much as possible, and then defer the JSON_ARRAYAGG rewrite
trick to the planner, perhaps during expression preprocessing.

I tried hacking on this idea to see how it would look in practice, and
here is what I got.

- Richard

On Tue, Mar 3, 2026 at 11:32 PM Richard Guo <guofenglinux@gmail.com> wrote:

On Tue, Mar 3, 2026 at 10:03 AM Richard Guo <guofenglinux@gmail.com> wrote:

That is a good point I hadn't considered. So I think the ideal fix is
to have the parser preserve the user's original JSON_ARRAY(query)
syntax as much as possible, and then defer the JSON_ARRAYAGG rewrite
trick to the planner, perhaps during expression preprocessing.

I tried hacking on this idea to see how it would look in practice, and
here is what I got.

Here is an updated version of the patch. The main change is that it
now uses DirectFunctionCall1 to build the empty JSON array constant,
which is more efficient and consistent with other call sites.

- Richard