public inbox for [email protected]help / color / mirror / Atom feed
Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> 18+ messages / 6 participants [nested] [flat]
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-03-02 06:37 Tom Lane <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Tom Lane @ 2026-03-02 06:37 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> Richard Guo <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-03-03 01:03 Richard Guo <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Richard Guo @ 2026-03-03 01:03 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> On Mon, Mar 2, 2026 at 3:37 PM Tom Lane <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-03-03 14:32 Richard Guo <[email protected]> parent: Richard Guo <[email protected]> 0 siblings, 2 replies; 18+ messages in thread From: Richard Guo @ 2026-03-03 14:32 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> On Tue, Mar 3, 2026 at 10:03 AM Richard Guo <[email protected]> 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 Attachments: [application/octet-stream] v2-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch (21.7K, 2-v2-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch) download | inline diff: From 99fe0f82702e89f692133561e59c6829ffd0558b Mon Sep 17 00:00:00 2001 From: Richard Guo <[email protected]> Date: Mon, 2 Mar 2026 12:11:00 +0900 Subject: [PATCH v2] Fix JSON_ARRAY(query) empty set handling and view deparsing According to the SQL/JSON standard, JSON_ARRAY(query) must return an empty JSON array ('[]') when the subquery returns zero rows. Previously, the parser rewrote JSON_ARRAY(query) into a JSON_ARRAYAGG aggregate function. Because this aggregate evaluates to NULL over an empty set without a GROUP BY clause, the constructor erroneously returned NULL. Additionally, this premature rewrite baked physical implementation details into the catalog, preventing ruleutils.c from deparsing the original syntax for views. This patch resolves both issues by preserving the user's semantic intent in the parsetree. A new JSCTOR_JSON_ARRAY_QUERY node type is introduced to identify query constructors. The parser now emits this node wrapping a standard EXPR_SUBLINK, allowing ruleutils.c to recognize and deparse the original syntax. The physical transformation is now deferred to the planner. During planning, the user's subquery is wrapped in an outer SELECT block, so that clauses like ORDER BY and LIMIT are evaluated before the JSON_ARRAYAGG aggregate is applied. A fully analyzed Aggref is then attached to this wrapper, and the entire construct is encapsulated in a COALESCE expression. This ensures that a NULL resulting from an empty set is caught and replaced with an empty JSON array. --- doc/src/sgml/func/func-json.sgml | 3 +- src/backend/optimizer/util/clauses.c | 176 +++++++++++++++++++++++++- src/backend/parser/parse_expr.c | 76 +++-------- src/backend/utils/adt/ruleutils.c | 20 +++ src/include/nodes/primnodes.h | 15 ++- src/test/regress/expected/sqljson.out | 92 +++++++++++++- src/test/regress/sql/sqljson.sql | 38 ++++++ 7 files changed, 351 insertions(+), 69 deletions(-) diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml index 1ec73cff464..d510ba8375b 100644 --- a/doc/src/sgml/func/func-json.sgml +++ b/doc/src/sgml/func/func-json.sgml @@ -620,7 +620,8 @@ which must be a SELECT query returning a single column. If <literal>ABSENT ON NULL</literal> is specified, NULL values are ignored. This is always the case if a - <replaceable>query_expression</replaceable> is used. + <replaceable>query_expression</replaceable> is used. If the query returns + no rows, an empty JSON array is returned. </para> <para> <literal>json_array(1,true,json '{"a":null}')</literal> diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index a41d81734cf..43827af2fe0 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -20,6 +20,7 @@ #include "postgres.h" #include "access/htup_details.h" +#include "catalog/pg_aggregate.h" #include "catalog/pg_class.h" #include "catalog/pg_language.h" #include "catalog/pg_operator.h" @@ -133,6 +134,7 @@ static Expr *simplify_function(Oid funcid, eval_const_expressions_context *context); static Node *simplify_aggref(Aggref *aggref, eval_const_expressions_context *context); +static Node *rewrite_json_array_subquery(JsonConstructorExpr *expr); static List *reorder_function_arguments(List *args, int pronargs, HeapTuple func_tuple); static List *add_function_defaults(List *args, int pronargs, @@ -3010,7 +3012,6 @@ eval_const_expressions_mutator(Node *node, } break; } - case T_JsonValueExpr: { JsonValueExpr *jve = (JsonValueExpr *) node; @@ -3034,7 +3035,18 @@ eval_const_expressions_mutator(Node *node, (Expr *) formatted_expr, copyObject(jve->format)); } + case T_JsonConstructorExpr: + { + JsonConstructorExpr *jce = (JsonConstructorExpr *) node; + if (jce->type == JSCTOR_JSON_ARRAY_QUERY) + { + Node *rewritten = rewrite_json_array_subquery(jce); + + return eval_const_expressions_mutator(rewritten, context); + } + } + break; case T_SubPlan: case T_AlternativeSubPlan: @@ -4378,6 +4390,168 @@ simplify_aggref(Aggref *aggref, eval_const_expressions_context *context) return (Node *) aggref; } +/* + * Subroutine for eval_const_expressions: transform a JSCTOR_JSON_ARRAY_QUERY + * expression into its executable form: + * + * COALESCE((SELECT JSON_ARRAYAGG(col) FROM (user_query)), '[]') + * + * This is done during planning rather than parse analysis so that ruleutils.c + * can deparse the original syntax for views. + * + * The user's query is wrapped in an outer SELECT so that clauses like ORDER BY + * and LIMIT are evaluated before aggregation. COALESCE ensures that we return + * an empty array rather than NULL on an empty result set, per the SQL/JSON + * standard. + */ +static Node * +rewrite_json_array_subquery(JsonConstructorExpr *expr) +{ + SubLink *sublink = linitial_node(SubLink, expr->args); + SubLink *newsublink; + Query *subquery; + Query *newquery; + List *colnames; + TargetEntry *tle; + RangeTblEntry *rte; + RangeTblRef *rtr; + Var *newvar; + JsonConstructorExpr *newjsctor; + Aggref *aggref; + Const *empty_json_const; + Oid aggfnoid; + Oid aggtype; + Oid typinput; + Oid typioparam; + CoalesceExpr *coalesce; + + Assert(expr->type == JSCTOR_JSON_ARRAY_QUERY); + Assert(sublink->subLinkType == EXPR_SUBLINK); + + newsublink = copyObject(sublink); + subquery = (Query *) newsublink->subselect; + + /* + * Since we are pushing the subquery down one level, we must increment the + * sublevels of any outer references it contains. + */ + IncrementVarSublevelsUp((Node *) subquery, 1, 1); + + /* Most of the upper Query struct can be left as zeroes/nulls */ + newquery = makeNode(Query); + newquery->commandType = CMD_SELECT; + newquery->querySource = subquery->querySource; + newquery->canSetTag = true; + + tle = linitial_node(TargetEntry, subquery->targetList); + colnames = list_make1(makeString(tle->resname ? + pstrdup(tle->resname) : + pstrdup("json_col"))); + + /* Build a suitable RTE for the subquery */ + rte = makeNode(RangeTblEntry); + rte->rtekind = RTE_SUBQUERY; + rte->subquery = subquery; + rte->eref = makeAlias("json_array_subquery", colnames); + rte->lateral = false; + rte->inh = false; + rte->inFromCl = true; + newquery->rtable = list_make1(rte); + + /* Build a suitable jointree for the subquery */ + rtr = makeNode(RangeTblRef); + rtr->rtindex = 1; + newquery->jointree = makeFromExpr(list_make1(rtr), NULL);; + + /* Create a Var referencing the subquery's single output column */ + newvar = makeVar(1, + tle->resno, + exprType((Node *) tle->expr), + exprTypmod((Node *) tle->expr), + exprCollation((Node *) tle->expr), + 0); + + /* + * Construct the JSON_ARRAYAGG aggregate node. We must manually resolve + * the function OID and build a fully analyzed Aggref because we are + * bypassing parse analysis. + */ + if (expr->returning && + expr->returning->format->format_type == JS_FORMAT_JSONB) + { + aggfnoid = expr->absent_on_null ? F_JSONB_AGG_STRICT : F_JSONB_AGG; + aggtype = JSONBOID; + } + else + { + aggfnoid = expr->absent_on_null ? F_JSON_AGG_STRICT : F_JSON_AGG; + aggtype = JSONOID; + } + + aggref = makeNode(Aggref); + aggref->aggfnoid = aggfnoid; + aggref->aggtype = aggtype; + aggref->aggcollid = InvalidOid; + aggref->inputcollid = exprCollation((Node *) tle->expr); + aggref->aggtranstype = InvalidOid; + aggref->aggargtypes = list_make1_oid(exprType((Node *) tle->expr)); + aggref->aggdirectargs = NIL; + aggref->args = list_make1(makeTargetEntry((Expr *) newvar, 1, NULL, false)); + aggref->aggorder = NIL; + aggref->aggdistinct = NIL; + aggref->aggfilter = NULL; + aggref->aggstar = false; + aggref->aggvariadic = false; + aggref->aggkind = AGGKIND_NORMAL; + aggref->aggpresorted = false; + aggref->agglevelsup = 0; + aggref->aggsplit = AGGSPLIT_SIMPLE; + aggref->aggno = -1; + aggref->aggtransno = -1; + aggref->location = expr->location; + + /* + * Attach the aggregate to the outer query's targetlist. The aggregate is + * wrapped in a JSCTOR_JSON_ARRAYAGG node to hold the RETURNING and ABSENT + * ON NULL formats from the original expression. + */ + newjsctor = makeNode(JsonConstructorExpr); + newjsctor->type = JSCTOR_JSON_ARRAYAGG; + newjsctor->args = NIL; + newjsctor->func = (Expr *) aggref; + newjsctor->coercion = copyObject(expr->coercion); + newjsctor->returning = copyObject(expr->returning); + newjsctor->absent_on_null = expr->absent_on_null; + newjsctor->unique = expr->unique; + newjsctor->location = expr->location; + + newquery->targetList = list_make1(makeTargetEntry((Expr *) newjsctor, + 1, + pstrdup("json_arrayagg"), + false)); + newquery->hasAggs = true; + + newsublink->subselect = (Node *) newquery; + + /* + * Wrap the entire construct in a COALESCE expression. If the inner query + * returns zero rows, the aggregate evaluates to NULL. We use COALESCE to + * return an empty JSON array instead. + */ + getTypeInputInfo(aggtype, &typinput, &typioparam); + empty_json_const = makeConst(aggtype, -1, InvalidOid, -1, + OidInputFunctionCall(typinput, "[]", typioparam, -1), + false, false); + + coalesce = makeNode(CoalesceExpr); + coalesce->coalescetype = aggtype; + coalesce->coalescecollid = InvalidOid; + coalesce->args = list_make2(newsublink, empty_json_const); + coalesce->location = expr->location; + + return (Node *) coalesce; +} + /* * var_is_nonnullable: check to see if the Var cannot be NULL * diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index dcfe1acc4c3..06a28ec5e15 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -3767,75 +3767,39 @@ transformJsonObjectConstructor(ParseState *pstate, JsonObjectConstructor *ctor) } /* - * Transform JSON_ARRAY(query [FORMAT] [RETURNING] [ON NULL]) into - * (SELECT JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]) FROM (query) q(a)) + * Transform JSON_ARRAY(subquery) constructor. + * + * JSON_ARRAY(subquery) is transformed into a JsonConstructorExpr node of type + * JSCTOR_JSON_ARRAY_QUERY. The result is coerced to the target type given by + * ctor->output. */ static Node * transformJsonArrayQueryConstructor(ParseState *pstate, JsonArrayQueryConstructor *ctor) { - SubLink *sublink = makeNode(SubLink); - SelectStmt *select = makeNode(SelectStmt); - RangeSubselect *range = makeNode(RangeSubselect); - Alias *alias = makeNode(Alias); - ResTarget *target = makeNode(ResTarget); - JsonArrayAgg *agg = makeNode(JsonArrayAgg); - ColumnRef *colref = makeNode(ColumnRef); + SubLink *sublink; Query *query; - ParseState *qpstate; - - /* Transform query only for counting target list entries. */ - qpstate = make_parsestate(pstate); - - query = transformStmt(qpstate, copyObject(ctor->query)); - - if (count_nonjunk_tlist_entries(query->targetList) != 1) - ereport(ERROR, - errcode(ERRCODE_SYNTAX_ERROR), - errmsg("subquery must return only one column"), - parser_errposition(pstate, ctor->location)); - - free_parsestate(qpstate); - - colref->fields = list_make2(makeString(pstrdup("q")), - makeString(pstrdup("a"))); - colref->location = ctor->location; - - /* - * No formatting necessary, so set formatted_expr to be the same as - * raw_expr. - */ - agg->arg = makeJsonValueExpr((Expr *) colref, (Expr *) colref, - ctor->format); - agg->absent_on_null = ctor->absent_on_null; - agg->constructor = makeNode(JsonAggConstructor); - agg->constructor->agg_order = NIL; - agg->constructor->output = ctor->output; - agg->constructor->location = ctor->location; - - target->name = NULL; - target->indirection = NIL; - target->val = (Node *) agg; - target->location = ctor->location; - - alias->aliasname = pstrdup("q"); - alias->colnames = list_make1(makeString(pstrdup("a"))); - - range->lateral = false; - range->subquery = ctor->query; - range->alias = alias; - - select->targetList = list_make1(target); - select->fromClause = list_make1(range); + JsonReturning *returning; + List *args; + sublink = makeNode(SubLink); sublink->subLinkType = EXPR_SUBLINK; sublink->subLinkId = 0; sublink->testexpr = NULL; sublink->operName = NIL; - sublink->subselect = (Node *) select; + sublink->subselect = (Node *) ctor->query; sublink->location = ctor->location; - return transformExprRecurse(pstate, (Node *) sublink); + sublink = (SubLink *) transformExprRecurse(pstate, (Node *) sublink); + + query = (Query *) sublink->subselect; + args = list_make1(linitial_node(TargetEntry, query->targetList)->expr); + returning = transformJsonConstructorOutput(pstate, ctor->output, args); + + return makeJsonConstructorExpr(pstate, JSCTOR_JSON_ARRAY_QUERY, + list_make1(sublink), NULL, returning, + false, ctor->absent_on_null, + ctor->location); } /* diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index f16f1535785..274d3e60d18 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -11740,6 +11740,26 @@ get_json_constructor(JsonConstructorExpr *ctor, deparse_context *context, get_json_agg_constructor(ctor, context, "JSON_ARRAYAGG", false); return; } + else if (ctor->type == JSCTOR_JSON_ARRAY_QUERY) + { + /* + * Bypass get_sublink_expr to avoid its hardcoded parentheses. We + * want JSON_ARRAY(SELECT ...), not JSON_ARRAY((SELECT ...)). + */ + + SubLink *sublink = linitial_node(SubLink, ctor->args); + + appendStringInfo(buf, "JSON_ARRAY("); + + get_query_def((Query *) sublink->subselect, buf, context->namespaces, + NULL, false, context->prettyFlags, context->wrapColumn, + context->indentLevel); + + get_json_constructor_options(ctor, buf); + appendStringInfoChar(buf, ')'); + + return; + } switch (ctor->type) { diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 384df50c80a..8435e76105b 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1713,13 +1713,14 @@ typedef struct JsonValueExpr typedef enum JsonConstructorType { - JSCTOR_JSON_OBJECT = 1, - JSCTOR_JSON_ARRAY = 2, - JSCTOR_JSON_OBJECTAGG = 3, - JSCTOR_JSON_ARRAYAGG = 4, - JSCTOR_JSON_PARSE = 5, - JSCTOR_JSON_SCALAR = 6, - JSCTOR_JSON_SERIALIZE = 7, + JSCTOR_JSON_OBJECT, + JSCTOR_JSON_ARRAY, + JSCTOR_JSON_ARRAY_QUERY, + JSCTOR_JSON_OBJECTAGG, + JSCTOR_JSON_ARRAYAGG, + JSCTOR_JSON_PARSE, + JSCTOR_JSON_SCALAR, + JSCTOR_JSON_SERIALIZE, } JsonConstructorType; /* diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index c7b9e575445..c7c6c6413e3 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -724,6 +724,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT [[{ "a" : 123 }]] (1 row) +-- JSON_ARRAY(subquery) SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i)); json_array ------------ @@ -757,6 +758,31 @@ SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE)); [true] (1 row) +-- JSON_ARRAY(subquery) with empty result set +SELECT JSON_ARRAY(SELECT 1 WHERE FALSE); + json_array +------------ + [] +(1 row) + +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4); + json_array +------------ + [] +(1 row) + +-- JSON_ARRAY(subquery) with a correlated subquery in the WHERE clause +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + a +--- + + 4 +(2 rows) + -- Should fail SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i)); ERROR: subquery must return only one column @@ -1093,7 +1119,7 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING QUERY PLAN --------------------------------------------------------------------- Result - Output: (InitPlan expr_1).col1 + Output: COALESCE((InitPlan expr_1).col1, '[]'::jsonb) InitPlan expr_1 -> Aggregate Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb) @@ -1105,9 +1131,67 @@ CREATE VIEW json_array_subquery_view AS SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb); \sv json_array_subquery_view CREATE OR REPLACE VIEW public.json_array_subquery_view AS - SELECT ( SELECT JSON_ARRAYAGG(q.a RETURNING jsonb) AS "json_arrayagg" - FROM ( SELECT foo.i - FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array" + SELECT JSON_ARRAY( SELECT foo.i + FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i) RETURNING jsonb) AS "json_array" +EXPLAIN (VERBOSE, COSTS OFF) +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); + QUERY PLAN +--------------------------------------------------------------------- + Result + Output: COALESCE((InitPlan expr_1).col1, '[]'::jsonb) + InitPlan expr_1 + -> Aggregate + Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb) + -> Limit + Output: "*VALUES*".column1 + -> Sort + Output: "*VALUES*".column1 + Sort Key: "*VALUES*".column1 + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1 +(12 rows) + +CREATE OR REPLACE VIEW json_array_subquery_view AS +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); +\sv json_array_subquery_view +CREATE OR REPLACE VIEW public.json_array_subquery_view AS + SELECT JSON_ARRAY( SELECT foo.i + FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i) + ORDER BY foo.i + LIMIT 3 RETURNING jsonb) AS "json_array" +DROP VIEW json_array_subquery_view; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + QUERY PLAN +----------------------------------------------------------------------- + Values Scan on "*VALUES*" + Output: "*VALUES*".column1 + Filter: (COALESCE((SubPlan expr_1), '[]'::jsonb) = '[]'::jsonb) + SubPlan expr_1 + -> Aggregate + Output: JSON_ARRAYAGG("*VALUES*_1".column1 RETURNING jsonb) + -> Values Scan on "*VALUES*_1" + Output: "*VALUES*_1".column1 + Filter: ("*VALUES*_1".column1 = "*VALUES*".column1) +(9 rows) + +CREATE VIEW json_array_subquery_view AS +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; +\sv json_array_subquery_view +CREATE OR REPLACE VIEW public.json_array_subquery_view AS + SELECT a + FROM ( VALUES (1), (2), (NULL::integer), (4)) t1(a) + WHERE JSON_ARRAY( SELECT t2.b + FROM ( VALUES (1), (2), (3)) t2(b) + WHERE t2.b = t1.a RETURNING jsonb) = '[]'::jsonb DROP VIEW json_array_subquery_view; -- IS JSON predicate SELECT NULL IS JSON; diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql index 343d344d270..1033bf4287a 100644 --- a/src/test/regress/sql/sqljson.sql +++ b/src/test/regress/sql/sqljson.sql @@ -193,6 +193,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING text)); SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text)); SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT JSON); +-- JSON_ARRAY(subquery) SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i)); SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i)); SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) RETURNING jsonb); @@ -201,6 +202,17 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL) SELECT JSON_ARRAY(SELECT i FROM (VALUES (3), (1), (NULL), (2)) foo(i) ORDER BY i); SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE)); +-- JSON_ARRAY(subquery) with empty result set +SELECT JSON_ARRAY(SELECT 1 WHERE FALSE); +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4); + +-- JSON_ARRAY(subquery) with a correlated subquery in the WHERE clause +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + -- Should fail SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i)); SELECT JSON_ARRAY(SELECT i, i FROM (VALUES (1)) foo(i)); @@ -384,6 +396,32 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING \sv json_array_subquery_view +EXPLAIN (VERBOSE, COSTS OFF) +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); + +CREATE OR REPLACE VIEW json_array_subquery_view AS +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); + +\sv json_array_subquery_view + +DROP VIEW json_array_subquery_view; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + +CREATE VIEW json_array_subquery_view AS +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + +\sv json_array_subquery_view + DROP VIEW json_array_subquery_view; -- IS JSON predicate -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-03-04 06:35 Richard Guo <[email protected]> parent: Richard Guo <[email protected]> 1 sibling, 0 replies; 18+ messages in thread From: Richard Guo @ 2026-03-04 06:35 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> On Tue, Mar 3, 2026 at 11:32 PM Richard Guo <[email protected]> wrote: > On Tue, Mar 3, 2026 at 10:03 AM Richard Guo <[email protected]> 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 Attachments: [application/octet-stream] v3-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch (21.8K, 2-v3-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch) download | inline diff: From 222740d6798f08e84b5f54c12093d0e9cb089453 Mon Sep 17 00:00:00 2001 From: Richard Guo <[email protected]> Date: Mon, 2 Mar 2026 12:11:00 +0900 Subject: [PATCH v3] Fix JSON_ARRAY(query) empty set handling and view deparsing According to the SQL/JSON standard, JSON_ARRAY(query) must return an empty JSON array ('[]') when the subquery returns zero rows. Previously, the parser rewrote JSON_ARRAY(query) into a JSON_ARRAYAGG aggregate function. Because this aggregate evaluates to NULL over an empty set without a GROUP BY clause, the constructor erroneously returned NULL. Additionally, this premature rewrite baked physical implementation details into the catalog, preventing ruleutils.c from deparsing the original syntax for views. This patch resolves both issues by preserving the user's semantic intent in the parsetree. A new JSCTOR_JSON_ARRAY_QUERY node type is introduced to identify query constructors. The parser now emits this node wrapping a standard EXPR_SUBLINK, allowing ruleutils.c to recognize and deparse the original syntax. The physical transformation is now deferred to the planner. During planning, the user's subquery is wrapped in an outer SELECT block, so that clauses like ORDER BY and LIMIT are evaluated before the JSON_ARRAYAGG aggregate is applied. A fully analyzed Aggref is then attached to this wrapper, and the entire construct is encapsulated in a COALESCE expression. This ensures that a NULL resulting from an empty set is caught and replaced with an empty JSON array. --- doc/src/sgml/func/func-json.sgml | 3 +- src/backend/optimizer/util/clauses.c | 180 +++++++++++++++++++++++++- src/backend/parser/parse_expr.c | 76 +++-------- src/backend/utils/adt/ruleutils.c | 20 +++ src/include/nodes/primnodes.h | 15 ++- src/test/regress/expected/sqljson.out | 92 ++++++++++++- src/test/regress/sql/sqljson.sql | 38 ++++++ 7 files changed, 355 insertions(+), 69 deletions(-) diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml index 1ec73cff464..d510ba8375b 100644 --- a/doc/src/sgml/func/func-json.sgml +++ b/doc/src/sgml/func/func-json.sgml @@ -620,7 +620,8 @@ which must be a SELECT query returning a single column. If <literal>ABSENT ON NULL</literal> is specified, NULL values are ignored. This is always the case if a - <replaceable>query_expression</replaceable> is used. + <replaceable>query_expression</replaceable> is used. If the query returns + no rows, an empty JSON array is returned. </para> <para> <literal>json_array(1,true,json '{"a":null}')</literal> diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index a41d81734cf..97d0f02eb1b 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -20,6 +20,7 @@ #include "postgres.h" #include "access/htup_details.h" +#include "catalog/pg_aggregate.h" #include "catalog/pg_class.h" #include "catalog/pg_language.h" #include "catalog/pg_operator.h" @@ -133,6 +134,7 @@ static Expr *simplify_function(Oid funcid, eval_const_expressions_context *context); static Node *simplify_aggref(Aggref *aggref, eval_const_expressions_context *context); +static Node *rewrite_json_array_subquery(JsonConstructorExpr *expr); static List *reorder_function_arguments(List *args, int pronargs, HeapTuple func_tuple); static List *add_function_defaults(List *args, int pronargs, @@ -3010,7 +3012,6 @@ eval_const_expressions_mutator(Node *node, } break; } - case T_JsonValueExpr: { JsonValueExpr *jve = (JsonValueExpr *) node; @@ -3034,7 +3035,18 @@ eval_const_expressions_mutator(Node *node, (Expr *) formatted_expr, copyObject(jve->format)); } + case T_JsonConstructorExpr: + { + JsonConstructorExpr *jce = (JsonConstructorExpr *) node; + if (jce->type == JSCTOR_JSON_ARRAY_QUERY) + { + Node *rewritten = rewrite_json_array_subquery(jce); + + return eval_const_expressions_mutator(rewritten, context); + } + } + break; case T_SubPlan: case T_AlternativeSubPlan: @@ -4378,6 +4390,172 @@ simplify_aggref(Aggref *aggref, eval_const_expressions_context *context) return (Node *) aggref; } +/* + * Subroutine for eval_const_expressions: transform a JSCTOR_JSON_ARRAY_QUERY + * expression into its executable form: + * + * COALESCE((SELECT JSON_ARRAYAGG(col) FROM (user_query)), '[]') + * + * This is done during planning rather than parse analysis so that ruleutils.c + * can deparse the original syntax for views. + * + * The user's query is wrapped in an outer SELECT so that clauses like ORDER BY + * and LIMIT are evaluated before the JSON_ARRAYAGG aggregate is applied. + * COALESCE ensures that we return an empty array rather than NULL on an empty + * result set, per the SQL/JSON standard. + */ +static Node * +rewrite_json_array_subquery(JsonConstructorExpr *expr) +{ + SubLink *sublink = linitial_node(SubLink, expr->args); + SubLink *newsublink; + Query *subquery; + Query *newquery; + List *colnames; + TargetEntry *tle; + RangeTblEntry *rte; + RangeTblRef *rtr; + Var *newvar; + JsonConstructorExpr *newjsctor; + Aggref *aggref; + Const *empty_json_const; + Oid aggfnoid; + Oid aggtype; + PGFunction in_func; + CoalesceExpr *coalesce; + + Assert(expr->type == JSCTOR_JSON_ARRAY_QUERY); + Assert(sublink->subLinkType == EXPR_SUBLINK); + + newsublink = copyObject(sublink); + subquery = (Query *) newsublink->subselect; + + /* + * Since we are pushing the subquery down one level, we must increment the + * sublevels of any outer references it contains. + */ + IncrementVarSublevelsUp((Node *) subquery, 1, 1); + + /* Most of the upper Query struct can be left as zeroes/nulls */ + newquery = makeNode(Query); + newquery->commandType = CMD_SELECT; + newquery->querySource = subquery->querySource; + newquery->canSetTag = true; + + tle = linitial_node(TargetEntry, subquery->targetList); + colnames = list_make1(makeString(tle->resname ? + pstrdup(tle->resname) : + pstrdup("json_col"))); + + /* Build a suitable RTE for the subquery */ + rte = makeNode(RangeTblEntry); + rte->rtekind = RTE_SUBQUERY; + rte->subquery = subquery; + rte->eref = makeAlias("json_array_subquery", colnames); + rte->lateral = false; + rte->inh = false; + rte->inFromCl = true; + newquery->rtable = list_make1(rte); + + /* Build a suitable jointree for the subquery */ + rtr = makeNode(RangeTblRef); + rtr->rtindex = 1; + newquery->jointree = makeFromExpr(list_make1(rtr), NULL);; + + /* Create a Var referencing the subquery's single output column */ + newvar = makeVar(1, + tle->resno, + exprType((Node *) tle->expr), + exprTypmod((Node *) tle->expr), + exprCollation((Node *) tle->expr), + 0); + + /* + * Construct the JSON_ARRAYAGG aggregate node. We must manually resolve + * the function OID and build a fully analyzed Aggref because we are + * bypassing parse analysis. + */ + if (expr->returning && + expr->returning->format->format_type == JS_FORMAT_JSONB) + { + aggfnoid = expr->absent_on_null ? F_JSONB_AGG_STRICT : F_JSONB_AGG; + aggtype = JSONBOID; + in_func = jsonb_in; + } + else + { + aggfnoid = expr->absent_on_null ? F_JSON_AGG_STRICT : F_JSON_AGG; + aggtype = JSONOID; + in_func = json_in; + } + + aggref = makeNode(Aggref); + aggref->aggfnoid = aggfnoid; + aggref->aggtype = aggtype; + aggref->aggcollid = InvalidOid; + aggref->inputcollid = exprCollation((Node *) newvar); + aggref->aggtranstype = InvalidOid; + aggref->aggargtypes = list_make1_oid(exprType((Node *) newvar)); + aggref->aggdirectargs = NIL; + aggref->args = list_make1(makeTargetEntry((Expr *) newvar, 1, NULL, false)); + aggref->aggorder = NIL; + aggref->aggdistinct = NIL; + aggref->aggfilter = NULL; + aggref->aggstar = false; + aggref->aggvariadic = false; + aggref->aggkind = AGGKIND_NORMAL; + aggref->aggpresorted = false; + aggref->agglevelsup = 0; + aggref->aggsplit = AGGSPLIT_SIMPLE; + aggref->aggno = -1; + aggref->aggtransno = -1; + aggref->location = expr->location; + + /* + * Attach the aggregate to the outer query's targetlist. The aggregate is + * wrapped in a JSCTOR_JSON_ARRAYAGG node to hold the RETURNING and ABSENT + * ON NULL formats from the original expression. + */ + newjsctor = makeNode(JsonConstructorExpr); + newjsctor->type = JSCTOR_JSON_ARRAYAGG; + newjsctor->args = NIL; + newjsctor->func = (Expr *) aggref; + newjsctor->coercion = copyObject(expr->coercion); + newjsctor->returning = copyObject(expr->returning); + newjsctor->absent_on_null = expr->absent_on_null; + newjsctor->unique = expr->unique; + newjsctor->location = expr->location; + + newquery->targetList = list_make1(makeTargetEntry((Expr *) newjsctor, + 1, + pstrdup("json_arrayagg"), + false)); + newquery->hasAggs = true; + + newsublink->subselect = (Node *) newquery; + + /* + * Wrap the entire construct in a COALESCE expression. If the inner query + * returns zero rows, the aggregate evaluates to NULL. We use COALESCE to + * return an empty JSON array instead. + */ + empty_json_const = makeConst(aggtype, + -1, + InvalidOid, + -1, + DirectFunctionCall1(in_func, CStringGetDatum("[]")), + false, + false); + + coalesce = makeNode(CoalesceExpr); + coalesce->coalescetype = aggtype; + coalesce->coalescecollid = InvalidOid; + coalesce->args = list_make2(newsublink, empty_json_const); + coalesce->location = expr->location; + + return (Node *) coalesce; +} + /* * var_is_nonnullable: check to see if the Var cannot be NULL * diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index dcfe1acc4c3..06a28ec5e15 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -3767,75 +3767,39 @@ transformJsonObjectConstructor(ParseState *pstate, JsonObjectConstructor *ctor) } /* - * Transform JSON_ARRAY(query [FORMAT] [RETURNING] [ON NULL]) into - * (SELECT JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]) FROM (query) q(a)) + * Transform JSON_ARRAY(subquery) constructor. + * + * JSON_ARRAY(subquery) is transformed into a JsonConstructorExpr node of type + * JSCTOR_JSON_ARRAY_QUERY. The result is coerced to the target type given by + * ctor->output. */ static Node * transformJsonArrayQueryConstructor(ParseState *pstate, JsonArrayQueryConstructor *ctor) { - SubLink *sublink = makeNode(SubLink); - SelectStmt *select = makeNode(SelectStmt); - RangeSubselect *range = makeNode(RangeSubselect); - Alias *alias = makeNode(Alias); - ResTarget *target = makeNode(ResTarget); - JsonArrayAgg *agg = makeNode(JsonArrayAgg); - ColumnRef *colref = makeNode(ColumnRef); + SubLink *sublink; Query *query; - ParseState *qpstate; - - /* Transform query only for counting target list entries. */ - qpstate = make_parsestate(pstate); - - query = transformStmt(qpstate, copyObject(ctor->query)); - - if (count_nonjunk_tlist_entries(query->targetList) != 1) - ereport(ERROR, - errcode(ERRCODE_SYNTAX_ERROR), - errmsg("subquery must return only one column"), - parser_errposition(pstate, ctor->location)); - - free_parsestate(qpstate); - - colref->fields = list_make2(makeString(pstrdup("q")), - makeString(pstrdup("a"))); - colref->location = ctor->location; - - /* - * No formatting necessary, so set formatted_expr to be the same as - * raw_expr. - */ - agg->arg = makeJsonValueExpr((Expr *) colref, (Expr *) colref, - ctor->format); - agg->absent_on_null = ctor->absent_on_null; - agg->constructor = makeNode(JsonAggConstructor); - agg->constructor->agg_order = NIL; - agg->constructor->output = ctor->output; - agg->constructor->location = ctor->location; - - target->name = NULL; - target->indirection = NIL; - target->val = (Node *) agg; - target->location = ctor->location; - - alias->aliasname = pstrdup("q"); - alias->colnames = list_make1(makeString(pstrdup("a"))); - - range->lateral = false; - range->subquery = ctor->query; - range->alias = alias; - - select->targetList = list_make1(target); - select->fromClause = list_make1(range); + JsonReturning *returning; + List *args; + sublink = makeNode(SubLink); sublink->subLinkType = EXPR_SUBLINK; sublink->subLinkId = 0; sublink->testexpr = NULL; sublink->operName = NIL; - sublink->subselect = (Node *) select; + sublink->subselect = (Node *) ctor->query; sublink->location = ctor->location; - return transformExprRecurse(pstate, (Node *) sublink); + sublink = (SubLink *) transformExprRecurse(pstate, (Node *) sublink); + + query = (Query *) sublink->subselect; + args = list_make1(linitial_node(TargetEntry, query->targetList)->expr); + returning = transformJsonConstructorOutput(pstate, ctor->output, args); + + return makeJsonConstructorExpr(pstate, JSCTOR_JSON_ARRAY_QUERY, + list_make1(sublink), NULL, returning, + false, ctor->absent_on_null, + ctor->location); } /* diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index f16f1535785..274d3e60d18 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -11740,6 +11740,26 @@ get_json_constructor(JsonConstructorExpr *ctor, deparse_context *context, get_json_agg_constructor(ctor, context, "JSON_ARRAYAGG", false); return; } + else if (ctor->type == JSCTOR_JSON_ARRAY_QUERY) + { + /* + * Bypass get_sublink_expr to avoid its hardcoded parentheses. We + * want JSON_ARRAY(SELECT ...), not JSON_ARRAY((SELECT ...)). + */ + + SubLink *sublink = linitial_node(SubLink, ctor->args); + + appendStringInfo(buf, "JSON_ARRAY("); + + get_query_def((Query *) sublink->subselect, buf, context->namespaces, + NULL, false, context->prettyFlags, context->wrapColumn, + context->indentLevel); + + get_json_constructor_options(ctor, buf); + appendStringInfoChar(buf, ')'); + + return; + } switch (ctor->type) { diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 384df50c80a..8435e76105b 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1713,13 +1713,14 @@ typedef struct JsonValueExpr typedef enum JsonConstructorType { - JSCTOR_JSON_OBJECT = 1, - JSCTOR_JSON_ARRAY = 2, - JSCTOR_JSON_OBJECTAGG = 3, - JSCTOR_JSON_ARRAYAGG = 4, - JSCTOR_JSON_PARSE = 5, - JSCTOR_JSON_SCALAR = 6, - JSCTOR_JSON_SERIALIZE = 7, + JSCTOR_JSON_OBJECT, + JSCTOR_JSON_ARRAY, + JSCTOR_JSON_ARRAY_QUERY, + JSCTOR_JSON_OBJECTAGG, + JSCTOR_JSON_ARRAYAGG, + JSCTOR_JSON_PARSE, + JSCTOR_JSON_SCALAR, + JSCTOR_JSON_SERIALIZE, } JsonConstructorType; /* diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index c7b9e575445..c7c6c6413e3 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -724,6 +724,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT [[{ "a" : 123 }]] (1 row) +-- JSON_ARRAY(subquery) SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i)); json_array ------------ @@ -757,6 +758,31 @@ SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE)); [true] (1 row) +-- JSON_ARRAY(subquery) with empty result set +SELECT JSON_ARRAY(SELECT 1 WHERE FALSE); + json_array +------------ + [] +(1 row) + +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4); + json_array +------------ + [] +(1 row) + +-- JSON_ARRAY(subquery) with a correlated subquery in the WHERE clause +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + a +--- + + 4 +(2 rows) + -- Should fail SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i)); ERROR: subquery must return only one column @@ -1093,7 +1119,7 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING QUERY PLAN --------------------------------------------------------------------- Result - Output: (InitPlan expr_1).col1 + Output: COALESCE((InitPlan expr_1).col1, '[]'::jsonb) InitPlan expr_1 -> Aggregate Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb) @@ -1105,9 +1131,67 @@ CREATE VIEW json_array_subquery_view AS SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb); \sv json_array_subquery_view CREATE OR REPLACE VIEW public.json_array_subquery_view AS - SELECT ( SELECT JSON_ARRAYAGG(q.a RETURNING jsonb) AS "json_arrayagg" - FROM ( SELECT foo.i - FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array" + SELECT JSON_ARRAY( SELECT foo.i + FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i) RETURNING jsonb) AS "json_array" +EXPLAIN (VERBOSE, COSTS OFF) +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); + QUERY PLAN +--------------------------------------------------------------------- + Result + Output: COALESCE((InitPlan expr_1).col1, '[]'::jsonb) + InitPlan expr_1 + -> Aggregate + Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb) + -> Limit + Output: "*VALUES*".column1 + -> Sort + Output: "*VALUES*".column1 + Sort Key: "*VALUES*".column1 + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1 +(12 rows) + +CREATE OR REPLACE VIEW json_array_subquery_view AS +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); +\sv json_array_subquery_view +CREATE OR REPLACE VIEW public.json_array_subquery_view AS + SELECT JSON_ARRAY( SELECT foo.i + FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i) + ORDER BY foo.i + LIMIT 3 RETURNING jsonb) AS "json_array" +DROP VIEW json_array_subquery_view; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + QUERY PLAN +----------------------------------------------------------------------- + Values Scan on "*VALUES*" + Output: "*VALUES*".column1 + Filter: (COALESCE((SubPlan expr_1), '[]'::jsonb) = '[]'::jsonb) + SubPlan expr_1 + -> Aggregate + Output: JSON_ARRAYAGG("*VALUES*_1".column1 RETURNING jsonb) + -> Values Scan on "*VALUES*_1" + Output: "*VALUES*_1".column1 + Filter: ("*VALUES*_1".column1 = "*VALUES*".column1) +(9 rows) + +CREATE VIEW json_array_subquery_view AS +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; +\sv json_array_subquery_view +CREATE OR REPLACE VIEW public.json_array_subquery_view AS + SELECT a + FROM ( VALUES (1), (2), (NULL::integer), (4)) t1(a) + WHERE JSON_ARRAY( SELECT t2.b + FROM ( VALUES (1), (2), (3)) t2(b) + WHERE t2.b = t1.a RETURNING jsonb) = '[]'::jsonb DROP VIEW json_array_subquery_view; -- IS JSON predicate SELECT NULL IS JSON; diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql index 343d344d270..1033bf4287a 100644 --- a/src/test/regress/sql/sqljson.sql +++ b/src/test/regress/sql/sqljson.sql @@ -193,6 +193,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING text)); SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text)); SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT JSON); +-- JSON_ARRAY(subquery) SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i)); SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i)); SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) RETURNING jsonb); @@ -201,6 +202,17 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL) SELECT JSON_ARRAY(SELECT i FROM (VALUES (3), (1), (NULL), (2)) foo(i) ORDER BY i); SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE)); +-- JSON_ARRAY(subquery) with empty result set +SELECT JSON_ARRAY(SELECT 1 WHERE FALSE); +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4); + +-- JSON_ARRAY(subquery) with a correlated subquery in the WHERE clause +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + -- Should fail SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i)); SELECT JSON_ARRAY(SELECT i, i FROM (VALUES (1)) foo(i)); @@ -384,6 +396,32 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING \sv json_array_subquery_view +EXPLAIN (VERBOSE, COSTS OFF) +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); + +CREATE OR REPLACE VIEW json_array_subquery_view AS +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); + +\sv json_array_subquery_view + +DROP VIEW json_array_subquery_view; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + +CREATE VIEW json_array_subquery_view AS +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + +\sv json_array_subquery_view + DROP VIEW json_array_subquery_view; -- IS JSON predicate -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-16 06:04 Richard Guo <[email protected]> parent: Richard Guo <[email protected]> 1 sibling, 1 reply; 18+ messages in thread From: Richard Guo @ 2026-04-16 06:04 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> On Tue, Mar 3, 2026 at 11:32 PM Richard Guo <[email protected]> wrote: > On Tue, Mar 3, 2026 at 10:03 AM Richard Guo <[email protected]> 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 Attachments: [application/octet-stream] v4-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch (20.9K, 2-v4-0001-Fix-JSON_ARRAY-query-empty-set-handling-and-view-.patch) download | inline diff: From 2883d47ac0ccd0f14e8fb1f84d334b6e3331a5c7 Mon Sep 17 00:00:00 2001 From: Richard Guo <[email protected]> Date: Mon, 2 Mar 2026 12:11:00 +0900 Subject: [PATCH v4] Fix JSON_ARRAY(query) empty set handling and view deparsing According to the SQL/JSON standard, JSON_ARRAY(query) must return an empty JSON array ('[]') when the subquery returns zero rows. Previously, the parser rewrote JSON_ARRAY(query) into a JSON_ARRAYAGG aggregate function. Because this aggregate evaluates to NULL over an empty set without a GROUP BY clause, the constructor erroneously returned NULL. Additionally, this premature rewrite baked physical implementation details into the catalog, preventing ruleutils.c from deparsing the original syntax for views. This patch resolves both issues by introducing a new JSCTOR_JSON_ARRAY_QUERY constructor type. The parser builds the executable form --- a COALESCE-wrapped JSON_ARRAYAGG subquery --- from raw parse nodes via transformExprRecurse, and stores it in the func field. The original transformed Query is kept in a new orig_query field so that ruleutils.c can deparse the original syntax for views. During planning, eval_const_expressions replaces the node with the pre-built func expression. --- doc/src/sgml/func/func-json.sgml | 3 +- src/backend/nodes/nodeFuncs.c | 11 +++ src/backend/optimizer/util/clauses.c | 15 +++- src/backend/parser/parse_expr.c | 111 +++++++++++++++++++++---- src/backend/utils/adt/ruleutils.c | 15 ++++ src/include/nodes/primnodes.h | 14 ++-- src/test/regress/expected/sqljson.out | 113 +++++++++++++++++++++++++- src/test/regress/sql/sqljson.sql | 49 +++++++++++ 8 files changed, 304 insertions(+), 27 deletions(-) diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml index 4cd338fe6e3..3d97e2b5375 100644 --- a/doc/src/sgml/func/func-json.sgml +++ b/doc/src/sgml/func/func-json.sgml @@ -620,7 +620,8 @@ which must be a SELECT query returning a single column. If <literal>ABSENT ON NULL</literal> is specified, NULL values are ignored. This is always the case if a - <replaceable>query_expression</replaceable> is used. + <replaceable>query_expression</replaceable> is used. If the query returns + no rows, an empty JSON array is returned. </para> <para> <literal>json_array(1,true,json '{"a":null}')</literal> diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index c0b880ec233..cca0a6c0a64 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -1002,8 +1002,16 @@ exprCollation(const Node *expr) { const JsonConstructorExpr *ctor = (const JsonConstructorExpr *) expr; + /* + * Collation comes from coercion if present, otherwise from + * func. The func fallback is needed in cases where func + * already produces the final output type and no coercion is + * needed (cf. the JSCTOR_JSON_ARRAY_QUERY case). + */ if (ctor->coercion) coll = exprCollation((Node *) ctor->coercion); + else if (ctor->func) + coll = exprCollation((Node *) ctor->func); else coll = InvalidOid; } @@ -1264,8 +1272,11 @@ exprSetCollation(Node *expr, Oid collation) { JsonConstructorExpr *ctor = (JsonConstructorExpr *) expr; + /* See comment in exprCollation() */ if (ctor->coercion) exprSetCollation((Node *) ctor->coercion, collation); + else if (ctor->func) + exprSetCollation((Node *) ctor->func, collation); else Assert(!OidIsValid(collation)); /* result is always a * json[b] type */ diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index fcf6d7fff2a..cd86311bb0b 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -3244,7 +3244,6 @@ eval_const_expressions_mutator(Node *node, } break; } - case T_JsonValueExpr: { JsonValueExpr *jve = (JsonValueExpr *) node; @@ -3268,7 +3267,21 @@ eval_const_expressions_mutator(Node *node, (Expr *) formatted_expr, copyObject(jve->format)); } + case T_JsonConstructorExpr: + { + JsonConstructorExpr *jce = (JsonConstructorExpr *) node; + /* + * JSCTOR_JSON_ARRAY_QUERY carries a pre-built executable form + * in its func field (a COALESCE-wrapped JSON_ARRAYAGG + * subquery, constructed during parse analysis). Replace the + * node with that expression and continue simplifying. + */ + if (jce->type == JSCTOR_JSON_ARRAY_QUERY) + return eval_const_expressions_mutator((Node *) jce->func, + context); + } + break; case T_SubPlan: case T_AlternativeSubPlan: diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index f535f3b9351..c3c7aa29720 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -3792,24 +3792,53 @@ transformJsonObjectConstructor(ParseState *pstate, JsonObjectConstructor *ctor) } /* - * Transform JSON_ARRAY(query [FORMAT] [RETURNING] [ON NULL]) into - * (SELECT JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]) FROM (query) q(a)) + * Transform JSON_ARRAY(subquery) constructor. + * + * JSON_ARRAY(subquery) is transformed into a JsonConstructorExpr node of type + * JSCTOR_JSON_ARRAY_QUERY. The node carries: + * + * - func: the executable form, which is a COALESCE expression wrapping a + * JSON_ARRAYAGG subquery: + * + * COALESCE((SELECT JSON_ARRAYAGG(a) FROM (subquery) q(a)), '[]') + * + * The COALESCE ensures that an empty result set produces '[]' rather than + * NULL, per the SQL/JSON standard. + * + * - orig_query: the transformed Query of the user's original subquery, so + * that ruleutils.c can deparse the original JSON_ARRAY(SELECT ...) syntax + * for view definitions. */ static Node * transformJsonArrayQueryConstructor(ParseState *pstate, JsonArrayQueryConstructor *ctor) { - SubLink *sublink = makeNode(SubLink); - SelectStmt *select = makeNode(SelectStmt); - RangeSubselect *range = makeNode(RangeSubselect); - Alias *alias = makeNode(Alias); - ResTarget *target = makeNode(ResTarget); - JsonArrayAgg *agg = makeNode(JsonArrayAgg); - ColumnRef *colref = makeNode(ColumnRef); Query *query; ParseState *qpstate; + SubLink *sublink; + SelectStmt *select; + RangeSubselect *range; + Alias *alias; + ResTarget *target; + JsonArrayAgg *agg; + ColumnRef *colref; + Node *exec_expr; + CoalesceExpr *coalesce; + Const *empty_const; + Oid result_type; + Oid typinput; + Oid typioparam; + int16 typlen; + bool typbyval; + JsonReturning *returning; + List *args; + Node *result; - /* Transform query only for counting target list entries. */ + /* + * Transform a copy of the subquery to validate the single-column + * constraint and to obtain the transformed Query for deparsing. This + * uses a private ParseState so it doesn't affect the main parse context. + */ qpstate = make_parsestate(pstate); query = transformStmt(qpstate, copyObject(ctor->query)); @@ -3822,14 +3851,20 @@ transformJsonArrayQueryConstructor(ParseState *pstate, free_parsestate(qpstate); + /* + * Build the executable form by constructing query: + * + * (SELECT JSON_ARRAYAGG(a [FORMAT] [RETURNING]) FROM (subquery) q(a)) + * + * ... using raw parse tree nodes, then transforming via + * transformExprRecurse. + */ + colref = makeNode(ColumnRef); colref->fields = list_make2(makeString(pstrdup("q")), makeString(pstrdup("a"))); colref->location = ctor->location; - /* - * No formatting necessary, so set formatted_expr to be the same as - * raw_expr. - */ + agg = makeNode(JsonArrayAgg); agg->arg = makeJsonValueExpr((Expr *) colref, (Expr *) colref, ctor->format); agg->absent_on_null = ctor->absent_on_null; @@ -3838,21 +3873,26 @@ transformJsonArrayQueryConstructor(ParseState *pstate, agg->constructor->output = ctor->output; agg->constructor->location = ctor->location; + target = makeNode(ResTarget); target->name = NULL; target->indirection = NIL; target->val = (Node *) agg; target->location = ctor->location; + alias = makeNode(Alias); alias->aliasname = pstrdup("q"); alias->colnames = list_make1(makeString(pstrdup("a"))); + range = makeNode(RangeSubselect); range->lateral = false; range->subquery = ctor->query; range->alias = alias; + select = makeNode(SelectStmt); select->targetList = list_make1(target); select->fromClause = list_make1(range); + sublink = makeNode(SubLink); sublink->subLinkType = EXPR_SUBLINK; sublink->subLinkId = 0; sublink->testexpr = NULL; @@ -3860,7 +3900,48 @@ transformJsonArrayQueryConstructor(ParseState *pstate, sublink->subselect = (Node *) select; sublink->location = ctor->location; - return transformExprRecurse(pstate, (Node *) sublink); + exec_expr = transformExprRecurse(pstate, (Node *) sublink); + + /* + * Wrap in COALESCE so that an empty result set produces '[]' rather than + * NULL. The empty-array constant is created in the output type so that + * the COALESCE arguments have consistent types. + */ + result_type = exprType(exec_expr); + getTypeInputInfo(result_type, &typinput, &typioparam); + get_typlenbyval(result_type, &typlen, &typbyval); + + empty_const = makeConst(result_type, + -1, + exprCollation(exec_expr), + (int) typlen, + OidInputFunctionCall(typinput, "[]", + typioparam, -1), + false, + typbyval); + + coalesce = makeNode(CoalesceExpr); + coalesce->coalescetype = result_type; + coalesce->coalescecollid = exprCollation(exec_expr); + coalesce->args = list_make2(exec_expr, empty_const); + coalesce->location = ctor->location; + + /* + * Build the JSCTOR_JSON_ARRAY_QUERY node. The COALESCE goes in func as + * the executable form; during planning, eval_const_expressions replaces + * the entire node with func. The transformed Query is stored in + * orig_query so that ruleutils.c can deparse the original syntax. + */ + args = list_make1(linitial_node(TargetEntry, query->targetList)->expr); + returning = transformJsonConstructorOutput(pstate, ctor->output, args); + + result = makeJsonConstructorExpr(pstate, JSCTOR_JSON_ARRAY_QUERY, + NIL, (Expr *) coalesce, returning, + false, ctor->absent_on_null, + ctor->location); + ((JsonConstructorExpr *) result)->orig_query = (Node *) query; + + return result; } /* diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 090e8cc28c1..2ddd70372b1 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -12281,6 +12281,21 @@ get_json_constructor(JsonConstructorExpr *ctor, deparse_context *context, get_json_agg_constructor(ctor, context, "JSON_ARRAYAGG", false); return; } + else if (ctor->type == JSCTOR_JSON_ARRAY_QUERY) + { + Query *query = castNode(Query, ctor->orig_query); + + appendStringInfo(buf, "JSON_ARRAY("); + + get_query_def(query, buf, context->namespaces, NULL, false, + context->prettyFlags, context->wrapColumn, + context->indentLevel); + + get_json_constructor_options(ctor, buf); + appendStringInfoChar(buf, ')'); + + return; + } switch (ctor->type) { diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 6dfc946c20b..0c69e31da54 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1715,12 +1715,13 @@ typedef struct JsonValueExpr typedef enum JsonConstructorType { JSCTOR_JSON_OBJECT = 1, - JSCTOR_JSON_ARRAY = 2, - JSCTOR_JSON_OBJECTAGG = 3, - JSCTOR_JSON_ARRAYAGG = 4, - JSCTOR_JSON_PARSE = 5, - JSCTOR_JSON_SCALAR = 6, - JSCTOR_JSON_SERIALIZE = 7, + JSCTOR_JSON_ARRAY, + JSCTOR_JSON_ARRAY_QUERY, + JSCTOR_JSON_OBJECTAGG, + JSCTOR_JSON_ARRAYAGG, + JSCTOR_JSON_PARSE, + JSCTOR_JSON_SCALAR, + JSCTOR_JSON_SERIALIZE, } JsonConstructorType; /* @@ -1735,6 +1736,7 @@ typedef struct JsonConstructorExpr Expr *func; /* underlying json[b]_xxx() function call */ Expr *coercion; /* coercion to RETURNING type */ JsonReturning *returning; /* RETURNING clause */ + Node *orig_query; /* JSON_ARRAY query for deparsing; not walked */ bool absent_on_null; /* ABSENT ON NULL? */ bool unique; /* WITH UNIQUE KEYS? (JSON_OBJECT[AGG] only) */ ParseLoc location; diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index f3be69838bf..a14936a4e6e 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -724,6 +724,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT [[{ "a" : 123 }]] (1 row) +-- JSON_ARRAY(subquery) SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i)); json_array ------------ @@ -757,6 +758,31 @@ SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE)); [true] (1 row) +-- JSON_ARRAY(subquery) with empty result set +SELECT JSON_ARRAY(SELECT 1 WHERE FALSE); + json_array +------------ + [] +(1 row) + +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4); + json_array +------------ + [] +(1 row) + +-- JSON_ARRAY(subquery) with a correlated subquery in the WHERE clause +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + a +--- + + 4 +(2 rows) + -- Should fail SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i)); ERROR: subquery must return only one column @@ -1093,7 +1119,7 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING QUERY PLAN --------------------------------------------------------------------- Result - Output: (InitPlan expr_1).col1 + Output: COALESCE((InitPlan expr_1).col1, '[]'::jsonb) InitPlan expr_1 -> Aggregate Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb) @@ -1105,9 +1131,88 @@ CREATE VIEW json_array_subquery_view AS SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb); \sv json_array_subquery_view CREATE OR REPLACE VIEW public.json_array_subquery_view AS - SELECT ( SELECT JSON_ARRAYAGG(q.a RETURNING jsonb) AS "json_arrayagg" - FROM ( SELECT foo.i - FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array" + SELECT JSON_ARRAY( SELECT foo.i + FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i) RETURNING jsonb) AS "json_array" +EXPLAIN (VERBOSE, COSTS OFF) +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); + QUERY PLAN +--------------------------------------------------------------------- + Result + Output: COALESCE((InitPlan expr_1).col1, '[]'::jsonb) + InitPlan expr_1 + -> Aggregate + Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb) + -> Limit + Output: "*VALUES*".column1 + -> Sort + Output: "*VALUES*".column1 + Sort Key: "*VALUES*".column1 + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1 +(12 rows) + +CREATE OR REPLACE VIEW json_array_subquery_view AS +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); +\sv json_array_subquery_view +CREATE OR REPLACE VIEW public.json_array_subquery_view AS + SELECT JSON_ARRAY( SELECT foo.i + FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i) + ORDER BY foo.i + LIMIT 3 RETURNING jsonb) AS "json_array" +DROP VIEW json_array_subquery_view; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + QUERY PLAN +----------------------------------------------------------------------- + Values Scan on "*VALUES*" + Output: "*VALUES*".column1 + Filter: (COALESCE((SubPlan expr_1), '[]'::jsonb) = '[]'::jsonb) + SubPlan expr_1 + -> Aggregate + Output: JSON_ARRAYAGG("*VALUES*_1".column1 RETURNING jsonb) + -> Values Scan on "*VALUES*_1" + Output: "*VALUES*_1".column1 + Filter: ("*VALUES*_1".column1 = "*VALUES*".column1) +(9 rows) + +CREATE VIEW json_array_subquery_view AS +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; +\sv json_array_subquery_view +CREATE OR REPLACE VIEW public.json_array_subquery_view AS + SELECT a + FROM ( VALUES (1), (2), (NULL::integer), (4)) t1(a) + WHERE JSON_ARRAY( SELECT t2.b + FROM ( VALUES (1), (2), (3)) t2(b) + WHERE t2.b = t1.a RETURNING jsonb) = '[]'::jsonb +DROP VIEW json_array_subquery_view; +-- JSON_ARRAY(subquery) with RETURNING text +EXPLAIN (VERBOSE, COSTS OFF) +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING text); + QUERY PLAN +-------------------------------------------------------------------- + Result + Output: COALESCE((InitPlan expr_1).col1, '[]'::text) + InitPlan expr_1 + -> Aggregate + Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING text) + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1 +(7 rows) + +CREATE VIEW json_array_subquery_view AS +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING text); +\sv json_array_subquery_view +CREATE OR REPLACE VIEW public.json_array_subquery_view AS + SELECT JSON_ARRAY( SELECT foo.i + FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i) RETURNING text) AS "json_array" DROP VIEW json_array_subquery_view; -- Test mutability of JSON_OBJECTAGG, JSON_ARRAYAGG, JSON_ARRAY, JSON_OBJECT create type comp1 as (a int, b date); diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql index 5b2c4661556..00ecf6161bf 100644 --- a/src/test/regress/sql/sqljson.sql +++ b/src/test/regress/sql/sqljson.sql @@ -193,6 +193,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING text)); SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text)); SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT JSON); +-- JSON_ARRAY(subquery) SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i)); SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i)); SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) RETURNING jsonb); @@ -201,6 +202,17 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL) SELECT JSON_ARRAY(SELECT i FROM (VALUES (3), (1), (NULL), (2)) foo(i) ORDER BY i); SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE)); +-- JSON_ARRAY(subquery) with empty result set +SELECT JSON_ARRAY(SELECT 1 WHERE FALSE); +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4); + +-- JSON_ARRAY(subquery) with a correlated subquery in the WHERE clause +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + -- Should fail SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i)); SELECT JSON_ARRAY(SELECT i, i FROM (VALUES (1)) foo(i)); @@ -384,6 +396,43 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING \sv json_array_subquery_view +EXPLAIN (VERBOSE, COSTS OFF) +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); + +CREATE OR REPLACE VIEW json_array_subquery_view AS +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) ORDER BY i LIMIT 3 RETURNING jsonb); + +\sv json_array_subquery_view + +DROP VIEW json_array_subquery_view; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + +CREATE VIEW json_array_subquery_view AS +SELECT * FROM (VALUES (1), (2), (NULL), (4)) t1(a) +WHERE JSON_ARRAY( + SELECT b FROM (VALUES (1), (2), (3)) t2(b) WHERE b = t1.a + RETURNING jsonb +) = '[]'::jsonb; + +\sv json_array_subquery_view + +DROP VIEW json_array_subquery_view; + +-- JSON_ARRAY(subquery) with RETURNING text +EXPLAIN (VERBOSE, COSTS OFF) +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING text); + +CREATE VIEW json_array_subquery_view AS +SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING text); + +\sv json_array_subquery_view + DROP VIEW json_array_subquery_view; -- Test mutability of JSON_OBJECTAGG, JSON_ARRAYAGG, JSON_ARRAY, JSON_OBJECT -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-20 09:05 Amit Langote <[email protected]> parent: Richard Guo <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Amit Langote @ 2026-04-20 09:05 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Tom Lane <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> Hi Richard, On Thu, Apr 16, 2026 at 3:05 PM Richard Guo <[email protected]> wrote: > On Tue, Mar 3, 2026 at 11:32 PM Richard Guo <[email protected]> wrote: > > On Tue, Mar 3, 2026 at 10:03 AM Richard Guo <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-21 00:57 Richard Guo <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 2 replies; 18+ messages in thread From: Richard Guo @ 2026-04-21 00:57 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: Tom Lane <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> On Mon, Apr 20, 2026 at 6:05 PM Amit Langote <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-21 01:12 Richard Guo <[email protected]> parent: Richard Guo <[email protected]> 1 sibling, 1 reply; 18+ messages in thread From: Richard Guo @ 2026-04-21 01:12 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: Tom Lane <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> On Tue, Apr 21, 2026 at 9:57 AM Richard Guo <[email protected]> wrote: > On Mon, Apr 20, 2026 at 6:05 PM Amit Langote <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-21 02:30 Tom Lane <[email protected]> parent: Richard Guo <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Tom Lane @ 2026-04-21 02:30 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Amit Langote <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; [email protected]; Álvaro Herrera <[email protected]> Richard Guo <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-21 03:20 Richard Guo <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Richard Guo @ 2026-04-21 03:20 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Amit Langote <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; [email protected]; Álvaro Herrera <[email protected]> On Tue, Apr 21, 2026 at 11:30 AM Tom Lane <[email protected]> wrote: > Richard Guo <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-21 03:57 Amit Langote <[email protected]> parent: Richard Guo <[email protected]> 1 sibling, 1 reply; 18+ messages in thread From: Amit Langote @ 2026-04-21 03:57 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Tom Lane <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> On Tue, Apr 21, 2026 at 9:57 AM Richard Guo <[email protected]> wrote: > On Mon, Apr 20, 2026 at 6:05 PM Amit Langote <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-21 05:45 Richard Guo <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 0 replies; 18+ messages in thread From: Richard Guo @ 2026-04-21 05:45 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: Tom Lane <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]> On Tue, Apr 21, 2026 at 12:57 PM Amit Langote <[email protected]> wrote: > On Tue, Apr 21, 2026 at 9:57 AM Richard Guo <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-23 00:55 Richard Guo <[email protected]> parent: Richard Guo <[email protected]> 0 siblings, 2 replies; 18+ messages in thread From: Richard Guo @ 2026-04-23 00:55 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Amit Langote <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; [email protected]; Álvaro Herrera <[email protected]>; Nathan Bossart <[email protected]>; Heikki Linnakangas <[email protected]>; Melanie Plageman <[email protected]> On Tue, Apr 21, 2026 at 12:20 PM Richard Guo <[email protected]> wrote: > > On Tue, Apr 21, 2026 at 11:30 AM Tom Lane <[email protected]> wrote: > > Richard Guo <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-23 06:51 Michael Paquier <[email protected]> parent: Richard Guo <[email protected]> 1 sibling, 1 reply; 18+ messages in thread From: Michael Paquier @ 2026-04-23 06:51 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Langote <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]>; Nathan Bossart <[email protected]>; Heikki Linnakangas <[email protected]>; Melanie Plageman <[email protected]> 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 Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-24 12:04 Richard Guo <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 0 replies; 18+ messages in thread From: Richard Guo @ 2026-04-24 12:04 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Langote <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; Álvaro Herrera <[email protected]>; Nathan Bossart <[email protected]>; Heikki Linnakangas <[email protected]>; Melanie Plageman <[email protected]> On Thu, Apr 23, 2026 at 3:51 PM Michael Paquier <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-24 19:58 Nathan Bossart <[email protected]> parent: Richard Guo <[email protected]> 1 sibling, 1 reply; 18+ messages in thread From: Nathan Bossart @ 2026-04-24 19:58 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Tom Lane <[email protected]>; Amit Langote <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; [email protected]; Álvaro Herrera <[email protected]>; Heikki Linnakangas <[email protected]>; Melanie Plageman <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-04-30 22:04 Melanie Plageman <[email protected]> parent: Nathan Bossart <[email protected]> 0 siblings, 1 reply; 18+ messages in thread From: Melanie Plageman @ 2026-04-30 22:04 UTC (permalink / raw) To: Nathan Bossart <[email protected]>; +Cc: Richard Guo <[email protected]>; Tom Lane <[email protected]>; Amit Langote <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; [email protected]; Álvaro Herrera <[email protected]>; Heikki Linnakangas <[email protected]> On Fri, Apr 24, 2026 at 3:58 PM Nathan Bossart <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> @ 2026-05-01 00:26 Richard Guo <[email protected]> parent: Melanie Plageman <[email protected]> 0 siblings, 0 replies; 18+ messages in thread From: Richard Guo @ 2026-05-01 00:26 UTC (permalink / raw) To: Melanie Plageman <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Tom Lane <[email protected]>; Amit Langote <[email protected]>; Vik Fearing <[email protected]>; [email protected]; [email protected]; [email protected]; Álvaro Herrera <[email protected]>; Heikki Linnakangas <[email protected]> On Fri, May 1, 2026 at 7:05 AM Melanie Plageman <[email protected]> wrote: > On Fri, Apr 24, 2026 at 3:58 PM Nathan Bossart <[email protected]> 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 ^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2026-05-01 00:26 UTC | newest] Thread overview: 18+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-02 06:37 Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Tom Lane <[email protected]> 2026-03-03 01:03 ` Richard Guo <[email protected]> 2026-03-03 14:32 ` Richard Guo <[email protected]> 2026-03-04 06:35 ` Richard Guo <[email protected]> 2026-04-16 06:04 ` Richard Guo <[email protected]> 2026-04-20 09:05 ` Amit Langote <[email protected]> 2026-04-21 00:57 ` Richard Guo <[email protected]> 2026-04-21 01:12 ` Richard Guo <[email protected]> 2026-04-21 02:30 ` Tom Lane <[email protected]> 2026-04-21 03:20 ` Richard Guo <[email protected]> 2026-04-23 00:55 ` Richard Guo <[email protected]> 2026-04-23 06:51 ` Michael Paquier <[email protected]> 2026-04-24 12:04 ` Richard Guo <[email protected]> 2026-04-24 19:58 ` Nathan Bossart <[email protected]> 2026-04-30 22:04 ` Melanie Plageman <[email protected]> 2026-05-01 00:26 ` Richard Guo <[email protected]> 2026-04-21 03:57 ` Amit Langote <[email protected]> 2026-04-21 05:45 ` Richard Guo <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox