Horton

Thread

Commits

  1. Enforce RETURNING typmod for empty-set JSON_ARRAY(query)

  2. Fix JSON_ARRAY(query) empty set handling and view deparsing

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

    The Post Office <noreply@postgresql.org> — 2026-02-26T09:57:50Z

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

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

    Vik Fearing <vik@postgresfriends.org> — 2026-02-26T14:20:08Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-02-27T14:44:20Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-03-02T05:09:46Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-03-02T05:45:03Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-02T06:37:29Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-03-03T01:03:49Z

    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

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

    jian he <jian.universality@gmail.com> — 2026-03-03T02:41:47Z

    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/

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

    Lukas Eder <lukas.eder@gmail.com> — 2026-03-03T07:16:49Z

    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 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/
>

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

    Richard Guo <guofenglinux@gmail.com> — 2026-03-03T14:32:12Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-03-04T06:35:56Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-04-16T06:04:49Z

    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

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

    Amit Langote <amitlangote09@gmail.com> — 2026-04-20T08:29:00Z

    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

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

    Amit Langote <amitlangote09@gmail.com> — 2026-04-20T09:05:33Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-04-21T00:57:34Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-04-21T01:12:55Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-21T02:30:32Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-04-21T03:20:02Z

    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

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

    Amit Langote <amitlangote09@gmail.com> — 2026-04-21T03:57:12Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-04-21T05:45:01Z

    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

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

    Richard Guo <guofenglinux@gmail.com> — 2026-04-23T00:55:10Z

    On Tue, Apr 21, 2026 at 12:20 PM Richard Guo <guofenglinux@gmail.com> wrote:
>
> 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.

Not sure the RMT mailing list works or not, maybe I'd better CC RMT
members.

- Richard

> 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

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

    Michael Paquier <michael@paquier.xyz> — 2026-04-23T06:51:11Z

    On Thu, Apr 23, 2026 at 09:55:10AM +0900, Richard Guo wrote:
> Not sure the RMT mailing list works or not, maybe I'd better CC RMT
> members.

The mailing list you have attached is used every year by the RMT
members for their internal discussions, attaching it to a
pgsql-hackers or pgsql-bugs thread is not the usual practice, so I
would suggest to avoid that in the future.  Adding the RMT members
directly in CC is enough if input is required for an open item.
--
Michael

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

    Richard Guo <guofenglinux@gmail.com> — 2026-04-24T12:04:03Z

    On Thu, Apr 23, 2026 at 3:51 PM Michael Paquier <michael@paquier.xyz> wrote:
> On Thu, Apr 23, 2026 at 09:55:10AM +0900, Richard Guo wrote:
> > Not sure the RMT mailing list works or not, maybe I'd better CC RMT
> > members.

> The mailing list you have attached is used every year by the RMT
> members for their internal discussions, attaching it to a
> pgsql-hackers or pgsql-bugs thread is not the usual practice, so I
> would suggest to avoid that in the future.  Adding the RMT members
> directly in CC is enough if input is required for an open item.

Thanks for the information.  Will avoid that in the future.  Since the
RMT members are already in CC, I'll wait for their feedback.

- Richard

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

    Nathan Bossart <nathandbossart@gmail.com> — 2026-04-24T19:58:25Z

    On Thu, Apr 23, 2026 at 09:55:10AM +0900, Richard Guo wrote:
>> 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.

I am fine with committing this for v19, assuming there is consensus on the
patch content.

Heikki/Melanie: Any objections?

-- 
nathan

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

    Melanie Plageman <melanieplageman@gmail.com> — 2026-04-30T22:04:54Z

    On Fri, Apr 24, 2026 at 3:58 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> On Thu, Apr 23, 2026 at 09:55:10AM +0900, Richard Guo wrote:
> >> 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.
>
> I am fine with committing this for v19, assuming there is consensus on the
> patch content.
>
> Heikki/Melanie: Any objections?

Nope. I'm +1 on committing this now. 19 is unreleased -- I see no
reason to wait a year.

- Melanie

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

    Richard Guo <guofenglinux@gmail.com> — 2026-05-01T00:26:32Z

    On Fri, May 1, 2026 at 7:05 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:
