From: Andrew Dunstan Date: Mon, 13 Sep 2021 18:03:14 -0400 Subject: [PATCH 3/4] JSON_TABLE PLAN DEFAULT clause --- doc/src/sgml/func.sgml | 118 ++++++++++++++++--- src/backend/nodes/copyfuncs.c | 4 + src/backend/nodes/equalfuncs.c | 3 + src/backend/nodes/outfuncs.c | 3 + src/backend/nodes/readfuncs.c | 3 + src/backend/parser/gram.y | 41 ++++++- src/backend/parser/parse_jsontable.c | 13 ++- src/backend/utils/adt/jsonpath_exec.c | 120 +++++++++++++++----- src/backend/utils/adt/ruleutils.c | 9 ++ src/include/nodes/parsenodes.h | 13 +++ src/include/nodes/primnodes.h | 3 + src/include/parser/kwlist.h | 1 + src/test/regress/expected/jsonb_sqljson.out | 118 +++++++++++++++++++ src/test/regress/sql/jsonb_sqljson.sql | 65 +++++++++++ src/tools/pgindent/typedefs.list | 1 + 15 files changed, 462 insertions(+), 53 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index e86c8992c3..746f24501a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19207,6 +19207,10 @@ FROM JSON_TABLE ( context_item, path_expression PASSING { value AS varname } , ... COLUMNS ( json_table_column , ... ) + + PLAN DEFAULT ( { INNER | OUTER } , { CROSS | UNION } + | { CROSS | UNION } , { INNER | OUTER } ) + ) where json_table_column is: @@ -19268,7 +19272,8 @@ where json_table_column is: 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. + data. Optionally, you can specify how to join the columns returned + by NESTED PATH using the PLAN DEFAULT clause. @@ -19281,22 +19286,6 @@ where json_table_column is: the resulting rows are joined to the parent row. - - Columns with parent/child relationship are joined using - LEFT OUTER JOIN, so that the parent row - is always included into the output even if it does not have any child rows - after joining the data returned by NESTED PATH, - with NULL values inserted into the child columns if the corresponding - values are missing. - - - - Sibling columns are joined using - FULL OUTER JOIN ON FALSE, so that both parent and child - rows are included into the output, with NULL values inserted - into both child and parrent columns for all missing values. - - Parameters @@ -19468,6 +19457,10 @@ where json_table_column is: JSON_TABLE expressions in an SQL statement. + + You can use the PLAN DEFAULT clause to define how + to join the columns returned by NESTED PATH clauses. + @@ -19490,8 +19483,99 @@ where json_table_column is: + + + PLAN DEFAULT ( option , ... ) + + + + Defines how to join the data returned by NESTED PATH + clauses to the constructed view. The INNER and + OUTER options define the joining plan for parent/child + columns, while UNION and CROSS + affect the sibling columns. You can override the default plans for all + columns at once. + + + + To join columns with parent/child relationship, you can use: + + + + + INNER + + + + + Use INNER JOIN, so that the parent row + is omitted from the output if it does not have any child rows + after joining the data returned by NESTED PATH. + + + + + + + OUTER + + + + + Use LEFT OUTER JOIN, so that the parent row + is always included into the output even if it does not have any child rows + after joining the data returned by NESTED PATH, with NULL values + inserted into the child columns if the corresponding + values are missing. + + + This is the default option for joining columns with parent/child relationship. + + + + + + + To join sibling columns, you can use: + + + + + + UNION + + + + + Use FULL OUTER JOIN ON FALSE, so that both parent and child + rows are included into the output, with NULL values inserted + into both child and parrent columns for all missing values. + + + This is the default option for joining sibling columns. + + + + + + + CROSS + + + + + Use CROSS JOIN, so that the output includes + a row for every possible combination of rows from the left-hand + and the right-hand columns. + + + + + + + diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 5e35e57697..ffa8f7e39b 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2677,6 +2677,7 @@ _copyJsonTable(const JsonTable *from) COPY_NODE_FIELD(columns); COPY_NODE_FIELD(on_error); COPY_NODE_FIELD(alias); + COPY_SCALAR_FIELD(join_type); COPY_SCALAR_FIELD(location); return newnode; @@ -2715,6 +2716,8 @@ _copyJsonTableParentNode(const JsonTableParentNode *from) COPY_NODE_FIELD(path); COPY_NODE_FIELD(child); + COPY_SCALAR_FIELD(outerJoin); + COPY_SCALAR_FIELD(unionJoin); COPY_SCALAR_FIELD(colMin); COPY_SCALAR_FIELD(colMax); @@ -2731,6 +2734,7 @@ _copyJsonTableSiblingNode(const JsonTableSiblingNode *from) COPY_NODE_FIELD(larg); COPY_NODE_FIELD(rarg); + COPY_SCALAR_FIELD(cross); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index adcb608756..5351be8bb4 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -152,6 +152,8 @@ _equalJsonTableParentNode(const JsonTableParentNode *a, const JsonTableParentNod { COMPARE_NODE_FIELD(path); COMPARE_NODE_FIELD(child); + COMPARE_SCALAR_FIELD(outerJoin); + COMPARE_SCALAR_FIELD(unionJoin); COMPARE_SCALAR_FIELD(colMin); COMPARE_SCALAR_FIELD(colMax); @@ -163,6 +165,7 @@ _equalJsonTableSiblingNode(const JsonTableSiblingNode *a, const JsonTableSibling { COMPARE_NODE_FIELD(larg); COMPARE_NODE_FIELD(rarg); + COMPARE_SCALAR_FIELD(cross); return true; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 80bb16e504..2a18d6ad66 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -1872,6 +1872,8 @@ _outJsonTableParentNode(StringInfo str, const JsonTableParentNode *node) WRITE_NODE_FIELD(path); WRITE_NODE_FIELD(child); + WRITE_BOOL_FIELD(outerJoin); + WRITE_BOOL_FIELD(unionJoin); WRITE_INT_FIELD(colMin); WRITE_INT_FIELD(colMax); } @@ -1883,6 +1885,7 @@ _outJsonTableSiblingNode(StringInfo str, const JsonTableSiblingNode *node) WRITE_NODE_FIELD(larg); WRITE_NODE_FIELD(rarg); + WRITE_BOOL_FIELD(cross); } /***************************************************************************** diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index cc173a38ec..b9f12205b3 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1506,6 +1506,8 @@ _readJsonTableParentNode(void) READ_NODE_FIELD(path); READ_NODE_FIELD(child); + READ_BOOL_FIELD(outerJoin); + READ_BOOL_FIELD(unionJoin); READ_INT_FIELD(colMin); READ_INT_FIELD(colMax); @@ -1519,6 +1521,7 @@ _readJsonTableSiblingNode(void) READ_NODE_FIELD(larg); READ_NODE_FIELD(rarg); + READ_BOOL_FIELD(cross); READ_DONE(); } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 01804dab46..f9ab89bf99 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -667,6 +667,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type json_encoding json_encoding_clause_opt + json_table_plan_clause_opt + json_table_default_plan + json_table_default_plan_choices + json_table_default_plan_inner_outer + json_table_default_plan_union_cross json_wrapper_clause_opt json_wrapper_behavior json_conditional_or_unconditional_opt @@ -780,7 +785,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER - PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PATH PLACING PLANS POLICY + PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PATH PLACING PLAN PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -15515,13 +15520,15 @@ json_table: JSON_TABLE '(' json_api_common_syntax json_table_columns_clause + json_table_plan_clause_opt json_table_error_clause_opt ')' { JsonTable *n = makeNode(JsonTable); n->common = (JsonCommon *) $3; n->columns = $4; - n->on_error = $5; + n->join_type = $5; + n->on_error = $6; n->location = @1; $$ = (Node *) n; } @@ -15658,6 +15665,34 @@ path_opt: | /* EMPTY */ { } ; +json_table_plan_clause_opt: + json_table_default_plan { $$ = $1; } + | /* EMPTY */ { $$ = JSTPJ_OUTER | JSTPJ_UNION; } + ; + +json_table_default_plan: + PLAN DEFAULT '(' json_table_default_plan_choices ')' { $$ = $4; } + ; + +json_table_default_plan_choices: + json_table_default_plan_inner_outer { $$ = $1 | JSTPJ_UNION; } + | json_table_default_plan_inner_outer ',' + json_table_default_plan_union_cross { $$ = $1 | $3; } + | json_table_default_plan_union_cross { $$ = $1 | JSTPJ_OUTER; } + | json_table_default_plan_union_cross ',' + json_table_default_plan_inner_outer { $$ = $1 | $3; } + ; + +json_table_default_plan_inner_outer: + INNER_P { $$ = JSTPJ_INNER; } + | OUTER_P { $$ = JSTPJ_OUTER; } + ; + +json_table_default_plan_union_cross: + UNION { $$ = JSTPJ_UNION; } + | CROSS { $$ = JSTPJ_CROSS; } + ; + json_returning_clause_opt: RETURNING Typename { @@ -16557,6 +16592,7 @@ unreserved_keyword: | PASSING | PASSWORD | PATH + | PLAN | PLANS | POLICY | PRECEDING @@ -17172,6 +17208,7 @@ bare_label_keyword: | PASSWORD | PATH | PLACING + | PLAN | PLANS | POLICY | POSITION diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c index 94c96606c1..41fe7659de 100644 --- a/src/backend/parser/parse_jsontable.c +++ b/src/backend/parser/parse_jsontable.c @@ -174,12 +174,13 @@ transformNestedJsonTableColumn(JsonTableContext *cxt, JsonTableColumn *jtc) } static Node * -makeJsonTableSiblingJoin(Node *lnode, Node *rnode) +makeJsonTableSiblingJoin(bool cross, Node *lnode, Node *rnode) { JsonTableSiblingNode *join = makeNode(JsonTableSiblingNode); join->larg = lnode; join->rarg = rnode; + join->cross = cross; return (Node *) join; } @@ -187,7 +188,7 @@ makeJsonTableSiblingJoin(Node *lnode, Node *rnode) /* * Recursively transform child (nested) JSON_TABLE columns. * - * Child columns are transformed into a binary tree of union-joined + * Child columns are transformed into a binary tree of union/cross-joined * JsonTableSiblingNodes. */ static Node * @@ -195,8 +196,9 @@ transformJsonTableChildColumns(JsonTableContext *cxt, List *columns) { Node *res = NULL; ListCell *lc; + bool cross = cxt->table->join_type & JSTPJ_CROSS; - /* transform all nested columns into union join */ + /* transform all nested columns into union/cros join */ foreach(lc, columns) { JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc)); @@ -208,7 +210,7 @@ transformJsonTableChildColumns(JsonTableContext *cxt, List *columns) node = transformNestedJsonTableColumn(cxt, jtc); /* join transformed node with previous sibling nodes */ - res = res ? makeJsonTableSiblingJoin(res, node) : node; + res = res ? makeJsonTableSiblingJoin(cross, res, node) : node; } return res; @@ -386,6 +388,9 @@ transformJsonTableColumns(JsonTableContext *cxt, List *columns, char *pathSpec, /* transform recursively nested columns */ node->child = transformJsonTableChildColumns(cxt, columns); + node->outerJoin = cxt->table->join_type & JSTPJ_OUTER; + node->unionJoin = cxt->table->join_type & JSTPJ_UNION; + return node; } diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index ebfa226bf3..4512c529e7 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -175,6 +175,7 @@ struct JsonTableScanState Datum current; int ordinal; bool currentIsNull; + bool outerJoin; bool errorOnError; bool advanceNested; bool reset; @@ -188,6 +189,7 @@ struct JsonTableJoinState { JsonTableJoinState *left; JsonTableJoinState *right; + bool cross; bool advanceRight; } join; JsonTableScanState scan; @@ -3166,6 +3168,7 @@ JsonTableInitScanState(JsonTableContext *cxt, JsonTableScanState *scan, int i; scan->parent = parent; + scan->outerJoin = node->outerJoin; scan->errorOnError = node->errorOnError; scan->path = DatumGetJsonPathP(node->path->constvalue); scan->args = args; @@ -3192,6 +3195,7 @@ JsonTableInitPlanState(JsonTableContext *cxt, Node *plan, JsonTableSiblingNode *join = castNode(JsonTableSiblingNode, plan); state->is_join = true; + state->u.join.cross = join->cross; state->u.join.left = JsonTableInitPlanState(cxt, join->larg, parent); state->u.join.right = JsonTableInitPlanState(cxt, join->rarg, parent); } @@ -3328,8 +3332,26 @@ JsonTableSetDocument(TableFuncScanState *state, Datum value) JsonTableResetContextItem(&cxt->root, value); } +/* Recursively reset scan and its child nodes */ +static void +JsonTableRescanRecursive(JsonTableJoinState *state) +{ + if (state->is_join) + { + JsonTableRescanRecursive(state->u.join.left); + JsonTableRescanRecursive(state->u.join.right); + state->u.join.advanceRight = false; + } + else + { + JsonTableRescan(&state->u.scan); + if (state->u.scan.nested) + JsonTableRescanRecursive(state->u.scan.nested); + } +} + /* - * Fetch next row from a union joined scan. + * Fetch next row from a cross/union joined scan. * * Returned false at the end of a scan, true otherwise. */ @@ -3339,17 +3361,48 @@ JsonTableNextJoinRow(JsonTableJoinState *state) if (!state->is_join) return JsonTableNextRow(&state->u.scan); - if (!state->u.join.advanceRight) + if (state->u.join.advanceRight) { - /* fetch next outer row */ - if (JsonTableNextJoinRow(state->u.join.left)) + /* fetch next inner row */ + if (JsonTableNextJoinRow(state->u.join.right)) return true; - state->u.join.advanceRight = true; /* next inner row */ + /* inner rows are exhausted */ + if (state->u.join.cross) + state->u.join.advanceRight = false; /* next outer row */ + else + return false; /* end of scan */ } - /* fetch next inner row */ - return JsonTableNextJoinRow(state->u.join.right); + while (!state->u.join.advanceRight) + { + /* fetch next outer row */ + bool left = JsonTableNextJoinRow(state->u.join.left); + + if (state->u.join.cross) + { + if (!left) + return false; /* end of scan */ + + JsonTableRescanRecursive(state->u.join.right); + + if (!JsonTableNextJoinRow(state->u.join.right)) + continue; /* next outer row */ + + state->u.join.advanceRight = true; /* next inner row */ + } + else if (!left) + { + if (!JsonTableNextJoinRow(state->u.join.right)) + return false; /* end of scan */ + + state->u.join.advanceRight = true; /* next inner row */ + } + + break; + } + + return true; } /* Recursively set 'reset' flag of scan and its child nodes */ @@ -3373,16 +3426,13 @@ JsonTableJoinReset(JsonTableJoinState *state) } /* - * Fetch next row from a simple scan with outer joined nested subscans. + * Fetch next row from a simple scan with outer/inner joined nested subscans. * * Returned false at the end of a scan, true otherwise. */ static bool JsonTableNextRow(JsonTableScanState *scan) { - JsonbValue *jbv; - MemoryContext oldcxt; - /* reset context item if requested */ if (scan->reset) { @@ -3394,34 +3444,44 @@ JsonTableNextRow(JsonTableScanState *scan) if (scan->advanceNested) { /* fetch next nested row */ - if (JsonTableNextJoinRow(scan->nested)) - return true; + scan->advanceNested = JsonTableNextJoinRow(scan->nested); - scan->advanceNested = false; + if (scan->advanceNested) + return true; } - /* fetch next row */ - jbv = JsonValueListNext(&scan->found, &scan->iter); - - if (!jbv) + for (;;) { - scan->current = PointerGetDatum(NULL); - scan->currentIsNull = true; - return false; /* end of scan */ - } + /* fetch next row */ + JsonbValue *jbv = JsonValueListNext(&scan->found, &scan->iter); + MemoryContext oldcxt; - /* set current row item */ - oldcxt = MemoryContextSwitchTo(scan->mcxt); - scan->current = JsonbPGetDatum(JsonbValueToJsonb(jbv)); - scan->currentIsNull = false; - MemoryContextSwitchTo(oldcxt); + if (!jbv) + { + scan->current = PointerGetDatum(NULL); + scan->currentIsNull = true; + return false; /* end of scan */ + } - scan->ordinal++; + /* set current row item */ + oldcxt = MemoryContextSwitchTo(scan->mcxt); + scan->current = JsonbPGetDatum(JsonbValueToJsonb(jbv)); + scan->currentIsNull = false; + MemoryContextSwitchTo(oldcxt); + + scan->ordinal++; + + if (!scan->nested) + break; - if (scan->nested) - { JsonTableJoinReset(scan->nested); + scan->advanceNested = JsonTableNextJoinRow(scan->nested); + + if (scan->advanceNested || scan->outerJoin) + break; + + /* state->ordinal--; */ /* skip current outer row, reset counter */ } return true; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index a52337b586..780f6520c6 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -11182,6 +11182,15 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit) get_json_table_columns(tf, root, context, showimplicit); + if (!root->outerJoin || !root->unionJoin) + { + appendStringInfoChar(buf, ' '); + appendContextKeyword(context, "PLAN DEFAULT", 0, 0, 0); + appendStringInfo(buf, "(%s, %s)", + root->outerJoin ? "OUTER" : "INNER", + root->unionJoin ? "UNION" : "CROSS"); + } + 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 55dbdd8107..0de78dd6ad 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1655,6 +1655,18 @@ typedef struct JsonTableColumn int location; /* token location, or -1 if unknown */ } JsonTableColumn; +/* + * JsonTablePlanJoinType - + * flags for JSON_TABLE join types representation + */ +typedef enum JsonTablePlanJoinType +{ + JSTPJ_INNER = 0x01, + JSTPJ_OUTER = 0x02, + JSTPJ_CROSS = 0x04, + JSTPJ_UNION = 0x08, +} JsonTablePlanJoinType; + /* * JsonTable - * untransformed representation of JSON_TABLE @@ -1664,6 +1676,7 @@ typedef struct JsonTable NodeTag type; JsonCommon *common; /* common JSON path syntax fields */ List *columns; /* list of JsonTableColumn */ + JsonTablePlanJoinType join_type; /* DEFAULT PLAN join type */ JsonBehavior *on_error; /* ON ERROR behavior, if specified */ 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 57420a572f..1aa3e01048 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1476,6 +1476,8 @@ typedef struct JsonTableParentNode NodeTag type; Const *path; /* jsonpath constant */ Node *child; /* nested columns, if any */ + bool outerJoin; /* outer or inner join for nested columns? */ + bool unionJoin; /* union or cross join for nested columns? */ int colMin; /* min column index in the resulting column list */ int colMax; /* max column index in the resulting column list */ bool errorOnError; /* ERROR/EMPTY ON ERROR behavior */ @@ -1490,6 +1492,7 @@ typedef struct JsonTableSiblingNode NodeTag type; Node *larg; /* left join node */ Node *rarg; /* right join node */ + bool cross; /* cross or union join? */ } JsonTableSiblingNode; /* ---------------- diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 15f586d455..6065bbae88 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -334,6 +334,7 @@ PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("path", PATH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("plan", PLAN, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD, BARE_LABEL) diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index d89c3bef44..0cd8f73c66 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -1420,6 +1420,124 @@ from 4 | -1 | 2 | (11 rows) +-- default plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' columns ( c int path '$' ) + ) + plan default (outer, union) + ) 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) + +-- default plan (inner, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' columns ( c int path '$' ) + ) + plan default (inner) + ) jt; + n | a | b | c +---+----+---+---- + 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 | +(10 rows) + +-- default plan (inner, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' columns ( c int path '$' ) + ) + plan default (cross, inner) + ) jt; + n | a | b | c +---+---+---+---- + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 +(9 rows) + +-- default plan (outer, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' columns ( c int path '$' ) + ) + plan default (outer, cross) + ) jt; + n | a | b | c +---+----+---+---- + 1 | 1 | | + 2 | 2 | 1 | 10 + 2 | 2 | 1 | + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | + 2 | 2 | 3 | 20 + 3 | 3 | | + 4 | -1 | | +(12 rows) + -- Should succeed (JSON arguments are passed to root and nested paths) SELECT * FROM diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql index 6614dd6e45..cd97ff7f46 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -547,6 +547,71 @@ from ) ) jt; +-- default plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' columns ( c int path '$' ) + ) + plan default (outer, union) + ) jt; + +-- default plan (inner, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' columns ( c int path '$' ) + ) + plan default (inner) + ) jt; + +-- default plan (inner, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' columns ( c int path '$' ) + ) + plan default (cross, inner) + ) jt; + +-- default plan (outer, cross) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' columns ( c int path '$' ) + ) + plan default (outer, cross) + ) jt; + + -- Should succeed (JSON arguments are passed to root and nested paths) SELECT * FROM diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 063d639f65..27cf806043 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1237,6 +1237,7 @@ JsonTableColumnType JsonTableContext JsonTableJoinState JsonTableParentNode +JsonTablePlanJoinType JsonTableScanState JsonTableSiblingNode JsonTokenType -- 2.25.4 --------------5BB5E3CCDF424A5E7F475161 Content-Type: text/x-patch; charset=UTF-8; name="0004-JSON_TABLE-PLAN-clause-v51.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="0004-JSON_TABLE-PLAN-clause-v51.patch" =46rom d4f4cf355094ec4a9c05f192e15d0f1d3294c1f6 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Mon, 13 Sep 2021 18:18:24 -0400 Subject: [PATCH 4/4] JSON_TABLE PLAN clause --- doc/src/sgml/func.sgml | 97 +++- src/backend/nodes/copyfuncs.c | 26 +- src/backend/nodes/equalfuncs.c | 2 +- src/backend/nodes/makefuncs.c | 19 + src/backend/nodes/outfuncs.c | 2 +- src/backend/nodes/readfuncs.c | 2 +- src/backend/parser/gram.y | 101 +++- src/backend/parser/parse_jsontable.c | 322 +++++++++++-- src/backend/utils/adt/ruleutils.c | 57 ++- src/include/nodes/makefuncs.h | 2 + src/include/nodes/parsenodes.h | 31 +- src/include/nodes/primnodes.h | 2 +- src/test/regress/expected/jsonb_sqljson.out | 506 ++++++++++++++++++-- src/test/regress/sql/jsonb_sqljson.sql | 355 ++++++++++++-- src/tools/pgindent/typedefs.list | 2 + 15 files changed, 1389 insertions(+), 137 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 746f24501a..a660cdf956 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19205,9 +19205,10 @@ FROM =20 JSON_TABLE ( - context_item, path_expression<= /replaceable> PASSING { value AS varname } , ... = + context_item, path_expression<= /replaceable> AS json_path_name PASSING { value AS varname } , ... COLUMNS ( json_table_column , ... ) + PLAN ( json_table_plan ) | PLAN DEFAULT ( { INNER | OUTER } , { CROSS | UNION } | { CROSS | UNION } , { INNER | OUTER } ) @@ -19231,6 +19232,16 @@ where json_tabl= e_column is: | NESTED PATH json_path_specification AS path_name COLUMNS ( json_table_column , ... ) | name FOR ORDINALITY + +json_table_plan is: + + json_path_name { OUTER | INNER= } json_table_plan_primary + | json_table_plan_primary { UNION json_table_plan_primary } ... + | json_table_plan_primary { CROSS json_table_plan_primary } ... + +json_table_plan_primary is: + + json_path_name | ( json_tabl= e_plan ) =20 =20 @@ -19273,7 +19284,7 @@ where json_table= _column is: joined to the row that generated them, so you do not have to expli= citly join the constructed view with the original table holding JSON= data. Optionally, you can specify how to join the columns returned= - by NESTED PATH using the PLAN DEFAULT<= /literal> clause. + by NESTED PATH using the PLAN clause. =20 @@ -19458,7 +19469,7 @@ where json_table= _column is: =20 - You can use the PLAN DEFAULT clause to define ho= w + You can use the PLAN clause to define how to join the columns returned by NESTED PATH clauses. @@ -19485,18 +19496,31 @@ where json_tab= le_column is: =20 - PLAN DEFAULT ( option , ... ) + AS json_path_name - - Defines how to join the data returned by NESTED PATH<= /replaceable> - clauses to the constructed view. The INNER and - OUTER options define the joining plan for paren= t/child - columns, while UNION and CROSS - affect the sibling columns. You can override the default plans for= all - columns at once. - =20 + + The optional json_path_name serves as an= + identifier of the provided json_path_specification. + The path name must be unique and cannot coincide with column names.= + When using the PLAN clause, you must specify the= names + for all the paths, including the row pattern. Each path name can ap= pear in + the PLAN clause only once. + + + + + + + PLAN ( json_table_plan ) + + + + + Defines how to join the data returned by NESTED PATH + clauses to the constructed view. + To join columns with parent/child relationship, you can use: @@ -19575,6 +19599,23 @@ where json_tabl= e_column is: =20 + + + + PLAN DEFAULT ( option , ... ) + + + + Overrides the default joining plans. The INNER = and + OUTER options define the joining plan for paren= t/child + columns, while UNION and CROSS + affect the sibling columns. You can override the default plans for= all columns at once. + Even though the path names are not included into the PLAN= DEFAULT + clause, they must be provided for all the paths to conform to + the SQL/JSON standard. + + + =20 @@ -19606,9 +19647,35 @@ SELECT jt.* FROM =20 - - - + + 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 =3D director2; + + + + =20 diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.= c index ffa8f7e39b..f3d84485fe 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2675,9 +2675,9 @@ _copyJsonTable(const JsonTable *from) =20 COPY_NODE_FIELD(common); COPY_NODE_FIELD(columns); + COPY_NODE_FIELD(plan); COPY_NODE_FIELD(on_error); COPY_NODE_FIELD(alias); - COPY_SCALAR_FIELD(join_type); COPY_SCALAR_FIELD(location); =20 return newnode; @@ -2695,6 +2695,7 @@ _copyJsonTableColumn(const JsonTableColumn *from) COPY_STRING_FIELD(name); COPY_NODE_FIELD(typeName); COPY_STRING_FIELD(pathspec); + COPY_STRING_FIELD(pathname); COPY_SCALAR_FIELD(format); COPY_SCALAR_FIELD(wrapper); COPY_SCALAR_FIELD(omit_quotes); @@ -2706,6 +2707,24 @@ _copyJsonTableColumn(const JsonTableColumn *from) return newnode; } =20 +/* + * _copyJsonTablePlan + */ +static JsonTablePlan * +_copyJsonTablePlan(const JsonTablePlan *from) +{ + JsonTablePlan *newnode =3D makeNode(JsonTablePlan); + + COPY_SCALAR_FIELD(plan_type); + COPY_SCALAR_FIELD(join_type); + COPY_STRING_FIELD(pathname); + COPY_NODE_FIELD(plan1); + COPY_NODE_FIELD(plan2); + COPY_SCALAR_FIELD(location); + + return newnode; +} + /* * _copyJsonTableParentNode */ @@ -2715,9 +2734,9 @@ _copyJsonTableParentNode(const JsonTableParentNode = *from) JsonTableParentNode *newnode =3D makeNode(JsonTableParentNode); =20 COPY_NODE_FIELD(path); + COPY_STRING_FIELD(name); COPY_NODE_FIELD(child); COPY_SCALAR_FIELD(outerJoin); - COPY_SCALAR_FIELD(unionJoin); COPY_SCALAR_FIELD(colMin); COPY_SCALAR_FIELD(colMax); =20 @@ -5822,6 +5841,9 @@ copyObjectImpl(const void *from) case T_JsonTableColumn: retval =3D _copyJsonTableColumn(from); break; + case T_JsonTablePlan: + retval =3D _copyJsonTablePlan(from); + break; case T_JsonTableParentNode: retval =3D _copyJsonTableParentNode(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfunc= s.c index 5351be8bb4..123d5062e7 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -151,9 +151,9 @@ static bool _equalJsonTableParentNode(const JsonTableParentNode *a, const JsonTableP= arentNode *b) { COMPARE_NODE_FIELD(path); + COMPARE_STRING_FIELD(name); COMPARE_NODE_FIELD(child); COMPARE_SCALAR_FIELD(outerJoin); - COMPARE_SCALAR_FIELD(unionJoin); COMPARE_SCALAR_FIELD(colMin); COMPARE_SCALAR_FIELD(colMax); =20 diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.= c index ca374dcb29..46d7f61e25 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -864,6 +864,25 @@ makeJsonBehavior(JsonBehaviorType type, Node *defaul= t_expr) return behavior; } =20 +/* + * makeJsonTableJoinedPlan - + * creates a joined JsonTablePlan node + */ +Node * +makeJsonTableJoinedPlan(JsonTablePlanJoinType type, Node *plan1, Node *p= lan2, + int location) +{ + JsonTablePlan *n =3D makeNode(JsonTablePlan); + + n->plan_type =3D JSTP_JOINED; + n->join_type =3D type; + n->plan1 =3D castNode(JsonTablePlan, plan1); + n->plan2 =3D castNode(JsonTablePlan, plan2); + n->location =3D location; + + return (Node *) n; +} + /* * makeJsonEncoding - * converts JSON encoding name to enum JsonEncoding diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 2a18d6ad66..8c0b78d73f 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -1871,9 +1871,9 @@ _outJsonTableParentNode(StringInfo str, const JsonT= ableParentNode *node) WRITE_NODE_TYPE("JSONTABPNODE"); =20 WRITE_NODE_FIELD(path); + WRITE_STRING_FIELD(name); WRITE_NODE_FIELD(child); WRITE_BOOL_FIELD(outerJoin); - WRITE_BOOL_FIELD(unionJoin); WRITE_INT_FIELD(colMin); WRITE_INT_FIELD(colMax); } diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.= c index b9f12205b3..d73689ee97 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1505,9 +1505,9 @@ _readJsonTableParentNode(void) READ_LOCALS(JsonTableParentNode); =20 READ_NODE_FIELD(path); + READ_STRING_FIELD(name); READ_NODE_FIELD(child); READ_BOOL_FIELD(outerJoin); - READ_BOOL_FIELD(unionJoin); READ_INT_FIELD(colMin); READ_INT_FIELD(colMax); =20 diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index f9ab89bf99..1ed59f63c8 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -652,6 +652,18 @@ static Node *makeRecursiveViewSelect(char *relname, = List *aliases, Node *query); json_table_formatted_column_definition json_table_exists_column_definition json_table_nested_columns + json_table_plan_clause_opt + json_table_specific_plan + json_table_plan + json_table_plan_simple + json_table_plan_parent_child + json_table_plan_outer + json_table_plan_inner + json_table_plan_sibling + json_table_plan_union + json_table_plan_cross + json_table_plan_primary + json_table_default_plan =20 %type json_name_and_value_list json_value_expr_list @@ -667,8 +679,6 @@ static Node *makeRecursiveViewSelect(char *relname, L= ist *aliases, Node *query); =20 %type json_encoding json_encoding_clause_opt - json_table_plan_clause_opt - json_table_default_plan json_table_default_plan_choices json_table_default_plan_inner_outer json_table_default_plan_union_cross @@ -15527,7 +15537,7 @@ json_table: JsonTable *n =3D makeNode(JsonTable); n->common =3D (JsonCommon *) $3; n->columns =3D $4; - n->join_type =3D $5; + n->plan =3D (JsonTablePlan *) $5; n->on_error =3D $6; n->location =3D @1; $$ =3D (Node *) n; @@ -15649,12 +15659,15 @@ json_table_formatted_column_definition: ; =20 json_table_nested_columns: - NESTED path_opt Sconst json_table_columns_clause + NESTED path_opt Sconst + json_as_path_name_clause_opt + json_table_columns_clause { JsonTableColumn *n =3D makeNode(JsonTableColumn); n->coltype =3D JTC_NESTED; n->pathspec =3D $3; - n->columns =3D $4; + n->pathname =3D $4; + n->columns =3D $5; n->location =3D @1; $$ =3D (Node *) n; } @@ -15666,12 +15679,84 @@ path_opt: ; =20 json_table_plan_clause_opt: - json_table_default_plan { $$ =3D $1; } - | /* EMPTY */ { $$ =3D JSTPJ_OUTER | JSTPJ_UNION; } + json_table_specific_plan { $$ =3D $1; } + | json_table_default_plan { $$ =3D $1; } + | /* EMPTY */ { $$ =3D NULL; } + ; + +json_table_specific_plan: + PLAN '(' json_table_plan ')' { $$ =3D $3; } + ; + +json_table_plan: + json_table_plan_simple + | json_table_plan_parent_child + | json_table_plan_sibling + ; + +json_table_plan_simple: + json_table_path_name + { + JsonTablePlan *n =3D makeNode(JsonTablePlan); + n->plan_type =3D JSTP_SIMPLE; + n->pathname =3D $1; + n->location =3D @1; + $$ =3D (Node *) n; + } + ; + +json_table_plan_parent_child: + json_table_plan_outer + | json_table_plan_inner + ; + +json_table_plan_outer: + json_table_plan_simple OUTER_P json_table_plan_primary + { $$ =3D makeJsonTableJoinedPlan(JSTPJ_OUTER, $1, $3, @1); } + ; + +json_table_plan_inner: + json_table_plan_simple INNER_P json_table_plan_primary + { $$ =3D makeJsonTableJoinedPlan(JSTPJ_INNER, $1, $3, @1); } + ; + +json_table_plan_sibling: + json_table_plan_union + | json_table_plan_cross + ; + +json_table_plan_union: + json_table_plan_primary UNION json_table_plan_primary + { $$ =3D makeJsonTableJoinedPlan(JSTPJ_UNION, $1, $3, @1); } + | json_table_plan_union UNION json_table_plan_primary + { $$ =3D makeJsonTableJoinedPlan(JSTPJ_UNION, $1, $3, @1); } + ; + +json_table_plan_cross: + json_table_plan_primary CROSS json_table_plan_primary + { $$ =3D makeJsonTableJoinedPlan(JSTPJ_CROSS, $1, $3, @1); } + | json_table_plan_cross CROSS json_table_plan_primary + { $$ =3D makeJsonTableJoinedPlan(JSTPJ_CROSS, $1, $3, @1); } + ; + +json_table_plan_primary: + json_table_plan_simple { $$ =3D $1; } + | '(' json_table_plan ')' + { + castNode(JsonTablePlan, $2)->location =3D @1; + $$ =3D $2; + } ; =20 json_table_default_plan: - PLAN DEFAULT '(' json_table_default_plan_choices ')' { $$ =3D $4; } + PLAN DEFAULT '(' json_table_default_plan_choices ')' + { + JsonTablePlan *n =3D makeNode(JsonTablePlan); + n->plan_type =3D JSTP_DEFAULT; + n->join_type =3D $4; + n->location =3D @1; + $$ =3D (Node *) n; + } ; =20 json_table_default_plan_choices: diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/pa= rse_jsontable.c index 41fe7659de..eae5e699db 100644 --- a/src/backend/parser/parse_jsontable.c +++ b/src/backend/parser/parse_jsontable.c @@ -38,12 +38,15 @@ typedef struct JsonTableContext JsonTable *table; /* untransformed node */ TableFunc *tablefunc; /* transformed node */ List *pathNames; /* list of all path and columns names */ + int pathNameId; /* path name id counter */ Oid contextItemTypid; /* type oid of context item (json/jsonb) */ } JsonTableContext; =20 static JsonTableParentNode * transformJsonTableColumns(JsonTableContext = *cxt, + JsonTablePlan *plan, List *columns, char *pathSpec, + char **pathName, int location); =20 static Node * @@ -156,19 +159,154 @@ registerAllJsonTableColumns(JsonTableContext *cxt,= List *columns) JsonTableColumn *jtc =3D castNode(JsonTableColumn, lfirst(lc)); =20 if (jtc->coltype =3D=3D JTC_NESTED) + { + if (jtc->pathname) + registerJsonTableColumn(cxt, jtc->pathname); + registerAllJsonTableColumns(cxt, jtc->columns); + } else + { registerJsonTableColumn(cxt, jtc->name); + } + } +} + +/* Generate a new unique JSON_TABLE path name. */ +static char * +generateJsonTablePathName(JsonTableContext *cxt) +{ + char namebuf[32]; + char *name =3D namebuf; + + do + { + snprintf(namebuf, sizeof(namebuf), "json_table_path_%d", + ++cxt->pathNameId); + } while (isJsonTablePathNameDuplicate(cxt, name)); + + name =3D pstrdup(name); + cxt->pathNames =3D lappend(cxt->pathNames, name); + + return name; +} + +/* Collect sibling path names from plan to the specified list. */ +static void +collectSiblingPathsInJsonTablePlan(JsonTablePlan *plan, List **paths) +{ + if (plan->plan_type =3D=3D JSTP_SIMPLE) + *paths =3D lappend(*paths, plan->pathname); + else if (plan->plan_type =3D=3D JSTP_JOINED) + { + if (plan->join_type =3D=3D JSTPJ_INNER || + plan->join_type =3D=3D JSTPJ_OUTER) + { + Assert(plan->plan1->plan_type =3D=3D JSTP_SIMPLE); + *paths =3D lappend(*paths, plan->plan1->pathname); + } + else if (plan->join_type =3D=3D JSTPJ_CROSS || + plan->join_type =3D=3D JSTPJ_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, JsonTablePlan *plan, + List *columns) +{ + ListCell *lc1; + List *siblings =3D NIL; + int nchildren =3D 0; + + if (plan) + collectSiblingPathsInJsonTablePlan(plan, &siblings); + + foreach(lc1, columns) + { + JsonTableColumn *jtc =3D castNode(JsonTableColumn, lfirst(lc1)); + + if (jtc->coltype =3D=3D JTC_NESTED) + { + ListCell *lc2; + bool found =3D false; + + if (!jtc->pathname) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("nested JSON_TABLE columns shall contain " + "explicit AS pathname specification if " + "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 =3D !strcmp(jtc->pathname, lfirst(lc2)))) + break; + } + + if (!found) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("plan node for nested path %s " + "was not found in plan", jtc->pathname), + parser_errposition(pstate, jtc->location))); + + nchildren++; + } } + + if (list_length(siblings) > nchildren) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("plan node contains some extra or " + "duplicate sibling nodes"), + parser_errposition(pstate, plan ? plan->location : -1))); +} + +static JsonTableColumn * +findNestedJsonTableColumn(List *columns, const char *pathname) +{ + ListCell *lc; + + foreach(lc, columns) + { + JsonTableColumn *jtc =3D castNode(JsonTableColumn, lfirst(lc)); + + if (jtc->coltype =3D=3D JTC_NESTED && + jtc->pathname && + !strcmp(jtc->pathname, pathname)) + return jtc; + } + + return NULL; } =20 static Node * -transformNestedJsonTableColumn(JsonTableContext *cxt, JsonTableColumn *j= tc) +transformNestedJsonTableColumn(JsonTableContext *cxt, JsonTableColumn *j= tc, + JsonTablePlan *plan) { JsonTableParentNode *node; + char *pathname =3D jtc->pathname; =20 - node =3D transformJsonTableColumns(cxt, jtc->columns, jtc->pathspec, - jtc->location); + node =3D transformJsonTableColumns(cxt, plan, jtc->columns, jtc->pathsp= ec, + &pathname, jtc->location); + node->name =3D pstrdup(pathname); =20 return (Node *) node; } @@ -186,34 +324,78 @@ makeJsonTableSiblingJoin(bool cross, Node *lnode, N= ode *rnode) } =20 /* - * Recursively transform child (nested) JSON_TABLE columns. + * Recursively transform child JSON_TABLE plan. * - * Child columns are transformed into a binary tree of union/cross-joine= d - * JsonTableSiblingNodes. + * Default plan is transformed into a cross/union join of its nested col= umns. + * Simple and outer/inner plans are transformed into a JsonTableParentNo= de by + * finding and transforming corresponding nested column. + * Sibling plans are recursively transformed into a JsonTableSiblingNode= =2E */ static Node * -transformJsonTableChildColumns(JsonTableContext *cxt, List *columns) +transformJsonTableChildPlan(JsonTableContext *cxt, JsonTablePlan *plan, + List *columns) { - Node *res =3D NULL; - ListCell *lc; - bool cross =3D cxt->table->join_type & JSTPJ_CROSS; + JsonTableColumn *jtc =3D NULL; =20 - /* transform all nested columns into union/cros join */ - foreach(lc, columns) + if (!plan || plan->plan_type =3D=3D JSTP_DEFAULT) { - JsonTableColumn *jtc =3D castNode(JsonTableColumn, lfirst(lc)); - Node *node; + /* unspecified or default plan */ + Node *res =3D NULL; + ListCell *lc; + bool cross =3D plan && (plan->join_type & JSTPJ_CROSS); =20 - if (jtc->coltype !=3D JTC_NESTED) - continue; + /* transform all nested columns into cross/union join */ + foreach(lc, columns) + { + JsonTableColumn *jtc =3D castNode(JsonTableColumn, lfirst(lc)); + Node *node; =20 - node =3D transformNestedJsonTableColumn(cxt, jtc); + if (jtc->coltype !=3D JTC_NESTED) + continue; + + node =3D transformNestedJsonTableColumn(cxt, jtc, plan); + + /* join transformed node with previous sibling nodes */ + res =3D res ? makeJsonTableSiblingJoin(cross, res, node) : node; + } =20 - /* join transformed node with previous sibling nodes */ - res =3D res ? makeJsonTableSiblingJoin(cross, res, node) : node; + return res; + } + else if (plan->plan_type =3D=3D JSTP_SIMPLE) + { + jtc =3D findNestedJsonTableColumn(columns, plan->pathname); } + else if (plan->plan_type =3D=3D JSTP_JOINED) + { + if (plan->join_type =3D=3D JSTPJ_INNER || + plan->join_type =3D=3D JSTPJ_OUTER) + { + Assert(plan->plan1->plan_type =3D=3D JSTP_SIMPLE); + jtc =3D findNestedJsonTableColumn(columns, plan->plan1->pathname); + } + else + { + Node *node1 =3D + transformJsonTableChildPlan(cxt, plan->plan1, columns); + Node *node2 =3D + transformJsonTableChildPlan(cxt, plan->plan2, columns); + + return makeJsonTableSiblingJoin(plan->join_type =3D=3D JSTPJ_CROSS, + node1, node2); + } + } + else + elog(ERROR, "invalid JSON_TABLE plan type %d", plan->plan_type); =20 - return res; + if (!jtc) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("path name was %s not found in nested columns list", + plan->pathname), + parser_errposition(cxt->pstate, plan->location))); + + return transformNestedJsonTableColumn(cxt, jtc, plan); } =20 /* Check whether type is json/jsonb, array, or record. */ @@ -377,19 +559,80 @@ makeParentJsonTableNode(JsonTableContext *cxt, char= *pathSpec, List *columns) } =20 static JsonTableParentNode * -transformJsonTableColumns(JsonTableContext *cxt, List *columns, char *pa= thSpec, +transformJsonTableColumns(JsonTableContext *cxt, JsonTablePlan *plan, + List *columns, char *pathSpec, char **pathName, int location) { JsonTableParentNode *node; + JsonTablePlan *childPlan; + bool defaultPlan =3D !plan || plan->plan_type =3D=3D JSTP_DEFAULT; + + if (!*pathName) + { + if (cxt->table->plan) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE expression"), + errdetail("JSON_TABLE columns shall contain " + "explicit AS pathname specification if " + "explicit PLAN clause is used"), + parser_errposition(cxt->pstate, location))); + + *pathName =3D generateJsonTablePathName(cxt); + } + + if (defaultPlan) + childPlan =3D plan; + else + { + /* validate parent and child plans */ + JsonTablePlan *parentPlan; + + if (plan->plan_type =3D=3D JSTP_JOINED) + { + if (plan->join_type !=3D JSTPJ_INNER && + plan->join_type !=3D JSTPJ_OUTER) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("expected INNER or OUTER JSON_TABLE plan node"), + parser_errposition(cxt->pstate, plan->location))); + + parentPlan =3D plan->plan1; + childPlan =3D plan->plan2; + + Assert(parentPlan->plan_type !=3D JSTP_JOINED); + Assert(parentPlan->pathname); + } + else + { + parentPlan =3D plan; + childPlan =3D NULL; + } + + if (strcmp(parentPlan->pathname, *pathName)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid JSON_TABLE plan"), + errdetail("path name mismatch: expected %s but %s is given", + *pathName, parentPlan->pathname), + parser_errposition(cxt->pstate, plan->location))); + + validateJsonTableChildPlan(cxt->pstate, childPlan, columns); + } =20 /* transform only non-nested columns */ node =3D makeParentJsonTableNode(cxt, pathSpec, columns); + node->name =3D pstrdup(*pathName); =20 - /* transform recursively nested columns */ - node->child =3D transformJsonTableChildColumns(cxt, columns); - - node->outerJoin =3D cxt->table->join_type & JSTPJ_OUTER; - node->unionJoin =3D cxt->table->join_type & JSTPJ_UNION; + if (childPlan || defaultPlan) + { + /* transform recursively nested columns */ + node->child =3D transformJsonTableChildPlan(cxt, childPlan, columns); + if (node->child) + node->outerJoin =3D !plan || (plan->join_type & JSTPJ_OUTER); + /* else: default plan case, no children found */ + } =20 return node; } @@ -407,7 +650,9 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)= JsonTableContext cxt; TableFunc *tf =3D makeNode(TableFunc); JsonFuncExpr *jfe =3D makeNode(JsonFuncExpr); + JsonTablePlan *plan =3D jt->plan; JsonCommon *jscommon; + char *rootPathName =3D jt->common->pathname; char *rootPath; bool is_lateral; =20 @@ -415,9 +660,31 @@ transformJsonTable(ParseState *pstate, JsonTable *jt= ) cxt.table =3D jt; cxt.tablefunc =3D tf; cxt.pathNames =3D NIL; + cxt.pathNameId =3D 0; + + if (rootPathName) + registerJsonTableColumn(&cxt, rootPathName); =20 registerAllJsonTableColumns(&cxt, jt->columns); =20 +#if 0 /* XXX it' unclear from the standard whether root path name is man= datory or not */ + if (plan && plan->plan_type !=3D JSTP_DEFAULT && !rootPathName) + { + /* Assign root path name and create corresponding plan node */ + JsonTablePlan *rootNode =3D makeNode(JsonTablePlan); + JsonTablePlan *rootPlan =3D (JsonTablePlan *) + makeJsonTableJoinedPlan(JSTPJ_OUTER, (Node *) rootNode, + (Node *) plan, jt->location); + + rootPathName =3D generateJsonTablePathName(&cxt); + + rootNode->plan_type =3D JSTP_SIMPLE; + rootNode->pathname =3D rootPathName; + + plan =3D rootPlan; + } +#endif + jscommon =3D copyObject(jt->common); jscommon->pathspec =3D makeStringConst(pstrdup("$"), -1); =20 @@ -453,7 +720,8 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)= =20 rootPath =3D castNode(A_Const, jt->common->pathspec)->val.sval.val; =20 - tf->plan =3D (Node *) transformJsonTableColumns(&cxt, jt->columns, root= Path, + tf->plan =3D (Node *) transformJsonTableColumns(&cxt, plan, jt->columns= , + rootPath, &rootPathName, jt->common->location); =20 tf->ordinalitycol =3D -1; /* undefine ordinality column number */ diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ru= leutils.c index 780f6520c6..e19749d12a 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -11019,10 +11019,54 @@ get_json_table_nested_columns(TableFunc *tf, No= de *node, appendStringInfoChar(context->buf, ' '); appendContextKeyword(context, "NESTED PATH ", 0, 0, 0); get_const_expr(n->path, context, -1); + appendStringInfo(context->buf, " AS %s", quote_identifier(n->name)); get_json_table_columns(tf, n, context, showimplicit); } } =20 +/* + * get_json_table_plan - Parse back a JSON_TABLE plan + */ +static void +get_json_table_plan(TableFunc *tf, Node *node, deparse_context *context,= + bool parenthesize) +{ + if (parenthesize) + appendStringInfoChar(context->buf, '('); + + if (IsA(node, JsonTableSiblingNode)) + { + JsonTableSiblingNode *n =3D (JsonTableSiblingNode *) node; + + get_json_table_plan(tf, n->larg, context, + IsA(n->larg, JsonTableSiblingNode) || + castNode(JsonTableParentNode, n->larg)->child); + + appendStringInfoString(context->buf, n->cross ? " CROSS " : " UNION ")= ; + + get_json_table_plan(tf, n->rarg, context, + IsA(n->rarg, JsonTableSiblingNode) || + castNode(JsonTableParentNode, n->rarg)->child); + } + else + { + JsonTableParentNode *n =3D castNode(JsonTableParentNode, node); + + appendStringInfoString(context->buf, quote_identifier(n->name)); + + if (n->child) + { + appendStringInfoString(context->buf, + n->outerJoin ? " OUTER " : " INNER "); + get_json_table_plan(tf, n->child, context, + IsA(n->child, JsonTableSiblingNode)); + } + } + + if (parenthesize) + appendStringInfoChar(context->buf, ')'); +} + /* * get_json_table_columns - Parse back JSON_TABLE columns */ @@ -11151,6 +11195,8 @@ get_json_table(TableFunc *tf, deparse_context *co= ntext, bool showimplicit) =20 get_const_expr(root->path, context, -1); =20 + appendStringInfo(buf, " AS %s", quote_identifier(root->name)); + if (jexpr->passing_values) { ListCell *lc1, *lc2; @@ -11182,14 +11228,9 @@ get_json_table(TableFunc *tf, deparse_context *c= ontext, bool showimplicit) =20 get_json_table_columns(tf, root, context, showimplicit); =20 - if (!root->outerJoin || !root->unionJoin) - { - appendStringInfoChar(buf, ' '); - appendContextKeyword(context, "PLAN DEFAULT", 0, 0, 0); - appendStringInfo(buf, "(%s, %s)", - root->outerJoin ? "OUTER" : "INNER", - root->unionJoin ? "UNION" : "CROSS"); - } + appendStringInfoChar(buf, ' '); + appendContextKeyword(context, "PLAN ", 0, 0, 0); + get_json_table_plan(tf, (Node *) root, context, true); =20 if (jexpr->on_error->btype !=3D JSON_BEHAVIOR_EMPTY) get_json_behavior(jexpr->on_error, context, "ERROR"); diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.= h index 3f55d4b7fb..405547bad6 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -110,6 +110,8 @@ extern JsonFormat *makeJsonFormat(JsonFormatType type= , JsonEncoding encoding, int location); extern JsonValueExpr *makeJsonValueExpr(Expr *expr, JsonFormat *format);= extern JsonBehavior *makeJsonBehavior(JsonBehaviorType type, Node *expr)= ; +extern Node *makeJsonTableJoinedPlan(JsonTablePlanJoinType type, + Node *plan1, Node *plan2, int location); extern Node *makeJsonKeyValue(Node *key, Node *value); extern Node *makeJsonIsPredicate(Node *expr, JsonFormat *format, JsonValueType vtype, bool unique_keys, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenode= s.h index 0de78dd6ad..d5f7dbcaa9 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1646,6 +1646,7 @@ typedef struct JsonTableColumn char *name; /* column name */ TypeName *typeName; /* column type name */ JsonPathSpec pathspec; /* path specification, if any */ + char *pathname; /* path name, if any */ JsonFormat *format; /* JSON format clause, if specified */ JsonWrapper wrapper; /* WRAPPER behavior for formatted columns */ bool omit_quotes; /* omit or keep quotes on scalar strings? */ @@ -1655,6 +1656,17 @@ typedef struct JsonTableColumn int location; /* token location, or -1 if unknown */ } JsonTableColumn; =20 +/* + * JsonTablePlanType - + * flags for JSON_TABLE plan node types representation + */ +typedef enum JsonTablePlanType +{ + JSTP_DEFAULT, + JSTP_SIMPLE, + JSTP_JOINED, +} JsonTablePlanType; + /* * JsonTablePlanJoinType - * flags for JSON_TABLE join types representation @@ -1667,6 +1679,23 @@ typedef enum JsonTablePlanJoinType JSTPJ_UNION =3D 0x08, } JsonTablePlanJoinType; =20 +typedef struct JsonTablePlan JsonTablePlan; + +/* + * JsonTablePlan - + * untransformed representation of JSON_TABLE plan node + */ +struct JsonTablePlan +{ + NodeTag type; + JsonTablePlanType plan_type; /* plan type */ + JsonTablePlanJoinType join_type; /* join type (for joined plan only) */= + JsonTablePlan *plan1; /* first joined plan */ + JsonTablePlan *plan2; /* second joined plan */ + char *pathname; /* path name (for simple plan only) */ + int location; /* token location, or -1 if unknown */ +}; + /* * JsonTable - * untransformed representation of JSON_TABLE @@ -1676,7 +1705,7 @@ typedef struct JsonTable NodeTag type; JsonCommon *common; /* common JSON path syntax fields */ List *columns; /* list of JsonTableColumn */ - JsonTablePlanJoinType join_type; /* DEFAULT PLAN join type */ + JsonTablePlan *plan; /* join plan, if specified */ JsonBehavior *on_error; /* ON ERROR behavior, if specified */ 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 1aa3e01048..7884578cec 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1475,9 +1475,9 @@ typedef struct JsonTableParentNode { NodeTag type; Const *path; /* jsonpath constant */ + char *name; /* path name */ Node *child; /* nested columns, if any */ bool outerJoin; /* outer or inner join for nested columns? */ - bool unionJoin; /* union or cross join for nested columns? */ int colMin; /* min column index in the resulting column list */ int colMax; /* max column index in the resulting column list */ bool errorOnError; /* ERROR/EMPTY ON ERROR behavior */ diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regre= ss/expected/jsonb_sqljson.out index 0cd8f73c66..81f94e3c91 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -1136,18 +1136,18 @@ SELECT * FROM ia int[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$', - NESTED PATH '$[1]' COLUMNS ( + NESTED PATH '$[1]' AS p1 COLUMNS ( a1 int, - NESTED PATH '$[*]' COLUMNS ( + NESTED PATH '$[*]' AS "p1 1" COLUMNS ( a11 text ), b1 text ), - NESTED PATH '$[2]' COLUMNS ( - NESTED PATH '$[*]' COLUMNS ( + NESTED PATH '$[2]' AS p2 COLUMNS ( + NESTED PATH '$[*]' AS "p2:1" COLUMNS ( a21 text ), - NESTED PATH '$[*]' COLUMNS ( + NESTED PATH '$[*]' AS p22 COLUMNS ( a22 text ) ) @@ -1187,7 +1187,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS "json_table".a21, "json_table".a22 FROM JSON_TABLE( - 'null'::jsonb, '$[*]' + 'null'::jsonb, '$[*]' AS json_table_path_1 PASSING 1 + 2 AS a, '"foo"'::json AS "b c" @@ -1218,34 +1218,35 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS= ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$', - NESTED PATH '$[1]' + NESTED PATH '$[1]' AS p1 COLUMNS ( a1 integer PATH '$."a1"', b1 text PATH '$."b1"', - NESTED PATH '$[*]' + NESTED PATH '$[*]' AS "p1 1" COLUMNS ( a11 text PATH '$."a11"' ) ), - NESTED PATH '$[2]' + NESTED PATH '$[2]' AS p2 COLUMNS ( - NESTED PATH '$[*]' + NESTED PATH '$[*]' AS "p2:1" COLUMNS ( a21 text PATH '$."a21"' ), - NESTED PATH '$[*]' + NESTED PATH '$[*]' AS p22 COLUMNS ( a22 text PATH '$."a22"' ) ) ) + PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 O= UTER ("p2:1" UNION p22)))) ) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; - = = = = = = = QUERY P= LAN = = = = = = = =20 -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------- + = = = = = = = = QUERY P= LAN = = = = = = = = =20 +------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------------------------------------------------------------------- Table Function Scan on "json_table" Output: "json_table".id, "json_table".id2, "json_table"."int", "json_= table".text, "json_table"."char(4)", "json_table".bool, "json_table"."num= eric", "json_table".domain, "json_table".js, "json_table".jb, "json_table= ".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table"= =2Ejsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json= _table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb= 2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jb= a, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, = "json_table".a22 - Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' PASSING 3 AS a,= '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, = "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '= $', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_= domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSO= N PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(= 4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUO= TES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boo= lean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON= ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 j= son PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q json= b PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba js= onb[] PATH '$', NESTED PATH '$[1]' COLUMNS (a1 integer PATH '$."a1"', b1 = text PATH '$."b1"', NESTED PATH '$[*]' COLUMNS (a11 text PATH '$."a11"'))= , NESTED PATH '$[2]' COLUMNS ( NESTED PATH '$[*]' COLUMNS (a21 text PATH = '$."a21"'), NESTED PATH '$[*]' COLUMNS (a22 text PATH '$."a22"')))) + Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_p= ath_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY,= id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)= " character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$= ', domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$'= , jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', j= sv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq js= onb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '= $."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS P= ATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UN= KNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONA= L WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta te= xt[] PATH '$', jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS (a1= integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 = 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS (= NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED P= ATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_pa= th_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))) (3 rows) =20 DROP VIEW jsonb_table_view; @@ -1337,13 +1338,49 @@ ERROR: cannot cast type boolean to jsonb LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX.= =2E. ^ -- JSON_TABLE: nested paths and plans +-- Should fail (JSON_TABLE columns shall contain explicit AS path +-- specifications if explicit PLAN clause is used) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' -- AS required here + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; +ERROR: invalid JSON_TABLE expression +LINE 2: jsonb '[]', '$'=20 + ^ +DETAIL: JSON_TABLE columns shall contain explicit AS pathname specifica= tion if explicit PLAN clause is used +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( -- AS required here + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; +ERROR: invalid JSON_TABLE expression +LINE 4: NESTED PATH '$' COLUMNS (=20 + ^ +DETAIL: JSON_TABLE columns shall contain explicit AS pathname specifica= tion if explicit PLAN clause is used -- Should fail (column names anf path names shall be distinct) SELECT * FROM JSON_TABLE( - jsonb '[]', '$' + jsonb '[]', '$' AS a COLUMNS ( - a int, - b text, - a jsonb + a int + ) +) jt; +ERROR: duplicate JSON_TABLE column name: a +HINT: JSON_TABLE path names and column names shall be distinct from one= another +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS a + COLUMNS ( + b int, + NESTED PATH '$' AS a + COLUMNS ( + c int + ) ) ) jt; ERROR: duplicate JSON_TABLE column name: a @@ -1352,10 +1389,9 @@ SELECT * FROM JSON_TABLE( jsonb '[]', '$' COLUMNS ( b int, - NESTED PATH '$' + NESTED PATH '$' AS b COLUMNS ( - c int, - b text + c int ) ) ) jt; @@ -1364,22 +1400,209 @@ HINT: JSON_TABLE path names and column names sh= all be distinct from one another SELECT * FROM JSON_TABLE( jsonb '[]', '$' COLUMNS ( - NESTED PATH '$' + NESTED PATH '$' AS a COLUMNS ( b int ), NESTED PATH '$' COLUMNS ( - NESTED PATH '$' + NESTED PATH '$' AS a COLUMNS ( - c int, - b text + c int ) ) ) ) jt; -ERROR: duplicate JSON_TABLE column name: b +ERROR: duplicate JSON_TABLE column name: a HINT: JSON_TABLE path names and column names shall be distinct from one= another +-- JSON_TABLE: plan validation +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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; +ERROR: invalid JSON_TABLE plan +LINE 12: PLAN (p1) + ^ +DETAIL: path name mismatch: expected p0 but p1 is given +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 4: NESTED PATH '$' AS p1 COLUMNS ( + ^ +DETAIL: plan node for nested path p1 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER p3) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 4: NESTED PATH '$' AS p1 COLUMNS ( + ^ +DETAIL: plan node for nested path p1 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 UNION p1 UNION p11) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 12: PLAN (p0 UNION p1 UNION p11) + ^ +DETAIL: expected INNER or OUTER JSON_TABLE plan node +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER (p1 CROSS p13)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 8: NESTED PATH '$' AS p2 COLUMNS ( + ^ +DETAIL: plan node for nested path p2 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER (p1 CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 5: NESTED PATH '$' AS p11 COLUMNS ( foo int ), + ^ +DETAIL: plan node for nested path p11 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER ((p1 UNION p11) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 12: PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) + ^ +DETAIL: plan node contains some extra or duplicate sibling nodes +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER ((p1 INNER p11) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 6: NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ^ +DETAIL: plan node for nested path p12 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2)) +) jt; +ERROR: invalid JSON_TABLE plan +LINE 9: NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ^ +DETAIL: plan node for nested path p21 was not found in plan +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))) +) jt; + bar | foo | baz=20 +-----+-----+----- +(0 rows) + +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' -- without root path name + COLUMNS ( + NESTED PATH '$' 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 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)) +) jt; +ERROR: invalid JSON_TABLE expression +LINE 2: jsonb 'null', 'strict $[*]'=20 + ^ +DETAIL: JSON_TABLE columns shall contain explicit AS pathname specifica= tion if explicit PLAN clause is used -- JSON_TABLE: plan execution CREATE TEMP TABLE jsonb_table_test (js jsonb); INSERT INTO jsonb_table_test @@ -1397,12 +1620,12 @@ select from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + 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 =20 @@ -1426,12 +1649,12 @@ select from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) plan default (outer, union) ) jt; @@ -1450,18 +1673,78 @@ from 4 | -1 | 2 | =20 (11 rows) =20 +-- specific plan (p outer (pb union pc)) +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 '$' ) + ) + plan (p outer (pb union pc)) + ) jt; + n | a | b | c =20 +---+----+---+---- + 1 | 1 | | =20 + 2 | 2 | 1 | =20 + 2 | 2 | 2 | =20 + 2 | 2 | 3 | =20 + 2 | 2 | | 10 + 2 | 2 | | =20 + 2 | 2 | | 20 + 3 | 3 | 1 | =20 + 3 | 3 | 2 | =20 + 4 | -1 | 1 | =20 + 4 | -1 | 2 | =20 +(11 rows) + +-- specific plan (p outer (pc union pb)) +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 '$' ) + ) + plan (p outer (pc union pb)) + ) jt; + n | a | c | b=20 +---+----+----+--- + 1 | 1 | | =20 + 2 | 2 | 10 | =20 + 2 | 2 | | =20 + 2 | 2 | 20 | =20 + 2 | 2 | | 1 + 2 | 2 | | 2 + 2 | 2 | | 3 + 3 | 3 | | 1 + 3 | 3 | | 2 + 4 | -1 | | 1 + 4 | -1 | | 2 +(11 rows) + -- default plan (inner, union) select jt.* from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) plan default (inner) ) jt; @@ -1479,18 +1762,47 @@ from 4 | -1 | 2 | =20 (10 rows) =20 +-- specific plan (p inner (pb union pc)) +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 '$' ) + ) + plan (p inner (pb union pc)) + ) jt; + n | a | b | c =20 +---+----+---+---- + 2 | 2 | 1 | =20 + 2 | 2 | 2 | =20 + 2 | 2 | 3 | =20 + 2 | 2 | | 10 + 2 | 2 | | =20 + 2 | 2 | | 20 + 3 | 3 | 1 | =20 + 3 | 3 | 2 | =20 + 4 | -1 | 1 | =20 + 4 | -1 | 2 | =20 +(10 rows) + -- default plan (inner, cross) select jt.* from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) plan default (cross, inner) ) jt; @@ -1507,18 +1819,46 @@ from 2 | 2 | 3 | 20 (9 rows) =20 +-- specific plan (p inner (pb cross pc)) +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 '$' ) + ) + plan (p inner (pb cross pc)) + ) jt; + n | a | b | c =20 +---+---+---+---- + 2 | 2 | 1 | 10 + 2 | 2 | 1 | =20 + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | =20 + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | =20 + 2 | 2 | 3 | 20 +(9 rows) + -- default plan (outer, cross) select jt.* from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) plan default (outer, cross) ) jt; @@ -1538,6 +1878,90 @@ from 4 | -1 | | =20 (12 rows) =20 +-- specific plan (p outer (pb cross pc)) +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 '$' ) + ) + plan (p outer (pb cross pc)) + ) jt; + n | a | b | c =20 +---+----+---+---- + 1 | 1 | | =20 + 2 | 2 | 1 | 10 + 2 | 2 | 1 | =20 + 2 | 2 | 1 | 20 + 2 | 2 | 2 | 10 + 2 | 2 | 2 | =20 + 2 | 2 | 2 | 20 + 2 | 2 | 3 | 10 + 2 | 2 | 3 | =20 + 2 | 2 | 3 | 20 + 3 | 3 | | =20 + 4 | -1 | | =20 +(12 rows) + +select + jt.*, b1 + 100 as b +from + json_table (jsonb + '[ + {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]}, + {"a": 2, "b": [10, 20], "c": [1, null, 2]}, + {"x": "3", "b": [11, 22, 33, 44]} + ]', + '$[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on error, + nested path 'strict $.b[*]' as pb columns ( + b text format json path '$', + nested path 'strict $[*]' as pb1 columns ( + b1 int path '$' + ) + ), + nested path 'strict $.c[*]' as pc columns ( + c text format json path '$', + nested path 'strict $[*]' as pc1 columns ( + c1 int path '$' + ) + ) + ) + --plan default(outer, cross) + plan(p outer ((pb inner pb1) cross (pc outer pc1))) + ) jt; + n | a | b | b1 | c | c1 | b =20 +---+---+--------------+-----+------+----+----- + 1 | 1 | [1, 10] | 1 | 1 | | 101 + 1 | 1 | [1, 10] | 1 | null | | 101 + 1 | 1 | [1, 10] | 1 | 2 | | 101 + 1 | 1 | [1, 10] | 10 | 1 | | 110 + 1 | 1 | [1, 10] | 10 | null | | 110 + 1 | 1 | [1, 10] | 10 | 2 | | 110 + 1 | 1 | [2] | 2 | 1 | | 102 + 1 | 1 | [2] | 2 | null | | 102 + 1 | 1 | [2] | 2 | 2 | | 102 + 1 | 1 | [3, 30, 300] | 3 | 1 | | 103 + 1 | 1 | [3, 30, 300] | 3 | null | | 103 + 1 | 1 | [3, 30, 300] | 3 | 2 | | 103 + 1 | 1 | [3, 30, 300] | 30 | 1 | | 130 + 1 | 1 | [3, 30, 300] | 30 | null | | 130 + 1 | 1 | [3, 30, 300] | 30 | 2 | | 130 + 1 | 1 | [3, 30, 300] | 300 | 1 | | 400 + 1 | 1 | [3, 30, 300] | 300 | null | | 400 + 1 | 1 | [3, 30, 300] | 300 | 2 | | 400 + 2 | 2 | | | | | =20 + 3 | | | | | | =20 +(20 rows) + -- Should succeed (JSON arguments are passed to root and nested paths) SELECT * FROM diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sq= l/jsonb_sqljson.sql index cd97ff7f46..be2d65b3ff 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -414,18 +414,18 @@ SELECT * FROM ta text[] PATH '$', jba jsonb[] PATH '$', =20 - NESTED PATH '$[1]' COLUMNS ( + NESTED PATH '$[1]' AS p1 COLUMNS ( a1 int, - NESTED PATH '$[*]' COLUMNS ( + NESTED PATH '$[*]' AS "p1 1" COLUMNS ( a11 text ), b1 text ), - NESTED PATH '$[2]' COLUMNS ( - NESTED PATH '$[*]' COLUMNS ( + NESTED PATH '$[2]' AS p2 COLUMNS ( + NESTED PATH '$[*]' AS "p2:1" COLUMNS ( a21 text ), - NESTED PATH '$[*]' COLUMNS ( + NESTED PATH '$[*]' AS p22 COLUMNS ( a22 text ) ) @@ -478,13 +478,42 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (= a jsonb EXISTS PATH '$.a')); =20 -- JSON_TABLE: nested paths and plans =20 +-- Should fail (JSON_TABLE columns shall contain explicit AS path +-- specifications if explicit PLAN clause is used) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' -- AS required here + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( -- AS required here + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; + -- Should fail (column names anf path names shall be distinct) SELECT * FROM JSON_TABLE( - jsonb '[]', '$' + jsonb '[]', '$' AS a + COLUMNS ( + a int + ) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' AS a COLUMNS ( - a int, - b text, - a jsonb + b int, + NESTED PATH '$' AS a + COLUMNS ( + c int + ) ) ) jt; =20 @@ -492,10 +521,9 @@ SELECT * FROM JSON_TABLE( jsonb '[]', '$' COLUMNS ( b int, - NESTED PATH '$' + NESTED PATH '$' AS b COLUMNS ( - c int, - b text + c int ) ) ) jt; @@ -503,21 +531,176 @@ SELECT * FROM JSON_TABLE( SELECT * FROM JSON_TABLE( jsonb '[]', '$' COLUMNS ( - NESTED PATH '$' + NESTED PATH '$' AS a COLUMNS ( b int ), NESTED PATH '$' COLUMNS ( - NESTED PATH '$' + NESTED PATH '$' AS a COLUMNS ( - c int, - b text + c int ) ) ) ) jt; =20 +-- JSON_TABLE: plan validation + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER p3) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 UNION p1 UNION p11) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER (p1 CROSS p13)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER (p1 CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER ((p1 UNION p11) CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER ((p1 INNER p11) CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' AS p0 + COLUMNS ( + NESTED PATH '$' 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 (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb 'null', 'strict $[*]' -- without root path name + COLUMNS ( + NESTED PATH '$' 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 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)) +) jt; + -- JSON_TABLE: plan execution =20 CREATE TEMP TABLE jsonb_table_test (js jsonb); @@ -538,12 +721,12 @@ select from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) ) jt; =20 @@ -553,64 +736,174 @@ select from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) plan default (outer, union) ) jt; =20 +-- specific plan (p outer (pb union pc)) +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 '$' ) + ) + plan (p outer (pb union pc)) + ) jt; + +-- specific plan (p outer (pc union pb)) +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 '$' ) + ) + plan (p outer (pc union pb)) + ) jt; + -- default plan (inner, union) select jt.* from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) plan default (inner) ) jt; =20 +-- specific plan (p inner (pb union pc)) +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 '$' ) + ) + plan (p inner (pb union pc)) + ) jt; + -- default plan (inner, cross) select jt.* from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) plan default (cross, inner) ) jt; =20 +-- specific plan (p inner (pb cross pc)) +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 '$' ) + ) + plan (p inner (pb cross pc)) + ) jt; + -- default plan (outer, cross) select jt.* from jsonb_table_test jtt, json_table ( - jtt.js,'strict $[*]' + jtt.js,'strict $[*]' as p columns ( n for ordinality, a int path 'lax $.a' default -1 on empty, - nested path 'strict $.b[*]' columns ( b int path '$' ), - nested path 'strict $.c[*]' columns ( c int path '$' ) + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) ) plan default (outer, cross) ) jt; =20 +-- specific plan (p outer (pb cross pc)) +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 '$' ) + ) + plan (p outer (pb cross pc)) + ) jt; + + +select + jt.*, b1 + 100 as b +from + json_table (jsonb + '[ + {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]}, + {"a": 2, "b": [10, 20], "c": [1, null, 2]}, + {"x": "3", "b": [11, 22, 33, 44]} + ]', + '$[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on error, + nested path 'strict $.b[*]' as pb columns ( + b text format json path '$', + nested path 'strict $[*]' as pb1 columns ( + b1 int path '$' + ) + ), + nested path 'strict $.c[*]' as pc columns ( + c text format json path '$', + nested path 'strict $[*]' as pc1 columns ( + c1 int path '$' + ) + ) + ) + --plan default(outer, cross) + plan(p outer ((pb inner pb1) cross (pc outer pc1))) + ) jt; =20 -- Should succeed (JSON arguments are passed to root and nested paths) SELECT * diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typede= fs.list index 27cf806043..38cb738c1a 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1237,7 +1237,9 @@ JsonTableColumnType JsonTableContext JsonTableJoinState JsonTableParentNode +JsonTablePlan JsonTablePlanJoinType +JsonTablePlanType JsonTableScanState JsonTableSiblingNode JsonTokenType --=20 2.25.4 --------------5BB5E3CCDF424A5E7F475161--