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

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.

After a second look at this approach, I don't like it very much. It
manually constructed the new querytree, including Aggref,
RangeTblEntry, and JsonConstructorExpr nodes, during planning,
bypassing parse analysis entirely. This is essentially repeating the
parser's work by hand in the planner, which is fragile and prone to
failing to handle all cases correctly.

Maybe a simpler way is to keep the JSON_ARRAYAGG rewrite trick in the
parser, as the current master does, but wrap the result in a COALESCE
to handle the empty-set case. We can preserve a copy of the user's
original subquery in a new field of JsonConstructorExpr, and then
ruleutils.c can use this field to deparse the original
JSON_ARRAY(SELECT ...) syntax for view definitions. You may think
this would introduce extra transform work, but it wouldn't: the
current master already transforms the original subquery to validate
the single-column constraint, then throws the result away. We simply
keep it instead.

I tried this idea and ended up with the attached.

- Richard

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

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 []?

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

Hi Richard,

On Thu, Apr 16, 2026 at 3:05 PM Richard Guo <guofenglinux@gmail.com> wrote:

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.

After a second look at this approach, I don't like it very much. It
manually constructed the new querytree, including Aggref,
RangeTblEntry, and JsonConstructorExpr nodes, during planning,
bypassing parse analysis entirely. This is essentially repeating the
parser's work by hand in the planner, which is fragile and prone to
failing to handle all cases correctly.

Maybe a simpler way is to keep the JSON_ARRAYAGG rewrite trick in the
parser, as the current master does, but wrap the result in a COALESCE
to handle the empty-set case. We can preserve a copy of the user's
original subquery in a new field of JsonConstructorExpr, and then
ruleutils.c can use this field to deparse the original
JSON_ARRAY(SELECT ...) syntax for view definitions. You may think
this would introduce extra transform work, but it wouldn't: the
current master already transforms the original subquery to validate
the single-column constraint, then throws the result away. We simply
keep it instead.

I tried this idea and ended up with the attached.

Agreed that v4 is the better direction.

A couple of minor nits.

The comment on orig_query could say "not walked" a bit more helpfully, e.g.

Node *orig_query; /* for deparse only; not walked (func is) */

I also noticed that the comment for 'func' is incomplete as it is and
this change warrants an update. Maybe a bit long, but how about:

Expr *func; /* expression producing the result:
* Aggref/WindowFunc for *AGG,
* CoalesceExpr for ARRAY_QUERY,
* json[b]_xxx() call for remaining types */

--
Thanks, Amit Langote

On Mon, Apr 20, 2026 at 6:05 PM Amit Langote <amitlangote09@gmail.com> wrote:

Agreed that v4 is the better direction.

Thanks for review!

The comment on orig_query could say "not walked" a bit more helpfully, e.g.

Node *orig_query; /* for deparse only; not walked (func is) */

Sounds good.

I also noticed that the comment for 'func' is incomplete as it is and
this change warrants an update. Maybe a bit long, but how about:

Expr *func; /* expression producing the result:
* Aggref/WindowFunc for *AGG,
* CoalesceExpr for ARRAY_QUERY,
* json[b]_xxx() call for remaining types */

It seems that func is NULL for "remaining types". How about we go
with:

Expr *func; /* executable expression:
* Aggref/WindowFunc for *AGG,
* CoalesceExpr for ARRAY_QUERY,
* NULL for other types (executor calls
* underlying json[b]_xxx() functions) */

(maybe we should place the multi-line comment above the field.)

- Richard

On Tue, Apr 21, 2026 at 9:57 AM Richard Guo <guofenglinux@gmail.com> wrote:

On Mon, Apr 20, 2026 at 6:05 PM Amit Langote <amitlangote09@gmail.com> wrote:

Agreed that v4 is the better direction.

Thanks for review!

Another question I'd like to raise: is it OK to commit this patch to
master given that feature freeze has passed? I think the answer is
yes, because this is arguably a bug fix rather than a new feature.
However, it does change user-visible behavior, and existing app code
that relies on the NULL behavior would break. So if we commit it, we
need to add in the release notes about this incompatibility.

Thoughts?

- Richard

Richard Guo <guofenglinux@gmail.com> writes:

Another question I'd like to raise: is it OK to commit this patch to
master given that feature freeze has passed? I think the answer is
yes, because this is arguably a bug fix rather than a new feature.
However, it does change user-visible behavior, and existing app code
that relies on the NULL behavior would break. So if we commit it, we
need to add in the release notes about this incompatibility.

