aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/nodes/copyfuncs.c26
-rw-r--r--src/backend/nodes/equalfuncs.c3
-rw-r--r--src/backend/nodes/makefuncs.c19
-rw-r--r--src/backend/nodes/outfuncs.c3
-rw-r--r--src/backend/nodes/readfuncs.c3
-rw-r--r--src/backend/parser/gram.y130
-rw-r--r--src/backend/parser/parse_jsontable.c323
-rw-r--r--src/backend/utils/adt/jsonpath_exec.c118
-rw-r--r--src/backend/utils/adt/ruleutils.c50
-rw-r--r--src/include/nodes/makefuncs.h2
-rw-r--r--src/include/nodes/nodes.h1
-rw-r--r--src/include/nodes/parsenodes.h42
-rw-r--r--src/include/nodes/primnodes.h3
-rw-r--r--src/include/parser/kwlist.h1
-rw-r--r--src/test/regress/expected/jsonb_sqljson.out600
-rw-r--r--src/test/regress/sql/jsonb_sqljson.sql396
-rw-r--r--src/tools/pgindent/typedefs.list3
17 files changed, 1614 insertions, 109 deletions
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 1a74122f139..d5760b10067 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2696,6 +2696,7 @@ _copyJsonTable(const JsonTable *from)
COPY_NODE_FIELD(common);
COPY_NODE_FIELD(columns);
+ COPY_NODE_FIELD(plan);
COPY_NODE_FIELD(on_error);
COPY_NODE_FIELD(alias);
COPY_SCALAR_FIELD(location);
@@ -2715,6 +2716,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);
@@ -2727,6 +2729,24 @@ _copyJsonTableColumn(const JsonTableColumn *from)
}
/*
+ * _copyJsonTablePlan
+ */
+static JsonTablePlan *
+_copyJsonTablePlan(const JsonTablePlan *from)
+{
+ JsonTablePlan *newnode = 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;
+}
+
+/*
* _copyJsonTableParent
*/
static JsonTableParent *
@@ -2735,7 +2755,9 @@ _copyJsonTableParent(const JsonTableParent *from)
JsonTableParent *newnode = makeNode(JsonTableParent);
COPY_NODE_FIELD(path);
+ COPY_STRING_FIELD(name);
COPY_NODE_FIELD(child);
+ COPY_SCALAR_FIELD(outerJoin);
COPY_SCALAR_FIELD(colMin);
COPY_SCALAR_FIELD(colMax);
@@ -2752,6 +2774,7 @@ _copyJsonTableSibling(const JsonTableSibling *from)
COPY_NODE_FIELD(larg);
COPY_NODE_FIELD(rarg);
+ COPY_SCALAR_FIELD(cross);
return newnode;
}
@@ -5929,6 +5952,9 @@ copyObjectImpl(const void *from)
case T_JsonTableColumn:
retval = _copyJsonTableColumn(from);
break;
+ case T_JsonTablePlan:
+ retval = _copyJsonTablePlan(from);
+ break;
case T_JsonTableParent:
retval = _copyJsonTableParent(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 5c21850c975..1f765f42c91 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -181,7 +181,9 @@ static bool
_equalJsonTableParent(const JsonTableParent *a, const JsonTableParent *b)
{
COMPARE_NODE_FIELD(path);
+ COMPARE_STRING_FIELD(name);
COMPARE_NODE_FIELD(child);
+ COMPARE_SCALAR_FIELD(outerJoin);
COMPARE_SCALAR_FIELD(colMin);
COMPARE_SCALAR_FIELD(colMax);
@@ -193,6 +195,7 @@ _equalJsonTableSibling(const JsonTableSibling *a, const JsonTableSibling *b)
{
COMPARE_NODE_FIELD(larg);
COMPARE_NODE_FIELD(rarg);
+ COMPARE_SCALAR_FIELD(cross);
return true;
}
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index cd6c300e7bf..41e26a0fe67 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -868,6 +868,25 @@ makeJsonBehavior(JsonBehaviorType type, Node *default_expr)
}
/*
+ * makeJsonTableJoinedPlan -
+ * creates a joined JsonTablePlan node
+ */
+Node *
+makeJsonTableJoinedPlan(JsonTablePlanJoinType type, Node *plan1, Node *plan2,
+ int location)
+{
+ JsonTablePlan *n = makeNode(JsonTablePlan);
+
+ n->plan_type = JSTP_JOINED;
+ n->join_type = type;
+ n->plan1 = castNode(JsonTablePlan, plan1);
+ n->plan2 = castNode(JsonTablePlan, plan2);
+ n->location = 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 213396f9992..abb1f787eff 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1875,7 +1875,9 @@ _outJsonTableParent(StringInfo str, const JsonTableParent *node)
WRITE_NODE_TYPE("JSONTABPNODE");
WRITE_NODE_FIELD(path);
+ WRITE_STRING_FIELD(name);
WRITE_NODE_FIELD(child);
+ WRITE_BOOL_FIELD(outerJoin);
WRITE_INT_FIELD(colMin);
WRITE_INT_FIELD(colMax);
}
@@ -1887,6 +1889,7 @@ _outJsonTableSibling(StringInfo str, const JsonTableSibling *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 19e257684cd..e7d008b2c51 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1541,7 +1541,9 @@ _readJsonTableParent(void)
READ_LOCALS(JsonTableParent);
READ_NODE_FIELD(path);
+ READ_STRING_FIELD(name);
READ_NODE_FIELD(child);
+ READ_BOOL_FIELD(outerJoin);
READ_INT_FIELD(colMin);
READ_INT_FIELD(colMax);
@@ -1555,6 +1557,7 @@ _readJsonTableSibling(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 13fa5bea87a..7e3f4a5d275 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -683,6 +683,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
%type <list> json_name_and_value_list
json_value_expr_list
@@ -698,6 +710,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> json_encoding
json_encoding_clause_opt
+ 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
@@ -812,7 +827,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
@@ -15928,13 +15943,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->plan = (JsonTablePlan *) $5;
+ n->on_error = $6;
n->location = @1;
$$ = (Node *) n;
}
@@ -16055,12 +16072,15 @@ json_table_formatted_column_definition:
;
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 = makeNode(JsonTableColumn);
n->coltype = JTC_NESTED;
n->pathspec = $3;
- n->columns = $4;
+ n->pathname = $4;
+ n->columns = $5;
n->location = @1;
$$ = (Node *) n;
}
@@ -16071,6 +16091,106 @@ path_opt:
| /* EMPTY */ { }
;
+json_table_plan_clause_opt:
+ json_table_specific_plan { $$ = $1; }
+ | json_table_default_plan { $$ = $1; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
+json_table_specific_plan:
+ PLAN '(' json_table_plan ')' { $$ = $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 = makeNode(JsonTablePlan);
+ n->plan_type = JSTP_SIMPLE;
+ n->pathname = $1;
+ n->location = @1;
+ $$ = (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
+ { $$ = makeJsonTableJoinedPlan(JSTPJ_OUTER, $1, $3, @1); }
+ ;
+
+json_table_plan_inner:
+ json_table_plan_simple INNER_P json_table_plan_primary
+ { $$ = 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
+ { $$ = makeJsonTableJoinedPlan(JSTPJ_UNION, $1, $3, @1); }
+ | json_table_plan_union UNION json_table_plan_primary
+ { $$ = makeJsonTableJoinedPlan(JSTPJ_UNION, $1, $3, @1); }
+ ;
+
+json_table_plan_cross:
+ json_table_plan_primary CROSS json_table_plan_primary
+ { $$ = makeJsonTableJoinedPlan(JSTPJ_CROSS, $1, $3, @1); }
+ | json_table_plan_cross CROSS json_table_plan_primary
+ { $$ = makeJsonTableJoinedPlan(JSTPJ_CROSS, $1, $3, @1); }
+ ;
+
+json_table_plan_primary:
+ json_table_plan_simple { $$ = $1; }
+ | '(' json_table_plan ')'
+ {
+ castNode(JsonTablePlan, $2)->location = @1;
+ $$ = $2;
+ }
+ ;
+
+json_table_default_plan:
+ PLAN DEFAULT '(' json_table_default_plan_choices ')'
+ {
+ JsonTablePlan *n = makeNode(JsonTablePlan);
+ n->plan_type = JSTP_DEFAULT;
+ n->join_type = $4;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+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
{
@@ -16951,6 +17071,7 @@ unreserved_keyword:
| PASSING
| PASSWORD
| PATH
+ | PLAN
| PLANS
| POLICY
| PRECEDING
@@ -17568,6 +17689,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 dd75a40bf6f..c7dcefa11cb 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -37,13 +37,16 @@ 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;
static JsonTableParent * transformJsonTableColumns(JsonTableContext *cxt,
- List *columns,
- char *pathSpec,
- int location);
+ JsonTablePlan *plan,
+ List *columns,
+ char *pathSpec,
+ char **pathName,
+ int location);
static Node *
makeStringConst(char *str, int location)
@@ -154,62 +157,239 @@ registerAllJsonTableColumns(JsonTableContext *cxt, List *columns)
JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
if (jtc->coltype == 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 = namebuf;
+
+ do
+ {
+ snprintf(namebuf, sizeof(namebuf), "json_table_path_%d",
+ ++cxt->pathNameId);
+ } while (isJsonTablePathNameDuplicate(cxt, name));
+
+ name = pstrdup(name);
+ cxt->pathNames = 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 == JSTP_SIMPLE)
+ *paths = lappend(*paths, plan->pathname);
+ else if (plan->plan_type == JSTP_JOINED)
+ {
+ if (plan->join_type == JSTPJ_INNER ||
+ plan->join_type == JSTPJ_OUTER)
+ {
+ Assert(plan->plan1->plan_type == JSTP_SIMPLE);
+ *paths = lappend(*paths, plan->plan1->pathname);
+ }
+ else if (plan->join_type == JSTPJ_CROSS ||
+ plan->join_type == 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 = NIL;
+ int nchildren = 0;
+
+ if (plan)
+ collectSiblingPathsInJsonTablePlan(plan, &siblings);
+
+ foreach(lc1, columns)
+ {
+ JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1));
+
+ if (jtc->coltype == JTC_NESTED)
+ {
+ ListCell *lc2;
+ bool found = false;
+
+ if (!jtc->pathname)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("nested JSON_TABLE columns must contain an explicit AS pathname specification if an explicit PLAN clause is used"),
+ parser_errposition(pstate, jtc->location)));
+
+ /* find nested path name in the list of sibling path names */
+ foreach(lc2, siblings)
+ {
+ if ((found = !strcmp(jtc->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 = castNode(JsonTableColumn, lfirst(lc));
+
+ if (jtc->coltype == JTC_NESTED &&
+ jtc->pathname &&
+ !strcmp(jtc->pathname, pathname))
+ return jtc;
}
+
+ return NULL;
}
static Node *
-transformNestedJsonTableColumn(JsonTableContext *cxt, JsonTableColumn *jtc)
+transformNestedJsonTableColumn(JsonTableContext *cxt, JsonTableColumn *jtc,
+ JsonTablePlan *plan)
{
JsonTableParent *node;
+ char *pathname = jtc->pathname;
- node = transformJsonTableColumns(cxt, jtc->columns, jtc->pathspec,
- jtc->location);
+ node = transformJsonTableColumns(cxt, plan, jtc->columns, jtc->pathspec,
+ &pathname, jtc->location);
+ node->name = pstrdup(pathname);
return (Node *) node;
}
static Node *
-makeJsonTableSiblingJoin(Node *lnode, Node *rnode)
+makeJsonTableSiblingJoin(bool cross, Node *lnode, Node *rnode)
{
JsonTableSibling *join = makeNode(JsonTableSibling);
join->larg = lnode;
join->rarg = rnode;
+ join->cross = cross;
return (Node *) join;
}
/*
- * Recursively transform child (nested) JSON_TABLE columns.
+ * Recursively transform child JSON_TABLE plan.
*
- * Child columns are transformed into a binary tree of union-joined
- * JsonTableSiblings.
+ * Default plan is transformed into a cross/union join of its nested columns.
+ * Simple and outer/inner plans are transformed into a JsonTableParent by
+ * finding and transforming corresponding nested column.
+ * Sibling plans are recursively transformed into a JsonTableSibling.
*/
static Node *
-transformJsonTableChildColumns(JsonTableContext *cxt, List *columns)
+transformJsonTableChildPlan(JsonTableContext *cxt, JsonTablePlan *plan,
+ List *columns)
{
- Node *res = NULL;
- ListCell *lc;
+ JsonTableColumn *jtc = NULL;
- /* transform all nested columns into union join */
- foreach(lc, columns)
+ if (!plan || plan->plan_type == JSTP_DEFAULT)
{
- JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
- Node *node;
+ /* unspecified or default plan */
+ Node *res = NULL;
+ ListCell *lc;
+ bool cross = plan && (plan->join_type & JSTPJ_CROSS);
- if (jtc->coltype != JTC_NESTED)
- continue;
+ /* transform all nested columns into cross/union join */
+ foreach(lc, columns)
+ {
+ JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
+ Node *node;
+
+ if (jtc->coltype != JTC_NESTED)
+ continue;
- node = transformNestedJsonTableColumn(cxt, jtc);
+ node = transformNestedJsonTableColumn(cxt, jtc, plan);
- /* join transformed node with previous sibling nodes */
- res = res ? makeJsonTableSiblingJoin(res, node) : node;
+ /* join transformed node with previous sibling nodes */
+ res = res ? makeJsonTableSiblingJoin(cross, res, node) : node;
+ }
+
+ return res;
}
+ else if (plan->plan_type == JSTP_SIMPLE)
+ {
+ jtc = findNestedJsonTableColumn(columns, plan->pathname);
+ }
+ else if (plan->plan_type == JSTP_JOINED)
+ {
+ if (plan->join_type == JSTPJ_INNER ||
+ plan->join_type == JSTPJ_OUTER)
+ {
+ Assert(plan->plan1->plan_type == JSTP_SIMPLE);
+ jtc = findNestedJsonTableColumn(columns, plan->plan1->pathname);
+ }
+ else
+ {
+ Node *node1 =
+ transformJsonTableChildPlan(cxt, plan->plan1, columns);
+ Node *node2 =
+ transformJsonTableChildPlan(cxt, plan->plan2, columns);
+
+ return makeJsonTableSiblingJoin(plan->join_type == JSTPJ_CROSS,
+ node1, node2);
+ }
+ }
+ else
+ elog(ERROR, "invalid JSON_TABLE plan type %d", plan->plan_type);
+
+ 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 res;
+ return transformNestedJsonTableColumn(cxt, jtc, plan);
}
/* Check whether type is json/jsonb, array, or record. */
@@ -374,16 +554,80 @@ makeParentJsonTableNode(JsonTableContext *cxt, char *pathSpec, List *columns)
}
static JsonTableParent *
-transformJsonTableColumns(JsonTableContext *cxt, List *columns, char *pathSpec,
+transformJsonTableColumns(JsonTableContext *cxt, JsonTablePlan *plan,
+ List *columns, char *pathSpec, char **pathName,
int location)
{
JsonTableParent *node;
+ JsonTablePlan *childPlan;
+ bool defaultPlan = !plan || plan->plan_type == JSTP_DEFAULT;
+
+ if (!*pathName)
+ {
+ if (cxt->table->plan)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid JSON_TABLE expression"),
+ errdetail("JSON_TABLE columns must contain "
+ "explicit AS pathname specification if "
+ "explicit PLAN clause is used"),
+ parser_errposition(cxt->pstate, location)));
+
+ *pathName = generateJsonTablePathName(cxt);
+ }
+
+ if (defaultPlan)
+ childPlan = plan;
+ else
+ {
+ /* validate parent and child plans */
+ JsonTablePlan *parentPlan;
+
+ if (plan->plan_type == JSTP_JOINED)
+ {
+ if (plan->join_type != JSTPJ_INNER &&
+ plan->join_type != 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 = plan->plan1;
+ childPlan = plan->plan2;
+
+ Assert(parentPlan->plan_type != JSTP_JOINED);
+ Assert(parentPlan->pathname);
+ }
+ else
+ {
+ parentPlan = plan;
+ childPlan = 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);
+ }
/* transform only non-nested columns */
node = makeParentJsonTableNode(cxt, pathSpec, columns);
+ node->name = pstrdup(*pathName);
- /* transform recursively nested columns */
- node->child = transformJsonTableChildColumns(cxt, columns);
+ if (childPlan || defaultPlan)
+ {
+ /* transform recursively nested columns */
+ node->child = transformJsonTableChildPlan(cxt, childPlan, columns);
+ if (node->child)
+ node->outerJoin = !plan || (plan->join_type & JSTPJ_OUTER);
+ /* else: default plan case, no children found */
+ }
return node;
}
@@ -401,7 +645,9 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
JsonTableContext cxt;
TableFunc *tf = makeNode(TableFunc);
JsonFuncExpr *jfe = makeNode(JsonFuncExpr);
+ JsonTablePlan *plan = jt->plan;
JsonCommon *jscommon;
+ char *rootPathName = jt->common->pathname;
char *rootPath;
bool is_lateral;
@@ -409,9 +655,31 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
cxt.table = jt;
cxt.tablefunc = tf;
cxt.pathNames = NIL;
+ cxt.pathNameId = 0;
+
+ if (rootPathName)
+ registerJsonTableColumn(&cxt, rootPathName);
registerAllJsonTableColumns(&cxt, jt->columns);
+#if 0 /* XXX it' unclear from the standard whether root path name is mandatory or not */
+ if (plan && plan->plan_type != JSTP_DEFAULT && !rootPathName)
+ {
+ /* Assign root path name and create corresponding plan node */
+ JsonTablePlan *rootNode = makeNode(JsonTablePlan);
+ JsonTablePlan *rootPlan = (JsonTablePlan *)
+ makeJsonTableJoinedPlan(JSTPJ_OUTER, (Node *) rootNode,
+ (Node *) plan, jt->location);
+
+ rootPathName = generateJsonTablePathName(&cxt);
+
+ rootNode->plan_type = JSTP_SIMPLE;
+ rootNode->pathname = rootPathName;
+
+ plan = rootPlan;
+ }
+#endif
+
jscommon = copyObject(jt->common);
jscommon->pathspec = makeStringConst(pstrdup("$"), -1);
@@ -447,7 +715,8 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
rootPath = castNode(A_Const, jt->common->pathspec)->val.sval.sval;
- tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns, rootPath,
+ tf->plan = (Node *) transformJsonTableColumns(&cxt, plan, jt->columns,
+ rootPath, &rootPathName,
jt->common->location);
tf->ordinalitycol = -1; /* undefine ordinality column number */
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index c55b3aae027..ee9b5089b92 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,
JsonTableSibling *join = castNode(JsonTableSibling, 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.
*
* Returns 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 */
+ }
+
+ 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;
}
- /* fetch next inner row */
- return JsonTableNextJoinRow(state->u.join.right);
+ 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.
*
* Returns 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,42 @@ 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;
}
return true;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index e6173a9db42..3296ad070ed 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11169,11 +11169,55 @@ get_json_table_nested_columns(TableFunc *tf, Node *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);
}
}
/*
+ * 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, JsonTableSibling))
+ {
+ JsonTableSibling *n = (JsonTableSibling *) node;
+
+ get_json_table_plan(tf, n->larg, context,
+ IsA(n->larg, JsonTableSibling) ||
+ castNode(JsonTableParent, n->larg)->child);
+
+ appendStringInfoString(context->buf, n->cross ? " CROSS " : " UNION ");
+
+ get_json_table_plan(tf, n->rarg, context,
+ IsA(n->rarg, JsonTableSibling) ||
+ castNode(JsonTableParent, n->rarg)->child);
+ }
+ else
+ {
+ JsonTableParent *n = castNode(JsonTableParent, 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, JsonTableSibling));
+ }
+ }
+
+ if (parenthesize)
+ appendStringInfoChar(context->buf, ')');
+}
+
+/*
* get_json_table_columns - Parse back JSON_TABLE columns
*/
static void
@@ -11301,6 +11345,8 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit)
get_const_expr(root->path, context, -1);
+ appendStringInfo(buf, " AS %s", quote_identifier(root->name));
+
if (jexpr->passing_values)
{
ListCell *lc1, *lc2;
@@ -11333,6 +11379,10 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit)
get_json_table_columns(tf, root, context, showimplicit);
+ appendStringInfoChar(buf, ' ');
+ appendContextKeyword(context, "PLAN ", 0, 0, 0);
+ get_json_table_plan(tf, (Node *) root, context, true);
+
if (jexpr->on_error->btype != 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 872f2f0828f..c717468eb39 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/nodes.h b/src/include/nodes/nodes.h
index aefce33e284..300824258ed 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -518,6 +518,7 @@ typedef enum NodeTag
T_JsonIsPredicate,
T_JsonTable,
T_JsonTableColumn,
+ T_JsonTablePlan,
T_JsonCommon,
T_JsonArgument,
T_JsonKeyValue,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e58211eac1b..4a2ca81f3c0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1688,6 +1688,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? */
@@ -1698,6 +1699,46 @@ typedef struct JsonTableColumn
} JsonTableColumn;
/*
+ * 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
+ */
+typedef enum JsonTablePlanJoinType
+{
+ JSTPJ_INNER = 0x01,
+ JSTPJ_OUTER = 0x02,
+ JSTPJ_CROSS = 0x04,
+ JSTPJ_UNION = 0x08,
+} JsonTablePlanJoinType;
+
+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
*/
@@ -1706,6 +1747,7 @@ typedef struct JsonTable
NodeTag type;
JsonCommon *common; /* common JSON path syntax fields */
List *columns; /* list of JsonTableColumn */
+ 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 290898cfd75..66d32fc0062 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1473,7 +1473,9 @@ typedef struct JsonTableParent
{
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? */
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 */
@@ -1488,6 +1490,7 @@ typedef struct JsonTableSibling
NodeTag type;
Node *larg; /* left join node */
Node *rarg; /* right join node */
+ bool cross; /* cross or union join? */
} JsonTableSibling;
/* ----------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 9097ce7b26d..0caa7310f2d 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -336,6 +336,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 144cc0c5578..ae77af7ae23 100644
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -1140,18 +1140,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
)
)
@@ -1191,7 +1191,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"
@@ -1222,34 +1222,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 OUTER ("p2:1" UNION p22))))
)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "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 JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] 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_path_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 '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] 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 PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
(3 rows)
DROP VIEW jsonb_table_view;
@@ -1341,13 +1342,49 @@ ERROR: cannot cast type boolean to jsonb
LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX...
^
-- JSON_TABLE: nested paths and plans
+-- Should fail (JSON_TABLE columns must contain explicit AS path
+-- specifications if explicit PLAN clause is used)
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' -- AS <path name> required here
+ COLUMNS (
+ foo int PATH '$'
+ )
+ PLAN DEFAULT (UNION)
+) jt;
+ERROR: invalid JSON_TABLE expression
+LINE 2: jsonb '[]', '$' -- AS <path name> required here
+ ^
+DETAIL: JSON_TABLE columns must contain explicit AS pathname specification if explicit PLAN clause is used
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS path1
+ COLUMNS (
+ NESTED PATH '$' COLUMNS ( -- AS <path name> required here
+ foo int PATH '$'
+ )
+ )
+ PLAN DEFAULT (UNION)
+) jt;
+ERROR: invalid JSON_TABLE expression
+LINE 4: NESTED PATH '$' COLUMNS ( -- AS <path name> required here
+ ^
+DETAIL: JSON_TABLE columns must contain explicit AS pathname specification if explicit PLAN clause is used
-- Should fail (column names must be distinct)
SELECT * FROM JSON_TABLE(
- jsonb '[]', '$'
+ jsonb '[]', '$' AS a
+ COLUMNS (
+ a int
+ )
+) jt;
+ERROR: duplicate JSON_TABLE column name: a
+HINT: JSON_TABLE column names must be distinct from one another
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS a
COLUMNS (
- a int,
- b text,
- a jsonb
+ b int,
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
)
) jt;
ERROR: duplicate JSON_TABLE column name: a
@@ -1356,10 +1393,9 @@ SELECT * FROM JSON_TABLE(
jsonb '[]', '$'
COLUMNS (
b int,
- NESTED PATH '$'
+ NESTED PATH '$' AS b
COLUMNS (
- c int,
- b text
+ c int
)
)
) jt;
@@ -1368,22 +1404,209 @@ HINT: JSON_TABLE column names must 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 column names must 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
+-----+-----+-----
+(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 $[*]' -- without root path name
+ ^
+DETAIL: JSON_TABLE columns must contain explicit AS pathname specification if explicit PLAN clause is used
-- JSON_TABLE: plan execution
CREATE TEMP TABLE jsonb_table_test (js jsonb);
INSERT INTO jsonb_table_test
@@ -1401,12 +1624,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
@@ -1424,6 +1647,325 @@ from
4 | -1 | 2 |
(11 rows)
+-- default plan (outer, union)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ 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)
+
+-- 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
+---+----+---+----
+ 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)
+
+-- 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
+---+----+----+---
+ 1 | 1 | |
+ 2 | 2 | 10 |
+ 2 | 2 | |
+ 2 | 2 | 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 $[*]' 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 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)
+
+-- 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
+---+----+---+----
+ 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 $[*]' 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 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)
+
+-- 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
+---+---+---+----
+ 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 $[*]' 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 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)
+
+-- 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
+---+----+---+----
+ 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)
+
+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
+---+---+--------------+-----+------+----+-----
+ 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 | | | | |
+ 3 | | | | | |
+(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/sql/jsonb_sqljson.sql
index 62236c9fb15..90c59754889 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -418,18 +418,18 @@ SELECT * FROM
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
)
)
@@ -482,13 +482,42 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
-- JSON_TABLE: nested paths and plans
+-- Should fail (JSON_TABLE columns must contain explicit AS path
+-- specifications if explicit PLAN clause is used)
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' -- AS <path name> required here
+ COLUMNS (
+ foo int PATH '$'
+ )
+ PLAN DEFAULT (UNION)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS path1
+ COLUMNS (
+ NESTED PATH '$' COLUMNS ( -- AS <path name> required here
+ foo int PATH '$'
+ )
+ )
+ PLAN DEFAULT (UNION)
+) jt;
+
-- Should fail (column names must be distinct)
SELECT * FROM JSON_TABLE(
- jsonb '[]', '$'
+ jsonb '[]', '$' AS a
COLUMNS (
- a int,
- b text,
- a jsonb
+ a int
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS a
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
)
) jt;
@@ -496,10 +525,9 @@ SELECT * FROM JSON_TABLE(
jsonb '[]', '$'
COLUMNS (
b int,
- NESTED PATH '$'
+ NESTED PATH '$' AS b
COLUMNS (
- c int,
- b text
+ c int
)
)
) jt;
@@ -507,21 +535,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;
+-- 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
CREATE TEMP TABLE jsonb_table_test (js jsonb);
@@ -542,13 +725,188 @@ 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;
+
+-- default plan (outer, union)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (outer, union)
+ ) jt;
+
+-- 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 $[*]' 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 default (inner)
+ ) jt;
+
+-- 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 $[*]' 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 default (cross, inner)
+ ) jt;
+
+-- 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 $[*]' 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 default (outer, cross)
+ ) jt;
+
+-- 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;
-- Should succeed (JSON arguments are passed to root and nested paths)
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 4b12c575ab5..9b4f77fbf18 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1244,6 +1244,9 @@ JsonTableColumnType
JsonTableContext
JsonTableJoinState
JsonTableParent
+JsonTablePlan
+JsonTablePlanJoinType
+JsonTablePlanType
JsonTableScanState
JsonTableSibling
JsonTokenType
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-19 17:07:00 +0000