Re: EXPLAIN (VERBOSE) fails with for JSON_ARRAYAGG/JSON_OBJECTAGG + window function - Mailing list pgsql-bugs

On Fri, Jul 3, 2026 at 1:58 AM Thom Brown <thom@linux.com> wrote:
> CREATE TEMP TABLE t (g int, name text);
> INSERT INTO t VALUES (1, 'a'), (1, 'b'), (2, 'c');
>
> EXPLAIN (VERBOSE)
> SELECT g,
>   JSON_ARRAYAGG(name RETURNING jsonb) AS names,
>   row_number() OVER (ORDER BY g)      AS rn
> FROM t
> GROUP BY g;

> ERROR:  invalid JsonConstructorExpr underlying node type: 6

Reproduced here.  get_json_agg_constructor() expects that ctor->func
is Aggref or WindowFunc, but what it gets here is a Var.

This is because the query has both window functions and grouped
aggregates.  make_window_input_target() flattens the final tlist using
pull_var_clause, which pulls the Aggref out of its JsonConstructorExpr
wrapper.  Then fix_upper_expr() matches that inner Aggref against the
Agg subplan's tlist and replaces it with an OUTER Var.

A simple fix is:

--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12389,6 +12389,8 @@ get_json_agg_constructor(JsonConstructorExpr
*ctor, deparse_context *context,
        get_windowfunc_expr_helper((WindowFunc *) ctor->func, context,
                                   funcname, options.data,
                                   is_json_objectagg);
+   else if (IsA(ctor->func, Var))
+       get_rule_expr((Node *) ctor->func, context, false);
    else
        elog(ERROR, "invalid JsonConstructorExpr underlying node type: %d",
             nodeTag(ctor->func));

Will work on a patch on it.

- Richard