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

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