From 234947aea76cc9c3dfa5a200cf3bc395e52f582b Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Thu, 18 Jan 2024 18:00:06 +0900 Subject: [PATCH v49 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 | 94 +++++- 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 | 127 +++++++- src/backend/utils/adt/jsonpath_exec.c | 148 +++++++++- src/backend/utils/adt/ruleutils.c | 49 +++- src/include/nodes/parsenodes.h | 2 + src/include/nodes/primnodes.h | 22 ++ src/include/parser/kwlist.h | 1 + .../test/expected/sql-sqljson_jsontable.c | 14 +- .../expected/sql-sqljson_jsontable.stderr | 8 + .../expected/sql-sqljson_jsontable.stdout | 1 + .../ecpg/test/sql/sqljson_jsontable.pgc | 8 + .../regress/expected/sqljson_jsontable.out | 274 ++++++++++++++++++ src/test/regress/sql/sqljson_jsontable.sql | 129 +++++++++ src/tools/pgindent/typedefs.list | 2 + 17 files changed, 895 insertions(+), 26 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1467791666..feace8c773 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18893,6 +18893,22 @@ DETAIL: Missing "]" after array dimensions. a given output row. + + JSON data stored at a nested level of the row pattern can be extracted using + the NESTED PATH clause. Each + NESTED PATH clause can be used to generate one or more + columns using the data from a nested level of the row pattern, which can be + specified using a COLUMNS clause. Rows constructed from + such columns are called child rows and are joined + agaist the row constructed from the columns specified in the parent + COLUMNS clause to get the row in the final view. Child + columns may themselves contain a NESTED PATH + specifification thus allowing to extract data located at arbitrary nesting + levels. Columns produced by NESTED PATHs at the same + level are considered to be siblings and are joined + with each other before joining to the parent row. + + The rows produced by JSON_TABLE are laterally joined to the row that generated them, so you do not have to explicitly join @@ -18924,6 +18940,8 @@ where json_table_column is: { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON ERROR | name type EXISTS PATH path_expression { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR + | NESTED PATH json_path_specification AS path_name + COLUMNS ( json_table_column , ... ) @@ -18971,7 +18989,8 @@ where json_table_column is: Adds an ordinality column that provides sequential row numbering starting - from 1. + from 1. Each NESTED PATH (see below) gets its own + counter for any nested ordinality columns. @@ -19058,6 +19077,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. + + + @@ -19173,6 +19219,52 @@ SELECT jt.* FROM 1 | horror | Psycho | "Alfred Hitchcock" 2 | thriller | Vertigo | "Alfred Hitchcock" (2 rows) + + + + The following is a modified version of the above query to show the usage + of NESTED PATH for populating title and director + columns, illustrating how they are joined to the parent columns id and + kind: + +SELECT jt.* FROM + my_films, + JSON_TABLE ( js, '$.favorites[*] ? (@.films[*].director == $filter)' + PASSING 'Alfred Hitchcock' AS filter + COLUMNS ( + id FOR ORDINALITY, + kind text PATH '$.kind', + NESTED PATH '$.films[*]' COLUMNS ( + title text FORMAT JSON PATH '$.title' OMIT QUOTES, + director text PATH '$.director' KEEP QUOTES))) AS jt; + id | kind | title | director +----+----------+---------+-------------------- + 1 | horror | Psycho | "Alfred Hitchcock" + 2 | thriller | Vertigo | "Alfred Hitchcock" +(2 rows) + + + + The following is the same query but without the filter in the root + path: + +SELECT jt.* FROM + my_films, + JSON_TABLE ( js, '$.favorites[*]' + COLUMNS ( + id FOR ORDINALITY, + kind text PATH '$.kind', + NESTED PATH '$.films[*]' COLUMNS ( + title text FORMAT JSON PATH '$.title' OMIT QUOTES, + director text PATH '$.director' KEEP QUOTES))) AS jt; + id | kind | title | director +----+----------+-----------------+-------------------- + 1 | comedy | Bananas | "Woody Allen" + 1 | comedy | The Dinner Game | "Francis Veber" + 2 | horror | Psycho | "Alfred Hitchcock" + 3 | thriller | Vertigo | "Alfred Hitchcock" + 4 | drama | Yojimbo | "Akira Kurosawa" +(5 rows) 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 fcd0d834b2..e1df1894b6 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 6ea68722e3..6dcf5cceb8 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -752,7 +752,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 @@ -881,8 +881,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 */ @@ -14218,6 +14221,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: @@ -17636,6 +17668,7 @@ unreserved_keyword: | MOVE | NAME_P | NAMES + | NESTED | NEW | NEXT | NFC @@ -18250,6 +18283,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 e0e2c7d61e..b4e6afbdc9 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 - @@ -170,13 +177,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); + } } } @@ -232,6 +258,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) { @@ -241,9 +273,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 @@ -301,6 +336,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, break; } + case JTC_NESTED: + continue; + default: elog(ERROR, "unknown JSON_TABLE column type: %d", (int) rawc->coltype); break; @@ -312,7 +350,14 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, tf->colvalexprs = lappend(tf->colvalexprs, colexpr); } - return makeJsonTablePathScan(pathspec, errorOnError); + /* End of column range */ + colMax = list_length(tf->colvalexprs) - 1; + + /* Transform recursively nested columns */ + childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns); + + return makeJsonTablePathScan(pathspec, errorOnError, colMin, colMax, + childplan); } /* @@ -395,11 +440,50 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, return jfexpr; } +/* + * 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; + + /* transform all nested columns into UNION join */ + 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); + + /* Join nested plan with previous sibling nested plans. */ + if (plan) + plan = makeJsonTableSiblingJoin(plan, nested); + else + plan = nested; + } + + return plan; +} + /* * Create a JsonTablePlan for given path and ON ERROR behavior. */ static JsonTablePlan * -makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError) +makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError, + int colMin, int colMax, + JsonTablePlan *childplan) { JsonTablePathScan *scan = makeNode(JsonTablePathScan); char *pathstring; @@ -416,5 +500,22 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError) scan->path = makeJsonTablePath(value, pathspec->name); scan->errorOnError = errorOnError; + scan->colMin = colMin; + scan->colMax = colMax; + + scan->child = childplan; + return (JsonTablePlan *) scan; } + +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 75c468bc08..d9ad80b077 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -202,6 +202,23 @@ 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; + + /**/ + bool advanceNested; + bool advanceRight; + bool reset; } JsonTablePlanState; /* Random number to identify JsonTableExecContext for sanity checking */ @@ -213,6 +230,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,15 +360,18 @@ 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); static void JsonTableResetRowPattern(JsonTablePlanState *plan, Datum item); +static void JsonTablePlanReset(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 JsonTablePlanNextRow(JsonTablePlanState *planstate); +static bool JsonTablePlanPathNextRow(JsonTablePlanState *planstate); const TableFuncRoutine JsonbTableRoutine = { @@ -4087,8 +4113,11 @@ JsonTableInitOpaque(TableFuncScanState *state, int natts) } } + cxt->colplanstates = palloc(sizeof(JsonTablePlanState *) * + list_length(tf->colvalexprs)); + /* Initialize plan */ - cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, args, + cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, NULL, args, CurrentMemoryContext); state->opaque = cxt; @@ -4113,19 +4142,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 +4167,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 <= 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; @@ -4192,13 +4239,33 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item) planstate->ordinal = 0; } +/* Recursively set 'reset' flag of planstate and its child nodes */ +static void +JsonTablePlanReset(JsonTablePlanState *planstate) +{ + if (IsA(planstate->plan, JsonTablePathScan)) + { + planstate->reset = true; + planstate->advanceNested = false; + + if (planstate->nested) + JsonTablePlanReset(planstate->nested); + } + else if (IsA(planstate->plan, JsonTableSiblingJoin)) + { + JsonTablePlanReset(planstate->left); + JsonTablePlanReset(planstate->right); + planstate->advanceRight = false; + } +} + /* * Fetch next row from a JsonTablePlan's path evaluation result. * * 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; @@ -4226,6 +4293,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->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 (!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. @@ -4256,7 +4396,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 c9e3ac88cb..cd1c695f38 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 ") @@ -11620,11 +11625,44 @@ 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, 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; @@ -11705,6 +11743,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->colMax >= scan->colMin); + if (PRETTY_INDENT(context)) context->indentLevel -= PRETTYINDENT_VAR; @@ -11768,7 +11810,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 9946ef6387..0a8e6af42b 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1815,6 +1815,7 @@ typedef enum JsonTableColumnType JTC_REGULAR, JTC_EXISTS, JTC_FORMATTED, + JTC_NESTED, } JsonTableColumnType; /* @@ -1831,6 +1832,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 9c44240d0c..36dce9f2bc 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1865,8 +1865,30 @@ typedef struct JsonTablePathScan /* 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. */ + JsonTablePlan *child; } JsonTablePathScan; +/* + * JsonTableSiblingJoin - + * Plan to union-join rows of nested paths of the same level + */ +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 2d4a0c6a07..6344d7cfc6 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 42a1b176e7..b2a0f11eb6 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 d3713cff5c..9262cf71a1 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 615507e602..1e6f358a89 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 6d721bb37f..aa2b4494bb 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 77c04d9763..f11c78c2d0 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; @@ -634,3 +733,178 @@ 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} + ]' +); +-- 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_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, 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 '$ ? (@ >= $y)' + ) + ) + ) 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) + diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index bdce46361d..a00bc030db 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; @@ -288,3 +315,105 @@ 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} + ]' +); + +-- 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_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, 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 '$ ? (@ >= $y)' + ) + ) + ) jt; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index ba8471b144..3bedea5338 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1327,6 +1327,7 @@ JsonPathMutableContext JsonPathParseItem JsonPathParseResult JsonPathPredicateCallback +JsonPathSpec JsonPathString JsonPathVariable JsonQuotes @@ -1345,6 +1346,7 @@ JsonTablePathSpec JsonTablePlan JsonTablePlanRowSource JsonTablePlanState +JsonTableSiblingJoin JsonTokenType JsonTransformStringValuesAction JsonTypeCategory -- 2.43.0