Обсуждение: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34

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

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