> On Fri, Apr 24, 2026 at 3:58 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
> > On Thu, Apr 23, 2026 at 09:55:10AM +0900, Richard Guo wrote:
> > >> 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.

> > I am fine with committing this for v19, assuming there is consensus on the
> > patch content.
> >
> > Heikki/Melanie: Any objections?

> Nope. I'm +1 on committing this now. 19 is unreleased -- I see no
> reason to wait a year.

Thank you, Nathan and Melanie.  I now have two of the three RMT
approvals, so I believe I'm good to go.  Will commit this shortly.

- Richard

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

    Richard Guo <guofenglinux@gmail.com> — 2026-05-01T01:23:28Z

    On Fri, May 1, 2026 at 9:26 AM Richard Guo <guofenglinux@gmail.com> wrote:
> Thank you, Nathan and Melanie.  I now have two of the three RMT
> approvals, so I believe I'm good to go.  Will commit this shortly.

Committed.

- Richard

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

    Ayush Tiwari <ayushtiwari.slg01@gmail.com> — 2026-05-07T14:44:52Z

    Hi,


On Fri, 1 May 2026 at 06:53, Richard Guo <guofenglinux@gmail.com> wrote:

> On Fri, May 1, 2026 at 9:26 AM Richard Guo <guofenglinux@gmail.com> wrote:
> > Thank you, Nathan and Melanie.  I now have two of the three RMT
> > approvals, so I believe I'm good to go.  Will commit this shortly.
>
> Committed.
>

While looking at the JSON_ARRAY(query) empty-set fix, I noticed what
looks like a typmod issue in the new empty-array fallback.

I understand from this discussion that returning [] for an empty
JSON_ARRAY(query) input is intentional and required by SQL/JSON.  This
report is
about the RETURNING typmod not being enforced on that new [] fallback.

The non-empty query form enforces the RETURNING typmod:

  SELECT JSON_ARRAY(SELECT 1 RETURNING varchar(1));
  ERROR:  value too long for type character varying(1)

but the empty query form returns a value that does not fit the declared
type:

  SELECT JSON_ARRAY(SELECT 1 WHERE false RETURNING varchar(1));
   json_array
  ------------
   []
  (1 row)

The same inconsistency is visible through a view: the column is stored as
varchar(1), and pg_get_viewdef() shows RETURNING character varying(1), but
executing the view can still return the two-character value [].

The issue appears to be in transformJsonArrayQueryConstructor(): the
COALESCE
fallback builds the empty-array constant with typmod -1, and later
eval_const_expressions() replaces JSCTOR_JSON_ARRAY_QUERY with this
pre-built
func expression.  At that point the JsonConstructorExpr wrapper's RETURNING
typmod is no longer enough to enforce varchar(1).

I think the right fix is probably to make the executable expression stored
in
func carry the RETURNING typmod coercion.  This would also match the
direction
sketched earlier in the thread, where the fallback was described as
'[]'::[RETURNING_TYPE].  For example, coerceJsonFuncExpr() could notice
same-type/different-typmod cases, and transformJsonArrayQueryConstructor()
could
apply it to the COALESCE expression before storing that expression in the
JSCTOR_JSON_ARRAY_QUERY node.

Regards,
Ayush

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

    Richard Guo <guofenglinux@gmail.com> — 2026-05-08T08:35:27Z

    On Thu, May 7, 2026 at 11:45 PM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:
> The non-empty query form enforces the RETURNING typmod:
>
>   SELECT JSON_ARRAY(SELECT 1 RETURNING varchar(1));
>   ERROR:  value too long for type character varying(1)
>
> but the empty query form returns a value that does not fit the declared type:
>
>   SELECT JSON_ARRAY(SELECT 1 WHERE false RETURNING varchar(1));
>    json_array
>   ------------
>    []
>   (1 row)

Nice catch.  The empty-array Const was built with typmod -1, and the
type input function was invoked with typmod -1, so any RETURNING
length restriction was silently bypassed.

I've pushed a fix that builds Const and calls the input function with
the typmod of the non-empty COALESCE argument.

- Richard