From 18a80ba29961dd8ac3705686167520af2f45cc8f Mon Sep 17 00:00:00 2001 From: Nikita Malakhov Date: Mon, 22 Dec 2025 13:51:12 +0300 Subject: [PATCH] JSON_TABLE PLAN clause part 1/2 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch adds the PLAN clause to JSON_TABLE expression allowing user to specify how data from nested paths are joined, allowing considerable freedom in shaping the tabular output of JSON_TABLE. PLAN DEFAULT allows user to specify global strategies when dealing with sibling or child nested paths. This is often sufficient to achieve the necessary goal, and is considerably simpler than the full PLAN clause, which allows the user to specify the strategy to be used for each named nested path. Code was taken from v45-0001-JSON_TABLE.patch from https://www.postgresql.org/message-id/CA%2BHiwqE1gcPkQhBko%2BUbvVvAtRBaLfOpmHbFrK79pW_5F51Oww%40mail.gmail.com Original patch was modified according to recent changes in SQL/JSON JSON_TABLE, and invalid PLAN test results found while adapting original patch to these changes. This patch [part 1] includes all changes - grammar, plan parsing and JSON_TABLE columns transformation. Author: Nikita Glukhov Author: Teodor Sigaev Author: Oleg Bartunov Author: Alexander Korotkov Author: Andrew Dunstan Author: Amit Langote Author: Anton A. Melnikov Author: Nikita Malakhov Reviewers have included (in no particular order) Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera, Jian He Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com --- src/backend/nodes/makefuncs.c | 54 ++++ src/backend/parser/gram.y | 101 ++++++- src/backend/parser/parse_jsontable.c | 393 +++++++++++++++++++++----- src/backend/utils/adt/jsonpath_exec.c | 167 ++++++++--- src/backend/utils/adt/ruleutils.c | 53 +++- src/include/nodes/makefuncs.h | 5 + src/include/nodes/parsenodes.h | 43 +++ src/include/nodes/primnodes.h | 2 + src/tools/pgindent/typedefs.list | 7 + 9 files changed, 713 insertions(+), 112 deletions(-) diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index e2d9e9be41a..5349c0e042b 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -963,6 +963,60 @@ makeJsonBehavior(JsonBehaviorType btype, Node *expr, int location) return behavior; } +/* + * makeJsonTableDefaultPlan - + * creates a JsonTablePlanSpec node to represent a "default" JSON_TABLE plan + * with given join strategy + */ +Node * +makeJsonTableDefaultPlan(JsonTablePlanJoinType join_type, int location) +{ + JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec); + + n->plan_type = JSTP_DEFAULT; + n->join_type = join_type; + n->location = location; + + return (Node *) n; +} + +/* + * makeJsonTableSimplePlan - + * creates a JsonTablePlanSpec node to represent a "simple" JSON_TABLE plan + * for given PATH + */ +Node * +makeJsonTableSimplePlan(char *pathname, int location) +{ + JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec); + + n->plan_type = JSTP_SIMPLE; + n->pathname = pathname; + n->location = location; + + return (Node *) n; +} + +/* + * makeJsonTableJoinedPlan - + * creates a JsonTablePlanSpec node to represent join between the given + * pair of plans + */ +Node * +makeJsonTableJoinedPlan(JsonTablePlanJoinType type, Node *plan1, Node *plan2, + int location) +{ + JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec); + + n->plan_type = JSTP_JOINED; + n->join_type = type; + n->plan1 = castNode(JsonTablePlanSpec, plan1); + n->plan2 = castNode(JsonTablePlanSpec, plan2); + n->location = location; + + return (Node *) n; +} + /* * makeJsonKeyValue - * creates a JsonKeyValue node diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 28f4e11e30f..0a0f27c8145 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -666,6 +666,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); json_table json_table_column_definition json_table_column_path_clause_opt + json_table_plan_clause_opt + json_table_plan + json_table_plan_simple + json_table_plan_outer + json_table_plan_inner + json_table_plan_union + json_table_plan_cross + json_table_plan_primary %type json_name_and_value_list json_value_expr_list json_array_aggregate_order_by_clause_opt @@ -677,6 +685,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type json_behavior_type json_predicate_type_constraint json_quotes_clause_opt + json_table_default_plan_choices + json_table_default_plan_inner_outer + json_table_default_plan_union_cross json_wrapper_behavior %type json_key_uniqueness_constraint_opt json_object_constructor_null_clause_opt @@ -14483,6 +14494,7 @@ json_table: json_value_expr ',' a_expr json_table_path_name_opt json_passing_clause_opt COLUMNS '(' json_table_column_definition_list ')' + json_table_plan_clause_opt json_on_error_clause_opt ')' { @@ -14494,13 +14506,15 @@ json_table: castNode(A_Const, $5)->val.node.type != T_String) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("only string constants are supported in JSON_TABLE path specification"), + errmsg("only string constants are supported in" + " JSON_TABLE path specification"), parser_errposition(@5)); pathstring = castNode(A_Const, $5)->val.sval.sval; n->pathspec = makeJsonTablePathSpec(pathstring, $6, @5, @6); n->passing = $7; n->columns = $10; - n->on_error = (JsonBehavior *) $12; + n->planspec = (JsonTablePlanSpec *) $12; + n->on_error = (JsonBehavior *) $13; n->location = @1; $$ = (Node *) n; } @@ -14592,8 +14606,7 @@ json_table_column_definition: JsonTableColumn *n = makeNode(JsonTableColumn); n->coltype = JTC_NESTED; - n->pathspec = (JsonTablePathSpec *) - makeJsonTablePathSpec($3, NULL, @3, -1); + n->pathspec = makeJsonTablePathSpec($3, NULL, @3, -1); n->columns = $6; n->location = @1; $$ = (Node *) n; @@ -14604,8 +14617,7 @@ json_table_column_definition: JsonTableColumn *n = makeNode(JsonTableColumn); n->coltype = JTC_NESTED; - n->pathspec = (JsonTablePathSpec *) - makeJsonTablePathSpec($3, $5, @3, @5); + n->pathspec = makeJsonTablePathSpec($3, $5, @3, @5); n->columns = $8; n->location = @1; $$ = (Node *) n; @@ -14624,6 +14636,83 @@ json_table_column_path_clause_opt: { $$ = NULL; } ; +json_table_plan_clause_opt: + PLAN '(' json_table_plan ')' + { $$ = $3; } + | PLAN DEFAULT '(' json_table_default_plan_choices ')' + { $$ = makeJsonTableDefaultPlan($4, @1); } + | /* EMPTY */ + { $$ = NULL; } + ; + +json_table_plan: + json_table_plan_simple + | json_table_plan_outer + | json_table_plan_inner + | json_table_plan_union + | json_table_plan_cross + ; + +json_table_plan_simple: + name + { $$ = makeJsonTableSimplePlan($1, @1); } + ; + +json_table_plan_outer: + json_table_plan_simple OUTER_P json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_OUTER, $1, $3, @1); } + ; + +json_table_plan_inner: + json_table_plan_simple INNER_P json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_INNER, $1, $3, @1); } + ; + +json_table_plan_union: + json_table_plan_primary UNION json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_UNION, $1, $3, @1); } + | json_table_plan_union UNION json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_UNION, $1, $3, @1); } + ; + +json_table_plan_cross: + json_table_plan_primary CROSS json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_CROSS, $1, $3, @1); } + | json_table_plan_cross CROSS json_table_plan_primary + { $$ = makeJsonTableJoinedPlan(JSTP_JOIN_CROSS, $1, $3, @1); } + ; + +json_table_plan_primary: + json_table_plan_simple + { $$ = $1; } + | '(' json_table_plan ')' + { + castNode(JsonTablePlanSpec, $2)->location = @1; + $$ = $2; + } + ; + +json_table_default_plan_choices: + json_table_default_plan_inner_outer + { $$ = $1 | JSTP_JOIN_UNION; } + | json_table_default_plan_union_cross + { $$ = $1 | JSTP_JOIN_OUTER; } + | json_table_default_plan_inner_outer ',' json_table_default_plan_union_cross + { $$ = $1 | $3; } + | json_table_default_plan_union_cross ',' json_table_default_plan_inner_outer + { $$ = $1 | $3; } + ; + +json_table_default_plan_inner_outer: + INNER_P { $$ = JSTP_JOIN_INNER; } + | OUTER_P { $$ = JSTP_JOIN_OUTER; } + ; + +json_table_default_plan_union_cross: + UNION { $$ = JSTP_JOIN_UNION; } + | CROSS { $$ = JSTP_JOIN_CROSS; } + ; + /***************************************************************************** * * Type syntax diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c index 13d533b83f3..7d7c595c819 100644 --- a/src/backend/parser/parse_jsontable.c +++ b/src/backend/parser/parse_jsontable.c @@ -39,17 +39,22 @@ typedef struct JsonTableParseContext } JsonTableParseContext; static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt, + JsonTablePlanSpec *planspec, List *columns, - List *passingArgs, - JsonTablePathSpec *pathspec); + List *passing_Args, + JsonTablePathSpec * pathspec); static JsonTablePlan *transformJsonTableNestedColumns(JsonTableParseContext *cxt, - List *passingArgs, + JsonTablePlanSpec *plan, + List *passing_Args, List *columns); static JsonFuncExpr *transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, - List *passingArgs); + List *passingArgs, + bool errorOnError); static bool isCompositeType(Oid typid); -static JsonTablePlan *makeJsonTablePathScan(JsonTablePathSpec *pathspec, +static JsonTablePlan *makeJsonTablePathScan(JsonTableParseContext *cxt, + JsonTablePathSpec *pathspec, + JsonTablePlanSpec *planspec, bool errorOnError, int colMin, int colMax, JsonTablePlan *childplan); @@ -57,8 +62,14 @@ static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt, List *columns); static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name); static char *generateJsonTablePathName(JsonTableParseContext *cxt); -static JsonTablePlan *makeJsonTableSiblingJoin(JsonTablePlan *lplan, +static void validateJsonTableChildPlan(ParseState *pstate, + JsonTablePlanSpec *plan, + List *columns); +static JsonTablePlan *makeJsonTableSiblingJoin(bool cross, + JsonTablePlan *lplan, JsonTablePlan *rplan); +static void +appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingArgs); /* * transformJsonTable - @@ -76,6 +87,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) TableFunc *tf; JsonFuncExpr *jfe; JsonExpr *je; + JsonTablePlanSpec *plan = jt->planspec; JsonTablePathSpec *rootPathSpec = jt->pathspec; bool is_lateral; JsonTableParseContext cxt = {pstate}; @@ -94,8 +106,21 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) parser_errposition(pstate, jt->on_error->location)); cxt.pathNameId = 0; + if (rootPathSpec->name == NULL) + { + if (jt->planspec != NULL) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE expression"), + errdetail("JSON_TABLE path must contain" + " explicit AS pathname specification if" + " explicit PLAN clause is used"), + parser_errposition(pstate, rootPathSpec->location))); + rootPathSpec->name = generateJsonTablePathName(&cxt); + } + cxt.pathNames = list_make1(rootPathSpec->name); CheckDuplicateColumnOrPathNames(&cxt, jt->columns); @@ -135,7 +160,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) */ cxt.jt = jt; cxt.tf = tf; - tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns, + tf->plan = (Node *) transformJsonTableColumns(&cxt, plan, jt->columns, jt->passing, rootPathSpec); @@ -246,25 +271,110 @@ generateJsonTablePathName(JsonTableParseContext *cxt) * their type/collation information to cxt->tf. */ static JsonTablePlan * -transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, - List *passingArgs, +transformJsonTableColumns(JsonTableParseContext *cxt, + JsonTablePlanSpec *planspec, + List *columns, + List *passing_Args, JsonTablePathSpec *pathspec) { - ParseState *pstate = cxt->pstate; JsonTable *jt = cxt->jt; TableFunc *tf = cxt->tf; - ListCell *col; - bool ordinality_found = false; + JsonTablePathScan *scan; + JsonTablePlanSpec *childPlanSpec; + bool defaultPlan = planspec == NULL || + planspec->plan_type == JSTP_DEFAULT; bool errorOnError = jt->on_error && jt->on_error->btype == JSON_BEHAVIOR_ERROR; - Oid contextItemTypid = exprType(tf->docexpr); int colMin, colMax; - JsonTablePlan *childplan; + JsonTablePlan *childplan = NULL; /* Start of column range */ colMin = list_length(tf->colvalexprs); + if (defaultPlan) + childPlanSpec = planspec; + else + { + /* validate parent and child plans */ + JsonTablePlanSpec *parentPlanSpec; + + if (planspec->plan_type == JSTP_JOINED) + { + if (planspec->join_type != JSTP_JOIN_INNER && + planspec->join_type != JSTP_JOIN_OUTER) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan clause"), + errdetail("Expected INNER or OUTER."), + parser_errposition(cxt->pstate, planspec->location))); + + parentPlanSpec = planspec->plan1; + childPlanSpec = planspec->plan2; + + Assert(parentPlanSpec->plan_type != JSTP_JOINED); + Assert(parentPlanSpec->pathname); + } + else + { + parentPlanSpec = planspec; + childPlanSpec = NULL; + } + + if (strcmp(parentPlanSpec->pathname, pathspec->name) != 0) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("PATH name mismatch: expected %s but %s is given.", + pathspec->name, parentPlanSpec->pathname), + parser_errposition(cxt->pstate, planspec->location))); + + validateJsonTableChildPlan(cxt->pstate, childPlanSpec, columns); + } + + appendJsonTableColumns(cxt, columns, passing_Args); + + /* 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; + + if (childPlanSpec || defaultPlan) + { + /* transform recursively nested columns */ + childplan = transformJsonTableNestedColumns(cxt, childPlanSpec, + columns, passing_Args); + } + + /* transform only non-nested columns */ + scan = (JsonTablePathScan *) makeJsonTablePathScan(cxt, pathspec, + planspec, + errorOnError, + colMin, + colMax, + childplan); + + return (JsonTablePlan *) scan; +} + +/* Append transformed non-nested JSON_TABLE columns to the TableFunc node */ +static void +appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingArgs) +{ + ListCell *col; + ParseState *pstate = cxt->pstate; + JsonTable *jt = cxt->jt; + TableFunc *tf = cxt->tf; + bool ordinality_found = false; + JsonBehavior *on_error = jt->on_error; + bool errorOnError = on_error && + on_error->btype == JSON_BEHAVIOR_ERROR; + Oid contextItemTypid = exprType(tf->docexpr); + foreach(col, columns) { JsonTableColumn *rawc = castNode(JsonTableColumn, lfirst(col)); @@ -273,12 +383,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, Oid typcoll = InvalidOid; Node *colexpr; - if (rawc->coltype != JTC_NESTED) - { - Assert(rawc->name); + if (rawc->name) tf->colnames = lappend(tf->colnames, makeString(pstrdup(rawc->name))); - } /* * Determine the type and typmod for the new column. FOR ORDINALITY @@ -324,7 +431,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, param->typeMod = -1; jfe = transformJsonTableColumn(rawc, (Node *) param, - passingArgs); + passingArgs, errorOnError); colexpr = transformExpr(pstate, (Node *) jfe, EXPR_KIND_FROM_FUNCTION); @@ -349,22 +456,6 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, tf->colcollations = lappend_oid(tf->colcollations, typcoll); tf->colvalexprs = lappend(tf->colvalexprs, colexpr); } - - /* 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); } /* @@ -395,7 +486,7 @@ isCompositeType(Oid typid) */ static JsonFuncExpr * transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, - List *passingArgs) + List *passingArgs, bool errorOnError) { Node *pathspec; JsonFuncExpr *jfexpr = makeNode(JsonFuncExpr); @@ -437,6 +528,8 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, jfexpr->output->returning->format = jtc->format; jfexpr->on_empty = jtc->on_empty; jfexpr->on_error = jtc->on_error; + if (jfexpr->on_error == NULL && errorOnError) + jfexpr->on_error = makeJsonBehavior(JSON_BEHAVIOR_ERROR, NULL, -1); jfexpr->quotes = jtc->quotes; jfexpr->wrapper = jtc->wrapper; jfexpr->location = jtc->location; @@ -444,49 +537,126 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, return jfexpr; } +static JsonTableColumn * +findNestedJsonTableColumn(List *columns, const char *pathname) +{ + ListCell *lc; + + foreach(lc, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); + + if (jtc->coltype == JTC_NESTED && + jtc->pathspec->name && + !strcmp(jtc->pathspec->name, pathname)) + return jtc; + } + + return NULL; +} /* * Recursively transform nested columns and create child plan(s) that will be * used to evaluate their row patterns. + * + * Default plan is transformed into a cross/union join of its nested columns. + * Simple and outer/inner plans are transformed into a JsonTablePlan by + * finding and transforming corresponding nested column. + * Sibling plans are recursively transformed into a JsonTableSiblingJoin. */ static JsonTablePlan * transformJsonTableNestedColumns(JsonTableParseContext *cxt, - List *passingArgs, - List *columns) + JsonTablePlanSpec *planspec, + List *columns, + List *passingArgs) { - JsonTablePlan *plan = NULL; - ListCell *lc; + JsonTableColumn *jtc = NULL; - /* - * 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) + if (!planspec || planspec->plan_type == JSTP_DEFAULT) { - JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); - JsonTablePlan *nested; + /* unspecified or default plan */ + JsonTablePlan *plan = NULL; + ListCell *lc; + bool cross = planspec && (planspec->join_type & JSTP_JOIN_CROSS); - if (jtc->coltype != JTC_NESTED) - continue; + /* + * 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 *col = castNode(JsonTableColumn, lfirst(lc)); + JsonTablePlan *nested; - if (jtc->pathspec->name == NULL) - jtc->pathspec->name = generateJsonTablePathName(cxt); + if (col->coltype != JTC_NESTED) + continue; - nested = transformJsonTableColumns(cxt, jtc->columns, passingArgs, - jtc->pathspec); + if (col->pathspec->name == NULL) + { + col->pathspec->name = generateJsonTablePathName(cxt); + } - if (plan) - plan = makeJsonTableSiblingJoin(plan, nested); + nested = transformJsonTableColumns(cxt, planspec, col->columns, + passingArgs, + col->pathspec); + + /* Join nested plan with previous sibling nested plans. */ + if (plan) + plan = makeJsonTableSiblingJoin(cross, plan, nested); + else + plan = nested; + } + + return plan; + } + else if (planspec->plan_type == JSTP_SIMPLE) + { + jtc = findNestedJsonTableColumn(columns, planspec->pathname); + } + else if (planspec->plan_type == JSTP_JOINED) + { + if (planspec->join_type == JSTP_JOIN_INNER || + planspec->join_type == JSTP_JOIN_OUTER) + { + Assert(planspec->plan1->plan_type == JSTP_SIMPLE); + jtc = findNestedJsonTableColumn(columns, planspec->plan1->pathname); + } else - plan = nested; + { + JsonTablePlan *lplan = transformJsonTableNestedColumns(cxt, + planspec->plan1, + columns, + passingArgs); + JsonTablePlan *rplan = transformJsonTableNestedColumns(cxt, + planspec->plan2, + columns, + passingArgs); + + return makeJsonTableSiblingJoin(planspec->join_type == JSTP_JOIN_CROSS, + lplan, rplan); + } } + else + elog(ERROR, "invalid JSON_TABLE plan type %d", planspec->plan_type); - return plan; + if (!jtc) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan clause"), + errdetail("PATH name was %s not found in nested columns list.", + planspec->pathname), + parser_errposition(cxt->pstate, planspec->location))); + + return transformJsonTableColumns(cxt, planspec, jtc->columns, + passingArgs, + jtc->pathspec); } /* - * Create a JsonTablePlan for given path and ON ERROR behavior. + * Create transformed JSON_TABLE parent plan node by appending all non-nested + * columns to the TableFunc node and remembering their indices in the + * colvalexprs list. * * 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 @@ -494,7 +664,9 @@ transformJsonTableNestedColumns(JsonTableParseContext *cxt, * thus computed by 'childplan'. */ static JsonTablePlan * -makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, +makeJsonTablePathScan(JsonTableParseContext *cxt, JsonTablePathSpec *pathspec, + JsonTablePlanSpec *planspec, + bool errorOnError, int colMin, int colMax, JsonTablePlan *childplan) { @@ -513,11 +685,18 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, scan->path = makeJsonTablePath(value, pathspec->name); scan->errorOnError = errorOnError; - scan->child = childplan; + scan->child = NULL; + if(childplan) + scan->child = childplan; scan->colMin = colMin; scan->colMax = colMax; + if (scan->child) + scan->outerJoin = planspec == NULL || + (planspec->join_type & JSTP_JOIN_OUTER); + /* else: default plan case, no children found */ + return (JsonTablePlan *) scan; } @@ -529,13 +708,101 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, * sets of rows from 'lplan' and 'rplan'. */ static JsonTablePlan * -makeJsonTableSiblingJoin(JsonTablePlan *lplan, JsonTablePlan *rplan) +makeJsonTableSiblingJoin(bool cross, JsonTablePlan *lplan, JsonTablePlan *rplan) { JsonTableSiblingJoin *join = makeNode(JsonTableSiblingJoin); join->plan.type = T_JsonTableSiblingJoin; join->lplan = lplan; join->rplan = rplan; + join->cross = cross; return (JsonTablePlan *) join; } + +/* Collect sibling path names from plan to the specified list. */ +static void +collectSiblingPathsInJsonTablePlan(JsonTablePlanSpec *plan, List **paths) +{ + if (plan->plan_type == JSTP_SIMPLE) + *paths = lappend(*paths, plan->pathname); + else if (plan->plan_type == JSTP_JOINED) + { + if (plan->join_type == JSTP_JOIN_INNER || + plan->join_type == JSTP_JOIN_OUTER) + { + Assert(plan->plan1->plan_type == JSTP_SIMPLE); + *paths = lappend(*paths, plan->plan1->pathname); + } + else if (plan->join_type == JSTP_JOIN_CROSS || + plan->join_type == JSTP_JOIN_UNION) + { + collectSiblingPathsInJsonTablePlan(plan->plan1, paths); + collectSiblingPathsInJsonTablePlan(plan->plan2, paths); + } + else + elog(ERROR, "invalid JSON_TABLE join type %d", + plan->join_type); + } +} + +/* + * Validate child JSON_TABLE plan by checking that: + * - all nested columns have path names specified + * - all nested columns have corresponding node in the sibling plan + * - plan does not contain duplicate or extra nodes + */ +static void +validateJsonTableChildPlan(ParseState *pstate, JsonTablePlanSpec *plan, + List *columns) +{ + ListCell *lc1; + List *siblings = NIL; + int nchildren = 0; + + if (plan) + collectSiblingPathsInJsonTablePlan(plan, &siblings); + + foreach(lc1, columns) + { + JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1)); + + if (jtc->coltype == JTC_NESTED) + { + ListCell *lc2; + bool found = false; + + if (jtc->pathspec->name == NULL) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("nested JSON_TABLE columns must contain" + " an explicit AS pathname specification" + " if an explicit PLAN clause is used"), + parser_errposition(pstate, jtc->location)); + + /* find nested path name in the list of sibling path names */ + foreach(lc2, siblings) + { + if ((found = !strcmp(jtc->pathspec->name, lfirst(lc2)))) + break; + } + + if (!found) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE specification"), + errdetail("PLAN clause for nested path %s was not found.", + jtc->pathspec->name), + parser_errposition(pstate, jtc->location)); + + nchildren++; + } + } + + if (list_length(siblings) > nchildren) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan clause"), + errdetail("PLAN clause contains some extra or duplicate sibling nodes."), + parser_errposition(pstate, plan ? plan->location : -1)); +} diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index fc0e05e8789..333f27af43d 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -213,6 +213,13 @@ typedef struct JsonTablePlanState /* Parent plan, if this is a nested plan */ struct JsonTablePlanState *parent; + + /**/ + bool cross; + bool outerJoin; + bool advanceNested; + bool advanceRight; + bool reset; } JsonTablePlanState; /* Random number to identify JsonTableExecContext for sanity checking */ @@ -359,13 +366,13 @@ static JsonTablePlanState *JsonTableInitPlan(JsonTableExecContext *cxt, MemoryContext mcxt); static void JsonTableSetDocument(TableFuncScanState *state, Datum value); static void JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item); +static void JsonTableRescan(JsonTablePlanState *planstate); 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 = @@ -4206,6 +4213,7 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan, JsonTablePathScan *scan = (JsonTablePathScan *) plan; int i; + planstate->outerJoin = scan->outerJoin; planstate->path = DatumGetJsonPathP(scan->path->value->constvalue); planstate->args = args; planstate->mcxt = AllocSetContextCreate(mcxt, "JsonTableExecContext", @@ -4225,6 +4233,8 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan, { JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan; + planstate->cross = join->cross; + planstate->left = JsonTableInitPlan(cxt, join->lplan, parentstate, args, mcxt); planstate->right = JsonTableInitPlan(cxt, join->rplan, parentstate, @@ -4279,11 +4289,7 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item) JsonValueListClear(&planstate->found); } - /* Reset plan iterator to the beginning of the item list */ - JsonValueListInitIterator(&planstate->found, &planstate->iter); - planstate->current.value = PointerGetDatum(NULL); - planstate->current.isnull = true; - planstate->ordinal = 0; + JsonTableRescan(planstate); } /* @@ -4294,16 +4300,89 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item) static bool JsonTablePlanNextRow(JsonTablePlanState *planstate) { - if (IsA(planstate->plan, JsonTablePathScan)) - return JsonTablePlanScanNextRow(planstate); - else if (IsA(planstate->plan, JsonTableSiblingJoin)) - return JsonTablePlanJoinNextRow(planstate); + if (IsA(planstate->plan, JsonTableSiblingJoin)) + { + if (planstate->advanceRight) + { + /* fetch next inner row */ + if (JsonTablePlanNextRow(planstate->right)) + return true; + + /* inner rows are exhausted */ + if (planstate->cross) + planstate->advanceRight = false; /* next outer row */ + else + return false; /* end of scan */ + } + + while (!planstate->advanceRight) + { + /* fetch next outer row */ + bool more = JsonTablePlanNextRow(planstate->left); + + if (planstate->cross) + { + if (!more) + return false; /* end of scan */ + + JsonTableRescan(planstate->right); + + if (!JsonTablePlanNextRow(planstate->right)) + continue; /* next outer row */ + + planstate->advanceRight = true; /* next inner row */ + } + else if (!more) + { + if (!JsonTablePlanNextRow(planstate->right)) + return false; /* end of scan */ + + planstate->advanceRight = true; /* next inner row */ + } + + break; + } + } else - elog(ERROR, "invalid JsonTablePlan %d", (int) planstate->plan->type); + { + /* reset context item if requested */ + if (planstate->reset) + { + JsonTablePlanState *parent = planstate->parent; - Assert(false); - /* Appease compiler */ - return false; + Assert(parent != NULL && !parent->current.isnull); + JsonTableResetRowPattern(planstate, parent->current.value); + planstate->reset = false; + } + + if (planstate->advanceNested) + { + /* fetch next nested row */ + planstate->advanceNested = JsonTablePlanNextRow(planstate->nested); + if (planstate->advanceNested) + return true; + } + + for (;;) + { + if (!JsonTablePlanScanNextRow(planstate)) + return false; + + if (planstate->nested == NULL) + break; + + JsonTableResetNestedPlan(planstate->nested); + planstate->advanceNested = JsonTablePlanNextRow(planstate->nested); + + if (!planstate->advanceNested && !planstate->outerJoin) + continue; + + if (planstate->advanceNested || planstate->nested) + break; + } + } + + return true; } /* @@ -4389,47 +4468,28 @@ JsonTableResetNestedPlan(JsonTablePlanState *planstate) { JsonTablePlanState *parent = planstate->parent; - if (!parent->current.isnull) - JsonTableResetRowPattern(planstate, parent->current.value); + planstate->reset = true; + planstate->advanceNested = false; + + if (planstate->nested) + JsonTableResetNestedPlan(planstate->nested); /* * If this plan itself has a child nested plan, it will be reset when * the caller calls JsonTablePlanNextRow() on this plan. */ + if (!parent->current.isnull) + JsonTableResetRowPattern(planstate, parent->current.value); + } else if (IsA(planstate->plan, JsonTableSiblingJoin)) { JsonTableResetNestedPlan(planstate->left); JsonTableResetNestedPlan(planstate->right); + planstate->advanceRight = false; } } -/* - * 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; -} - /* * JsonTableFetchRow * Prepare the next "current" row for upcoming GetValue calls. @@ -4494,3 +4554,26 @@ JsonTableGetValue(TableFuncScanState *state, int colnum, return result; } + +/* Recursively reset planstate and its child nodes */ +static void +JsonTableRescan(JsonTablePlanState *planstate) +{ + if (IsA(planstate->plan, JsonTablePathScan)) + { + /* Reset plan iterator to the beginning of the item list */ + JsonValueListInitIterator(&planstate->found, &planstate->iter); + planstate->current.value = PointerGetDatum(NULL); + planstate->current.isnull = true; + planstate->ordinal = 0; + + if (planstate->nested) + JsonTableRescan(planstate->nested); + } + else if (IsA(planstate, JsonTableSiblingJoin)) + { + JsonTableRescan(planstate->left); + JsonTableRescan(planstate->right); + planstate->advanceRight = false; + } +} diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9f85eb86da1..6dbb34ce321 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -12102,6 +12102,49 @@ get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan, } } +/* + * get_json_table_plan - Parse back a JSON_TABLE plan + */ +static void +get_json_table_plan(TableFunc *tf, JsonTablePlan *plan, deparse_context *context, + bool parenthesize) +{ + if (parenthesize) + appendStringInfoChar(context->buf, '('); + + if (IsA(plan, JsonTablePathScan)) + { + JsonTablePathScan *s = castNode(JsonTablePathScan, plan); + + appendStringInfoString(context->buf, quote_identifier(s->path->name)); + + if (s->child) + { + appendStringInfoString(context->buf, + s->outerJoin ? " OUTER " : " INNER "); + get_json_table_plan(tf, s->child, context, + IsA(s->child, JsonTableSiblingJoin)); + } + } + else if (IsA(plan, JsonTableSiblingJoin)) + { + JsonTableSiblingJoin *j = (JsonTableSiblingJoin *) plan; + + get_json_table_plan(tf, j->lplan, context, + IsA(j->lplan, JsonTableSiblingJoin) || + castNode(JsonTablePathScan, j->lplan)->child); + + appendStringInfoString(context->buf, j->cross ? " CROSS " : " UNION "); + + get_json_table_plan(tf, j->rplan, context, + IsA(j->rplan, JsonTableSiblingJoin) || + castNode(JsonTablePathScan, j->rplan)->child); + } + + if (parenthesize) + appendStringInfoChar(context->buf, ')'); +} + /* * get_json_table_columns - Parse back JSON_TABLE columns */ @@ -12111,6 +12154,7 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan, bool showimplicit) { StringInfo buf = context->buf; + JsonExpr *jexpr = castNode(JsonExpr, tf->docexpr); ListCell *lc_colname; ListCell *lc_coltype; ListCell *lc_coltypmod; @@ -12190,6 +12234,9 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan, default_behavior = JSON_BEHAVIOR_NULL; } + if (jexpr->on_error->btype == JSON_BEHAVIOR_ERROR) + default_behavior = JSON_BEHAVIOR_ERROR; + appendStringInfoString(buf, " PATH "); get_json_path_spec(colexpr->path_spec, context, showimplicit); @@ -12267,7 +12314,11 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit) get_json_table_columns(tf, castNode(JsonTablePathScan, tf->plan), context, showimplicit); - if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY_ARRAY) + appendStringInfoChar(buf, ' '); + appendContextKeyword(context, "PLAN ", 0, 0, 0); + get_json_table_plan(tf, (JsonTablePlan *)root, context, true); + + if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY) get_json_behavior(jexpr->on_error, context, "ERROR"); if (PRETTY_INDENT(context)) diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index 5473ce9a288..17d761ef142 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -124,5 +124,10 @@ extern JsonTablePath *makeJsonTablePath(Const *pathvalue, char *pathname); extern JsonTablePathSpec *makeJsonTablePathSpec(char *string, char *name, int string_location, int name_location); +extern Node *makeJsonTableDefaultPlan(JsonTablePlanJoinType join_type, + int location); +extern Node *makeJsonTableSimplePlan(char *pathname, int location); +extern Node *makeJsonTableJoinedPlan(JsonTablePlanJoinType type, + Node *plan1, Node *plan2, int location); #endif /* MAKEFUNC_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index bc7adba4a0f..04fac3173f2 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1906,6 +1906,48 @@ typedef struct JsonTablePathSpec ParseLoc location; /* location of 'string' */ } JsonTablePathSpec; +/* + * JsonTablePlanType - + * flags for JSON_TABLE plan node types representation + */ +typedef enum JsonTablePlanType +{ + JSTP_DEFAULT, + JSTP_SIMPLE, + JSTP_JOINED, +} JsonTablePlanType; + +/* + * JsonTablePlanJoinType - + * JSON_TABLE join types for JSTP_JOINED plans + */ +typedef enum JsonTablePlanJoinType +{ + JSTP_JOIN_INNER = 0x01, + JSTP_JOIN_OUTER = 0x02, + JSTP_JOIN_CROSS = 0x04, + JSTP_JOIN_UNION = 0x08, +} JsonTablePlanJoinType; + +/* + * JsonTablePlanSpec - + * untransformed representation of JSON_TABLE's PLAN clause + */ +typedef struct JsonTablePlanSpec +{ + NodeTag type; + + JsonTablePlanType plan_type; /* plan type */ + JsonTablePlanJoinType join_type; /* join type (for joined plan only) */ + char *pathname; /* path name (for simple plan only) */ + + /* For joined plans */ + struct JsonTablePlanSpec *plan1; /* first joined plan */ + struct JsonTablePlanSpec *plan2; /* second joined plan */ + + ParseLoc location; /* token location, or -1 if unknown */ +} JsonTablePlanSpec; + /* * JsonTable - * untransformed representation of JSON_TABLE @@ -1917,6 +1959,7 @@ typedef struct JsonTable JsonTablePathSpec *pathspec; /* JSON path specification */ List *passing; /* list of PASSING clause arguments, if any */ List *columns; /* list of JsonTableColumn */ + JsonTablePlanSpec *planspec; /* join plan, if specified */ JsonBehavior *on_error; /* ON ERROR behavior */ Alias *alias; /* table alias in FROM clause */ bool lateral; /* does it have LATERAL prefix? */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 1b4436f2ff6..d235709b484 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1930,6 +1930,7 @@ typedef struct JsonTablePathScan /* Plan(s) for nested columns, if any. */ JsonTablePlan *child; + bool outerJoin; /* outer or inner join for nested columns? */ /* * 0-based index in TableFunc.colvalexprs of the 1st and the last column @@ -1951,6 +1952,7 @@ typedef struct JsonTableSiblingJoin JsonTablePlan *lplan; JsonTablePlan *rplan; + bool cross; } JsonTableSiblingJoin; /* ---------------- diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 04845d5e680..f8c2e216d92 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1426,6 +1426,7 @@ JsonPathKeyword JsonPathParseItem JsonPathParseResult JsonPathPredicateCallback +JsonPathSpec JsonPathString JsonPathVariable JsonQuotes @@ -1441,9 +1442,15 @@ JsonTableParseContext JsonTablePath JsonTablePathScan JsonTablePathSpec +JsonTableJoinState JsonTablePlan JsonTablePlanRowSource +JsonTablePlanSpec JsonTablePlanState +JsonTablePlanStateType +JsonTablePlanJoinType +JsonTablePlanType +JsonTableScanState JsonTableSiblingJoin JsonTokenType JsonTransformStringValuesAction -- 2.43.0