Well, if we definitely intend to commit a compatibility-breaking
change, I think it's better to commit it sooner not later. If we
wait till v20, all we accomplish is to give users another year to
write code that depends on the old behavior.

However, usually at this stage of the cycle the answer to such
questions is "let the RMT decide". Take the question to them
(cc'd).

regards, tom lane

On Tue, Apr 21, 2026 at 11:30 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Richard Guo <guofenglinux@gmail.com> writes:

Another question I'd like to raise: is it OK to commit this patch to
master given that feature freeze has passed? I think the answer is
yes, because this is arguably a bug fix rather than a new feature.
However, it does change user-visible behavior, and existing app code
that relies on the NULL behavior would break. So if we commit it, we
need to add in the release notes about this incompatibility.

Well, if we definitely intend to commit a compatibility-breaking
change, I think it's better to commit it sooner not later. If we
wait till v20, all we accomplish is to give users another year to
write code that depends on the old behavior.

However, usually at this stage of the cycle the answer to such
questions is "let the RMT decide". Take the question to them
(cc'd).

Thanks Tom for the suggestion.

Hi RMT,

I'd like to commit a fix for JSON_ARRAY(subquery) behavior that
involves a user-visible incompatibility, and would appreciate your
go/no-go since we're past feature freeze.

Summary:

- JSON_ARRAY(SELECT ...) currently returns NULL over an empty result
set, but the SQL/JSON standard requires it to return '[]'. Fixing
this changes user-visible output.

- The same patch also fixes a deparsing issue: views defined with
JSON_ARRAY(SELECT ...) are dumped back as the internal JSON_ARRAYAGG
rewrite instead of the original syntax.

- Richard

On Tue, Apr 21, 2026 at 9:57 AM Richard Guo <guofenglinux@gmail.com> wrote:

On Mon, Apr 20, 2026 at 6:05 PM Amit Langote <amitlangote09@gmail.com> wrote:

Agreed that v4 is the better direction.

Thanks for review!

The comment on orig_query could say "not walked" a bit more helpfully, e.g.

Node *orig_query; /* for deparse only; not walked (func is) */

Sounds good.

I also noticed that the comment for 'func' is incomplete as it is and
this change warrants an update. Maybe a bit long, but how about:

Expr *func; /* expression producing the result:
* Aggref/WindowFunc for *AGG,
* CoalesceExpr for ARRAY_QUERY,
* json[b]_xxx() call for remaining types */

It seems that func is NULL for "remaining types". How about we go
with:

Expr *func; /* executable expression:
* Aggref/WindowFunc for *AGG,
* CoalesceExpr for ARRAY_QUERY,
* NULL for other types (executor calls
* underlying json[b]_xxx() functions) */

Right.

(maybe we should place the multi-line comment above the field.)

Makes sense. Perhaps we should also move the description of individual
fields, where needed, into the comment above the struct definition
like it is done for the nearby JsonValueExpr. Like this:

/*
* JsonConstructorExpr -
* wrapper over FuncExpr/Aggref/WindowFunc/CoalesceExpr for SQL/JSON
* constructors
*
* func is the executable expression:
* - Aggref/WindowFunc for JSON_OBJECTAGG/JSON_ARRAYAGG,
* - CoalesceExpr for JSON_ARRAY(query),
* - NULL for other types (the executor calls the underlying json[b]_xxx()
* function directly).
*
* orig_query holds the user's original subquery for JSON_ARRAY(query),
* used only by ruleutils.c for deparsing; it is not walked because func
* is authoritative for all other purposes.
*/
typedef struct JsonConstructorExpr
{
Expr xpr;
JsonConstructorType type; /* constructor type */
List *args;
Expr *func; /* executable expression or NULL */
Node *orig_query; /* original subquery for deparsing */
Expr *coercion; /* coercion to RETURNING type */
JsonReturning *returning; /* RETURNING clause */
bool absent_on_null; /* ABSENT ON NULL? */
bool unique; /* WITH UNIQUE KEYS? (JSON_OBJECT[AGG] only) */
ParseLoc location;
} JsonConstructorExpr;

--
Thanks, Amit Langote

On Tue, Apr 21, 2026 at 12:57 PM Amit Langote <amitlangote09@gmail.com> wrote:

On Tue, Apr 21, 2026 at 9:57 AM Richard Guo <guofenglinux@gmail.com> wrote:

(maybe we should place the multi-line comment above the field.)

Makes sense. Perhaps we should also move the description of individual
fields, where needed, into the comment above the struct definition
like it is done for the nearby JsonValueExpr. Like this:

This looks even better. I will take this approach. Thanks!

- Richard