aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/catalog/sql_features.txt2
-rw-r--r--src/backend/nodes/nodeFuncs.c2
-rw-r--r--src/backend/parser/gram.y38
-rw-r--r--src/backend/parser/parse_jsontable.c150
-rw-r--r--src/backend/utils/adt/jsonpath_exec.c168
-rw-r--r--src/backend/utils/adt/ruleutils.c60
-rw-r--r--src/include/nodes/parsenodes.h2
-rw-r--r--src/include/nodes/primnodes.h34
-rw-r--r--src/include/parser/kwlist.h1
-rw-r--r--src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c14
-rw-r--r--src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr8
-rw-r--r--src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stdout1
-rw-r--r--src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc8
-rw-r--r--src/test/regress/expected/sqljson_jsontable.out385
-rw-r--r--src/test/regress/sql/sqljson_jsontable.sql210
-rw-r--r--src/tools/pgindent/typedefs.list1
16 files changed, 1054 insertions, 30 deletions
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 80ac59fba46..c002f37202f 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -553,7 +553,7 @@ T823 SQL/JSON: PASSING clause YES
T824 JSON_TABLE: specific PLAN clause NO
T825 SQL/JSON: ON EMPTY and ON ERROR clauses YES
T826 General value expression in ON ERROR or ON EMPTY clauses YES
-T827 JSON_TABLE: sibling NESTED COLUMNS clauses NO
+T827 JSON_TABLE: sibling NESTED COLUMNS clauses YES
T828 JSON_QUERY YES
T829 JSON_QUERY: array wrapper options YES
T830 Enforcing unique keys in SQL/JSON constructor functions YES
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index fcd0d834b20..e1df1894b69 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4159,6 +4159,8 @@ raw_expression_tree_walker_impl(Node *node,
return true;
if (WALK(jtc->on_error))
return true;
+ if (WALK(jtc->columns))
+ return true;
}
break;
case T_JsonTablePathSpec:
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ee7a89045c3..0523f7e891e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -755,7 +755,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE MERGE_ACTION METHOD
MINUTE_P MINVALUE MODE MONTH_P MOVE
- NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NFC NFD NFKC NFKD NO
+ NAME_P NAMES NATIONAL NATURAL NCHAR NESTED NEW NEXT NFC NFD NFKC NFKD NO
NONE NORMALIZE NORMALIZED
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
NULLS_P NUMERIC
@@ -884,8 +884,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
* the same precedence as IDENT. This allows resolving conflicts in the
* json_predicate_type_constraint and json_key_uniqueness_constraint_opt
* productions (see comments there).
+ *
+ * Like the UNBOUNDED PRECEDING/FOLLOWING case, NESTED is assigned a lower
+ * precedence than PATH to fix ambiguity in the json_table production.
*/
-%nonassoc UNBOUNDED /* ideally would have same precedence as IDENT */
+%nonassoc UNBOUNDED NESTED /* ideally would have same precedence as IDENT */
%nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT PATH
%left Op OPERATOR /* multi-character ops and user-defined operators */
@@ -14270,6 +14273,35 @@ json_table_column_definition:
n->location = @1;
$$ = (Node *) n;
}
+ | NESTED path_opt Sconst
+ COLUMNS '(' json_table_column_definition_list ')'
+ {
+ JsonTableColumn *n = makeNode(JsonTableColumn);
+
+ n->coltype = JTC_NESTED;
+ n->pathspec = (JsonTablePathSpec *)
+ makeJsonTablePathSpec($3, NULL, @3, -1);
+ n->columns = $6;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | NESTED path_opt Sconst AS name
+ COLUMNS '(' json_table_column_definition_list ')'
+ {
+ JsonTableColumn *n = makeNode(JsonTableColumn);
+
+ n->coltype = JTC_NESTED;
+ n->pathspec = (JsonTablePathSpec *)
+ makeJsonTablePathSpec($3, $5, @3, @5);
+ n->columns = $8;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+path_opt:
+ PATH
+ | /* EMPTY */
;
json_table_column_path_clause_opt:
@@ -17688,6 +17720,7 @@ unreserved_keyword:
| MOVE
| NAME_P
| NAMES
+ | NESTED
| NEW
| NEXT
| NFC
@@ -18304,6 +18337,7 @@ bare_label_keyword:
| NATIONAL
| NATURAL
| NCHAR
+ | NESTED
| NEW
| NEXT
| NFC
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 060f62170e8..99d3101f6b2 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -44,16 +44,23 @@ static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt,
List *columns,
List *passingArgs,
JsonTablePathSpec *pathspec);
+static JsonTablePlan *transformJsonTableNestedColumns(JsonTableParseContext *cxt,
+ List *passingArgs,
+ List *columns);
static JsonFuncExpr *transformJsonTableColumn(JsonTableColumn *jtc,
Node *contextItemExpr,
List *passingArgs);
static bool isCompositeType(Oid typid);
static JsonTablePlan *makeJsonTablePathScan(JsonTablePathSpec *pathspec,
- bool errorOnError);
+ bool errorOnError,
+ int colMin, int colMax,
+ JsonTablePlan *childplan);
static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
List *columns);
static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name);
static char *generateJsonTablePathName(JsonTableParseContext *cxt);
+static JsonTablePlan *makeJsonTableSiblingJoin(JsonTablePlan *lplan,
+ JsonTablePlan *rplan);
/*
* transformJsonTable -
@@ -172,13 +179,32 @@ CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
{
JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1));
- if (LookupPathOrColumnName(cxt, jtc->name))
- ereport(ERROR,
- errcode(ERRCODE_DUPLICATE_ALIAS),
- errmsg("duplicate JSON_TABLE column or path name: %s",
- jtc->name),
- parser_errposition(cxt->pstate, jtc->location));
- cxt->pathNames = lappend(cxt->pathNames, jtc->name);
+ if (jtc->coltype == JTC_NESTED)
+ {
+ if (jtc->pathspec->name)
+ {
+ if (LookupPathOrColumnName(cxt, jtc->pathspec->name))
+ ereport(ERROR,
+ errcode(ERRCODE_DUPLICATE_ALIAS),
+ errmsg("duplicate JSON_TABLE column or path name: %s",
+ jtc->pathspec->name),
+ parser_errposition(cxt->pstate,
+ jtc->pathspec->name_location));
+ cxt->pathNames = lappend(cxt->pathNames, jtc->pathspec->name);
+ }
+
+ CheckDuplicateColumnOrPathNames(cxt, jtc->columns);
+ }
+ else
+ {
+ if (LookupPathOrColumnName(cxt, jtc->name))
+ ereport(ERROR,
+ errcode(ERRCODE_DUPLICATE_ALIAS),
+ errmsg("duplicate JSON_TABLE column or path name: %s",
+ jtc->name),
+ parser_errposition(cxt->pstate, jtc->location));
+ cxt->pathNames = lappend(cxt->pathNames, jtc->name);
+ }
}
}
@@ -234,6 +260,12 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
bool errorOnError = jt->on_error &&
jt->on_error->btype == JSON_BEHAVIOR_ERROR;
Oid contextItemTypid = exprType(tf->docexpr);
+ int colMin,
+ colMax;
+ JsonTablePlan *childplan;
+
+ /* Start of column range */
+ colMin = list_length(tf->colvalexprs);
foreach(col, columns)
{
@@ -243,9 +275,12 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
Oid typcoll = InvalidOid;
Node *colexpr;
- Assert(rawc->name);
- tf->colnames = lappend(tf->colnames,
- makeString(pstrdup(rawc->name)));
+ if (rawc->coltype != JTC_NESTED)
+ {
+ Assert(rawc->name);
+ tf->colnames = lappend(tf->colnames,
+ makeString(pstrdup(rawc->name)));
+ }
/*
* Determine the type and typmod for the new column. FOR ORDINALITY
@@ -303,6 +338,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
break;
}
+ case JTC_NESTED:
+ continue;
+
default:
elog(ERROR, "unknown JSON_TABLE column type: %d", (int) rawc->coltype);
break;
@@ -314,7 +352,21 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
tf->colvalexprs = lappend(tf->colvalexprs, colexpr);
}
- return makeJsonTablePathScan(pathspec, errorOnError);
+ /* End of column range. */
+ if (list_length(tf->colvalexprs) == colMin)
+ {
+ /* No columns in this Scan beside the nested ones. */
+ colMax = colMin = -1;
+ }
+ else
+ colMax = list_length(tf->colvalexprs) - 1;
+
+ /* Recursively transform nested columns */
+ childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns);
+
+ /* Create a "parent" scan responsible for all columns handled above. */
+ return makeJsonTablePathScan(pathspec, errorOnError, colMin, colMax,
+ childplan);
}
/*
@@ -397,10 +449,58 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
}
/*
+ * Recursively transform nested columns and create child plan(s) that will be
+ * used to evaluate their row patterns.
+ */
+static JsonTablePlan *
+transformJsonTableNestedColumns(JsonTableParseContext *cxt,
+ List *passingArgs,
+ List *columns)
+{
+ JsonTablePlan *plan = NULL;
+ ListCell *lc;
+
+ /*
+ * If there are multiple NESTED COLUMNS clauses in 'columns', their
+ * respective plans will be combined using a "sibling join" plan, which
+ * effectively does a UNION of the sets of rows coming from each nested
+ * plan.
+ */
+ foreach(lc, columns)
+ {
+ JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
+ JsonTablePlan *nested;
+
+ if (jtc->coltype != JTC_NESTED)
+ continue;
+
+ if (jtc->pathspec->name == NULL)
+ jtc->pathspec->name = generateJsonTablePathName(cxt);
+
+ nested = transformJsonTableColumns(cxt, jtc->columns, passingArgs,
+ jtc->pathspec);
+
+ if (plan)
+ plan = makeJsonTableSiblingJoin(plan, nested);
+ else
+ plan = nested;
+ }
+
+ return plan;
+}
+
+/*
* Create a JsonTablePlan for given path and ON ERROR behavior.
+ *
+ * colMin and colMin give the range of columns computed by this scan in the
+ * global flat list of column expressions that will be passed to the
+ * JSON_TABLE's TableFunc. Both are -1 when all of columns are nested and
+ * thus computed by 'childplan'.
*/
static JsonTablePlan *
-makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError)
+makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError,
+ int colMin, int colMax,
+ JsonTablePlan *childplan)
{
JsonTablePathScan *scan = makeNode(JsonTablePathScan);
char *pathstring;
@@ -417,5 +517,29 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError)
scan->path = makeJsonTablePath(value, pathspec->name);
scan->errorOnError = errorOnError;
+ scan->child = childplan;
+
+ scan->colMin = colMin;
+ scan->colMax = colMax;
+
return (JsonTablePlan *) scan;
}
+
+/*
+ * Create a JsonTablePlan that will perform a join of the rows coming from
+ * 'lplan' and 'rplan'.
+ *
+ * The default way of "joining" the rows is to perform a UNION between the
+ * sets of rows from 'lplan' and 'rplan'.
+ */
+static JsonTablePlan *
+makeJsonTableSiblingJoin(JsonTablePlan *lplan, JsonTablePlan *rplan)
+{
+ JsonTableSiblingJoin *join = makeNode(JsonTableSiblingJoin);
+
+ join->plan.type = T_JsonTableSiblingJoin;
+ join->lplan = lplan;
+ join->rplan = rplan;
+
+ return (JsonTablePlan *) join;
+}
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 75c468bc085..103572ed932 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -202,6 +202,18 @@ typedef struct JsonTablePlanState
/* Counter for ORDINAL columns */
int ordinal;
+
+ /* Nested plan, if any */
+ struct JsonTablePlanState *nested;
+
+ /* Left sibling, if any */
+ struct JsonTablePlanState *left;
+
+ /* Right sibling, if any */
+ struct JsonTablePlanState *right;
+
+ /* Parent plan, if this is a nested plan */
+ struct JsonTablePlanState *parent;
} JsonTablePlanState;
/* Random number to identify JsonTableExecContext for sanity checking */
@@ -213,6 +225,12 @@ typedef struct JsonTableExecContext
/* State of the plan providing a row evaluated from "root" jsonpath */
JsonTablePlanState *rootplanstate;
+
+ /*
+ * Per-column JsonTablePlanStates for all columns including the nested
+ * ones.
+ */
+ JsonTablePlanState **colplanstates;
} JsonTableExecContext;
/* strict/lax flags is decomposed into four [un]wrap/error flags */
@@ -337,6 +355,7 @@ static void checkTimezoneIsUsedForCast(bool useTz, const char *type1,
static void JsonTableInitOpaque(TableFuncScanState *state, int natts);
static JsonTablePlanState *JsonTableInitPlan(JsonTableExecContext *cxt,
JsonTablePlan *plan,
+ JsonTablePlanState *parentstate,
List *args,
MemoryContext mcxt);
static void JsonTableSetDocument(TableFuncScanState *state, Datum value);
@@ -345,6 +364,9 @@ static bool JsonTableFetchRow(TableFuncScanState *state);
static Datum JsonTableGetValue(TableFuncScanState *state, int colnum,
Oid typid, int32 typmod, bool *isnull);
static void JsonTableDestroyOpaque(TableFuncScanState *state);
+static bool JsonTablePlanScanNextRow(JsonTablePlanState *planstate);
+static void JsonTableResetNestedPlan(JsonTablePlanState *planstate);
+static bool JsonTablePlanJoinNextRow(JsonTablePlanState *planstate);
static bool JsonTablePlanNextRow(JsonTablePlanState *planstate);
const TableFuncRoutine JsonbTableRoutine =
@@ -4087,8 +4109,14 @@ JsonTableInitOpaque(TableFuncScanState *state, int natts)
}
}
- /* Initialize plan */
- cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, args,
+ cxt->colplanstates = palloc(sizeof(JsonTablePlanState *) *
+ list_length(tf->colvalexprs));
+
+ /*
+ * Initialize plan for the root path and, recursively, also any child
+ * plans that compute the NESTED paths.
+ */
+ cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, NULL, args,
CurrentMemoryContext);
state->opaque = cxt;
@@ -4113,19 +4141,22 @@ JsonTableDestroyOpaque(TableFuncScanState *state)
/*
* JsonTableInitPlan
* Initialize information for evaluating jsonpath in the given
- * JsonTablePlan
+ * JsonTablePlan and, recursively, in any child plans
*/
static JsonTablePlanState *
JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan,
+ JsonTablePlanState *parentstate,
List *args, MemoryContext mcxt)
{
JsonTablePlanState *planstate = palloc0(sizeof(*planstate));
planstate->plan = plan;
+ planstate->parent = parentstate;
if (IsA(plan, JsonTablePathScan))
{
JsonTablePathScan *scan = (JsonTablePathScan *) plan;
+ int i;
planstate->path = DatumGetJsonPathP(scan->path->value->constvalue);
planstate->args = args;
@@ -4135,6 +4166,21 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan,
/* No row pattern evaluated yet. */
planstate->current.value = PointerGetDatum(NULL);
planstate->current.isnull = true;
+
+ for (i = scan->colMin; i >= 0 && i <= scan->colMax; i++)
+ cxt->colplanstates[i] = planstate;
+
+ planstate->nested = scan->child ?
+ JsonTableInitPlan(cxt, scan->child, planstate, args, mcxt) : NULL;
+ }
+ else if (IsA(plan, JsonTableSiblingJoin))
+ {
+ JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan;
+
+ planstate->left = JsonTableInitPlan(cxt, join->lplan, parentstate,
+ args, mcxt);
+ planstate->right = JsonTableInitPlan(cxt, join->rplan, parentstate,
+ args, mcxt);
}
return planstate;
@@ -4193,16 +4239,56 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item)
}
/*
- * Fetch next row from a JsonTablePlan's path evaluation result.
+ * Fetch next row from a JsonTablePlan.
*
* Returns false if the plan has run out of rows, true otherwise.
*/
static bool
JsonTablePlanNextRow(JsonTablePlanState *planstate)
{
- JsonbValue *jbv = JsonValueListNext(&planstate->found, &planstate->iter);
+ if (IsA(planstate->plan, JsonTablePathScan))
+ return JsonTablePlanScanNextRow(planstate);
+ else if (IsA(planstate->plan, JsonTableSiblingJoin))
+ return JsonTablePlanJoinNextRow(planstate);
+ else
+ elog(ERROR, "invalid JsonTablePlan %d", (int) planstate->plan->type);
+
+ Assert(false);
+ /* Appease compiler */
+ return false;
+}
+
+/*
+ * Fetch next row from a JsonTablePlan's path evaluation result and from
+ * any child nested path(s).
+ *
+ * Returns true if any of the paths (this or the nested) has more rows to
+ * return.
+ *
+ * By fetching the nested path(s)'s rows based on the parent row at each
+ * level, this essentially joins the rows of different levels. If a nested
+ * path at a given level has no matching rows, the columns of that level will
+ * compute to NULL, making it an OUTER join.
+ */
+static bool
+JsonTablePlanScanNextRow(JsonTablePlanState *planstate)
+{
+ JsonbValue *jbv;
MemoryContext oldcxt;
+ /*
+ * If planstate already has an active row and there is a nested plan,
+ * check if it has an active row to join with the former.
+ */
+ if (!planstate->current.isnull)
+ {
+ if (planstate->nested && JsonTablePlanNextRow(planstate->nested))
+ return true;
+ }
+
+ /* Fetch new row from the list of found values to set as active. */
+ jbv = JsonValueListNext(&planstate->found, &planstate->iter);
+
/* End of list? */
if (jbv == NULL)
{
@@ -4223,6 +4309,76 @@ JsonTablePlanNextRow(JsonTablePlanState *planstate)
/* Next row! */
planstate->ordinal++;
+ /* Process nested plan(s), if any. */
+ if (planstate->nested)
+ {
+ /* Re-evaluate the nested path using the above parent row. */
+ JsonTableResetNestedPlan(planstate->nested);
+
+ /*
+ * Now fetch the nested plan's current row to be joined against the
+ * parent row. Any further nested plans' paths will be re-evaluated
+ * reursively, level at a time, after setting each nested plan's
+ * current row.
+ */
+ (void) JsonTablePlanNextRow(planstate->nested);
+ }
+
+ /* There are more rows. */
+ return true;
+}
+
+/*
+ * Re-evaluate the row pattern of a nested plan using the new parent row
+ * pattern.
+ */
+static void
+JsonTableResetNestedPlan(JsonTablePlanState *planstate)
+{
+ /* This better be a child plan. */
+ Assert(planstate->parent != NULL);
+ if (IsA(planstate->plan, JsonTablePathScan))
+ {
+ JsonTablePlanState *parent = planstate->parent;
+
+ if (!parent->current.isnull)
+ JsonTableResetRowPattern(planstate, parent->current.value);
+
+ /*
+ * If this plan itself has a child nested plan, it will be reset when
+ * the caller calls JsonTablePlanNextRow() on this plan.
+ */
+ }
+ else if (IsA(planstate->plan, JsonTableSiblingJoin))
+ {
+ JsonTableResetNestedPlan(planstate->left);
+ JsonTableResetNestedPlan(planstate->right);
+ }
+}
+
+/*
+ * Fetch the next row from a JsonTableSiblingJoin.
+ *
+ * This is essentially a UNION between the rows from left and right siblings.
+ */
+static bool
+JsonTablePlanJoinNextRow(JsonTablePlanState *planstate)
+{
+
+ /* Fetch row from left sibling. */
+ if (!JsonTablePlanNextRow(planstate->left))
+ {
+ /*
+ * Left sibling ran out of rows, so start fetching from the right
+ * sibling.
+ */
+ if (!JsonTablePlanNextRow(planstate->right))
+ {
+ /* Right sibling ran out of row, so there are more rows. */
+ return false;
+ }
+ }
+
return true;
}
@@ -4256,7 +4412,7 @@ JsonTableGetValue(TableFuncScanState *state, int colnum,
GetJsonTableExecContext(state, "JsonTableGetValue");
ExprContext *econtext = state->ss.ps.ps_ExprContext;
ExprState *estate = list_nth(state->colvalexprs, colnum);
- JsonTablePlanState *planstate = cxt->rootplanstate;
+ JsonTablePlanState *planstate = cxt->colplanstates[colnum];
JsonTablePlanRowSource *current = &planstate->current;
Datum result;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 466d9576a21..52bf87ac55b 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -524,8 +524,13 @@ static char *flatten_reloptions(Oid relid);
static void get_reloptions(StringInfo buf, Datum reloptions);
static void get_json_path_spec(Node *path_spec, deparse_context *context,
bool showimplicit);
-static void get_json_table_columns(TableFunc *tf, deparse_context *context,
+static void get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
+ deparse_context *context,
bool showimplicit);
+static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
+ deparse_context *context,
+ bool showimplicit,
+ bool needcomma);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -11627,10 +11632,43 @@ get_xmltable(TableFunc *tf, deparse_context *context, bool showimplicit)
}
/*
+ * get_json_nested_columns - Parse back nested JSON_TABLE columns
+ */
+static void
+get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
+ deparse_context *context, bool showimplicit,
+ bool needcomma)
+{
+ if (IsA(plan, JsonTablePathScan))
+ {
+ JsonTablePathScan *scan = castNode(JsonTablePathScan, plan);
+
+ if (needcomma)
+ appendStringInfoChar(context->buf, ',');
+
+ appendStringInfoChar(context->buf, ' ');
+ appendContextKeyword(context, "NESTED PATH ", 0, 0, 0);
+ get_const_expr(scan->path->value, context, -1);
+ appendStringInfo(context->buf, " AS %s", quote_identifier(scan->path->name));
+ get_json_table_columns(tf, scan, context, showimplicit);
+ }
+ else if (IsA(plan, JsonTableSiblingJoin))
+ {
+ JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan;
+
+ get_json_table_nested_columns(tf, join->lplan, context, showimplicit,
+ needcomma);
+ get_json_table_nested_columns(tf, join->rplan, context, showimplicit,
+ true);
+ }
+}
+
+/*
* get_json_table_columns - Parse back JSON_TABLE columns
*/
static void
-get_json_table_columns(TableFunc *tf, deparse_context *context,
+get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
+ deparse_context *context,
bool showimplicit)
{
StringInfo buf = context->buf;
@@ -11663,7 +11701,16 @@ get_json_table_columns(TableFunc *tf, deparse_context *context,
typmod = lfirst_int(lc_coltypmod);
colexpr = castNode(JsonExpr, lfirst(lc_colvalexpr));
- if (colnum > 0)
+ /* Skip columns that don't belong to this scan. */
+ if (scan->colMin < 0 || colnum < scan->colMin)
+ {
+ colnum++;
+ continue;
+ }
+ if (colnum > scan->colMax)
+ break;
+
+ if (colnum > scan->colMin)
appendStringInfoString(buf, ", ");
colnum++;
@@ -11711,6 +11758,10 @@ get_json_table_columns(TableFunc *tf, deparse_context *context,
get_json_expr_options(colexpr, context, default_behavior);
}
+ if (scan->child)
+ get_json_table_nested_columns(tf, scan->child, context, showimplicit,
+ scan->colMin >= 0);
+
if (PRETTY_INDENT(context))
context->indentLevel -= PRETTYINDENT_VAR;
@@ -11774,7 +11825,8 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit)
context->indentLevel -= PRETTYINDENT_VAR;
}
- get_json_table_columns(tf, context, showimplicit);
+ get_json_table_columns(tf, castNode(JsonTablePathScan, tf->plan), context,
+ showimplicit);
if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY)
get_json_behavior(jexpr->on_error, context, "ERROR");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 01fa1a6c2e9..5e470d59023 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1844,6 +1844,7 @@ typedef enum JsonTableColumnType
JTC_REGULAR,
JTC_EXISTS,
JTC_FORMATTED,
+ JTC_NESTED,
} JsonTableColumnType;
/*
@@ -1860,6 +1861,7 @@ typedef struct JsonTableColumn
JsonFormat *format; /* JSON format clause, if specified */
JsonWrapper wrapper; /* WRAPPER behavior for formatted columns */
JsonQuotes quotes; /* omit or keep quotes on scalar strings? */
+ List *columns; /* nested columns */
JsonBehavior *on_empty; /* ON EMPTY behavior */
JsonBehavior *on_error; /* ON ERROR behavior */
int location; /* token location, or -1 if unknown */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 6657f34103f..dafe93a4c95 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1855,7 +1855,10 @@ typedef struct JsonTablePlan
NodeTag type;
} JsonTablePlan;
-/* JSON_TABLE plan to evaluate a JSON path expression */
+/*
+ * JSON_TABLE plan to evaluate a JSON path expression and NESTED paths, if
+ * any.
+ */
typedef struct JsonTablePathScan
{
JsonTablePlan plan;
@@ -1863,10 +1866,37 @@ typedef struct JsonTablePathScan
/* JSON path to evaluate */
JsonTablePath *path;
- /* ERROR/EMPTY ON ERROR behavior */
+ /*
+ * ERROR/EMPTY ON ERROR behavior; only significant in the plan for the
+ * top-level path.
+ */
bool errorOnError;
+
+ /* Plan(s) for nested columns, if any. */
+ JsonTablePlan *child;
+
+ /*
+ * 0-based index in TableFunc.colvalexprs of the 1st and the last column
+ * covered by this plan. Both are -1 if all columns are nested and thus
+ * computed by the child plan(s).
+ */
+ int colMin;
+ int colMax;
} JsonTablePathScan;
+/*
+ * JsonTableSiblingJoin -
+ * Plan to join rows of sibling NESTED COLUMNS clauses in the same parent
+ * COLUMNS clause
+ */
+typedef struct JsonTableSiblingJoin
+{
+ JsonTablePlan plan;
+
+ JsonTablePlan *lplan;
+ JsonTablePlan *rplan;
+} JsonTableSiblingJoin;
+
/* ----------------
* NullTest
*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 9e4be53d93d..f9a4afd4723 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -286,6 +286,7 @@ PG_KEYWORD("names", NAMES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("national", NATIONAL, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("natural", NATURAL, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("nchar", NCHAR, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("nested", NESTED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("new", NEW, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("next", NEXT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("nfc", NFC, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
index 42a1b176e72..b2a0f11eb6e 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
@@ -132,11 +132,21 @@ if (sqlca.sqlcode < 0) sqlprint();}
printf("Found foo=%d\n", foo);
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select foo from json_table ( jsonb '[{\"foo\":\"1\"}]' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) ) ) ) jt ( foo )", ECPGt_EOIT,
+ ECPGt_int,&(foo),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 31 "sqljson_jsontable.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 31 "sqljson_jsontable.pgc"
+
+ printf("Found foo=%d\n", foo);
+
{ ECPGdisconnect(__LINE__, "CURRENT");
-#line 26 "sqljson_jsontable.pgc"
+#line 34 "sqljson_jsontable.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
-#line 26 "sqljson_jsontable.pgc"
+#line 34 "sqljson_jsontable.pgc"
return 0;
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
index d3713cff5cf..9262cf71a10 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
@@ -12,5 +12,13 @@
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_get_data on line 20: RESULT: 1 offset: -1; array: no
[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 26: query: select foo from json_table ( jsonb '[{"foo":"1"}]' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) ) ) ) jt ( foo ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 26: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 26: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 26: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_finish: connection ecpg1_regression closed
[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stdout
index 615507e6024..1e6f358a894 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stdout
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stdout
@@ -1 +1,2 @@
Found foo=1
+Found foo=1
diff --git a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
index 6d721bb37ff..aa2b4494bb6 100644
--- a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
@@ -23,6 +23,14 @@ EXEC SQL END DECLARE SECTION;
)) jt (foo);
printf("Found foo=%d\n", foo);
+ EXEC SQL SELECT foo INTO :foo FROM JSON_TABLE(jsonb '[{"foo":"1"}]', '$[*]' AS p0
+ COLUMNS (
+ NESTED '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int )
+ )
+ )) jt (foo);
+ printf("Found foo=%d\n", foo);
+
EXEC SQL DISCONNECT;
return 0;
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 8ff9b4ef4b7..a00eec8a6f4 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -637,3 +637,388 @@ SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)
ERROR: only string constants are supported in JSON_TABLE path specification
LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
^
+-- JSON_TABLE: nested paths
+-- Duplicate path names
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS a
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+ERROR: duplicate JSON_TABLE column or path name: a
+LINE 5: NESTED PATH '$' AS a
+ ^
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS a
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS n_a
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+ b | c
+---+---
+ |
+(1 row)
+
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$'
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS b
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+ERROR: duplicate JSON_TABLE column or path name: b
+LINE 5: NESTED PATH '$' AS b
+ ^
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ b int
+ ),
+ NESTED PATH '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+ )
+) jt;
+ERROR: duplicate JSON_TABLE column or path name: a
+LINE 10: NESTED PATH '$' AS a
+ ^
+-- JSON_TABLE: plan execution
+CREATE TEMP TABLE jsonb_table_test (js jsonb);
+INSERT INTO jsonb_table_test
+VALUES (
+ '[
+ {"a": 1, "b": [], "c": []},
+ {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
+ {"a": 3, "b": [1, 2], "c": []},
+ {"x": "4", "b": [1, 2], "c": 123}
+ ]'
+);
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns (b_id for ordinality, b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns (c_id for ordinality, c int path '$' )
+ )
+ ) jt;
+ n | a | b_id | b | c_id | c
+---+----+------+---+------+----
+ 1 | 1 | | | |
+ 2 | 2 | 1 | 1 | |
+ 2 | 2 | 2 | 2 | |
+ 2 | 2 | 3 | 3 | |
+ 2 | 2 | | | 1 | 10
+ 2 | 2 | | | 2 |
+ 2 | 2 | | | 3 | 20
+ 3 | 3 | 1 | 1 | |
+ 3 | 3 | 2 | 2 | |
+ 4 | -1 | 1 | 1 | |
+ 4 | -1 | 2 | 2 | |
+(11 rows)
+
+-- PASSING arguments are passed to nested paths and their columns' paths
+SELECT *
+FROM
+ generate_series(1, 3) x,
+ generate_series(1, 3) y,
+ JSON_TABLE(jsonb
+ '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
+ 'strict $[*] ? (@[*] <= $x)'
+ PASSING x AS x, y AS y
+ COLUMNS (
+ y text FORMAT JSON PATH '$',
+ NESTED PATH 'strict $[*] ? (@ == $y)'
+ COLUMNS (
+ z int PATH '$'
+ )
+ )
+ ) jt;
+ x | y | y | z
+---+---+--------------+---
+ 1 | 1 | [1, 2, 3] | 1
+ 2 | 1 | [1, 2, 3] | 1
+ 2 | 1 | [2, 3, 4, 5] |
+ 3 | 1 | [1, 2, 3] | 1
+ 3 | 1 | [2, 3, 4, 5] |
+ 3 | 1 | [3, 4, 5, 6] |
+ 1 | 2 | [1, 2, 3] | 2
+ 2 | 2 | [1, 2, 3] | 2
+ 2 | 2 | [2, 3, 4, 5] | 2
+ 3 | 2 | [1, 2, 3] | 2
+ 3 | 2 | [2, 3, 4, 5] | 2
+ 3 | 2 | [3, 4, 5, 6] |
+ 1 | 3 | [1, 2, 3] | 3
+ 2 | 3 | [1, 2, 3] | 3
+ 2 | 3 | [2, 3, 4, 5] | 3
+ 3 | 3 | [1, 2, 3] | 3
+ 3 | 3 | [2, 3, 4, 5] | 3
+ 3 | 3 | [3, 4, 5, 6] | 3
+(18 rows)
+
+-- JSON_TABLE: Test backward parsing with nested paths
+CREATE VIEW jsonb_table_view_nested AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ NESTED PATH '$[1]' AS p1 COLUMNS (
+ a1 int,
+ NESTED PATH '$[*]' AS "p1 1" COLUMNS (
+ a11 text
+ ),
+ b1 text
+ ),
+ NESTED PATH '$[2]' AS p2 COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1" COLUMNS (
+ a21 text
+ ),
+ NESTED PATH '$[*]' AS p22 COLUMNS (
+ a22 text
+ )
+ )
+ )
+ );
+\sv jsonb_table_view_nested
+CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS
+ SELECT id,
+ a1,
+ b1,
+ a11,
+ a21,
+ a22
+ FROM JSON_TABLE(
+ 'null'::jsonb, '$[*]' AS json_table_path_0
+ PASSING
+ 1 + 2 AS a,
+ '"foo"'::json AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ NESTED PATH '$[1]' AS p1
+ COLUMNS (
+ a1 integer PATH '$."a1"',
+ b1 text PATH '$."b1"',
+ NESTED PATH '$[*]' AS "p1 1"
+ COLUMNS (
+ a11 text PATH '$."a11"'
+ )
+ ),
+ NESTED PATH '$[2]' AS p2
+ COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1"
+ COLUMNS (
+ a21 text PATH '$."a21"'
+ ),
+ NESTED PATH '$[*]' AS p22
+ COLUMNS (
+ a22 text PATH '$."a22"'
+ )
+ )
+ )
+ )
+DROP VIEW jsonb_table_view_nested;
+CREATE TABLE s (js jsonb);
+INSERT INTO s VALUES
+ ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]},{"z22": [32, 204,145]}]},"c": 3}'),
+ ('{"a":{"za":[{"z1": [21,4222]},{"z21": [32, 134,1345]}]},"c": 10}');
+-- error
+SELECT sub.* FROM s,
+ JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS (
+ xx int path '$.c',
+ NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
+ )) sub;
+ERROR: no SQL/JSON item
+-- Parent columns xx1, xx appear before NESTED ones
+SELECT sub.* FROM s,
+ (VALUES (23)) x(x), generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS (
+ NESTED PATH '$.a.za[2]' COLUMNS (
+ NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$')),
+ NESTED PATH '$.a.za[1]' columns (d int[] PATH '$.z21'),
+ NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*]' as z1 COLUMNS (a int PATH '$')),
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' as z21 COLUMNS (b int PATH '$')),
+ xx int PATH '$.c'
+ )) sub;
+ xx1 | xx | c | d | a | b
+-----+----+-----+---------------+------+------
+ 3 | 3 | 32 | | |
+ 3 | 3 | 204 | | |
+ 3 | 3 | 145 | | |
+ 3 | 3 | | {22,234,2345} | |
+ 3 | 3 | | | 11 |
+ 3 | 3 | | | 2222 |
+ 3 | 3 | | | | 22
+ 3 | 3 | | | | 234
+ 3 | 3 | | | | 2345
+ 10 | 10 | | {32,134,1345} | |
+ 10 | 10 | | | 21 |
+ 10 | 10 | | | 4222 |
+ 10 | 10 | | | | 32
+ 10 | 10 | | | | 134
+ 10 | 10 | | | | 1345
+(15 rows)
+
+-- Test applying PASSING variables at different nesting levels
+SELECT sub.* FROM s,
+ (VALUES (23)) x(x), generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS (
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[0].z1[*]' COLUMNS (NESTED PATH '$ ?(@ >= ($"x" -2))' COLUMNS (a int PATH '$')),
+ NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' COLUMNS (b int PATH '$'))
+ )) sub;
+ xx1 | a | b
+-----+------+------
+ 3 | |
+ 3 | 2222 |
+ 3 | | 2222
+ 10 | 21 |
+ 10 | 4222 |
+ 10 | | 21
+ 10 | | 4222
+(7 rows)
+
+-- Test applying PASSING variable to paths all the levels
+SELECT sub.* FROM s,
+ (VALUES (23)) x(x),
+ generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
+ COLUMNS (
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[1]'
+ COLUMNS (NESTED PATH '$.z21[*]' COLUMNS (b int PATH '$')),
+ NESTED PATH '$.a.za[1] ? (@.z21[*] >= ($"x"-1))' COLUMNS
+ (NESTED PATH '$.z21[*] ? (@ >= ($"y" + 3))' as z22 COLUMNS (a int PATH '$ ? (@ >= ($"y" + 12))')),
+ NESTED PATH '$.a.za[1]' COLUMNS
+ (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (c int PATH '$ ? (@ > ($"x" +111))'))
+ )) sub;
+ xx1 | b | a | c
+-----+------+------+------
+ 3 | 22 | |
+ 3 | 234 | |
+ 3 | 2345 | |
+ 3 | | |
+ 3 | | 234 |
+ 3 | | 2345 |
+ 3 | | | 234
+ 3 | | | 2345
+ 10 | 32 | |
+ 10 | 134 | |
+ 10 | 1345 | |
+ 10 | | 32 |
+ 10 | | 134 |
+ 10 | | 1345 |
+ 10 | | |
+ 10 | | | 1345
+(16 rows)
+
+----- test on empty behavior
+SELECT sub.* FROM s,
+ (values(23)) x(x),
+ generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
+ COLUMNS (
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$')),
+ NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))'),
+ NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int PATH '$')),
+ NESTED PATH '$.a.za[1]' COLUMNS
+ (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int PATH '$ ? (@ > ($"x" +111))' DEFAULT 0 ON EMPTY))
+ )) sub;
+ xx1 | c | d | a | b
+-----+-----+--------------------------+------+------
+ 3 | 32 | | |
+ 3 | 204 | | |
+ 3 | 145 | | |
+ 3 | | {"z21": [22, 234, 2345]} | |
+ 3 | | | 2222 |
+ 3 | | | | 234
+ 3 | | | | 2345
+ 10 | | | |
+ 10 | | | 21 |
+ 10 | | | 4222 |
+ 10 | | | | 0
+ 10 | | | | 1345
+(12 rows)
+
+CREATE OR REPLACE VIEW jsonb_table_view7 AS
+SELECT sub.* FROM s,
+ (values(23)) x(x),
+ generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
+ COLUMNS (
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$' WITHOUT WRAPPER OMIT QUOTES)),
+ NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))' WITH WRAPPER),
+ NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int PATH '$' KEEP QUOTES)),
+ NESTED PATH '$.a.za[1]' COLUMNS
+ (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int PATH '$ ? (@ > ($"x" +111))' DEFAULT 0 ON EMPTY))
+ )) sub;
+\sv jsonb_table_view7
+CREATE OR REPLACE VIEW public.jsonb_table_view7 AS
+ SELECT sub.xx1,
+ sub.c,
+ sub.d,
+ sub.a,
+ sub.b
+ FROM s,
+ ( VALUES (23)) x(x),
+ generate_series(13, 13) y(y),
+ LATERAL JSON_TABLE(
+ s.js, '$' AS c1
+ PASSING
+ x.x AS x,
+ y.y AS y
+ COLUMNS (
+ xx1 integer PATH '$."c"',
+ NESTED PATH '$."a"."za"[2]' AS json_table_path_0
+ COLUMNS (
+ NESTED PATH '$."z22"[*]' AS z22
+ COLUMNS (
+ c integer PATH '$' WITHOUT WRAPPER OMIT QUOTES
+ )
+ ),
+ NESTED PATH '$."a"."za"[1]' AS json_table_path_1
+ COLUMNS (
+ d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER KEEP QUOTES
+ ),
+ NESTED PATH '$."a"."za"[0]' AS json_table_path_2
+ COLUMNS (
+ NESTED PATH '$."z1"[*]?(@ >= $"x" - 2)' AS z1
+ COLUMNS (
+ a integer PATH '$' WITHOUT WRAPPER KEEP QUOTES
+ )
+ ),
+ NESTED PATH '$."a"."za"[1]' AS json_table_path_3
+ COLUMNS (
+ NESTED PATH '$."z21"[*]?(@ >= $"y" + 121)' AS z21
+ COLUMNS (
+ b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY
+ )
+ )
+ )
+ ) sub
+DROP VIEW jsonb_table_view7;
+DROP TABLE s;
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index bd4489b6881..3752ccc446b 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -289,3 +289,213 @@ FROM JSON_TABLE(
-- Should fail (not supported)
SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
+
+-- JSON_TABLE: nested paths
+
+-- Duplicate path names
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS a
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS a
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS n_a
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$'
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS b
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ b int
+ ),
+ NESTED PATH '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+ )
+) jt;
+
+
+-- JSON_TABLE: plan execution
+
+CREATE TEMP TABLE jsonb_table_test (js jsonb);
+
+INSERT INTO jsonb_table_test
+VALUES (
+ '[
+ {"a": 1, "b": [], "c": []},
+ {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
+ {"a": 3, "b": [1, 2], "c": []},
+ {"x": "4", "b": [1, 2], "c": 123}
+ ]'
+);
+
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns (b_id for ordinality, b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns (c_id for ordinality, c int path '$' )
+ )
+ ) jt;
+
+
+-- PASSING arguments are passed to nested paths and their columns' paths
+SELECT *
+FROM
+ generate_series(1, 3) x,
+ generate_series(1, 3) y,
+ JSON_TABLE(jsonb
+ '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
+ 'strict $[*] ? (@[*] <= $x)'
+ PASSING x AS x, y AS y
+ COLUMNS (
+ y text FORMAT JSON PATH '$',
+ NESTED PATH 'strict $[*] ? (@ == $y)'
+ COLUMNS (
+ z int PATH '$'
+ )
+ )
+ ) jt;
+
+-- JSON_TABLE: Test backward parsing with nested paths
+
+CREATE VIEW jsonb_table_view_nested AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ NESTED PATH '$[1]' AS p1 COLUMNS (
+ a1 int,
+ NESTED PATH '$[*]' AS "p1 1" COLUMNS (
+ a11 text
+ ),
+ b1 text
+ ),
+ NESTED PATH '$[2]' AS p2 COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1" COLUMNS (
+ a21 text
+ ),
+ NESTED PATH '$[*]' AS p22 COLUMNS (
+ a22 text
+ )
+ )
+ )
+ );
+
+\sv jsonb_table_view_nested
+DROP VIEW jsonb_table_view_nested;
+
+CREATE TABLE s (js jsonb);
+INSERT INTO s VALUES
+ ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]},{"z22": [32, 204,145]}]},"c": 3}'),
+ ('{"a":{"za":[{"z1": [21,4222]},{"z21": [32, 134,1345]}]},"c": 10}');
+
+-- error
+SELECT sub.* FROM s,
+ JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS (
+ xx int path '$.c',
+ NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
+ )) sub;
+
+-- Parent columns xx1, xx appear before NESTED ones
+SELECT sub.* FROM s,
+ (VALUES (23)) x(x), generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS (
+ NESTED PATH '$.a.za[2]' COLUMNS (
+ NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$')),
+ NESTED PATH '$.a.za[1]' columns (d int[] PATH '$.z21'),
+ NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*]' as z1 COLUMNS (a int PATH '$')),
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' as z21 COLUMNS (b int PATH '$')),
+ xx int PATH '$.c'
+ )) sub;
+
+-- Test applying PASSING variables at different nesting levels
+SELECT sub.* FROM s,
+ (VALUES (23)) x(x), generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS (
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[0].z1[*]' COLUMNS (NESTED PATH '$ ?(@ >= ($"x" -2))' COLUMNS (a int PATH '$')),
+ NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' COLUMNS (b int PATH '$'))
+ )) sub;
+
+-- Test applying PASSING variable to paths all the levels
+SELECT sub.* FROM s,
+ (VALUES (23)) x(x),
+ generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
+ COLUMNS (
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[1]'
+ COLUMNS (NESTED PATH '$.z21[*]' COLUMNS (b int PATH '$')),
+ NESTED PATH '$.a.za[1] ? (@.z21[*] >= ($"x"-1))' COLUMNS
+ (NESTED PATH '$.z21[*] ? (@ >= ($"y" + 3))' as z22 COLUMNS (a int PATH '$ ? (@ >= ($"y" + 12))')),
+ NESTED PATH '$.a.za[1]' COLUMNS
+ (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (c int PATH '$ ? (@ > ($"x" +111))'))
+ )) sub;
+
+----- test on empty behavior
+SELECT sub.* FROM s,
+ (values(23)) x(x),
+ generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
+ COLUMNS (
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$')),
+ NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))'),
+ NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int PATH '$')),
+ NESTED PATH '$.a.za[1]' COLUMNS
+ (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int PATH '$ ? (@ > ($"x" +111))' DEFAULT 0 ON EMPTY))
+ )) sub;
+
+CREATE OR REPLACE VIEW jsonb_table_view7 AS
+SELECT sub.* FROM s,
+ (values(23)) x(x),
+ generate_series(13, 13) y,
+ JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
+ COLUMNS (
+ xx1 int PATH '$.c',
+ NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$' WITHOUT WRAPPER OMIT QUOTES)),
+ NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))' WITH WRAPPER),
+ NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int PATH '$' KEEP QUOTES)),
+ NESTED PATH '$.a.za[1]' COLUMNS
+ (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int PATH '$ ? (@ > ($"x" +111))' DEFAULT 0 ON EMPTY))
+ )) sub;
+\sv jsonb_table_view7
+DROP VIEW jsonb_table_view7;
+DROP TABLE s;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c9d4ad4a76f..5a71eca96e0 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1358,6 +1358,7 @@ JsonTablePathSpec
JsonTablePlan
JsonTablePlanRowSource
JsonTablePlanState
+JsonTableSiblingJoin
JsonTokenType
JsonTransformStringValuesAction
JsonTypeCategory
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-21 18:11:01 +0000