From 7189446ed35811814e8d4b8b4a2f238e4234b585 Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Thu, 18 Jan 2024 18:00:06 +0900 Subject: [PATCH v47 2/2] JSON_TABLE: Add support for NESTED columns MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Author: Nikita Glukhov Author: Teodor Sigaev Author: Oleg Bartunov Author: Alexander Korotkov Author: Andrew Dunstan Author: Amit Langote Author: Jian He 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 --- doc/src/sgml/func.sgml | 85 +++++- src/backend/catalog/sql_features.txt | 2 +- src/backend/nodes/nodeFuncs.c | 2 + src/backend/parser/gram.y | 38 ++- src/backend/parser/parse_jsontable.c | 125 +++++++- src/backend/utils/adt/jsonpath_exec.c | 202 ++++++++++++- src/backend/utils/adt/ruleutils.c | 35 +++ src/include/nodes/parsenodes.h | 2 + src/include/nodes/primnodes.h | 22 ++ src/include/parser/kwlist.h | 1 + .../test/expected/sql-sqljson_jsontable.c | 15 + .../expected/sql-sqljson_jsontable.stderr | 15 + .../ecpg/test/sql/sqljson_jsontable.pgc | 12 +- .../regress/expected/sqljson_jsontable.out | 274 ++++++++++++++++++ src/test/regress/sql/sqljson_jsontable.sql | 129 +++++++++ src/tools/pgindent/typedefs.list | 2 + 16 files changed, 929 insertions(+), 32 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 46f7a87d0a..3d690b7d3a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18888,14 +18888,29 @@ DETAIL: Missing "]" after array dimensions. schema of the created view. For each column to be constructed, this clause provides a separate path expression that evaluates the row pattern, extracts a JSON item, and returns it as a - separate SQL value for the specified column. + separate SQL value for the specified column. If the required value + is stored in a nested level of the row pattern, it can be extracted + using the NESTED PATH subclause. Joining the + columns returned by NESTED PATH can add multiple + new rows to the constructed view. Such rows are called + child rows, as opposed to the + parent row that generates them. The rows produced by JSON_TABLE are laterally joined to the row that generated them, so you do not have to explicitly join the constructed view with the original table holding JSON - data. + + + Each NESTED PATH clause can generate one or more + columns. Columns produced by NESTED PATHs at the + same level are considered to be siblings, + while a column produced by a NESTED PATH is + considered to be a child of the column produced by a + NESTED PATH or row expression at a higher level. + Sibling columns are always joined first. Once they are processed, + the resulting rows are joined to the parent row. @@ -18926,6 +18941,8 @@ where json_table_column is: { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON ERROR | name type EXISTS PATH json_path_specification { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR + | NESTED PATH json_path_specification AS path_name + COLUMNS ( json_table_column , ... ) @@ -18972,7 +18989,9 @@ where json_table_column is: Adds an ordinality column that provides sequential row numbering. You can have only one ordinality column per table. Row numbering - is 1-based. + is 1-based. For child rows that result from the + NESTED PATH clauses (see below), the parent row + number is repeated. @@ -19079,6 +19098,33 @@ where json_table_column is: + + + + NESTED PATH json_path_specification AS json_path_name + COLUMNS ( json_table_column , ... ) + + + + + Extracts SQL/JSON items from nested levels of the row pattern, + generates one or more columns as defined by the COLUMNS + subclause, and inserts the extracted SQL/JSON items into each row of these + columns. The json_table_column expression in + the COLUMNS subclause uses the same syntax as in the + parent COLUMNS clause. + + + + The NESTED PATH syntax is recursive, + so you can go down multiple nested levels by specifying several + NESTED PATH subclauses within each other. + It allows to unnest the hierarchy of JSON objects and arrays + in a single function invocation rather than chaining several + JSON_TABLE expressions in an SQL statement. + + + @@ -19142,6 +19188,39 @@ SELECT jt.* FROM 3 | thriller | Vertigo | Alfred Hitchcock 4 | drama | Yojimbo | Akira Kurosawa (4 rows) + + + + + Find a director that has done films in two different genres: + +SELECT + director1 AS director, title1, kind1, title2, kind2 +FROM + my_films, + JSON_TABLE ( js, '$.favorites' AS favs COLUMNS ( + NESTED PATH '$[*]' AS films1 COLUMNS ( + kind1 text PATH '$.kind', + NESTED PATH '$.films[*]' AS film1 COLUMNS ( + title1 text PATH '$.title', + director1 text PATH '$.director') + ), + NESTED PATH '$[*]' AS films2 COLUMNS ( + kind2 text PATH '$.kind', + NESTED PATH '$.films[*]' AS film2 COLUMNS ( + title2 text PATH '$.title', + director2 text PATH '$.director' + ) + ) + ) + PLAN (favs OUTER ((films1 INNER film1) CROSS (films2 INNER film2))) + ) AS jt + WHERE kind1 > kind2 AND director1 = director2; + + director | title1 | kind1 | title2 | kind2 +------------------+---------+----------+--------+-------- + Alfred Hitchcock | Vertigo | thriller | Psycho | horror +(1 row) diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 80ac59fba4..c002f37202 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 333fee7d19..1fd70b7e0c 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -4156,6 +4156,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 cf0c56bd19..77eceef770 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -750,7 +750,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 @@ -879,8 +879,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 */ @@ -14199,6 +14202,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: @@ -17617,6 +17649,7 @@ unreserved_keyword: | MOVE | NAME_P | NAMES + | NESTED | NEW | NEXT | NFC @@ -18229,6 +18262,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 3c752f9d9e..ab1ffc1ac5 100644 --- a/src/backend/parser/parse_jsontable.c +++ b/src/backend/parser/parse_jsontable.c @@ -49,11 +49,18 @@ static Node *transformJsonTableColumn(JsonTableColumn *jtc, bool errorOnError); static bool typeIsComposite(Oid typid); static JsonTablePlan *makeJsonTablePlan(JsonTablePathSpec *pathspec, - bool errorOnError); + bool errorOnError, + int colMin, int colMax, + Node *childplan); static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt, List *columns); static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name); static char *generateJsonTablePathName(JsonTableParseContext *cxt); +static Node *transformJsonTableChildPlan(JsonTableParseContext *cxt, + List *columns); +static Node *transformNestedJsonTableColumn(JsonTableParseContext *cxt, + JsonTableColumn *jtc); +static Node *makeJsonTableSiblingJoin(Node *lnode, Node *rnode); /* * transformJsonTable - @@ -167,13 +174,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); + } } } @@ -228,6 +254,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; + Node *childplan; + + /* Start of column range */ + colMin = list_length(tf->colvalexprs); foreach(col, columns) { @@ -293,6 +325,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, break; } + case JTC_NESTED: + continue; + default: elog(ERROR, "unknown JSON_TABLE column type: %d", rawc->coltype); break; @@ -304,7 +339,13 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, tf->colvalexprs = lappend(tf->colvalexprs, colexpr); } - return makeJsonTablePlan(pathspec, errorOnError); + /* End of column range */ + colMax = list_length(tf->colvalexprs) - 1; + + /* Transform recursively nested columns */ + childplan = transformJsonTableChildPlan(cxt, columns); + + return makeJsonTablePlan(pathspec, errorOnError, colMin, colMax, childplan); } /* @@ -369,11 +410,70 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, return (Node *) jfexpr; } +/* + * Recursively transform child JSON_TABLE plan. + * + * 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 JsonTableSibling. + */ +static Node * +transformJsonTableChildPlan(JsonTableParseContext *cxt, + List *columns) +{ + Node *plan = NULL; + ListCell *lc; + + /* transform all nested columns into UNION join */ + foreach(lc, columns) + { + JsonTableColumn *col = castNode(JsonTableColumn, lfirst(lc)); + Node *nested; + + if (col->coltype != JTC_NESTED) + continue; + + nested = transformNestedJsonTableColumn(cxt, col); + + /* join transformed node with previous sibling nodes */ + if (plan) + plan = makeJsonTableSiblingJoin(plan, nested); + else + plan = nested; + } + + return plan; +} + +static Node * +transformNestedJsonTableColumn(JsonTableParseContext *cxt, + JsonTableColumn *jtc) +{ + if (jtc->pathspec->name == NULL) + jtc->pathspec->name = generateJsonTablePathName(cxt); + + return (Node *) transformJsonTableColumns(cxt, jtc->columns, + jtc->pathspec); +} + +static Node * +makeJsonTableSiblingJoin(Node *lnode, Node *rnode) +{ + JsonTableSiblingJoin *join = makeNode(JsonTableSiblingJoin); + + join->larg = lnode; + join->rarg = rnode; + + return (Node *) join; +} + /* * Create a JsonTablePlan for given path and ON ERROR behavior. */ static JsonTablePlan * -makeJsonTablePlan(JsonTablePathSpec *pathspec, bool errorOnError) +makeJsonTablePlan(JsonTablePathSpec *pathspec, bool errorOnError, + int colMin, int colMax, Node *childplan) { JsonTablePlan *plan = makeNode(JsonTablePlan); char *pathstring; @@ -388,6 +488,11 @@ makeJsonTablePlan(JsonTablePathSpec *pathspec, bool errorOnError) plan->path = makeJsonTablePath(value, pathspec->name); plan->errorOnError = errorOnError; + plan->colMin = colMin; + plan->colMax = colMax; + + plan->child = childplan; + return plan; } diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index 58498a8df6..0ee70294d4 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -167,7 +167,7 @@ typedef struct JsonValueListIterator typedef struct JsonTablePlanState { /* Original plan */ - JsonTablePlan *plan; + Node *plan; /* jsonpath to evaluate against the input doc to get the row pattern. */ JsonPath *path; @@ -188,6 +188,23 @@ typedef struct JsonTablePlanState /* Counter for ORDINAL columns for JsonTableGetValue() to use */ 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; + + /**/ + bool advanceNested; + bool advanceRight; + bool reset; } JsonTablePlanState; /* Random number to identify JsonTableExecContext for sanity checking */ @@ -197,6 +214,7 @@ typedef struct JsonTableExecContext { int magic; JsonTablePlanState *rootplanstate; + JsonTablePlanState **colexprplans; } JsonTableExecContext; /* strict/lax flags is decomposed into four [un]wrap/error flags */ @@ -320,9 +338,14 @@ static void checkTimezoneIsUsedForCast(bool useTz, const char *type1, static void JsonTableInitOpaque(TableFuncScanState *state, int natts); static JsonTablePlanState *JsonTableInitPlan(JsonTableExecContext *cxt, - JsonTablePlan *plan, + Node *plan, + JsonTablePlanState *parentstate, List *args, MemoryContext mcxt); +static void JsonTableInitPathScan(JsonTableExecContext *cxt, + JsonTablePlanState *planstate, + List *args, + MemoryContext mcxt); static void JsonTableSetDocument(TableFuncScanState *state, Datum value); static void JsonTableResetContextItem(JsonTablePlanState *plan, Datum item); static void JsonTableRescan(JsonTablePlanState *planstate); @@ -331,6 +354,9 @@ static Datum JsonTableGetValue(TableFuncScanState *state, int colnum, Oid typid, int32 typmod, bool *isnull); static void JsonTableDestroyOpaque(TableFuncScanState *state); static bool JsonTablePlanNextRow(JsonTablePlanState *planstate); +static bool JsonTablePlanPathNextRow(JsonTablePlanState *planstate); +static void JsonTableRescan(JsonTablePlanState *planstate); +static void JsonTablePlanReset(JsonTablePlanState *planstate); const TableFuncRoutine JsonbTableRoutine = { @@ -4072,8 +4098,11 @@ JsonTableInitOpaque(TableFuncScanState *state, int natts) } } + cxt->colexprplans = palloc(sizeof(JsonTablePlanState *) * + list_length(tf->colvalexprs)); + /* Initialize plan */ - cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, args, + cxt->rootplanstate = JsonTableInitPlan(cxt, (Node *) rootplan, NULL, args, CurrentMemoryContext); state->opaque = cxt; @@ -4100,13 +4129,14 @@ JsonTableDestroyOpaque(TableFuncScanState *state) * Initialize information for evaluating a jsonpath given in * JsonTablePlan */ -static JsonTablePlanState * -JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan, - List *args, MemoryContext mcxt) +static void +JsonTableInitPathScan(JsonTableExecContext *cxt, + JsonTablePlanState *planstate, + List *args, MemoryContext mcxt) { - JsonTablePlanState *planstate = palloc0(sizeof(*planstate)); + JsonTablePlan *plan = (JsonTablePlan *) planstate->plan; + int i; - planstate->plan = plan; planstate->path = DatumGetJsonPathP(plan->path->value->constvalue); planstate->args = args; planstate->mcxt = AllocSetContextCreate(mcxt, "JsonTableExecContext", @@ -4116,6 +4146,43 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan, planstate->currentRow = PointerGetDatum(NULL); planstate->currentRowIsNull = true; + for (i = plan->colMin; i <= plan->colMax; i++) + cxt->colexprplans[i] = planstate; +} + +/* + * JsonTableInitPlan + * Recursively initializes a JsonTablePlan and any child plans + */ +static JsonTablePlanState * +JsonTableInitPlan(JsonTableExecContext *cxt, Node *plan, + JsonTablePlanState *parentstate, + List *args, MemoryContext mcxt) +{ + JsonTablePlanState *planstate = palloc0(sizeof(*planstate)); + + planstate->plan = plan; + planstate->parent = parentstate; + + if (IsA(plan, JsonTableSiblingJoin)) + { + JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan; + + planstate->left = JsonTableInitPlan(cxt, (Node *) join->larg, + parentstate, args, mcxt); + planstate->right = JsonTableInitPlan(cxt, (Node *) join->rarg, + parentstate, args, mcxt); + } + else + { + JsonTablePlan *scan = (JsonTablePlan *) plan; + + JsonTableInitPathScan(cxt, planstate, args, mcxt); + + planstate->nested = scan->child ? + JsonTableInitPlan(cxt, scan->child, planstate, args, mcxt) : NULL; + } + return planstate; } @@ -4171,11 +4238,43 @@ JsonTableResetContextItem(JsonTablePlanState *planstate, Datum item) static void JsonTableRescan(JsonTablePlanState *planstate) { - /* Reset plan iterator to the beginning of the item list */ - JsonValueListInitIterator(&planstate->found, &planstate->iter); - planstate->currentRow = PointerGetDatum(NULL); - planstate->currentRowIsNull = true; - planstate->ordinal = 0; + if (IsA(planstate, JsonTableSiblingJoin)) + { + JsonTableRescan(planstate->left); + JsonTableRescan(planstate->right); + planstate->advanceRight = false; + } + else + { + /* Reset plan iterator to the beginning of the item list */ + JsonValueListInitIterator(&planstate->found, &planstate->iter); + planstate->currentRow = PointerGetDatum(NULL); + planstate->currentRowIsNull = true; + planstate->ordinal = 0; + + if (planstate->nested) + JsonTableRescan(planstate->nested); + } +} + +/* Recursively set 'reset' flag of planstate and its child nodes */ +static void +JsonTablePlanReset(JsonTablePlanState *planstate) +{ + if (IsA(planstate->plan, JsonTableSiblingJoin)) + { + JsonTablePlanReset(planstate->left); + JsonTablePlanReset(planstate->right); + planstate->advanceRight = false; + } + else + { + planstate->reset = true; + planstate->advanceNested = false; + + if (planstate->nested) + JsonTablePlanReset(planstate->nested); + } } /* @@ -4184,7 +4283,7 @@ JsonTableRescan(JsonTablePlanState *planstate) * Returns false if the plan has run out of rows, true otherwise. */ static bool -JsonTablePlanNextRow(JsonTablePlanState *planstate) +JsonTablePlanPathNextRow(JsonTablePlanState *planstate) { JsonbValue *jbv = JsonValueListNext(&planstate->found, &planstate->iter); MemoryContext oldcxt; @@ -4212,6 +4311,79 @@ JsonTablePlanNextRow(JsonTablePlanState *planstate) return true; } +/* + * Fetch next row from a JsonTablePlan. + * + * Returns false if the plan has run out of rows, true otherwise. + */ +static bool +JsonTablePlanNextRow(JsonTablePlanState *planstate) +{ + if (IsA(planstate->plan, JsonTableSiblingJoin)) + { + if (planstate->advanceRight) + { + /* fetch next inner row */ + if (JsonTablePlanNextRow(planstate->right)) + return true; + + /* inner rows are exhausted */ + /* next outer row */ + planstate->advanceRight = false; + } + + while (!planstate->advanceRight) + { + /* fetch next outer row */ + if (!JsonTablePlanNextRow(planstate->left)) + { + if (!JsonTablePlanNextRow(planstate->right)) + return false; /* end of scan */ + + planstate->advanceRight = true; /* next inner row */ + } + + break; + } + } + else + { + /* reset context item if requested */ + if (planstate->reset) + { + JsonTablePlanState *parent = planstate->parent; + + Assert(parent != NULL && !parent->currentRowIsNull); + JsonTableResetContextItem(planstate, parent->currentRow); + planstate->reset = false; + } + + if (planstate->advanceNested) + { + /* fetch next nested row */ + planstate->advanceNested = JsonTablePlanNextRow(planstate->nested); + if (planstate->advanceNested) + return true; + } + + for (;;) + { + if (!JsonTablePlanPathNextRow(planstate)) + return false; + + if (planstate->nested == NULL) + break; + + JsonTablePlanReset(planstate->nested); + planstate->advanceNested = JsonTablePlanNextRow(planstate->nested); + if (planstate->advanceNested || planstate->nested) + break; + } + } + + return true; +} + /* * JsonTableFetchRow * Prepare the next "current" row for upcoming GetValue calls. @@ -4242,7 +4414,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->colexprplans[colnum]; Datum result; /* Row pattern value is NULL */ diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 0187074d84..f80e7dd194 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -11583,6 +11583,37 @@ get_xmltable(TableFunc *tf, deparse_context *context, bool showimplicit) appendStringInfoChar(buf, ')'); } +/* + * get_json_nested_columns - Parse back nested JSON_TABLE columns + */ +static void +get_json_table_nested_columns(TableFunc *tf, Node *node, + deparse_context *context, bool showimplicit, + bool needcomma) +{ + if (IsA(node, JsonTableSiblingJoin)) + { + JsonTableSiblingJoin *n = (JsonTableSiblingJoin *) node; + + get_json_table_nested_columns(tf, n->larg, context, showimplicit, + needcomma); + get_json_table_nested_columns(tf, n->rarg, context, showimplicit, true); + } + else + { + JsonTablePlan *n = castNode(JsonTablePlan, node); + + if (needcomma) + appendStringInfoChar(context->buf, ','); + + appendStringInfoChar(context->buf, ' '); + appendContextKeyword(context, "NESTED PATH ", 0, 0, 0); + get_const_expr(n->path->value, context, -1); + appendStringInfo(context->buf, " AS %s", quote_identifier(n->path->name)); + get_json_table_columns(tf, n, context, showimplicit); + } +} + /* * get_json_table_columns - Parse back JSON_TABLE columns */ @@ -11668,6 +11699,10 @@ get_json_table_columns(TableFunc *tf, JsonTablePlan *plan, get_json_expr_options(colexpr, context, default_behavior); } + if (plan->child) + get_json_table_nested_columns(tf, plan->child, context, showimplicit, + plan->colMax >= plan->colMin); + if (PRETTY_INDENT(context)) context->indentLevel -= PRETTYINDENT_VAR; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 7f5b742b3c..9cc7eda6cb 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1811,6 +1811,7 @@ typedef enum JsonTableColumnType JTC_REGULAR, JTC_EXISTS, JTC_FORMATTED, + JTC_NESTED, } JsonTableColumnType; /* @@ -1827,6 +1828,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 c5b8966d33..5bc8ab4666 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1855,8 +1855,30 @@ typedef struct JsonTablePlan /* ERROR/EMPTY ON ERROR behavior */ bool errorOnError; + + /* + * 0-based index in TableFunc.colvalexprs of the 1st and the last column + * covered by this plan. + */ + int colMin; + int colMax; + + /* Plan for nested columns, if any. */ + Node *child; } JsonTablePlan; +/* + * JsonTableSiblingJoin - + * Plan to union-join rows of nested paths of the same level + */ +typedef struct JsonTableSiblingJoin +{ + NodeTag type; + + Node *larg; + Node *rarg; +} JsonTableSiblingJoin; + /* ---------------- * NullTest * diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index d57c0f2c42..cfc76ba181 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 eb690b9451..9854e574b8 100644 --- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c +++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c @@ -113,19 +113,34 @@ if (sqlca.sqlcode < 0) sqlprint();} #line 14 "sqljson_jsontable.pgc" +<<<<<<< HEAD { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( foo int , bar int , baz int ) ) jt", ECPGt_EOIT, ECPGt_EORT); #line 21 "sqljson_jsontable.pgc" if (sqlca.sqlcode < 0) sqlprint();} #line 21 "sqljson_jsontable.pgc" +======= + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) , nested path '$' as p12 columns ( bar int ) ) , nested path '$' as p2 columns ( nested path '$' as p21 columns ( baz int ) ) ) plan ( p1 ) ) jt", ECPGt_EOIT, ECPGt_EORT); +#line 26 "sqljson_jsontable.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 26 "sqljson_jsontable.pgc" +>>>>>>> be9e710d74 (JSON_TABLE) // error { ECPGdisconnect(__LINE__, "CURRENT"); +<<<<<<< HEAD #line 24 "sqljson_jsontable.pgc" if (sqlca.sqlcode < 0) sqlprint();} #line 24 "sqljson_jsontable.pgc" +======= +#line 29 "sqljson_jsontable.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 29 "sqljson_jsontable.pgc" +>>>>>>> be9e710d74 (JSON_TABLE) 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 42536c0006..86acf28771 100644 --- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr +++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr @@ -4,6 +4,7 @@ [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ECPGsetcommit on line 14: action "on"; connection "ecpg1_regression" [NO_PID]: sqlca: code: 0, state: 00000 +<<<<<<< HEAD [NO_PID]: ecpg_execute on line 16: query: select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( foo int , bar int , baz int ) ) jt; with 0 parameter(s) on connection ecpg1_regression [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_execute on line 16: using PQexec @@ -13,5 +14,19 @@ [NO_PID]: raising sqlcode -202 on line 16: too few arguments on line 16 [NO_PID]: sqlca: code: -202, state: 07002 SQL error: too few arguments on line 16 +======= +[NO_PID]: ecpg_execute on line 16: query: select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) , nested path '$' as p12 columns ( bar int ) ) , nested path '$' as p2 columns ( nested path '$' as p21 columns ( baz int ) ) ) plan ( p1 ) ) jt; with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 16: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_check_PQresult on line 16: bad response - ERROR: invalid JSON_TABLE plan +LINE 1: ...ted path '$' as p21 columns ( baz int ) ) ) plan ( p1 ) ) jt + ^ +DETAIL: PATH name mismatch: expected p0 but p1 is given. +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: raising sqlstate 42601 (sqlcode -400): invalid JSON_TABLE plan on line 16 +[NO_PID]: sqlca: code: -400, state: 42601 +SQL error: invalid JSON_TABLE plan on line 16 +>>>>>>> be9e710d74 (JSON_TABLE) [NO_PID]: ecpg_finish: connection ecpg1_regression closed [NO_PID]: sqlca: code: 0, state: 00000 diff --git a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc index a161479f72..9cd1498bf4 100644 --- a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc +++ b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc @@ -19,7 +19,17 @@ main () bar int, baz int )) jt; - // error + + EXEC SQL SELECT * FROM JSON_TABLE(jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ); EXEC SQL DISCONNECT; diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index 53495935ba..fb4e1da99b 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -218,6 +218,29 @@ FROM json_table_test vals (14 rows) -- JSON_TABLE: Test backward parsing +CREATE VIEW jsonb_table_view1 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 + ) + ) + ) + ); CREATE VIEW jsonb_table_view2 AS SELECT * FROM JSON_TABLE( @@ -267,6 +290,73 @@ SELECT * FROM ia int[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$')); +\sv jsonb_table_view1 +CREATE OR REPLACE VIEW public.jsonb_table_view1 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, + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + a11 text PATH '$."a11"', + a21 text PATH '$."a21"', + a22 text PATH '$."a22"', + NESTED PATH '$[1]' AS p1 + COLUMNS ( + id FOR ORDINALITY, + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + a11 text PATH '$."a11"', + a21 text PATH '$."a21"', + a22 text PATH '$."a22"', + NESTED PATH '$[*]' AS "p1 1" + COLUMNS ( + id FOR ORDINALITY, + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + a11 text PATH '$."a11"', + a21 text PATH '$."a21"', + a22 text PATH '$."a22"' + ) + ), + NESTED PATH '$[2]' AS p2 + COLUMNS ( + id FOR ORDINALITY, + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + a11 text PATH '$."a11"', + a21 text PATH '$."a21"', + a22 text PATH '$."a22"' + NESTED PATH '$[*]' AS "p2:1" + COLUMNS ( + id FOR ORDINALITY, + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + a11 text PATH '$."a11"', + a21 text PATH '$."a21"', + a22 text PATH '$."a22"' + ), + NESTED PATH '$[*]' AS p22 + COLUMNS ( + id FOR ORDINALITY, + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + a11 text PATH '$."a11"', + a21 text PATH '$."a21"', + a22 text PATH '$."a22"' + ) + ) + ) + ) \sv jsonb_table_view2 CREATE OR REPLACE VIEW public.jsonb_table_view2 AS SELECT "int", @@ -365,6 +455,14 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS jba jsonb[] PATH '$' ) ) +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view1; + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Table Function Scan on "json_table" + Output: "json_table".id, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22 + Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"', NESTED PATH '$[1]' AS p1 COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"')), NESTED PATH '$[2]' AS p2 COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"' NESTED PATH '$[*]' AS "p2:1" COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"'), NESTED PATH '$[*]' AS p22 COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"')))) +(3 rows) + EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- @@ -448,6 +546,7 @@ SELECT * FROM ] (1 row) +DROP VIEW jsonb_table_view1; DROP VIEW jsonb_table_view2; DROP VIEW jsonb_table_view3; DROP VIEW jsonb_table_view4; @@ -599,6 +698,181 @@ SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH W ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ... ^ +-- 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} + ]' +); +-- unspecified plan (outer, union) +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 int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | + 2 | 2 | 2 | + 2 | 2 | 3 | + 2 | 2 | | 10 + 2 | 2 | | + 2 | 2 | | 20 + 3 | 3 | 1 | + 3 | 3 | 2 | + 4 | -1 | 1 | + 4 | -1 | 2 | +(11 rows) + +-- Should succeed (JSON arguments are passed to root and nested paths) +SELECT * +FROM + generate_series(1, 4) 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 +---+---+--------------+--- + 2 | 1 | [1, 2, 3] | 1 + 2 | 1 | [1, 2, 3] | 2 + 2 | 1 | [1, 2, 3] | 3 + 3 | 1 | [1, 2, 3] | 1 + 3 | 1 | [1, 2, 3] | 2 + 3 | 1 | [1, 2, 3] | 3 + 3 | 1 | [2, 3, 4, 5] | 2 + 3 | 1 | [2, 3, 4, 5] | 3 + 3 | 1 | [2, 3, 4, 5] | 4 + 3 | 1 | [2, 3, 4, 5] | 5 + 4 | 1 | [1, 2, 3] | 1 + 4 | 1 | [1, 2, 3] | 2 + 4 | 1 | [1, 2, 3] | 3 + 4 | 1 | [2, 3, 4, 5] | 2 + 4 | 1 | [2, 3, 4, 5] | 3 + 4 | 1 | [2, 3, 4, 5] | 4 + 4 | 1 | [2, 3, 4, 5] | 5 + 4 | 1 | [3, 4, 5, 6] | 3 + 4 | 1 | [3, 4, 5, 6] | 4 + 4 | 1 | [3, 4, 5, 6] | 5 + 4 | 1 | [3, 4, 5, 6] | 6 + 2 | 2 | [1, 2, 3] | 2 + 2 | 2 | [1, 2, 3] | 3 + 3 | 2 | [1, 2, 3] | 2 + 3 | 2 | [1, 2, 3] | 3 + 3 | 2 | [2, 3, 4, 5] | 2 + 3 | 2 | [2, 3, 4, 5] | 3 + 3 | 2 | [2, 3, 4, 5] | 4 + 3 | 2 | [2, 3, 4, 5] | 5 + 4 | 2 | [1, 2, 3] | 2 + 4 | 2 | [1, 2, 3] | 3 + 4 | 2 | [2, 3, 4, 5] | 2 + 4 | 2 | [2, 3, 4, 5] | 3 + 4 | 2 | [2, 3, 4, 5] | 4 + 4 | 2 | [2, 3, 4, 5] | 5 + 4 | 2 | [3, 4, 5, 6] | 3 + 4 | 2 | [3, 4, 5, 6] | 4 + 4 | 2 | [3, 4, 5, 6] | 5 + 4 | 2 | [3, 4, 5, 6] | 6 + 2 | 3 | [1, 2, 3] | 3 + 3 | 3 | [1, 2, 3] | 3 + 3 | 3 | [2, 3, 4, 5] | 3 + 3 | 3 | [2, 3, 4, 5] | 4 + 3 | 3 | [2, 3, 4, 5] | 5 + 4 | 3 | [1, 2, 3] | 3 + 4 | 3 | [2, 3, 4, 5] | 3 + 4 | 3 | [2, 3, 4, 5] | 4 + 4 | 3 | [2, 3, 4, 5] | 5 + 4 | 3 | [3, 4, 5, 6] | 3 + 4 | 3 | [3, 4, 5, 6] | 4 + 4 | 3 | [3, 4, 5, 6] | 5 + 4 | 3 | [3, 4, 5, 6] | 6 +(52 rows) + -- Should fail (JSON arguments are not passed to column paths) SELECT * FROM JSON_TABLE( diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 86cb2e7d83..22629188ba 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -118,6 +118,30 @@ FROM json_table_test vals -- JSON_TABLE: Test backward parsing +CREATE VIEW jsonb_table_view1 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 + ) + ) + ) + ); + CREATE VIEW jsonb_table_view2 AS SELECT * FROM JSON_TABLE( @@ -172,12 +196,14 @@ SELECT * FROM ta text[] PATH '$', jba jsonb[] PATH '$')); +\sv jsonb_table_view1 \sv jsonb_table_view2 \sv jsonb_table_view3 \sv jsonb_table_view4 \sv jsonb_table_view5 \sv jsonb_table_view6 +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view1; EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2; EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3; EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4; @@ -205,6 +231,7 @@ SELECT * FROM "text" text PATH '$' )) json_table_func; +DROP VIEW jsonb_table_view1; DROP VIEW jsonb_table_view2; DROP VIEW jsonb_table_view3; DROP VIEW jsonb_table_view4; @@ -267,6 +294,108 @@ SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PAT SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER KEEP QUOTES)); SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER OMIT QUOTES)); +-- 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} + ]' +); + +-- unspecified plan (outer, union) +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 int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + ) jt; + + +-- Should succeed (JSON arguments are passed to root and nested paths) +SELECT * +FROM + generate_series(1, 4) 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; + -- Should fail (JSON arguments are not passed to column paths) SELECT * FROM JSON_TABLE( diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 206ed990c7..f305ee6e64 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1325,6 +1325,7 @@ JsonPathMutableContext JsonPathParseItem JsonPathParseResult JsonPathPredicateCallback +JsonPathSpec JsonPathString JsonPathVariable JsonQuotes @@ -1341,6 +1342,7 @@ JsonTablePath JsonTablePathSpec JsonTablePlan JsonTablePlanState +JsonTableSiblingJoin JsonTokenType JsonTransformStringValuesAction JsonTypeCategory -- 2.43.0