agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Andrew Dunstan <andrew@dunslane.net>
Subject: [PATCH 3/4] JSON_TABLE PLAN DEFAULT clause
Date: Mon, 13 Sep 2021 18:03:14 -0400
---
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 (
<replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> PASSING { <replaceable>value</replaceable> AS <replaceable>varname</replaceable> } <optional>, ...</optional> </optional>
COLUMNS ( <replaceable class="parameter">json_table_column</replaceable> <optional>, ...</optional> )
+ <optional>
+ PLAN DEFAULT ( { INNER | OUTER } <optional> , { CROSS | UNION } </optional>
+ | { CROSS | UNION } <optional> , { INNER | OUTER } </optional> )
+ </optional>
)
<phrase>
where <replaceable class="parameter">json_table_column</replaceable> is:
@@ -19268,7 +19272,8 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
The rows produced by <function>JSON_TABLE</function> 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 <acronym>JSON</acronym>
- data.
+ data. Optionally, you can specify how to join the columns returned
+ by <literal>NESTED PATH</literal> using the <literal>PLAN DEFAULT</literal> clause.
</para>
<para>
@@ -19281,22 +19286,6 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
the resulting rows are joined to the parent row.
</para>
- <para>
- Columns with parent/child relationship are joined using
- <literal>LEFT OUTER JOIN</literal>, 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 <literal>NESTED PATH</literal>,
- with NULL values inserted into the child columns if the corresponding
- values are missing.
- </para>
-
- <para>
- Sibling columns are joined using
- <literal>FULL OUTER JOIN ON FALSE</literal>, 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.
- </para>
-
</sect5>
<sect5>
<title>Parameters</title>
@@ -19468,6 +19457,10 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
<function>JSON_TABLE</function> expressions in an SQL statement.
</para>
+ <para>
+ You can use the <literal>PLAN DEFAULT</literal> clause to define how
+ to join the columns returned by <replaceable>NESTED PATH</replaceable> clauses.
+ </para>
</listitem>
</varlistentry>
@@ -19490,8 +19483,99 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <literal>PLAN DEFAULT ( <replaceable>option</replaceable> <optional>, ... </optional> )</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines how to join the data returned by <replaceable>NESTED PATH</replaceable>
+ clauses to the constructed view. The <literal>INNER</literal> and
+ <literal>OUTER</literal> options define the joining plan for parent/child
+ columns, while <literal>UNION</literal> and <literal>CROSS</literal>
+ affect the sibling columns. You can override the default plans for all
+ columns at once.
+ </para>
+
+ <para>
+ To join columns with parent/child relationship, you can use:
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal>INNER</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Use <literal>INNER JOIN</literal>, so that the parent row
+ is omitted from the output if it does not have any child rows
+ after joining the data returned by <literal>NESTED PATH</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>OUTER</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Use <literal>LEFT OUTER JOIN</literal>, 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 <literal>NESTED PATH</literal>, with NULL values
+ inserted into the child columns if the corresponding
+ values are missing.
+ </para>
+ <para>
+ This is the default option for joining columns with parent/child relationship.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ To join sibling columns, you can use:
+ </para>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal>UNION</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Use <literal>FULL OUTER JOIN ON FALSE</literal>, 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.
+ </para>
+ <para>
+ This is the default option for joining sibling columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>CROSS</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Use <literal>CROSS JOIN</literal>, so that the output includes
+ a row for every possible combination of rows from the left-hand
+ and the right-hand columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
+ </listitem>
+ </varlistentry>
+ </variablelist>
</sect5>
<sect5>
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 <ival> 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 <andrew@dunslane.net>
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
<synopsis>
JSON_TABLE (
- <replaceable>context_item</replaceable>, <replaceable>path_expression<=
/replaceable> <optional> PASSING { <replaceable>value</replaceable> AS <r=
eplaceable>varname</replaceable> } <optional>, ...</optional> </optional>=
+ <replaceable>context_item</replaceable>, <replaceable>path_expression<=
/replaceable> <optional> AS <replaceable>json_path_name</replaceable> </o=
ptional> <optional> PASSING { <replaceable>value</replaceable> AS <replac=
eable>varname</replaceable> } <optional>, ...</optional> </optional>
COLUMNS ( <replaceable class=3D"parameter">json_table_column</replacea=
ble> <optional>, ...</optional> )
<optional>
+ PLAN ( <replaceable class=3D"parameter">json_table_plan</replaceable=
> ) |
PLAN DEFAULT ( { INNER | OUTER } <optional> , { CROSS | UNION } </op=
tional>
| { CROSS | UNION } <optional> , { INNER | OUTER } </op=
tional> )
</optional>
@@ -19231,6 +19232,16 @@ where <replaceable class=3D"parameter">json_tabl=
e_column</replaceable> is:
| NESTED PATH <replaceable>json_path_specification</replaceable> <opti=
onal> AS <replaceable>path_name</replaceable> </optional>
COLUMNS ( <replaceable>json_table_column</replaceable> <optional=
>, ...</optional> )
| <replaceable>name</replaceable> FOR ORDINALITY
+<phrase>
+<replaceable>json_table_plan</replaceable> is:
+</phrase>
+ <replaceable>json_path_name</replaceable> <optional> { OUTER | INNER=
} <replaceable>json_table_plan_primary</replaceable> </optional>
+ | <replaceable>json_table_plan_primary</replaceable> { UNION <replacea=
ble>json_table_plan_primary</replaceable> } <optional>...</optional>
+ | <replaceable>json_table_plan_primary</replaceable> { CROSS <replacea=
ble>json_table_plan_primary</replaceable> } <optional>...</optional>
+<phrase>
+<replaceable>json_table_plan_primary</replaceable> is:
+</phrase>
+ <replaceable>json_path_name</replaceable> | ( <replaceable>json_tabl=
e_plan</replaceable> )
=20
</synopsis>
=20
@@ -19273,7 +19284,7 @@ where <replaceable class=3D"parameter">json_table=
_column</replaceable> 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 <acronym>JSON=
</acronym>
data. Optionally, you can specify how to join the columns returned=
- by <literal>NESTED PATH</literal> using the <literal>PLAN DEFAULT<=
/literal> clause.
+ by <literal>NESTED PATH</literal> using the <literal>PLAN</literal=
> clause.
</para>
=20
<para>
@@ -19458,7 +19469,7 @@ where <replaceable class=3D"parameter">json_table=
_column</replaceable> is:
</para>
=20
<para>
- You can use the <literal>PLAN DEFAULT</literal> clause to define ho=
w
+ You can use the <literal>PLAN</literal> clause to define how
to join the columns returned by <replaceable>NESTED PATH</replaceab=
le> clauses.
</para>
</listitem>
@@ -19485,18 +19496,31 @@ where <replaceable class=3D"parameter">json_tab=
le_column</replaceable> is:
=20
<varlistentry>
<term>
- <literal>PLAN DEFAULT ( <replaceable>option</replaceable> <optional=
>, ... </optional> )</literal>
+ <literal>AS <replaceable>json_path_name</replaceable></literal>
</term>
<listitem>
- <para>
- Defines how to join the data returned by <replaceable>NESTED PATH<=
/replaceable>
- clauses to the constructed view. The <literal>INNER</literal> and
- <literal>OUTER</literal> options define the joining plan for paren=
t/child
- columns, while <literal>UNION</literal> and <literal>CROSS</litera=
l>
- affect the sibling columns. You can override the default plans for=
all
- columns at once.
- </para>
=20
+ <para>
+ The optional <replaceable>json_path_name</replaceable> serves as an=
+ identifier of the provided <replaceable>json_path_specification</re=
placeable>.
+ The path name must be unique and cannot coincide with column names.=
+ When using the <literal>PLAN</literal> clause, you must specify the=
names
+ for all the paths, including the row pattern. Each path name can ap=
pear in
+ the <literal>PLAN</literal> clause only once.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>PLAN ( <replaceable class=3D"parameter">json_table_plan</r=
eplaceable> )</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Defines how to join the data returned by <replaceable>NESTED PATH</=
replaceable>
+ clauses to the constructed view.
+ </para>
<para>
To join columns with parent/child relationship, you can use:
</para>
@@ -19575,6 +19599,23 @@ where <replaceable class=3D"parameter">json_tabl=
e_column</replaceable> is:
=20
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>PLAN DEFAULT ( <replaceable>option</replaceable> <optional=
>, ... </optional> )</literal>
+ </term>
+ <listitem>
+ <para>
+ Overrides the default joining plans. The <literal>INNER</literal> =
and
+ <literal>OUTER</literal> options define the joining plan for paren=
t/child
+ columns, while <literal>UNION</literal> and <literal>CROSS</litera=
l>
+ 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 <literal>PLAN=
DEFAULT</literal>
+ clause, they must be provided for all the paths to conform to
+ the SQL/JSON standard.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect5>
=20
@@ -19606,9 +19647,35 @@ SELECT jt.* FROM
</screen>
</para>
=20
- </sect5>
- </sect4>
-
+ <para>
+ Find a director that has done films in two different genres:
+<screen>
+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;
+</screen>
+ </para>
+ </sect5>
+ </sect4>
</sect3>
=20
<sect3 id=3D"functions-sqljson-serializing">
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 <list> 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 <ival> 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 <path name> 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 <path name> 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 <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 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--
view thread (4+ messages) latest in thread
Message-ID: <no-message-id-660702@localhost>
Permalink: ../../no-message-id-660702@localhost/
Also on: postgresql.org/message-id/no-message-id-660702@localhost
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andrew@dunslane.net
Subject: Re: [PATCH 3/4] JSON_TABLE PLAN DEFAULT clause
In-Reply-To: <no-message-id-660702@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox