pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Cc: Oleg Bartunov <obartunov@gmail.com>
Cc: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Subject: Re: SQL/JSON: JSON_TABLE
Date: Thu, 28 Jun 2018 03:34:38 +0300
Message-ID: <78894d50-010d-0876-23cb-55cba86404e2@postgrespro.ru> (raw )
In-Reply-To: <3e32fbfd-d046-8f02-24e2-c766415eb5f2@postgrespro.ru >
References: <132f26c4-dfc6-f8fd-4764-2cbf455a3aec@postgrespro.ru >
<17404ea6-b496-f898-c039-6d73cf0cd9b5@postgrespro.ru >
<bf5f479a-ecce-6380-8415-9531e9f919c4@postgrespro.ru >
<0f26e0d7-fbf9-0bbe-7fe2-4237d2538e5a@postgrespro.ru >
<bbe6696b-ccd8-ffe2-a913-f808930a1240@postgrespro.ru >
<3e32fbfd-d046-8f02-24e2-c766415eb5f2@postgrespro.ru >
Attached 15th version of JSON_TABLE patches.
Implicit root path name assignment was disabled (it is unclear from standard).
Now all JSON path names are required if the explicit PLAN clause is used.
The documentation for JSON_TABLE can be found now in a separate patch:
https://www.postgresql.org/message-id/732208d3-56c3-25a4-8f08-3be1d54ad51b%40postgrespro.ru
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments: download all (2 files, .tar.gz)
0015-json_table-v15.patch (text/x-patch, 129.8K) download
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 1f0e581..b574348 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -2924,9 +2924,11 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
{
TableFunc *tablefunc = (TableFunc *) node;
+ APP_JUMB(tablefunc->functype);
JumbleExpr(jstate, tablefunc->docexpr);
JumbleExpr(jstate, tablefunc->rowexpr);
JumbleExpr(jstate, (Node *) tablefunc->colexprs);
+ JumbleExpr(jstate, (Node *) tablefunc->colvalexprs);
}
break;
case T_TableSampleClause:
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 73d94b7..5997822 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -2925,7 +2925,9 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es)
break;
case T_TableFuncScan:
Assert(rte->rtekind == RTE_TABLEFUNC);
- objectname = "xmltable";
+ objectname = rte->tablefunc ?
+ rte->tablefunc->functype == TFT_XMLTABLE ?
+ "xmltable" : "json_table" : NULL;
objecttag = "Table Function Name";
break;
case T_ValuesScan:
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 5b84312..2cee26f 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -2191,6 +2191,7 @@ ExecInitExprRec(Expr *node, ExprState *state,
var->var.cb_arg = var;
var->estate = ExecInitExpr(argexpr, state->parent);
var->econtext = NULL;
+ var->mcxt = NULL;
var->evaluated = false;
var->value = (Datum) 0;
var->isnull = true;
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index b3b3b08..1ca6d7b 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4170,6 +4170,7 @@ ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
case JSON_BEHAVIOR_NULL:
case JSON_BEHAVIOR_UNKNOWN:
+ case JSON_BEHAVIOR_EMPTY:
*is_null = true;
return (Datum) 0;
@@ -4235,8 +4236,14 @@ EvalJsonPathVar(void *cxt, bool *isnull)
if (!ecxt->evaluated)
{
+ MemoryContext oldcxt = ecxt->mcxt ?
+ MemoryContextSwitchTo(ecxt->mcxt) : NULL;
+
ecxt->value = ExecEvalExpr(ecxt->estate, ecxt->econtext, &ecxt->isnull);
ecxt->evaluated = true;
+
+ if (oldcxt)
+ MemoryContextSwitchTo(oldcxt);
}
*isnull = ecxt->isnull;
@@ -4426,6 +4433,11 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext,
*resnull = false;
break;
+ case IS_JSON_TABLE:
+ res = item;
+ *resnull = false;
+ break;
+
default:
elog(ERROR, "unrecognized SQL/JSON expression op %d",
jexpr->op);
@@ -4446,6 +4458,7 @@ ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext,
}
if (jexpr->op != IS_JSON_EXISTS &&
+ jexpr->op != IS_JSON_TABLE &&
(!empty ? jexpr->op != IS_JSON_VALUE :
/* result is already coerced in DEFAULT behavior case */
jexpr->on_empty.btype != JSON_BEHAVIOR_DEFAULT))
diff --git a/src/backend/executor/nodeTableFuncscan.c b/src/backend/executor/nodeTableFuncscan.c
index fed6f2b..dfae539 100644
--- a/src/backend/executor/nodeTableFuncscan.c
+++ b/src/backend/executor/nodeTableFuncscan.c
@@ -28,6 +28,7 @@
#include "executor/tablefunc.h"
#include "miscadmin.h"
#include "utils/builtins.h"
+#include "utils/jsonpath.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/xml.h"
@@ -161,8 +162,9 @@ ExecInitTableFuncScan(TableFuncScan *node, EState *estate, int eflags)
scanstate->ss.ps.qual =
ExecInitQual(node->scan.plan.qual, &scanstate->ss.ps);
- /* Only XMLTABLE is supported currently */
- scanstate->routine = &XmlTableRoutine;
+ /* Only XMLTABLE and JSON_TABLE are supported currently */
+ scanstate->routine =
+ tf->functype == TFT_XMLTABLE ? &XmlTableRoutine : &JsonbTableRoutine;
scanstate->perValueCxt =
AllocSetContextCreate(CurrentMemoryContext,
@@ -365,14 +367,17 @@ tfuncInitialize(TableFuncScanState *tstate, ExprContext *econtext, Datum doc)
routine->SetNamespace(tstate, ns_name, ns_uri);
}
- /* Install the row filter expression into the table builder context */
- value = ExecEvalExpr(tstate->rowexpr, econtext, &isnull);
- if (isnull)
- ereport(ERROR,
- (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
- errmsg("row filter expression must not be null")));
+ if (routine->SetRowFilter)
+ {
+ /* Install the row filter expression into the table builder context */
+ value = ExecEvalExpr(tstate->rowexpr, econtext, &isnull);
+ if (isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("row filter expression must not be null")));
- routine->SetRowFilter(tstate, TextDatumGetCString(value));
+ routine->SetRowFilter(tstate, TextDatumGetCString(value));
+ }
/*
* Install the column filter expressions into the table builder context.
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 8f51a0e..fe3977a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1288,6 +1288,7 @@ _copyTableFunc(const TableFunc *from)
{
TableFunc *newnode = makeNode(TableFunc);
+ COPY_SCALAR_FIELD(functype);
COPY_NODE_FIELD(ns_uris);
COPY_NODE_FIELD(ns_names);
COPY_NODE_FIELD(docexpr);
@@ -1298,7 +1299,9 @@ _copyTableFunc(const TableFunc *from)
COPY_NODE_FIELD(colcollations);
COPY_NODE_FIELD(colexprs);
COPY_NODE_FIELD(coldefexprs);
+ COPY_NODE_FIELD(colvalexprs);
COPY_BITMAPSET_FIELD(notnulls);
+ COPY_NODE_FIELD(plan);
COPY_SCALAR_FIELD(ordinalitycol);
COPY_LOCATION_FIELD(location);
@@ -2505,6 +2508,99 @@ _copyJsonArgument(const JsonArgument *from)
return newnode;
}
+/*
+ * _copyJsonTable
+ */
+static JsonTable *
+_copyJsonTable(const JsonTable *from)
+{
+ JsonTable *newnode = makeNode(JsonTable);
+
+ COPY_NODE_FIELD(common);
+ COPY_NODE_FIELD(columns);
+ COPY_NODE_FIELD(plan);
+ COPY_NODE_FIELD(on_error);
+ COPY_NODE_FIELD(alias);
+ COPY_SCALAR_FIELD(location);
+
+ return newnode;
+}
+
+/*
+ * _copyJsonTableColumn
+ */
+static JsonTableColumn *
+_copyJsonTableColumn(const JsonTableColumn *from)
+{
+ JsonTableColumn *newnode = makeNode(JsonTableColumn);
+
+ COPY_SCALAR_FIELD(coltype);
+ 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);
+ COPY_NODE_FIELD(columns);
+ COPY_NODE_FIELD(on_empty);
+ COPY_NODE_FIELD(on_error);
+ COPY_SCALAR_FIELD(location);
+
+ return newnode;
+}
+
+/*
+ * _copyJsonTablePlan
+ */
+static JsonTablePlan *
+_copyJsonTablePlan(const JsonTablePlan *from)
+{
+ JsonTablePlan *newnode = makeNode(JsonTablePlan);
+
+ COPY_SCALAR_FIELD(plan_type);
+ COPY_SCALAR_FIELD(join_type);
+ COPY_STRING_FIELD(pathname);
+ COPY_NODE_FIELD(plan1);
+ COPY_NODE_FIELD(plan2);
+ COPY_SCALAR_FIELD(location);
+
+ return newnode;
+}
+
+/*
+ * _copyJsonTableParentNode
+ */
+static JsonTableParentNode *
+_copyJsonTableParentNode(const JsonTableParentNode *from)
+{
+ JsonTableParentNode *newnode = makeNode(JsonTableParentNode);
+
+ COPY_NODE_FIELD(path);
+ COPY_STRING_FIELD(name);
+ COPY_NODE_FIELD(child);
+ COPY_SCALAR_FIELD(outerJoin);
+ COPY_SCALAR_FIELD(colMin);
+ COPY_SCALAR_FIELD(colMax);
+
+ return newnode;
+}
+
+/*
+ * _copyJsonTableSiblingNode
+ */
+static JsonTableSiblingNode *
+_copyJsonTableSiblingNode(const JsonTableSiblingNode *from)
+{
+ JsonTableSiblingNode *newnode = makeNode(JsonTableSiblingNode);
+
+ COPY_NODE_FIELD(larg);
+ COPY_NODE_FIELD(rarg);
+ COPY_SCALAR_FIELD(cross);
+
+ return newnode;
+}
+
/* ****************************************************************
* relation.h copy functions
*
@@ -5446,6 +5542,21 @@ copyObjectImpl(const void *from)
case T_JsonItemCoercions:
retval = _copyJsonItemCoercions(from);
break;
+ case T_JsonTable:
+ retval = _copyJsonTable(from);
+ break;
+ case T_JsonTableColumn:
+ retval = _copyJsonTableColumn(from);
+ break;
+ case T_JsonTablePlan:
+ retval = _copyJsonTablePlan(from);
+ break;
+ case T_JsonTableParentNode:
+ retval = _copyJsonTableParentNode(from);
+ break;
+ case T_JsonTableSiblingNode:
+ retval = _copyJsonTableSiblingNode(from);
+ break;
/*
* RELATION NODES
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index cd3ee22..d22d934 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -119,6 +119,7 @@ _equalRangeVar(const RangeVar *a, const RangeVar *b)
static bool
_equalTableFunc(const TableFunc *a, const TableFunc *b)
{
+ COMPARE_SCALAR_FIELD(functype);
COMPARE_NODE_FIELD(ns_uris);
COMPARE_NODE_FIELD(ns_names);
COMPARE_NODE_FIELD(docexpr);
@@ -129,7 +130,9 @@ _equalTableFunc(const TableFunc *a, const TableFunc *b)
COMPARE_NODE_FIELD(colcollations);
COMPARE_NODE_FIELD(colexprs);
COMPARE_NODE_FIELD(coldefexprs);
+ COMPARE_NODE_FIELD(colvalexprs);
COMPARE_BITMAPSET_FIELD(notnulls);
+ COMPARE_NODE_FIELD(plan);
COMPARE_SCALAR_FIELD(ordinalitycol);
COMPARE_LOCATION_FIELD(location);
@@ -137,6 +140,29 @@ _equalTableFunc(const TableFunc *a, const TableFunc *b)
}
static bool
+_equalJsonTableParentNode(const JsonTableParentNode *a, const JsonTableParentNode *b)
+{
+ COMPARE_NODE_FIELD(path);
+ COMPARE_STRING_FIELD(name);
+ COMPARE_NODE_FIELD(child);
+ COMPARE_SCALAR_FIELD(outerJoin);
+ COMPARE_SCALAR_FIELD(colMin);
+ COMPARE_SCALAR_FIELD(colMax);
+
+ return true;
+}
+
+static bool
+_equalJsonTableSiblingNode(const JsonTableSiblingNode *a, const JsonTableSiblingNode *b)
+{
+ COMPARE_NODE_FIELD(larg);
+ COMPARE_NODE_FIELD(rarg);
+ COMPARE_SCALAR_FIELD(cross);
+
+ return true;
+}
+
+static bool
_equalIntoClause(const IntoClause *a, const IntoClause *b)
{
COMPARE_NODE_FIELD(rel);
@@ -3286,6 +3312,12 @@ equal(const void *a, const void *b)
case T_JsonItemCoercions:
retval = _equalJsonItemCoercions(a, b);
break;
+ case T_JsonTableParentNode:
+ retval = _equalJsonTableParentNode(a, b);
+ break;
+ case T_JsonTableSiblingNode:
+ retval = _equalJsonTableSiblingNode(a, b);
+ break;
/*
* RELATION NODES
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index ebc41ea..334cabe 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -661,6 +661,25 @@ makeJsonBehavior(JsonBehaviorType type, Node *default_expr)
}
/*
+ * makeJsonTableJoinedPlan -
+ * creates a joined JsonTablePlan node
+ */
+Node *
+makeJsonTableJoinedPlan(JsonTablePlanJoinType type, Node *plan1, Node *plan2,
+ int location)
+{
+ JsonTablePlan *n = makeNode(JsonTablePlan);
+
+ n->plan_type = JSTP_JOINED;
+ n->join_type = type;
+ n->plan1 = castNode(JsonTablePlan, plan1);
+ n->plan2 = castNode(JsonTablePlan, plan2);
+ n->location = location;
+
+ return (Node *) n;
+}
+
+/*
* makeJsonEncoding -
* converts JSON encoding name to enum JsonEncoding
*/
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index f699977..c201ebf 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2291,6 +2291,8 @@ expression_tree_walker(Node *node,
return true;
if (walker(tf->coldefexprs, context))
return true;
+ if (walker(tf->colvalexprs, context))
+ return true;
}
break;
case T_JsonValueExpr:
@@ -3172,6 +3174,7 @@ expression_tree_mutator(Node *node,
MUTATE(newnode->rowexpr, tf->rowexpr, Node *);
MUTATE(newnode->colexprs, tf->colexprs, List *);
MUTATE(newnode->coldefexprs, tf->coldefexprs, List *);
+ MUTATE(newnode->colvalexprs, tf->colvalexprs, List *);
return (Node *) newnode;
}
break;
@@ -3993,6 +3996,30 @@ raw_expression_tree_walker(Node *node,
return true;
}
break;
+ case T_JsonTable:
+ {
+ JsonTable *jt = (JsonTable *) node;
+
+ if (walker(jt->common, context))
+ return true;
+ if (walker(jt->columns, context))
+ return true;
+ }
+ break;
+ case T_JsonTableColumn:
+ {
+ JsonTableColumn *jtc = (JsonTableColumn *) node;
+
+ if (walker(jtc->typename, context))
+ return true;
+ if (walker(jtc->on_empty, context))
+ return true;
+ if (walker(jtc->on_error, context))
+ return true;
+ if (jtc->coltype == JTC_NESTED && walker(jtc->columns, context))
+ return true;
+ }
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 9529717..79c0bc0 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1108,6 +1108,7 @@ _outTableFunc(StringInfo str, const TableFunc *node)
{
WRITE_NODE_TYPE("TABLEFUNC");
+ WRITE_ENUM_FIELD(functype, TableFuncType);
WRITE_NODE_FIELD(ns_uris);
WRITE_NODE_FIELD(ns_names);
WRITE_NODE_FIELD(docexpr);
@@ -1118,7 +1119,9 @@ _outTableFunc(StringInfo str, const TableFunc *node)
WRITE_NODE_FIELD(colcollations);
WRITE_NODE_FIELD(colexprs);
WRITE_NODE_FIELD(coldefexprs);
+ WRITE_NODE_FIELD(colvalexprs);
WRITE_BITMAPSET_FIELD(notnulls);
+ WRITE_NODE_FIELD(plan);
WRITE_INT_FIELD(ordinalitycol);
WRITE_LOCATION_FIELD(location);
}
@@ -1859,6 +1862,29 @@ _outJsonIsPredicateOpts(StringInfo str, const JsonIsPredicateOpts *node)
WRITE_BOOL_FIELD(unique_keys);
}
+static void
+_outJsonTableParentNode(StringInfo str, const JsonTableParentNode *node)
+{
+ WRITE_NODE_TYPE("JSONTABPNODE");
+
+ WRITE_NODE_FIELD(path);
+ WRITE_STRING_FIELD(name);
+ WRITE_NODE_FIELD(child);
+ WRITE_BOOL_FIELD(outerJoin);
+ WRITE_INT_FIELD(colMin);
+ WRITE_INT_FIELD(colMax);
+}
+
+static void
+_outJsonTableSiblingNode(StringInfo str, const JsonTableSiblingNode *node)
+{
+ WRITE_NODE_TYPE("JSONTABSNODE");
+
+ WRITE_NODE_FIELD(larg);
+ WRITE_NODE_FIELD(rarg);
+ WRITE_BOOL_FIELD(cross);
+}
+
/*****************************************************************************
*
* Stuff from relation.h.
@@ -4432,6 +4458,12 @@ outNode(StringInfo str, const void *obj)
case T_JsonItemCoercions:
_outJsonItemCoercions(str, obj);
break;
+ case T_JsonTableParentNode:
+ _outJsonTableParentNode(str, obj);
+ break;
+ case T_JsonTableSiblingNode:
+ _outJsonTableSiblingNode(str, obj);
+ break;
default:
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 6954854..00397c0 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -479,6 +479,7 @@ _readTableFunc(void)
{
READ_LOCALS(TableFunc);
+ READ_ENUM_FIELD(functype, TableFuncType);
READ_NODE_FIELD(ns_uris);
READ_NODE_FIELD(ns_names);
READ_NODE_FIELD(docexpr);
@@ -489,7 +490,9 @@ _readTableFunc(void)
READ_NODE_FIELD(colcollations);
READ_NODE_FIELD(colexprs);
READ_NODE_FIELD(coldefexprs);
+ READ_NODE_FIELD(colvalexprs);
READ_BITMAPSET_FIELD(notnulls);
+ READ_NODE_FIELD(plan);
READ_INT_FIELD(ordinalitycol);
READ_LOCATION_FIELD(location);
@@ -1403,6 +1406,33 @@ _readJsonExpr(void)
READ_DONE();
}
+static JsonTableParentNode *
+_readJsonTableParentNode(void)
+{
+ READ_LOCALS(JsonTableParentNode);
+
+ READ_NODE_FIELD(path);
+ READ_STRING_FIELD(name);
+ READ_NODE_FIELD(child);
+ READ_BOOL_FIELD(outerJoin);
+ READ_INT_FIELD(colMin);
+ READ_INT_FIELD(colMax);
+
+ READ_DONE();
+}
+
+static JsonTableSiblingNode *
+_readJsonTableSiblingNode(void)
+{
+ READ_LOCALS(JsonTableSiblingNode);
+
+ READ_NODE_FIELD(larg);
+ READ_NODE_FIELD(rarg);
+ READ_BOOL_FIELD(cross);
+
+ READ_DONE();
+}
+
/*
* _readJsonCoercion
*/
@@ -2880,6 +2910,10 @@ parseNodeString(void)
return_value = _readJsonCoercion();
else if (MATCH("JSONITEMCOERCIONS", 17))
return_value = _readJsonItemCoercions();
+ else if (MATCH("JSONTABPNODE", 12))
+ return_value = _readJsonTableParentNode();
+ else if (MATCH("JSONTABSNODE", 12))
+ return_value = _readJsonTableSiblingNode();
else
{
elog(ERROR, "badly formatted node string \"%.32s\"...", token);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 13ab627..259cd5b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -613,9 +613,29 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_object_aggregate_constructor
json_array_aggregate_constructor
json_path_specification
+ json_table
+ json_table_column_definition
+ json_table_ordinality_column_definition
+ json_table_regular_column_definition
+ json_table_formatted_column_definition
+ json_table_nested_columns
+ json_table_plan_clause_opt
+ json_table_specific_plan
+ json_table_plan
+ json_table_plan_simple
+ json_table_plan_parent_child
+ json_table_plan_outer
+ json_table_plan_inner
+ json_table_plan_sibling
+ json_table_plan_union
+ json_table_plan_cross
+ json_table_plan_primary
+ json_table_default_plan
%type <list> json_arguments
json_passing_clause_opt
+ json_table_columns_clause
+ json_table_column_definition_list
json_name_and_value_list
json_value_expr_list
json_array_aggregate_order_by_clause_opt
@@ -624,9 +644,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <str> json_table_path_name
json_as_path_name_clause_opt
+ json_table_column_path_specification_clause_opt
%type <ival> json_encoding
json_encoding_clause_opt
+ json_table_default_plan_choices
+ json_table_default_plan_inner_outer
+ json_table_default_plan_union_cross
json_wrapper_clause_opt
json_wrapper_behavior
json_conditional_or_unconditional_opt
@@ -640,6 +664,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_behavior_true
json_behavior_false
json_behavior_unknown
+ json_behavior_empty
json_behavior_empty_array
json_behavior_empty_object
json_behavior_default
@@ -647,6 +672,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_query_behavior
json_exists_error_behavior
json_exists_error_clause_opt
+ json_table_error_behavior
+ json_table_error_clause_opt
%type <on_behavior> json_value_on_behavior_clause_opt
json_query_on_behavior_clause_opt
@@ -718,7 +745,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG
- JSON_QUERY JSON_VALUE
+ JSON_QUERY JSON_TABLE JSON_VALUE
KEY KEYS KEEP
@@ -728,7 +755,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
MAPPING MATCH MATERIALIZED MAXVALUE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE
- NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NONE
+ NAME_P NAMES NATIONAL NATURAL NCHAR NESTED NEW NEXT NO NONE
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
NULLS_P NUMERIC
@@ -736,7 +763,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 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
@@ -827,7 +854,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
*/
%nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */
%nonassoc ERROR_P EMPTY_P DEFAULT ABSENT /* JSON error/empty behavior */
-%nonassoc FALSE_P KEEP OMIT PASSING TRUE_P UNKNOWN
+%nonassoc COLUMNS FALSE_P KEEP OMIT PASSING TRUE_P UNKNOWN
%nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
%left Op OPERATOR /* multi-character ops and user-defined operators */
%left '+' '-'
@@ -852,6 +879,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
/* kluge to keep xml_whitespace_option from causing shift/reduce conflicts */
%right PRESERVE STRIP_P
+%nonassoc json_table_column
+%nonassoc NESTED
+%left PATH
+
%nonassoc empty_json_unique
%left WITHOUT WITH_LA_UNIQUE
@@ -11989,6 +12020,19 @@ table_ref: relation_expr opt_alias_clause
$2->alias = $4;
$$ = (Node *) $2;
}
+ | json_table opt_alias_clause
+ {
+ JsonTable *jt = castNode(JsonTable, $1);
+ jt->alias = $2;
+ $$ = (Node *) jt;
+ }
+ | LATERAL_P json_table opt_alias_clause
+ {
+ JsonTable *jt = castNode(JsonTable, $2);
+ jt->alias = $3;
+ jt->lateral = true;
+ $$ = (Node *) jt;
+ }
;
@@ -12492,6 +12536,8 @@ xmltable_column_option_el:
{ $$ = makeDefElem("is_not_null", (Node *) makeInteger(true), @1); }
| NULL_P
{ $$ = makeDefElem("is_not_null", (Node *) makeInteger(false), @1); }
+ | PATH b_expr
+ { $$ = makeDefElem("path", $2, @1); }
;
xml_namespace_list:
@@ -14907,6 +14953,10 @@ json_behavior_unknown:
UNKNOWN { $$ = makeJsonBehavior(JSON_BEHAVIOR_UNKNOWN, NULL); }
;
+json_behavior_empty:
+ EMPTY_P { $$ = makeJsonBehavior(JSON_BEHAVIOR_EMPTY_OBJECT, NULL); }
+ ;
+
json_behavior_empty_array:
EMPTY_P ARRAY { $$ = makeJsonBehavior(JSON_BEHAVIOR_EMPTY_ARRAY, NULL); }
;
@@ -15018,6 +15068,235 @@ json_query_on_behavior_clause_opt:
{ $$.on_empty = NULL; $$.on_error = NULL; }
;
+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->plan = (JsonTablePlan *) $5;
+ n->on_error = $6;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+json_table_columns_clause:
+ COLUMNS '(' json_table_column_definition_list ')' { $$ = $3; }
+ ;
+
+json_table_column_definition_list:
+ json_table_column_definition
+ { $$ = list_make1($1); }
+ | json_table_column_definition_list ',' json_table_column_definition
+ { $$ = lappend($1, $3); }
+ ;
+
+json_table_column_definition:
+ json_table_ordinality_column_definition %prec json_table_column
+ | json_table_regular_column_definition %prec json_table_column
+ | json_table_formatted_column_definition %prec json_table_column
+ | json_table_nested_columns
+ ;
+
+json_table_ordinality_column_definition:
+ ColId FOR ORDINALITY
+ {
+ JsonTableColumn *n = makeNode(JsonTableColumn);
+ n->coltype = JTC_FOR_ORDINALITY;
+ n->name = $1;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+json_table_regular_column_definition:
+ ColId Typename
+ json_table_column_path_specification_clause_opt
+ json_value_on_behavior_clause_opt
+ {
+ JsonTableColumn *n = makeNode(JsonTableColumn);
+ n->coltype = JTC_REGULAR;
+ n->name = $1;
+ n->typename = $2;
+ n->format.type = JS_FORMAT_DEFAULT;
+ n->format.encoding = JS_ENC_DEFAULT;
+ n->wrapper = JSW_NONE;
+ n->omit_quotes = false;
+ n->pathspec = $3;
+ n->on_empty = $4.on_empty;
+ n->on_error = $4.on_error;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+json_table_error_behavior:
+ json_behavior_error
+ | json_behavior_empty
+ ;
+
+json_table_error_clause_opt:
+ json_table_error_behavior ON ERROR_P { $$ = $1; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
+json_table_column_path_specification_clause_opt:
+ PATH Sconst { $$ = $2; }
+ | /* EMPTY */ %prec json_table_column { $$ = NULL; }
+ ;
+
+json_table_formatted_column_definition:
+ ColId Typename FORMAT json_representation
+ json_table_column_path_specification_clause_opt
+ json_wrapper_clause_opt
+ json_quotes_clause_opt
+ json_query_on_behavior_clause_opt
+ {
+ JsonTableColumn *n = makeNode(JsonTableColumn);
+ n->coltype = JTC_FORMATTED;
+ n->name = $1;
+ n->typename = $2;
+ n->format = $4;
+ n->pathspec = $5;
+ n->wrapper = $6;
+ if (n->wrapper != JSW_NONE && $7 != JS_QUOTES_UNSPEC)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("SQL/JSON QUOTES behavior shall not be specified when WITH WRAPPER is used"),
+ parser_errposition(@7)));
+ n->omit_quotes = $7 == JS_QUOTES_OMIT;
+ n->on_empty = $8.on_empty;
+ n->on_error = $8.on_error;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+json_table_nested_columns:
+ NESTED path_opt Sconst
+ json_as_path_name_clause_opt
+ json_table_columns_clause
+ {
+ JsonTableColumn *n = makeNode(JsonTableColumn);
+ n->coltype = JTC_NESTED;
+ n->pathspec = $3;
+ n->pathname = $4;
+ n->columns = $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+path_opt:
+ PATH { }
+ | /* EMPTY */ { }
+ ;
+
+json_table_plan_clause_opt:
+ json_table_specific_plan { $$ = $1; }
+ | json_table_default_plan { $$ = $1; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
+json_table_specific_plan:
+ PLAN '(' json_table_plan ')' { $$ = $3; }
+ ;
+
+json_table_plan:
+ json_table_plan_simple
+ | json_table_plan_parent_child
+ | json_table_plan_sibling
+ ;
+
+json_table_plan_simple:
+ json_table_path_name
+ {
+ JsonTablePlan *n = makeNode(JsonTablePlan);
+ n->plan_type = JSTP_SIMPLE;
+ n->pathname = $1;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+json_table_plan_parent_child:
+ json_table_plan_outer
+ | json_table_plan_inner
+ ;
+
+json_table_plan_outer:
+ json_table_plan_simple OUTER_P json_table_plan_primary
+ { $$ = makeJsonTableJoinedPlan(JSTP_OUTER, $1, $3, @1); }
+ ;
+
+json_table_plan_inner:
+ json_table_plan_simple INNER_P json_table_plan_primary
+ { $$ = makeJsonTableJoinedPlan(JSTP_INNER, $1, $3, @1); }
+ ;
+
+json_table_plan_sibling:
+ json_table_plan_union
+ | json_table_plan_cross
+ ;
+
+json_table_plan_union:
+ json_table_plan_primary UNION json_table_plan_primary
+ { $$ = makeJsonTableJoinedPlan(JSTP_UNION, $1, $3, @1); }
+ | json_table_plan_union UNION json_table_plan_primary
+ { $$ = makeJsonTableJoinedPlan(JSTP_UNION, $1, $3, @1); }
+ ;
+
+json_table_plan_cross:
+ json_table_plan_primary CROSS json_table_plan_primary
+ { $$ = makeJsonTableJoinedPlan(JSTP_CROSS, $1, $3, @1); }
+ | json_table_plan_cross CROSS json_table_plan_primary
+ { $$ = makeJsonTableJoinedPlan(JSTP_CROSS, $1, $3, @1); }
+ ;
+
+json_table_plan_primary:
+ json_table_plan_simple { $$ = $1; }
+ | '(' json_table_plan ')'
+ {
+ castNode(JsonTablePlan, $2)->location = @1;
+ $$ = $2;
+ }
+ ;
+
+json_table_default_plan:
+ PLAN DEFAULT '(' json_table_default_plan_choices ')'
+ {
+ JsonTablePlan *n = makeNode(JsonTablePlan);
+ n->plan_type = JSTP_DEFAULT;
+ n->join_type = $4;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+json_table_default_plan_choices:
+ json_table_default_plan_inner_outer { $$ = $1 | JSTP_UNION; }
+ | json_table_default_plan_inner_outer ','
+ json_table_default_plan_union_cross { $$ = $1 | $3; }
+ | json_table_default_plan_union_cross { $$ = $1 | JSTP_OUTER; }
+ | json_table_default_plan_union_cross ','
+ json_table_default_plan_inner_outer { $$ = $1 | $3; }
+ ;
+
+json_table_default_plan_inner_outer:
+ INNER_P { $$ = JSTP_INNER; }
+ | OUTER_P { $$ = JSTP_OUTER; }
+ ;
+
+json_table_default_plan_union_cross:
+ UNION { $$ = JSTP_UNION; }
+ | CROSS { $$ = JSTP_CROSS; }
+ ;
json_output_clause_opt:
RETURNING Typename json_format_clause_opt
@@ -15787,6 +16066,7 @@ unreserved_keyword:
| MOVE
| NAME_P
| NAMES
+ | NESTED
| NEW
| NEXT
| NO
@@ -15815,6 +16095,8 @@ unreserved_keyword:
| PARTITION
| PASSING
| PASSWORD
+ | PATH
+ | PLAN
| PLANS
| POLICY
| PRECEDING
@@ -15971,6 +16253,7 @@ col_name_keyword:
| JSON_OBJECT
| JSON_OBJECTAGG
| JSON_QUERY
+ | JSON_TABLE
| JSON_VALUE
| LEAST
| NATIONAL
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index e147880..791414c 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -49,10 +49,20 @@
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/catcache.h"
+#include "utils/json.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
#include "utils/rel.h"
+/* Context for JSON_TABLE transformation */
+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;
/* Convenience macro for the most common makeNamespaceItem() case */
#define makeDefaultNSItem(rte) makeNamespaceItem(rte, true, true, false, true)
@@ -103,6 +113,10 @@ static WindowClause *findWindowClause(List *wclist, const char *name);
static Node *transformFrameOffset(ParseState *pstate, int frameOptions,
Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc,
Node *clause);
+static JsonTableParentNode * transformJsonTableColumns(ParseState *pstate,
+ JsonTableContext *cxt, JsonTablePlan *plan,
+ List *columns, char *pathSpec, char **pathName,
+ int location);
/*
@@ -760,6 +774,8 @@ transformRangeTableFunc(ParseState *pstate, RangeTableFunc *rtf)
Assert(!pstate->p_lateral_active);
pstate->p_lateral_active = true;
+ tf->functype = TFT_XMLTABLE;
+
/* Transform and apply typecast to the row-generating expression ... */
Assert(rtf->rowexpr != NULL);
tf->rowexpr = coerce_to_specific_type(pstate,
@@ -1049,6 +1065,18 @@ transformRangeTableSample(ParseState *pstate, RangeTableSample *rts)
return tablesample;
}
+static Node *
+makeStringConst(char *str, int location)
+{
+ A_Const *n = makeNode(A_Const);
+
+ n->val.type = T_String;
+ n->val.val.str = str;
+ n->location = location;
+
+ return (Node *)n;
+}
+
/*
* getRTEForSpecialRelationTypes
*
@@ -1080,6 +1108,642 @@ getRTEForSpecialRelationTypes(ParseState *pstate, RangeVar *rv)
}
/*
+ * Transform JSON_TABLE column
+ * - regular column into JSON_VALUE()
+ * - formatted column into JSON_QUERY()
+ */
+static Node *
+transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
+ List *passingArgs, bool errorOnError)
+{
+ JsonFuncExpr *jfexpr = makeNode(JsonFuncExpr);
+ JsonValueExpr *jvexpr = makeNode(JsonValueExpr);
+ JsonCommon *common = makeNode(JsonCommon);
+ JsonOutput *output = makeNode(JsonOutput);
+ JsonPathSpec pathspec;
+
+ jfexpr->op = jtc->coltype == JTC_REGULAR ? IS_JSON_VALUE : IS_JSON_QUERY;
+ jfexpr->common = common;
+ jfexpr->output = output;
+ jfexpr->on_empty = jtc->on_empty;
+ jfexpr->on_error = jtc->on_error;
+ if (!jfexpr->on_error && errorOnError)
+ jfexpr->on_error = makeJsonBehavior(JSON_BEHAVIOR_ERROR, NULL);
+ jfexpr->omit_quotes = jtc->omit_quotes;
+ jfexpr->wrapper = jtc->wrapper;
+ jfexpr->location = jtc->location;
+
+ output->typename = jtc->typename;
+ output->returning.format = jtc->format;
+
+ common->pathname = NULL;
+ common->expr = jvexpr;
+ common->passing = passingArgs;
+
+ if (jtc->pathspec)
+ pathspec = jtc->pathspec;
+ else
+ {
+ /* Construct default path as '$."column_name"' */
+ StringInfoData path;
+
+ initStringInfo(&path);
+
+ appendStringInfoString(&path, "$.");
+ escape_json(&path, jtc->name);
+
+ pathspec = path.data;
+ }
+
+ common->pathspec = makeStringConst(pathspec, -1);
+
+ jvexpr->expr = (Expr *) contextItemExpr;
+ jvexpr->format.type = JS_FORMAT_DEFAULT;
+ jvexpr->format.encoding = JS_ENC_DEFAULT;
+
+ return (Node *) jfexpr;
+}
+
+static bool
+isJsonTablePathNameDuplicate(JsonTableContext *cxt, const char *pathname)
+{
+ ListCell *lc;
+
+ foreach(lc, cxt->pathNames)
+ {
+ if (!strcmp(pathname, (const char *) lfirst(lc)))
+ return true;
+ }
+
+ return false;
+}
+
+/* Recursively register column name in the path name list. */
+static void
+registerJsonTableColumn(JsonTableContext *cxt, char *colname)
+{
+ if (isJsonTablePathNameDuplicate(cxt, colname))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_ALIAS),
+ errmsg("duplicate JSON_TABLE column name: %s", colname),
+ errhint("JSON_TABLE path names and column names shall be "
+ "distinct from one another")));
+
+ cxt->pathNames = lappend(cxt->pathNames, colname);
+}
+
+/* Recursively register all nested column names in the path name list. */
+static void
+registerAllJsonTableColumns(JsonTableContext *cxt, List *columns)
+{
+ ListCell *lc;
+
+ foreach(lc, columns)
+ {
+ JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
+
+ if (jtc->coltype == JTC_NESTED)
+ {
+ if (jtc->pathname)
+ registerJsonTableColumn(cxt, jtc->pathname);
+
+ registerAllJsonTableColumns(cxt, jtc->columns);
+ }
+ else
+ {
+ registerJsonTableColumn(cxt, jtc->name);
+ }
+ }
+}
+
+/* Generate a new unique JSON_TABLE path name. */
+static char *
+generateJsonTablePathName(JsonTableContext *cxt)
+{
+ char namebuf[32];
+ char *name = namebuf;
+
+ do
+ {
+ snprintf(namebuf, sizeof(namebuf), "json_table_path_%d",
+ ++cxt->pathNameId);
+ } while (isJsonTablePathNameDuplicate(cxt, name));
+
+ name = pstrdup(name);
+ cxt->pathNames = lappend(cxt->pathNames, name);
+
+ return name;
+}
+
+/* Collect sibling path names from plan to the specified list. */
+static void
+collectSiblingPathsInJsonTablePlan(JsonTablePlan *plan, List **paths)
+{
+ if (plan->plan_type == JSTP_SIMPLE)
+ *paths = lappend(*paths, plan->pathname);
+ else if (plan->plan_type == JSTP_JOINED)
+ {
+ if (plan->join_type == JSTP_INNER ||
+ plan->join_type == JSTP_OUTER)
+ {
+ Assert(plan->plan1->plan_type == JSTP_SIMPLE);
+ *paths = lappend(*paths, plan->plan1->pathname);
+ }
+ else if (plan->join_type == JSTP_CROSS ||
+ plan->join_type == JSTP_UNION)
+ {
+ collectSiblingPathsInJsonTablePlan(plan->plan1, paths);
+ collectSiblingPathsInJsonTablePlan(plan->plan2, paths);
+ }
+ else
+ elog(ERROR, "invalid JSON_TABLE join type %d",
+ plan->join_type);
+ }
+}
+
+/*
+ * Validate child JSON_TABLE plan by checking that:
+ * - all nested columns have path names specified
+ * - all nested columns have corresponding node in the sibling plan
+ * - plan does not contain duplicate or extra nodes
+ */
+static void
+validateJsonTableChildPlan(ParseState *pstate, JsonTablePlan *plan,
+ List *columns)
+{
+ ListCell *lc1;
+ List *siblings = NIL;
+ int nchilds = 0;
+
+ if (plan)
+ collectSiblingPathsInJsonTablePlan(plan, &siblings);
+
+ foreach(lc1, columns)
+ {
+ JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1));
+
+ if (jtc->coltype == JTC_NESTED)
+ {
+ ListCell *lc2;
+ bool found = false;
+
+ if (!jtc->pathname)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("nested JSON_TABLE columns 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 = !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)));
+
+ nchilds++;
+ }
+ }
+
+ if (list_length(siblings) > nchilds)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid JSON_TABLE plan"),
+ errdetail("plan node contains some extra or "
+ "duplicate sibling nodes"),
+ parser_errposition(pstate, plan ? plan->location : -1)));
+}
+
+static JsonTableColumn *
+findNestedJsonTableColumn(List *columns, const char *pathname)
+{
+ ListCell *lc;
+
+ foreach(lc, columns)
+ {
+ JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
+
+ if (jtc->coltype == JTC_NESTED &&
+ jtc->pathname &&
+ !strcmp(jtc->pathname, pathname))
+ return jtc;
+ }
+
+ return NULL;
+}
+
+static Node *
+transformNestedJsonTableColumn(ParseState *pstate, JsonTableContext *cxt,
+ JsonTableColumn *jtc, JsonTablePlan *plan)
+{
+ JsonTableParentNode *node;
+ char *pathname = jtc->pathname;
+
+ node = transformJsonTableColumns(pstate, cxt, plan,
+ jtc->columns, jtc->pathspec,
+ &pathname, jtc->location);
+ node->name = pstrdup(pathname);
+
+ return (Node *) node;
+}
+
+static Node *
+makeJsonTableSiblingJoin(bool cross, Node *lnode, Node *rnode)
+{
+ JsonTableSiblingNode *join = makeNode(JsonTableSiblingNode);
+
+ join->larg = lnode;
+ join->rarg = rnode;
+ join->cross = cross;
+
+ return (Node *) join;
+}
+
+/*
+ * Recursively transform child JSON_TABLE plan.
+ *
+ * Default plan is transformed into a cross/union join of its nested columns.
+ * Simple and outer/inner plans are transformed into a JsonTableParentNode by
+ * finding and transforming corresponding nested column.
+ * Sibling plans are recursively transformed into a JsonTableSiblingNode.
+ */
+static Node *
+transformJsonTableChildPlan(ParseState *pstate, JsonTableContext *cxt,
+ JsonTablePlan *plan, List *columns)
+{
+ JsonTableColumn *jtc = NULL;
+
+ if (!plan || plan->plan_type == JSTP_DEFAULT)
+ {
+ /* unspecified or default plan */
+ Node *res = NULL;
+ ListCell *lc;
+ bool cross = plan && (plan->join_type & JSTP_CROSS);
+
+ /* transform all nested columns into cross/union join */
+ foreach(lc, columns)
+ {
+ JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
+ Node *node;
+
+ if (jtc->coltype != JTC_NESTED)
+ continue;
+
+ node = transformNestedJsonTableColumn(pstate, cxt, jtc, plan);
+
+ /* join transformed node with previous sibling nodes */
+ res = res ? makeJsonTableSiblingJoin(cross, res, node) : node;
+ }
+
+ return res;
+ }
+ else if (plan->plan_type == JSTP_SIMPLE)
+ {
+ jtc = findNestedJsonTableColumn(columns, plan->pathname);
+ }
+ else if (plan->plan_type == JSTP_JOINED)
+ {
+ if (plan->join_type == JSTP_INNER ||
+ plan->join_type == JSTP_OUTER)
+ {
+ Assert(plan->plan1->plan_type == JSTP_SIMPLE);
+ jtc = findNestedJsonTableColumn(columns, plan->plan1->pathname);
+ }
+ else
+ {
+ Node *node1 =
+ transformJsonTableChildPlan(pstate, cxt, plan->plan1, columns);
+ Node *node2 =
+ transformJsonTableChildPlan(pstate, cxt, plan->plan2, columns);
+
+ return makeJsonTableSiblingJoin(plan->join_type == JSTP_CROSS,
+ node1, node2);
+ }
+ }
+ else
+ elog(ERROR, "invalid JSON_TABLE plan type %d", plan->plan_type);
+
+ if (!jtc)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid JSON_TABLE plan"),
+ errdetail("path name was %s not found in nested columns list",
+ plan->pathname),
+ parser_errposition(pstate, plan->location)));
+
+ return transformNestedJsonTableColumn(pstate, cxt, jtc, plan);
+}
+
+/* Append transformed non-nested JSON_TABLE columns to the TableFunc node */
+static void
+appendJsonTableColumns(ParseState *pstate, JsonTableContext *cxt, List *columns)
+{
+ JsonTable *jt = cxt->table;
+ TableFunc *tf = cxt->tablefunc;
+ bool errorOnError = jt->on_error &&
+ jt->on_error->btype == JSON_BEHAVIOR_ERROR;
+ ListCell *col;
+
+ foreach(col, columns)
+ {
+ JsonTableColumn *rawc = castNode(JsonTableColumn, lfirst(col));
+ Oid typid;
+ int32 typmod;
+ Node *colexpr;
+
+ if (rawc->name)
+ {
+ /* make sure column names are unique */
+ ListCell *colname;
+
+ foreach(colname, tf->colnames)
+ if (!strcmp((const char *) colname, rawc->name))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column name \"%s\" is not unique",
+ rawc->name),
+ parser_errposition(pstate, rawc->location)));
+
+ tf->colnames = lappend(tf->colnames,
+ makeString(pstrdup(rawc->name)));
+ }
+
+ /*
+ * Determine the type and typmod for the new column. FOR
+ * ORDINALITY columns are INTEGER by standard; the others are
+ * user-specified.
+ */
+ switch (rawc->coltype)
+ {
+ case JTC_FOR_ORDINALITY:
+ colexpr = NULL;
+ typid = INT4OID;
+ typmod = -1;
+ break;
+
+ case JTC_REGULAR:
+ case JTC_FORMATTED:
+ {
+ Node *je;
+ CaseTestExpr *param = makeNode(CaseTestExpr);
+
+ param->collation = InvalidOid;
+ param->typeId = cxt->contextItemTypid;
+ param->typeMod = -1;
+
+ je = transformJsonTableColumn(rawc, (Node *) param,
+ NIL, errorOnError);
+
+ colexpr = transformExpr(pstate, je, EXPR_KIND_FROM_FUNCTION);
+ assign_expr_collations(pstate, colexpr);
+
+ typid = exprType(colexpr);
+ typmod = exprTypmod(colexpr);
+ break;
+ }
+
+ case JTC_NESTED:
+ continue;
+
+ default:
+ elog(ERROR, "unknown JSON_TABLE column type: %d", rawc->coltype);
+ break;
+ }
+
+ tf->coltypes = lappend_oid(tf->coltypes, typid);
+ tf->coltypmods = lappend_int(tf->coltypmods, typmod);
+ tf->colcollations = lappend_oid(tf->colcollations,
+ type_is_collatable(typid)
+ ? DEFAULT_COLLATION_OID
+ : InvalidOid);
+ tf->colvalexprs = lappend(tf->colvalexprs, colexpr);
+ }
+}
+
+/*
+ * Create transformed JSON_TABLE parent plan node by appending all non-nested
+ * columns to the TableFunc node and remembering their indices in the
+ * colvalexprs list.
+ */
+static JsonTableParentNode *
+makeParentJsonTableNode(ParseState *pstate, JsonTableContext *cxt,
+ char *pathSpec, List *columns)
+{
+ JsonTableParentNode *node = makeNode(JsonTableParentNode);
+
+ node->path = makeConst(JSONPATHOID, -1, InvalidOid, -1,
+ DirectFunctionCall1(jsonpath_in,
+ CStringGetDatum(pathSpec)),
+ false, false);
+
+ /* save start of column range */
+ node->colMin = list_length(cxt->tablefunc->colvalexprs);
+
+ appendJsonTableColumns(pstate, cxt, columns);
+
+ /* save end of column range */
+ node->colMax = list_length(cxt->tablefunc->colvalexprs) - 1;
+
+ node->errorOnError =
+ cxt->table->on_error &&
+ cxt->table->on_error->btype == JSON_BEHAVIOR_ERROR;
+
+ return node;
+}
+
+static JsonTableParentNode *
+transformJsonTableColumns(ParseState *pstate, JsonTableContext *cxt,
+ JsonTablePlan *plan, List *columns,
+ char *pathSpec, char **pathName, int location)
+{
+ JsonTableParentNode *node;
+ JsonTablePlan *childPlan;
+ bool defaultPlan = !plan || plan->plan_type == JSTP_DEFAULT;
+
+ if (!*pathName)
+ {
+ if (cxt->table->plan)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid JSON_TABLE expression"),
+ errdetail("JSON_TABLE columns shall contain "
+ "explicit AS pathname specification if "
+ "explicit PLAN clause is used"),
+ parser_errposition(pstate, location)));
+
+ *pathName = generateJsonTablePathName(cxt);
+ }
+
+ if (defaultPlan)
+ childPlan = plan;
+ else
+ {
+ /* validate parent and child plans */
+ JsonTablePlan *parentPlan;
+
+ if (plan->plan_type == JSTP_JOINED)
+ {
+ if (plan->join_type != JSTP_INNER &&
+ plan->join_type != JSTP_OUTER)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid JSON_TABLE plan"),
+ errdetail("expected INNER or OUTER JSON_TABLE plan node"),
+ parser_errposition(pstate, plan->location)));
+
+ parentPlan = plan->plan1;
+ childPlan = plan->plan2;
+
+ Assert(parentPlan->plan_type != JSTP_JOINED);
+ Assert(parentPlan->pathname);
+ }
+ else
+ {
+ parentPlan = plan;
+ childPlan = NULL;
+ }
+
+ if (strcmp(parentPlan->pathname, *pathName))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid JSON_TABLE plan"),
+ errdetail("path name mismatch: expected %s but %s is given",
+ *pathName, parentPlan->pathname),
+ parser_errposition(pstate, plan->location)));
+
+
+ validateJsonTableChildPlan(pstate, childPlan, columns);
+ }
+
+ /* transform only non-nested columns */
+ node = makeParentJsonTableNode(pstate, cxt, pathSpec, columns);
+ node->name = pstrdup(*pathName);
+
+ if (childPlan || defaultPlan)
+ {
+ /* transform recursively nested columns */
+ node->child = transformJsonTableChildPlan(pstate, cxt, childPlan,
+ columns);
+ if (node->child)
+ node->outerJoin = !plan || (plan->join_type & JSTP_OUTER);
+ /* else: default plan case, no children found */
+ }
+
+ return node;
+}
+
+/*
+ * transformJsonTable -
+ * Transform a raw JsonTable into TableFunc.
+ *
+ * Transform the document-generating expression, the row-generating expression,
+ * the column-generating expressions, and the default value expressions.
+ */
+static RangeTblEntry *
+transformJsonTable(ParseState *pstate, JsonTable *jt)
+{
+ JsonTableContext cxt;
+ TableFunc *tf = makeNode(TableFunc);
+ JsonFuncExpr *jfe = makeNode(JsonFuncExpr);
+ JsonCommon *jscommon;
+ JsonTablePlan *plan = jt->plan;
+ char *rootPathName = jt->common->pathname;
+ char *rootPath;
+ bool is_lateral;
+
+ cxt.table = jt;
+ cxt.tablefunc = tf;
+ cxt.pathNames = NIL;
+ cxt.pathNameId = 0;
+
+ if (rootPathName)
+ registerJsonTableColumn(&cxt, rootPathName);
+
+ registerAllJsonTableColumns(&cxt, jt->columns);
+
+#if 0 /* XXX it' unclear from the standard whether root path name is mandatory or not */
+ if (plan && plan->plan_type != JSTP_DEFAULT && !rootPathName)
+ {
+ /* Assign root path name and create corresponding plan node */
+ JsonTablePlan *rootNode = makeNode(JsonTablePlan);
+ JsonTablePlan *rootPlan = (JsonTablePlan *)
+ makeJsonTableJoinedPlan(JSTP_OUTER, (Node *) rootNode,
+ (Node *) plan, jt->location);
+
+ rootPathName = generateJsonTablePathName(&cxt);
+
+ rootNode->plan_type = JSTP_SIMPLE;
+ rootNode->pathname = rootPathName;
+
+ plan = rootPlan;
+ }
+#endif
+
+ jscommon = copyObject(jt->common);
+ jscommon->pathspec = makeStringConst(pstrdup("$"), -1);
+
+ jfe->op = IS_JSON_TABLE;
+ jfe->common = jscommon;
+ jfe->on_error = jt->on_error;
+ jfe->location = jt->common->location;
+
+ /*
+ * We make lateral_only names of this level visible, whether or not the
+ * RangeTableFunc is explicitly marked LATERAL. This is needed for SQL
+ * spec compliance and seems useful on convenience grounds for all
+ * functions in FROM.
+ *
+ * (LATERAL can't nest within a single pstate level, so we don't need
+ * save/restore logic here.)
+ */
+ Assert(!pstate->p_lateral_active);
+ pstate->p_lateral_active = true;
+
+ tf->functype = TFT_JSON_TABLE;
+ tf->docexpr = transformExpr(pstate, (Node *) jfe, EXPR_KIND_FROM_FUNCTION);
+
+ cxt.contextItemTypid = exprType(tf->docexpr);
+
+ if (!IsA(jt->common->pathspec, A_Const) ||
+ castNode(A_Const, jt->common->pathspec)->val.type != T_String)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only string constants supported in JSON_TABLE path specification"),
+ parser_errposition(pstate,
+ exprLocation(jt->common->pathspec))));
+
+ rootPath = castNode(A_Const, jt->common->pathspec)->val.val.str;
+
+ tf->plan = (Node *) transformJsonTableColumns(pstate, &cxt, plan,
+ jt->columns,
+ rootPath, &rootPathName,
+ jt->common->location);
+
+ tf->ordinalitycol = -1; /* undefine ordinality column number */
+ tf->location = jt->location;
+
+ pstate->p_lateral_active = false;
+
+ /*
+ * Mark the RTE as LATERAL if the user said LATERAL explicitly, or if
+ * there are any lateral cross-references in it.
+ */
+ is_lateral = jt->lateral || contain_vars_of_level((Node *) tf, 0);
+
+ return addRangeTableEntryForTableFunc(pstate,
+ tf, jt->alias, is_lateral, true);
+}
+
+/*
* transformFromClauseItem -
* Transform a FROM-clause item, adding any required entries to the
* range table list being built in the ParseState, and return the
@@ -1211,6 +1875,31 @@ transformFromClauseItem(ParseState *pstate, Node *n,
rte->tablesample = transformRangeTableSample(pstate, rts);
return (Node *) rtr;
}
+ else if (IsA(n, JsonTable))
+ {
+ /* JsonTable is transformed into RangeSubselect */
+ /*
+ JsonTable *jt = castNode(JsonTable, n);
+ RangeSubselect *subselect = transformJsonTable(pstate, jt);
+
+ return transformFromClauseItem(pstate, (Node *) subselect,
+ top_rte, top_rti, namespace);
+ */
+ RangeTblRef *rtr;
+ RangeTblEntry *rte;
+ int rtindex;
+
+ rte = transformJsonTable(pstate, (JsonTable *) n);
+ /* assume new rte is at end */
+ rtindex = list_length(pstate->p_rtable);
+ Assert(rte == rt_fetch(rtindex, pstate->p_rtable));
+ *top_rte = rte;
+ *top_rti = rtindex;
+ *namespace = list_make1(makeDefaultNSItem(rte));
+ rtr = makeNode(RangeTblRef);
+ rtr->rtindex = rtindex;
+ return (Node *) rtr;
+ }
else if (IsA(n, JoinExpr))
{
/* A newfangled join expression */
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index ab24b35..cb6407a 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4417,7 +4417,7 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func)
Node *pathspec;
JsonFormatType format;
- if (func->common->pathname)
+ if (func->common->pathname && func->op != IS_JSON_TABLE)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("JSON_TABLE path name is not allowed here"),
@@ -4463,14 +4463,13 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func)
transformJsonPassingArgs(pstate, format, func->common->passing,
&jsexpr->passing);
- if (func->op != IS_JSON_EXISTS)
+ if (func->op != IS_JSON_EXISTS && func->op != IS_JSON_TABLE)
jsexpr->on_empty = transformJsonBehavior(pstate, func->on_empty,
JSON_BEHAVIOR_NULL);
jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
- func->op == IS_JSON_EXISTS ?
- JSON_BEHAVIOR_FALSE :
- JSON_BEHAVIOR_NULL);
+ func->op == IS_JSON_EXISTS ? JSON_BEHAVIOR_FALSE :
+ func->op == IS_JSON_TABLE ? JSON_BEHAVIOR_EMPTY : JSON_BEHAVIOR_NULL);
return jsexpr;
}
@@ -4724,6 +4723,21 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
jsexpr->returning.typmod = -1;
break;
+
+ case IS_JSON_TABLE:
+ jsexpr->returning.format.type = JS_FORMAT_DEFAULT;
+ jsexpr->returning.format.encoding = JS_ENC_DEFAULT;
+ jsexpr->returning.format.location = -1;
+ jsexpr->returning.typid = exprType(contextItemExpr);
+ jsexpr->returning.typmod = -1;
+
+ if (jsexpr->returning.typid != JSONBOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("JSON_TABLE() is not yet implemented for json type"),
+ parser_errposition(pstate, func->location)));
+
+ break;
}
return (Node *) jsexpr;
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index bf5df26..47b9831 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1662,7 +1662,8 @@ addRangeTableEntryForTableFunc(ParseState *pstate,
bool inFromCl)
{
RangeTblEntry *rte = makeNode(RangeTblEntry);
- char *refname = alias ? alias->aliasname : pstrdup("xmltable");
+ char *refname = alias ? alias->aliasname :
+ pstrdup(tf->functype == TFT_XMLTABLE ? "xmltable" : "json_table");
Alias *eref;
int numaliases;
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 7a43515..b40de13 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1944,6 +1944,9 @@ FigureColnameInternal(Node *node, char **name)
case IS_JSON_EXISTS:
*name = "json_exists";
return 2;
+ case IS_JSON_TABLE:
+ *name = "json_table";
+ return 2;
}
break;
default:
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index a252383..bf3275d 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -15,12 +15,16 @@
#include "miscadmin.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_type.h"
+#include "executor/execExpr.h"
#include "lib/stringinfo.h"
+#include "nodes/nodeFuncs.h"
#include "regex/regex.h"
#include "utils/builtins.h"
#include "utils/formatting.h"
#include "utils/json.h"
#include "utils/jsonpath.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
#include "utils/varlena.h"
#ifdef JSONPATH_JSON_C
@@ -55,6 +59,58 @@ typedef struct JsonValueListIterator
#define JsonValueListIteratorEnd ((ListCell *) -1)
+typedef struct JsonTableScanState JsonTableScanState;
+typedef struct JsonTableJoinState JsonTableJoinState;
+
+struct JsonTableScanState
+{
+ JsonTableScanState *parent;
+ JsonTableJoinState *nested;
+ MemoryContext mcxt;
+ JsonPath *path;
+ List *args;
+ JsonValueList found;
+ JsonValueListIterator iter;
+ Datum current;
+ int ordinal;
+ bool currentIsNull;
+ bool outerJoin;
+ bool errorOnError;
+ bool advanceNested;
+ bool reset;
+};
+
+struct JsonTableJoinState
+{
+ union
+ {
+ struct
+ {
+ JsonTableJoinState *left;
+ JsonTableJoinState *right;
+ bool cross;
+ bool advanceRight;
+ } join;
+ JsonTableScanState scan;
+ } u;
+ bool is_join;
+};
+
+/* random number to identify JsonTableContext */
+#define JSON_TABLE_CONTEXT_MAGIC 418352867
+
+typedef struct JsonTableContext
+{
+ int magic;
+ struct
+ {
+ ExprState *expr;
+ JsonTableScanState *scan;
+ } *colexprs;
+ JsonTableScanState root;
+ bool empty;
+} JsonTableContext;
+
static inline JsonPathExecResult recursiveExecute(JsonPathExecContext *cxt,
JsonPathItem *jsp, JsonbValue *jb,
JsonValueList *found);
@@ -64,6 +120,11 @@ static inline JsonPathExecResult recursiveExecuteUnwrap(JsonPathExecContext *cxt
static inline JsonbValue *wrapItemsInArray(const JsonValueList *items);
+static JsonTableJoinState *JsonTableInitPlanState(JsonTableContext *cxt,
+ Node *plan, JsonTableScanState *parent);
+
+static bool JsonTableNextRow(JsonTableScanState *scan);
+
static inline void
JsonValueListAppend(JsonValueList *jvl, JsonbValue *jbv)
@@ -2813,3 +2874,429 @@ JsonbPathValue(Datum jb, JsonPath *jp, bool *empty, List *vars)
return res;
}
+
+/*
+ * Returns private data from executor state. Ensure validity by check with
+ * MAGIC number.
+ */
+static inline JsonTableContext *
+GetJsonTableContext(TableFuncScanState *state, const char *fname)
+{
+ JsonTableContext *result;
+
+ if (!IsA(state, TableFuncScanState))
+ elog(ERROR, "%s called with invalid TableFuncScanState", fname);
+ result = (JsonTableContext *) state->opaque;
+ if (result->magic != JSON_TABLE_CONTEXT_MAGIC)
+ elog(ERROR, "%s called with invalid TableFuncScanState", fname);
+
+ return result;
+}
+
+/* Recursively initialize JSON_TABLE scan state */
+static void
+JsonTableInitScanState(JsonTableContext *cxt, JsonTableScanState *scan,
+ JsonTableParentNode *node, JsonTableScanState *parent,
+ List *args, MemoryContext mcxt)
+{
+ int i;
+
+ scan->parent = parent;
+ scan->outerJoin = node->outerJoin;
+ scan->errorOnError = node->errorOnError;
+ scan->path = DatumGetJsonPathP(node->path->constvalue);
+ scan->args = args;
+ scan->mcxt = AllocSetContextCreate(mcxt, "JsonTableContext",
+ ALLOCSET_DEFAULT_SIZES);
+ scan->nested = node->child ?
+ JsonTableInitPlanState(cxt, node->child, scan) : NULL;
+ scan->current = PointerGetDatum(NULL);
+ scan->currentIsNull = true;
+
+ for (i = node->colMin; i <= node->colMax; i++)
+ cxt->colexprs[i].scan = scan;
+}
+
+/* Recursively initialize JSON_TABLE scan state */
+static JsonTableJoinState *
+JsonTableInitPlanState(JsonTableContext *cxt, Node *plan,
+ JsonTableScanState *parent)
+{
+ JsonTableJoinState *state = palloc0(sizeof(*state));
+
+ if (IsA(plan, JsonTableSiblingNode))
+ {
+ 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);
+ }
+ else
+ {
+ JsonTableParentNode *node = castNode(JsonTableParentNode, plan);
+
+ state->is_join = false;
+
+ JsonTableInitScanState(cxt, &state->u.scan, node, parent,
+ parent->args, parent->mcxt);
+ }
+
+ return state;
+}
+
+/*
+ * JsonTableInitOpaque
+ * Fill in TableFuncScanState->opaque for JsonTable processor
+ */
+static void
+JsonTableInitOpaque(TableFuncScanState *state, int natts)
+{
+ JsonTableContext *cxt;
+ PlanState *ps = &state->ss.ps;
+ TableFuncScan *tfs = castNode(TableFuncScan, ps->plan);
+ TableFunc *tf = tfs->tablefunc;
+ JsonExpr *ci = castNode(JsonExpr, tf->docexpr);
+ JsonTableParentNode *root = castNode(JsonTableParentNode, tf->plan);
+ List *args = NIL;
+ ListCell *lc;
+ int i;
+
+ cxt = palloc0(sizeof(JsonTableContext));
+ cxt->magic = JSON_TABLE_CONTEXT_MAGIC;
+
+ if (list_length(ci->passing.values) > 0)
+ {
+ ListCell *exprlc;
+ ListCell *namelc;
+
+ forboth(exprlc, ci->passing.values,
+ namelc, ci->passing.names)
+ {
+ Expr *expr = (Expr *) lfirst(exprlc);
+ Value *name = (Value *) lfirst(namelc);
+ JsonPathVariableEvalContext *var = palloc(sizeof(*var));
+
+ var->var.varName = cstring_to_text(name->val.str);
+ var->var.typid = exprType((Node *) expr);
+ var->var.typmod = exprTypmod((Node *) expr);
+ var->var.cb = EvalJsonPathVar;
+ var->var.cb_arg = var;
+ var->estate = ExecInitExpr(expr, ps);
+ var->econtext = ps->ps_ExprContext;
+ var->mcxt = CurrentMemoryContext;
+ var->evaluated = false;
+ var->value = (Datum) 0;
+ var->isnull = true;
+
+ args = lappend(args, var);
+ }
+ }
+
+ cxt->colexprs = palloc(sizeof(*cxt->colexprs) *
+ list_length(tf->colvalexprs));
+
+ JsonTableInitScanState(cxt, &cxt->root, root, NULL, args,
+ CurrentMemoryContext);
+
+ i = 0;
+
+ foreach(lc, tf->colvalexprs)
+ {
+ Expr *expr = lfirst(lc);
+
+ cxt->colexprs[i].expr =
+ ExecInitExprWithCaseValue(expr, ps,
+ &cxt->colexprs[i].scan->current,
+ &cxt->colexprs[i].scan->currentIsNull);
+
+ i++;
+ }
+
+ state->opaque = cxt;
+}
+
+/* Reset scan iterator to the beginning of the item list */
+static void
+JsonTableRescan(JsonTableScanState *scan)
+{
+ memset(&scan->iter, 0, sizeof(scan->iter));
+ scan->current = PointerGetDatum(NULL);
+ scan->currentIsNull = true;
+ scan->advanceNested = false;
+ scan->ordinal = 0;
+}
+
+/* Reset context item of a scan, execute JSON path and reset a scan */
+static void
+JsonTableResetContextItem(JsonTableScanState *scan, Datum item)
+{
+ MemoryContext oldcxt;
+ JsonPathExecResult res;
+
+ JsonValueListClear(&scan->found);
+
+ MemoryContextResetOnly(scan->mcxt);
+
+ oldcxt = MemoryContextSwitchTo(scan->mcxt);
+
+ res = executeJsonPath(scan->path, scan->args, DatumGetJsonbP(item),
+ &scan->found);
+
+ MemoryContextSwitchTo(oldcxt);
+
+ if (jperIsError(res))
+ {
+ if (scan->errorOnError)
+ throwJsonPathError(res); /* does not return */
+ else
+ JsonValueListClear(&scan->found); /* EMPTY ON ERROR case */
+ }
+
+ JsonTableRescan(scan);
+}
+
+/*
+ * JsonTableSetDocument
+ * Install the input document
+ */
+static void
+JsonTableSetDocument(TableFuncScanState *state, Datum value)
+{
+ JsonTableContext *cxt = GetJsonTableContext(state, "JsonTableSetDocument");
+
+ 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 cross/union joined scan.
+ *
+ * Returned false at the end of a scan, true otherwise.
+ */
+static bool
+JsonTableNextJoinRow(JsonTableJoinState *state)
+{
+ if (!state->is_join)
+ return JsonTableNextRow(&state->u.scan);
+
+ if (state->u.join.advanceRight)
+ {
+ /* fetch next inner row */
+ if (JsonTableNextJoinRow(state->u.join.right))
+ return true;
+
+ /* inner rows are exhausted */
+ if (state->u.join.cross)
+ state->u.join.advanceRight = false; /* next outer row */
+ else
+ return false; /* end of scan */
+ }
+
+ while (!state->u.join.advanceRight)
+ {
+ /* fetch next outer row */
+ bool left = JsonTableNextJoinRow(state->u.join.left);
+
+ if (state->u.join.cross)
+ {
+ if (!left)
+ return false; /* end of scan */
+
+ JsonTableRescanRecursive(state->u.join.right);
+
+ if (!JsonTableNextJoinRow(state->u.join.right))
+ continue; /* next outer row */
+
+ state->u.join.advanceRight = true; /* next inner row */
+ }
+ else if (!left)
+ {
+ if (!JsonTableNextJoinRow(state->u.join.right))
+ return false; /* end of scan */
+
+ state->u.join.advanceRight = true; /* next inner row */
+ }
+
+ break;
+ }
+
+ return true;
+}
+
+/* Recursively set 'reset' flag of scan and its child nodes */
+static void
+JsonTableJoinReset(JsonTableJoinState *state)
+{
+ if (state->is_join)
+ {
+ JsonTableJoinReset(state->u.join.left);
+ JsonTableJoinReset(state->u.join.right);
+ state->u.join.advanceRight = false;
+ }
+ else
+ {
+ state->u.scan.reset = true;
+ state->u.scan.advanceNested = false;
+
+ if (state->u.scan.nested)
+ JsonTableJoinReset(state->u.scan.nested);
+ }
+}
+
+/*
+ * 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)
+{
+ /* reset context item if requested */
+ if (scan->reset)
+ {
+ Assert(!scan->parent->currentIsNull);
+ JsonTableResetContextItem(scan, scan->parent->current);
+ scan->reset = false;
+ }
+
+ if (scan->advanceNested)
+ {
+ /* fetch next nested row */
+ scan->advanceNested = JsonTableNextJoinRow(scan->nested);
+
+ if (scan->advanceNested)
+ return true;
+ }
+
+ for (;;)
+ {
+ /* fetch next row */
+ JsonbValue *jbv = JsonValueListNext(&scan->found, &scan->iter);
+ MemoryContext oldcxt;
+
+ if (!jbv)
+ {
+ scan->current = PointerGetDatum(NULL);
+ scan->currentIsNull = true;
+ return false; /* end of scan */
+ }
+
+ /* set current row item */
+ oldcxt = MemoryContextSwitchTo(scan->mcxt);
+ scan->current = JsonbPGetDatum(JsonbValueToJsonb(jbv));
+ scan->currentIsNull = false;
+ MemoryContextSwitchTo(oldcxt);
+
+ scan->ordinal++;
+
+ if (!scan->nested)
+ break;
+
+ JsonTableJoinReset(scan->nested);
+
+ scan->advanceNested = JsonTableNextJoinRow(scan->nested);
+
+ if (scan->advanceNested || scan->outerJoin)
+ break;
+
+ /* state->ordinal--; */ /* skip current outer row, reset counter */
+ }
+
+ return true;
+}
+
+/*
+ * JsonTableFetchRow
+ * Prepare the next "current" tuple for upcoming GetValue calls.
+ * Returns FALSE if the row-filter expression returned no more rows.
+ */
+static bool
+JsonTableFetchRow(TableFuncScanState *state)
+{
+ JsonTableContext *cxt = GetJsonTableContext(state, "JsonTableFetchRow");
+
+ if (cxt->empty)
+ return false;
+
+ return JsonTableNextRow(&cxt->root);
+}
+
+/*
+ * JsonTableGetValue
+ * Return the value for column number 'colnum' for the current row.
+ *
+ * This leaks memory, so be sure to reset often the context in which it's
+ * called.
+ */
+static Datum
+JsonTableGetValue(TableFuncScanState *state, int colnum,
+ Oid typid, int32 typmod, bool *isnull)
+{
+ JsonTableContext *cxt = GetJsonTableContext(state, "JsonTableGetValue");
+ ExprContext *econtext = state->ss.ps.ps_ExprContext;
+ ExprState *estate = cxt->colexprs[colnum].expr;
+ JsonTableScanState *scan = cxt->colexprs[colnum].scan;
+ Datum result;
+
+ if (scan->currentIsNull) /* NULL from outer/union join */
+ {
+ result = (Datum) 0;
+ *isnull = true;
+ }
+ else if (estate) /* regular column */
+ {
+ result = ExecEvalExpr(estate, econtext, isnull);
+ }
+ else
+ {
+ result = Int32GetDatum(scan->ordinal); /* ordinality column */
+ *isnull = false;
+ }
+
+ return result;
+}
+
+/*
+ * JsonTableDestroyOpaque
+ */
+static void
+JsonTableDestroyOpaque(TableFuncScanState *state)
+{
+ JsonTableContext *cxt = GetJsonTableContext(state, "JsonTableDestroyOpaque");
+
+ /* not valid anymore */
+ cxt->magic = 0;
+
+ state->opaque = NULL;
+}
+
+const TableFuncRoutine JsonbTableRoutine =
+{
+ JsonTableInitOpaque,
+ JsonTableSetDocument,
+ NULL,
+ NULL,
+ NULL,
+ JsonTableFetchRow,
+ JsonTableGetValue,
+ JsonTableDestroyOpaque
+};
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d6776a0..3e4b35f 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -469,6 +469,8 @@ static text *string_to_text(char *str);
static char *flatten_reloptions(Oid relid);
static void get_json_path_spec(Node *path_spec, deparse_context *context,
bool showimplicit);
+static void get_json_table_columns(TableFunc *tf, JsonTableParentNode *node,
+ deparse_context *context, bool showimplicit);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -8940,6 +8942,9 @@ get_rule_expr(Node *node, deparse_context *context,
case IS_JSON_EXISTS:
appendStringInfoString(buf, "JSON_EXISTS(");
break;
+ default:
+ elog(ERROR, "unexpected JsonExpr type: %d", jexpr->op);
+ break;
}
get_rule_expr(jexpr->raw_expr, context, showimplicit);
@@ -9974,16 +9979,14 @@ get_sublink_expr(SubLink *sublink, deparse_context *context)
/* ----------
- * get_tablefunc - Parse back a table function
+ * get_xmltable - Parse back a XMLTABLE function
* ----------
*/
static void
-get_tablefunc(TableFunc *tf, deparse_context *context, bool showimplicit)
+get_xmltable(TableFunc *tf, deparse_context *context, bool showimplicit)
{
StringInfo buf = context->buf;
- /* XMLTABLE is the only existing implementation. */
-
appendStringInfoString(buf, "XMLTABLE(");
if (tf->ns_uris != NIL)
@@ -10087,6 +10090,280 @@ get_tablefunc(TableFunc *tf, deparse_context *context, bool showimplicit)
appendStringInfoChar(buf, ')');
}
+/*
+ * get_json_nested_columns - Parse back nested JSON_TABLE columns
+ */
+static void
+get_json_table_nested_columns(TableFunc *tf, Node *node,
+ deparse_context *context, bool showimplicit,
+ bool needcomma)
+{
+ if (IsA(node, JsonTableSiblingNode))
+ {
+ JsonTableSiblingNode *n = (JsonTableSiblingNode *) node;
+
+ get_json_table_nested_columns(tf, n->larg, context, showimplicit,
+ needcomma);
+ get_json_table_nested_columns(tf, n->rarg, context, showimplicit, true);
+ }
+ else
+ {
+ JsonTableParentNode *n = castNode(JsonTableParentNode, node);
+
+ if (needcomma)
+ appendStringInfoChar(context->buf, ',');
+
+ appendStringInfoChar(context->buf, ' ');
+ appendContextKeyword(context, "NESTED PATH ", 0, 0, 0);
+ get_const_expr(n->path, context, -1);
+ appendStringInfo(context->buf, " AS %s", quote_identifier(n->name));
+ get_json_table_columns(tf, n, context, showimplicit);
+ }
+}
+
+/*
+ * get_json_table_plan - Parse back a JSON_TABLE plan
+ */
+static void
+get_json_table_plan(TableFunc *tf, Node *node, deparse_context *context,
+ bool parenthesize)
+{
+ if (parenthesize)
+ appendStringInfoChar(context->buf, '(');
+
+ if (IsA(node, JsonTableSiblingNode))
+ {
+ JsonTableSiblingNode *n = (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 = 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
+ */
+static void
+get_json_table_columns(TableFunc *tf, JsonTableParentNode *node,
+ deparse_context *context, bool showimplicit)
+{
+ StringInfo buf = context->buf;
+ ListCell *l1;
+ ListCell *l2;
+ ListCell *l3;
+ ListCell *l4;
+ int colnum = 0;
+
+ l2 = list_head(tf->coltypes);
+ l3 = list_head(tf->coltypmods);
+ l4 = list_head(tf->colvalexprs);
+
+ appendStringInfoChar(buf, ' ');
+ appendContextKeyword(context, "COLUMNS (", 0, 0, 0);
+
+ if (PRETTY_INDENT(context))
+ context->indentLevel += PRETTYINDENT_VAR;
+
+ foreach(l1, tf->colnames)
+ {
+ char *colname = strVal(lfirst(l1));
+ JsonExpr *colexpr;
+ Oid typid;
+ int32 typmod;
+ bool ordinality;
+
+ typid = lfirst_oid(l2);
+ l2 = lnext(l2);
+ typmod = lfirst_int(l3);
+ l3 = lnext(l3);
+ colexpr = castNode(JsonExpr, lfirst(l4));
+ l4 = lnext(l4);
+
+ if (colnum < node->colMin)
+ {
+ colnum++;
+ continue;
+ }
+
+ if (colnum > node->colMax)
+ break;
+
+ if (colnum > node->colMin)
+ appendStringInfoString(buf, ", ");
+
+ colnum++;
+
+ ordinality = !colexpr;
+
+ appendContextKeyword(context, "", 0, 0, 0);
+
+ appendStringInfo(buf, "%s %s", quote_identifier(colname),
+ ordinality ? "FOR ORDINALITY" :
+ format_type_with_typemod(typid, typmod));
+ if (ordinality)
+ continue;
+
+ if (colexpr->op == IS_JSON_QUERY)
+ appendStringInfoString(buf,
+ colexpr->format.type == JS_FORMAT_JSONB ?
+ " FORMAT JSONB" : " FORMAT JSON");
+
+ appendStringInfoString(buf, " PATH ");
+
+ get_json_path_spec(colexpr->path_spec, context, showimplicit);
+
+ if (colexpr->wrapper == JSW_CONDITIONAL)
+ appendStringInfo(buf, " WITH CONDITIONAL WRAPPER");
+
+ if (colexpr->wrapper == JSW_UNCONDITIONAL)
+ appendStringInfo(buf, " WITH UNCONDITIONAL WRAPPER");
+
+ if (colexpr->omit_quotes)
+ appendStringInfo(buf, " OMIT QUOTES");
+
+ if (colexpr->on_empty.btype != JSON_BEHAVIOR_NULL)
+ get_json_behavior(&colexpr->on_empty, context, "EMPTY");
+
+ if (colexpr->on_error.btype != JSON_BEHAVIOR_NULL)
+ get_json_behavior(&colexpr->on_error, context, "ERROR");
+ }
+
+ if (node->child)
+ get_json_table_nested_columns(tf, node->child, context, showimplicit,
+ node->colMax >= node->colMin);
+
+ if (PRETTY_INDENT(context))
+ context->indentLevel -= PRETTYINDENT_VAR;
+
+ appendContextKeyword(context, ")", 0, 0, 0);
+}
+
+/* ----------
+ * get_json_table - Parse back a JSON_TABLE function
+ * ----------
+ */
+static void
+get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit)
+{
+ StringInfo buf = context->buf;
+ JsonExpr *jexpr = castNode(JsonExpr, tf->docexpr);
+ JsonTableParentNode *root = castNode(JsonTableParentNode, tf->plan);
+
+ appendStringInfoString(buf, "JSON_TABLE(");
+
+ if (PRETTY_INDENT(context))
+ context->indentLevel += PRETTYINDENT_VAR;
+
+ appendContextKeyword(context, "", 0, 0, 0);
+
+ get_rule_expr(jexpr->raw_expr, context, showimplicit);
+
+ if (jexpr->format.type != JS_FORMAT_DEFAULT)
+ {
+ appendStringInfoString(buf,
+ jexpr->format.type == JS_FORMAT_JSONB ?
+ " FORMAT JSONB" : " FORMAT JSON");
+
+ if (jexpr->format.encoding != JS_ENC_DEFAULT)
+ {
+ const char *encoding =
+ jexpr->format.encoding == JS_ENC_UTF16 ? "UTF16" :
+ jexpr->format.encoding == JS_ENC_UTF32 ? "UTF32" :
+ "UTF8";
+
+ appendStringInfo(buf, " ENCODING %s", encoding);
+ }
+ }
+
+ appendStringInfoString(buf, ", ");
+
+ get_const_expr(root->path, context, -1);
+
+ appendStringInfo(buf, " AS %s", quote_identifier(root->name));
+
+ if (jexpr->passing.values)
+ {
+ ListCell *lc1, *lc2;
+ bool needcomma = false;
+
+ appendStringInfoChar(buf, ' ');
+ appendContextKeyword(context, "PASSING ", 0, 0, 0);
+
+ if (PRETTY_INDENT(context))
+ context->indentLevel += PRETTYINDENT_VAR;
+
+ forboth(lc1, jexpr->passing.names,
+ lc2, jexpr->passing.values)
+ {
+ if (needcomma)
+ appendStringInfoString(buf, ", ");
+ needcomma = true;
+
+ appendContextKeyword(context, "", 0, 0, 0);
+
+ get_rule_expr((Node *) lfirst(lc2), context, false);
+ appendStringInfo(buf, " AS %s",
+ quote_identifier(((Value *) lfirst(lc1))->val.str));
+ }
+
+ if (PRETTY_INDENT(context))
+ context->indentLevel -= PRETTYINDENT_VAR;
+ }
+
+ get_json_table_columns(tf, root, context, showimplicit);
+
+ appendStringInfoChar(buf, ' ');
+ appendContextKeyword(context, "PLAN ", 0, 0, 0);
+ get_json_table_plan(tf, (Node *) root, context, true);
+
+ if (jexpr->on_error.btype != JSON_BEHAVIOR_EMPTY)
+ get_json_behavior(&jexpr->on_error, context, "ERROR");
+
+ if (PRETTY_INDENT(context))
+ context->indentLevel -= PRETTYINDENT_VAR;
+
+ appendContextKeyword(context, ")", 0, 0, 0);
+}
+
+/* ----------
+ * get_tablefunc - Parse back a table function
+ * ----------
+ */
+static void
+get_tablefunc(TableFunc *tf, deparse_context *context, bool showimplicit)
+{
+ /* XMLTABLE and JSON_TABLE are the only existing implementations. */
+
+ if (tf->functype == TFT_XMLTABLE)
+ get_xmltable(tf, context, showimplicit);
+ else if (tf->functype == TFT_JSON_TABLE)
+ get_json_table(tf, context, showimplicit);
+}
+
/* ----------
* get_from_clause - Parse back a FROM clause
*
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index bd3fbee..561e9a9 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -793,6 +793,9 @@ extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item, bool is_jsonb,
struct JsonCoercionsState *coercions,
struct JsonCoercionState **pjcstate);
extern bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr);
+extern Datum ExecEvalExprPassingCaseValue(ExprState *estate,
+ ExprContext *econtext, bool *isnull,
+ Datum caseval_datum, bool caseval_isnull);
extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup);
extern Datum ExecAggTransReparent(AggState *aggstate, AggStatePerTrans pertrans,
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index f7aec03..78488bf 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -90,6 +90,8 @@ extern VacuumRelation *makeVacuumRelation(RangeVar *relation, Oid oid, List *va_
extern JsonValueExpr *makeJsonValueExpr(Expr *expr, JsonFormat format);
extern JsonBehavior *makeJsonBehavior(JsonBehaviorType type, Node *expr);
+extern Node *makeJsonTableJoinedPlan(JsonTablePlanJoinType type,
+ Node *plan1, Node *plan2, int location);
extern Node *makeJsonKeyValue(Node *key, Node *value);
extern Node *makeJsonIsPredicate(Node *expr, JsonFormat format,
JsonValueType vtype, bool unique_keys);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 4527b5e..25f1b46 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -198,6 +198,8 @@ typedef enum NodeTag
T_JsonExpr,
T_JsonCoercion,
T_JsonItemCoercions,
+ T_JsonTableParentNode,
+ T_JsonTableSiblingNode,
/*
* TAGS FOR EXPRESSION STATE NODES (execnodes.h)
@@ -486,6 +488,9 @@ typedef enum NodeTag
T_JsonFuncExpr,
T_JsonIsPredicate,
T_JsonExistsPredicate,
+ T_JsonTable,
+ T_JsonTableColumn,
+ T_JsonTablePlan,
T_JsonCommon,
T_JsonArgument,
T_JsonKeyValue,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 857f0b5..1622f41 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1442,6 +1442,18 @@ typedef enum JsonQuotes
} JsonQuotes;
/*
+ * JsonTableColumnType -
+ * enumeration of JSON_TABLE column types
+ */
+typedef enum
+{
+ JTC_FOR_ORDINALITY,
+ JTC_REGULAR,
+ JTC_FORMATTED,
+ JTC_NESTED,
+} JsonTableColumnType;
+
+/*
* JsonPathSpec -
* representation of JSON path constant
*/
@@ -1512,6 +1524,83 @@ typedef struct JsonFuncExpr
} JsonFuncExpr;
/*
+ * JsonTableColumn -
+ * untransformed representation of JSON_TABLE column
+ */
+typedef struct JsonTableColumn
+{
+ NodeTag type;
+ JsonTableColumnType coltype; /* column type */
+ 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? */
+ List *columns; /* nested columns */
+ JsonBehavior *on_empty; /* ON EMPTY behavior */
+ JsonBehavior *on_error; /* ON ERROR behavior */
+ int location; /* token location, or -1 if unknown */
+} JsonTableColumn;
+
+/*
+ * JsonTablePlanType -
+ * flags for JSON_TABLE plan node types representation
+ */
+typedef enum JsonTablePlanType
+{
+ JSTP_DEFAULT,
+ JSTP_SIMPLE,
+ JSTP_JOINED,
+} JsonTablePlanType;
+
+/*
+ * JsonTablePlanJoinType -
+ * flags for JSON_TABLE join types representation
+ */
+typedef enum JsonTablePlanJoinType
+{
+ JSTP_INNER = 0x01,
+ JSTP_OUTER = 0x02,
+ JSTP_CROSS = 0x04,
+ JSTP_UNION = 0x08,
+} JsonTablePlanJoinType;
+
+typedef struct JsonTablePlan JsonTablePlan;
+
+/*
+ * JsonTablePlan -
+ * untransformed representation of JSON_TABLE plan node
+ */
+struct JsonTablePlan
+{
+ NodeTag type;
+ JsonTablePlanType plan_type; /* plan type */
+ JsonTablePlanJoinType join_type; /* join type (for joined plan only) */
+ JsonTablePlan *plan1; /* first joined plan */
+ JsonTablePlan *plan2; /* second joined plan */
+ char *pathname; /* path name (for simple plan only) */
+ int location; /* token location, or -1 if unknown */
+};
+
+/*
+ * JsonTable -
+ * untransformed representation of JSON_TABLE
+ */
+typedef struct JsonTable
+{
+ NodeTag type;
+ JsonCommon *common; /* common JSON path syntax fields */
+ List *columns; /* list of JsonTableColumn */
+ JsonTablePlan *plan; /* join plan, if specified */
+ JsonBehavior *on_error; /* ON ERROR behavior, if specified */
+ Alias *alias; /* table alias in FROM clause */
+ bool lateral; /* does it have LATERAL prefix? */
+ int location; /* token location, or -1 if unknown */
+} JsonTable;
+
+/*
* JsonValueType -
* representation of JSON item type in IS JSON predicate
*/
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 4bfa016..19745dc 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -73,12 +73,19 @@ typedef struct RangeVar
int location; /* token location, or -1 if unknown */
} RangeVar;
+typedef enum TableFuncType
+{
+ TFT_XMLTABLE,
+ TFT_JSON_TABLE
+} TableFuncType;
+
/*
* TableFunc - node for a table function, such as XMLTABLE.
*/
typedef struct TableFunc
{
NodeTag type;
+ TableFuncType functype; /* XMLTABLE or JSON_TABLE */
List *ns_uris; /* list of namespace uri */
List *ns_names; /* list of namespace names */
Node *docexpr; /* input document expression */
@@ -89,7 +96,9 @@ typedef struct TableFunc
List *colcollations; /* OID list of column collation OIDs */
List *colexprs; /* list of column filter expressions */
List *coldefexprs; /* list of column default expressions */
+ List *colvalexprs; /* list of column value expressions */
Bitmapset *notnulls; /* nullability flag for each output column */
+ Node *plan; /* JSON_TABLE plan */
int ordinalitycol; /* counts from 0; -1 if none specified */
int location; /* token location, or -1 if unknown */
} TableFunc;
@@ -1182,7 +1191,8 @@ typedef enum JsonExprOp
{
IS_JSON_VALUE, /* JSON_VALUE() */
IS_JSON_QUERY, /* JSON_QUERY() */
- IS_JSON_EXISTS /* JSON_EXISTS() */
+ IS_JSON_EXISTS, /* JSON_EXISTS() */
+ IS_JSON_TABLE /* JSON_TABLE() */
} JsonExprOp;
/*
@@ -1335,6 +1345,35 @@ typedef struct JsonExpr
int location; /* token location, or -1 if unknown */
} JsonExpr;
+/*
+ * JsonTableParentNode -
+ * transformed representation of parent JSON_TABLE plan node
+ */
+typedef struct JsonTableParentNode
+{
+ NodeTag type;
+ Const *path; /* jsonpath constant */
+ char *name; /* path name */
+ JsonPassing passing; /* PASSING arguments */
+ Node *child; /* nested columns, if any */
+ bool outerJoin; /* outer or inner join for nested columns? */
+ int colMin; /* min column index in the resulting column list */
+ int colMax; /* max column index in the resulting column list */
+ bool errorOnError; /* ERROR/EMPTY ON ERROR behavior */
+} JsonTableParentNode;
+
+/*
+ * JsonTableSiblingNode -
+ * transformed representation of joined sibling JSON_TABLE plan node
+ */
+typedef struct JsonTableSiblingNode
+{
+ NodeTag type;
+ Node *larg; /* left join node */
+ Node *rarg; /* right join node */
+ bool cross; /* cross or union join? */
+} JsonTableSiblingNode;
+
/* ----------------
* NullTest
*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 7732c1b..95eae04 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -233,6 +233,7 @@ PG_KEYWORD("json_exists", JSON_EXISTS, COL_NAME_KEYWORD)
PG_KEYWORD("json_object", JSON_OBJECT, COL_NAME_KEYWORD)
PG_KEYWORD("json_objectagg", JSON_OBJECTAGG, COL_NAME_KEYWORD)
PG_KEYWORD("json_query", JSON_QUERY, COL_NAME_KEYWORD)
+PG_KEYWORD("json_table", JSON_TABLE, COL_NAME_KEYWORD)
PG_KEYWORD("json_value", JSON_VALUE, COL_NAME_KEYWORD)
PG_KEYWORD("keep", KEEP, UNRESERVED_KEYWORD)
PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD)
@@ -273,6 +274,7 @@ PG_KEYWORD("names", NAMES, UNRESERVED_KEYWORD)
PG_KEYWORD("national", NATIONAL, COL_NAME_KEYWORD)
PG_KEYWORD("natural", NATURAL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("nchar", NCHAR, COL_NAME_KEYWORD)
+PG_KEYWORD("nested", NESTED, UNRESERVED_KEYWORD)
PG_KEYWORD("new", NEW, UNRESERVED_KEYWORD)
PG_KEYWORD("next", NEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("no", NO, UNRESERVED_KEYWORD)
@@ -316,7 +318,9 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD)
+PG_KEYWORD("path", PATH, UNRESERVED_KEYWORD)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD)
+PG_KEYWORD("plan", PLAN, UNRESERVED_KEYWORD)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD)
PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD)
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 4071093..c469010 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -15,9 +15,10 @@
#define JSONPATH_H
#include "fmgr.h"
-#include "utils/jsonb.h"
+#include "executor/tablefunc.h"
#include "nodes/pg_list.h"
#include "nodes/primnodes.h"
+#include "utils/jsonb.h"
typedef struct
{
@@ -282,6 +283,7 @@ typedef struct JsonPathVariableEvalContext
JsonPathVariable var;
struct ExprContext *econtext;
struct ExprState *estate;
+ MemoryContext mcxt; /* memory context for cached value */
Datum value;
bool isnull;
bool evaluated;
@@ -312,4 +314,6 @@ extern Datum JsonPathQuery(Datum json, JsonPath *jp, JsonWrapper wrapper,
extern Datum EvalJsonPathVar(void *cxt, bool *isnull);
+extern const TableFuncRoutine JsonbTableRoutine;
+
#endif
diff --git a/src/test/regress/expected/json_sqljson.out b/src/test/regress/expected/json_sqljson.out
index f7d1568..fc26f27 100644
--- a/src/test/regress/expected/json_sqljson.out
+++ b/src/test/regress/expected/json_sqljson.out
@@ -1041,6 +1041,11 @@ INSERT INTO test_json_constraints VALUES ('{"a": 10}', 1);
ERROR: new row for relation "test_json_constraints" violates check constraint "test_json_constraint4"
DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]).
DROP TABLE test_json_constraints;
+-- JSON_TABLE
+SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text));
+ERROR: JSON_TABLE() is not yet implemented for json type
+LINE 1: SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo ...
+ ^
-- Extension: non-constant JSON path
SELECT JSON_EXISTS(json '{"a": 123}', '$' || '.' || 'a');
json_exists
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
index c516870..99e6306 100644
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -918,6 +918,952 @@ INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint4"
DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]).
DROP TABLE test_jsonb_constraints;
+-- JSON_TABLE
+-- Should fail (JSON_TABLE can be used only in FROM clause)
+SELECT JSON_TABLE('[]', '$');
+ERROR: syntax error at or near "("
+LINE 1: SELECT JSON_TABLE('[]', '$');
+ ^
+-- Should fail (no columns)
+SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
+ERROR: syntax error at or near ")"
+LINE 1: SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
+ ^
+-- NULL => empty table
+SELECT * FROM JSON_TABLE(NULL::jsonb, '$' COLUMNS (foo int)) bar;
+ foo
+-----
+(0 rows)
+
+--
+SELECT * FROM JSON_TABLE(jsonb '123', '$'
+ COLUMNS (item int PATH '$', foo int)) bar;
+ item | foo
+------+-----
+ 123 |
+(1 row)
+
+-- JSON_TABLE: basic functionality
+SELECT *
+FROM
+ (VALUES
+ ('1'),
+ ('[]'),
+ ('{}'),
+ ('[1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]')
+ ) vals(js)
+ LEFT OUTER JOIN
+-- JSON_TABLE is implicitly lateral
+ JSON_TABLE(
+ vals.js::jsonb, 'lax $[*]'
+ COLUMNS (
+ id FOR ORDINALITY,
+ id2 FOR ORDINALITY, -- allowed additional ordinality columns
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa'
+ )
+ ) jt
+ ON true;
+ js | id | id2 | int | text | char(4) | bool | numeric | js | jb | jst | jsc | jsv | jsb | aaa | aaa1
+--------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+-----------+-----------+--------------+------+------+--------------+-----+------
+ 1 | 1 | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |
+ [] | | | | | | | | | | | | | | |
+ {} | 1 | 1 | | | | | | | | {} | {} | {} | {} | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | "2" | "2" | "2" | "2" | "2" | "2" | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | | | | | | | null | null | null | null | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | 0 | false | fals | f | | false | false | false | fals | fals | false | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 1 | true | true | t | | true | true | true | true | true | true | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | | | | | | | | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | 123 | 123
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | [1,2] | [1,2 | | | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | "str" | "str | | | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | |
+(13 rows)
+
+-- JSON_TABLE: Test backward parsing
+CREATE VIEW jsonb_table_view AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ id2 FOR ORDINALITY, -- allowed additional ordinality columns
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa',
+ NESTED PATH '$[1]' AS p1 COLUMNS (
+ a1 int,
+ NESTED PATH '$[*]' AS "p1 1" COLUMNS (
+ a11 text
+ ),
+ b1 text
+ ),
+ NESTED PATH '$[2]' AS p2 COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1" COLUMNS (
+ a21 text
+ ),
+ NESTED PATH '$[*]' AS p22 COLUMNS (
+ a22 text
+ )
+ )
+ )
+ );
+\sv jsonb_table_view
+CREATE OR REPLACE VIEW public.jsonb_table_view AS
+ SELECT "json_table".id,
+ "json_table".id2,
+ "json_table"."int",
+ "json_table".text,
+ "json_table"."char(4)",
+ "json_table".bool,
+ "json_table"."numeric",
+ "json_table".js,
+ "json_table".jb,
+ "json_table".jst,
+ "json_table".jsc,
+ "json_table".jsv,
+ "json_table".jsb,
+ "json_table".aaa,
+ "json_table".aaa1,
+ "json_table".a1,
+ "json_table".b1,
+ "json_table".a11,
+ "json_table".a21,
+ "json_table".a22
+ FROM JSON_TABLE(
+ 'null'::jsonb, '$[*]' AS json_table_path_1
+ PASSING
+ 1 + 2 AS a,
+ '"foo"'::json 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 '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc character(4) FORMAT JSON PATH '$',
+ jsv character varying(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa integer PATH '$."aaa"',
+ aaa1 integer PATH '$."aaa"',
+ NESTED PATH '$[1]' AS p1
+ COLUMNS (
+ a1 integer PATH '$."a1"',
+ b1 text PATH '$."b1"',
+ NESTED PATH '$[*]' AS "p1 1"
+ COLUMNS (
+ a11 text PATH '$."a11"'
+ )
+ ),
+ NESTED PATH '$[2]' AS p2
+ COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1"
+ COLUMNS (
+ a21 text PATH '$."a21"'
+ ),
+ NESTED PATH '$[*]' AS p22
+ COLUMNS (
+ a22 text PATH '$."a22"'
+ )
+ )
+ )
+ PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))
+ )
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "json_table"
+ Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".aaa, "json_table".aaa1, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_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 '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb FORMAT JSON PATH '$', aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
+(3 rows)
+
+-- JSON_TABLE: ON EMPTY/ON ERROR behavior
+SELECT *
+FROM
+ (VALUES ('1'), ('"err"')) vals(js),
+ JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$')) jt;
+ js | a
+-------+---
+ 1 | 1
+ "err" |
+(2 rows)
+
+SELECT *
+FROM
+ (VALUES ('1'), ('"err"')) vals(js)
+ LEFT OUTER JOIN
+ JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt
+ ON true;
+ERROR: invalid input syntax for integer: "err"
+SELECT *
+FROM
+ (VALUES ('1'), ('"err"')) vals(js)
+ LEFT OUTER JOIN
+ JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
+ ON true;
+ERROR: invalid input syntax for integer: "err"
+SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
+ a
+---
+
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
+ERROR: SQL/JSON member not found
+SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
+ERROR: no SQL/JSON item
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+ a
+---
+ 2
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+ a
+---
+ 2
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+ a
+---
+ 1
+(1 row)
+
+-- 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 '[]', '$'
+ ^
+DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS path1
+ COLUMNS (
+ NESTED PATH '$' COLUMNS ( -- AS <path name> required here
+ foo int PATH '$'
+ )
+ )
+ PLAN DEFAULT (UNION)
+) jt;
+ERROR: invalid JSON_TABLE expression
+LINE 4: NESTED PATH '$' COLUMNS (
+ ^
+DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used
+-- Should fail (column names anf path names shall be distinct)
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS a
+ COLUMNS (
+ 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
+HINT: JSON_TABLE path names and column names shall be distinct from one another
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$'
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS b
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+ERROR: duplicate JSON_TABLE column name: b
+HINT: JSON_TABLE path names and column names shall be distinct from one another
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ b int
+ ),
+ NESTED PATH '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+ )
+) jt;
+ERROR: duplicate JSON_TABLE column 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
+-----+-----+-----
+(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 $[*]'
+ ^
+DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used
+-- JSON_TABLE: plan execution
+CREATE TEMP TABLE jsonb_table_test (js jsonb);
+INSERT INTO jsonb_table_test
+VALUES (
+ '[
+ {"a": 1, "b": [], "c": []},
+ {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
+ {"a": 3, "b": [1, 2], "c": []},
+ {"x": "4", "b": [1, 2], "c": 123}
+ ]'
+);
+-- unspecified plan (outer, union)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(11 rows)
+
+-- default plan (outer, union)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (outer, union)
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(11 rows)
+
+-- specific plan (p outer (pb union pc))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p outer (pb union pc))
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(11 rows)
+
+-- specific plan (p outer (pc union pb))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p outer (pc union pb))
+ ) jt;
+ n | a | c | b
+---+----+----+---
+ 1 | 1 | |
+ 2 | 2 | 10 |
+ 2 | 2 | |
+ 2 | 2 | 20 |
+ 2 | 2 | | 1
+ 2 | 2 | | 2
+ 2 | 2 | | 3
+ 3 | 3 | | 1
+ 3 | 3 | | 2
+ 4 | -1 | | 1
+ 4 | -1 | | 2
+(11 rows)
+
+-- default plan (inner, union)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (inner)
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(10 rows)
+
+-- specific plan (p inner (pb union pc))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p inner (pb union pc))
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(10 rows)
+
+-- default plan (inner, cross)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (cross, inner)
+ ) jt;
+ n | a | b | c
+---+---+---+----
+ 2 | 2 | 1 | 10
+ 2 | 2 | 1 |
+ 2 | 2 | 1 | 20
+ 2 | 2 | 2 | 10
+ 2 | 2 | 2 |
+ 2 | 2 | 2 | 20
+ 2 | 2 | 3 | 10
+ 2 | 2 | 3 |
+ 2 | 2 | 3 | 20
+(9 rows)
+
+-- specific plan (p inner (pb cross pc))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p inner (pb cross pc))
+ ) jt;
+ n | a | b | c
+---+---+---+----
+ 2 | 2 | 1 | 10
+ 2 | 2 | 1 |
+ 2 | 2 | 1 | 20
+ 2 | 2 | 2 | 10
+ 2 | 2 | 2 |
+ 2 | 2 | 2 | 20
+ 2 | 2 | 3 | 10
+ 2 | 2 | 3 |
+ 2 | 2 | 3 | 20
+(9 rows)
+
+-- default plan (outer, cross)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (outer, cross)
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 | 10
+ 2 | 2 | 1 |
+ 2 | 2 | 1 | 20
+ 2 | 2 | 2 | 10
+ 2 | 2 | 2 |
+ 2 | 2 | 2 | 20
+ 2 | 2 | 3 | 10
+ 2 | 2 | 3 |
+ 2 | 2 | 3 | 20
+ 3 | 3 | |
+ 4 | -1 | |
+(12 rows)
+
+-- specific plan (p outer (pb cross pc))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p outer (pb cross pc))
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 | 10
+ 2 | 2 | 1 |
+ 2 | 2 | 1 | 20
+ 2 | 2 | 2 | 10
+ 2 | 2 | 2 |
+ 2 | 2 | 2 | 20
+ 2 | 2 | 3 | 10
+ 2 | 2 | 3 |
+ 2 | 2 | 3 | 20
+ 3 | 3 | |
+ 4 | -1 | |
+(12 rows)
+
+select
+ jt.*, b1 + 100 as b
+from
+ json_table (jsonb
+ '[
+ {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]},
+ {"a": 2, "b": [10, 20], "c": [1, null, 2]},
+ {"x": "3", "b": [11, 22, 33, 44]}
+ ]',
+ '$[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on error,
+ nested path 'strict $.b[*]' as pb columns (
+ b text format json path '$',
+ nested path 'strict $[*]' as pb1 columns (
+ b1 int path '$'
+ )
+ ),
+ nested path 'strict $.c[*]' as pc columns (
+ c text format json path '$',
+ nested path 'strict $[*]' as pc1 columns (
+ c1 int path '$'
+ )
+ )
+ )
+ --plan default(outer, cross)
+ plan(p outer ((pb inner pb1) cross (pc outer pc1)))
+ ) jt;
+ n | a | b | b1 | c | c1 | b
+---+---+--------------+-----+------+----+-----
+ 1 | 1 | [1, 10] | 1 | 1 | | 101
+ 1 | 1 | [1, 10] | 1 | null | | 101
+ 1 | 1 | [1, 10] | 1 | 2 | | 101
+ 1 | 1 | [1, 10] | 10 | 1 | | 110
+ 1 | 1 | [1, 10] | 10 | null | | 110
+ 1 | 1 | [1, 10] | 10 | 2 | | 110
+ 1 | 1 | [2] | 2 | 1 | | 102
+ 1 | 1 | [2] | 2 | null | | 102
+ 1 | 1 | [2] | 2 | 2 | | 102
+ 1 | 1 | [3, 30, 300] | 3 | 1 | | 103
+ 1 | 1 | [3, 30, 300] | 3 | null | | 103
+ 1 | 1 | [3, 30, 300] | 3 | 2 | | 103
+ 1 | 1 | [3, 30, 300] | 30 | 1 | | 130
+ 1 | 1 | [3, 30, 300] | 30 | null | | 130
+ 1 | 1 | [3, 30, 300] | 30 | 2 | | 130
+ 1 | 1 | [3, 30, 300] | 300 | 1 | | 400
+ 1 | 1 | [3, 30, 300] | 300 | null | | 400
+ 1 | 1 | [3, 30, 300] | 300 | 2 | | 400
+ 2 | 2 | | | | |
+ 3 | | | | | |
+(20 rows)
+
+-- Should succeed (JSON arguments are passed to root and nested paths)
+SELECT *
+FROM
+ generate_series(1, 4) x,
+ generate_series(1, 3) y,
+ JSON_TABLE(jsonb
+ '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
+ 'strict $[*] ? (@[*] < $x)'
+ PASSING x AS x, y AS y
+ COLUMNS (
+ y text FORMAT JSON PATH '$',
+ NESTED PATH 'strict $[*] ? (@ >= $y)'
+ COLUMNS (
+ z int PATH '$'
+ )
+ )
+ ) jt;
+ x | y | y | z
+---+---+--------------+---
+ 2 | 1 | [1, 2, 3] | 1
+ 2 | 1 | [1, 2, 3] | 2
+ 2 | 1 | [1, 2, 3] | 3
+ 3 | 1 | [1, 2, 3] | 1
+ 3 | 1 | [1, 2, 3] | 2
+ 3 | 1 | [1, 2, 3] | 3
+ 3 | 1 | [2, 3, 4, 5] | 2
+ 3 | 1 | [2, 3, 4, 5] | 3
+ 3 | 1 | [2, 3, 4, 5] | 4
+ 3 | 1 | [2, 3, 4, 5] | 5
+ 4 | 1 | [1, 2, 3] | 1
+ 4 | 1 | [1, 2, 3] | 2
+ 4 | 1 | [1, 2, 3] | 3
+ 4 | 1 | [2, 3, 4, 5] | 2
+ 4 | 1 | [2, 3, 4, 5] | 3
+ 4 | 1 | [2, 3, 4, 5] | 4
+ 4 | 1 | [2, 3, 4, 5] | 5
+ 4 | 1 | [3, 4, 5, 6] | 3
+ 4 | 1 | [3, 4, 5, 6] | 4
+ 4 | 1 | [3, 4, 5, 6] | 5
+ 4 | 1 | [3, 4, 5, 6] | 6
+ 2 | 2 | [1, 2, 3] | 2
+ 2 | 2 | [1, 2, 3] | 3
+ 3 | 2 | [1, 2, 3] | 2
+ 3 | 2 | [1, 2, 3] | 3
+ 3 | 2 | [2, 3, 4, 5] | 2
+ 3 | 2 | [2, 3, 4, 5] | 3
+ 3 | 2 | [2, 3, 4, 5] | 4
+ 3 | 2 | [2, 3, 4, 5] | 5
+ 4 | 2 | [1, 2, 3] | 2
+ 4 | 2 | [1, 2, 3] | 3
+ 4 | 2 | [2, 3, 4, 5] | 2
+ 4 | 2 | [2, 3, 4, 5] | 3
+ 4 | 2 | [2, 3, 4, 5] | 4
+ 4 | 2 | [2, 3, 4, 5] | 5
+ 4 | 2 | [3, 4, 5, 6] | 3
+ 4 | 2 | [3, 4, 5, 6] | 4
+ 4 | 2 | [3, 4, 5, 6] | 5
+ 4 | 2 | [3, 4, 5, 6] | 6
+ 2 | 3 | [1, 2, 3] | 3
+ 3 | 3 | [1, 2, 3] | 3
+ 3 | 3 | [2, 3, 4, 5] | 3
+ 3 | 3 | [2, 3, 4, 5] | 4
+ 3 | 3 | [2, 3, 4, 5] | 5
+ 4 | 3 | [1, 2, 3] | 3
+ 4 | 3 | [2, 3, 4, 5] | 3
+ 4 | 3 | [2, 3, 4, 5] | 4
+ 4 | 3 | [2, 3, 4, 5] | 5
+ 4 | 3 | [3, 4, 5, 6] | 3
+ 4 | 3 | [3, 4, 5, 6] | 4
+ 4 | 3 | [3, 4, 5, 6] | 5
+ 4 | 3 | [3, 4, 5, 6] | 6
+(52 rows)
+
+-- Should fail (JSON arguments are not passed to column paths)
+SELECT *
+FROM JSON_TABLE(
+ jsonb '[1,2,3]',
+ '$[*] ? (@ < $x)'
+ PASSING 10 AS x
+ COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)')
+ ) jt;
+ERROR: could not find 'x' passed variable
-- Extension: non-constant JSON path
SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
json_exists
@@ -953,6 +1899,11 @@ SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error');
ERROR: bad jsonpath representation
DETAIL: syntax error, unexpected IDENT_P at or near " "
+-- Should fail (not supported)
+SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
+ERROR: only string constants supported in JSON_TABLE path specification
+LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
+ ^
-- Test parallel JSON_VALUE()
CREATE TABLE test_parallel_jsonb_value AS
SELECT i::text::jsonb AS js
diff --git a/src/test/regress/sql/json_sqljson.sql b/src/test/regress/sql/json_sqljson.sql
index 6146c45..896941c 100644
--- a/src/test/regress/sql/json_sqljson.sql
+++ b/src/test/regress/sql/json_sqljson.sql
@@ -302,6 +302,10 @@ INSERT INTO test_json_constraints VALUES ('{"a": 10}', 1);
DROP TABLE test_json_constraints;
+-- JSON_TABLE
+
+SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text));
+
-- Extension: non-constant JSON path
SELECT JSON_EXISTS(json '{"a": 123}', '$' || '.' || 'a');
SELECT JSON_VALUE(json '{"a": 123}', '$' || '.' || 'a');
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
index 857bef4..7dbdfc2 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -277,6 +277,582 @@ INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
DROP TABLE test_jsonb_constraints;
+-- JSON_TABLE
+
+-- Should fail (JSON_TABLE can be used only in FROM clause)
+SELECT JSON_TABLE('[]', '$');
+
+-- Should fail (no columns)
+SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
+
+-- NULL => empty table
+SELECT * FROM JSON_TABLE(NULL::jsonb, '$' COLUMNS (foo int)) bar;
+
+--
+SELECT * FROM JSON_TABLE(jsonb '123', '$'
+ COLUMNS (item int PATH '$', foo int)) bar;
+
+-- JSON_TABLE: basic functionality
+SELECT *
+FROM
+ (VALUES
+ ('1'),
+ ('[]'),
+ ('{}'),
+ ('[1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]')
+ ) vals(js)
+ LEFT OUTER JOIN
+-- JSON_TABLE is implicitly lateral
+ JSON_TABLE(
+ vals.js::jsonb, 'lax $[*]'
+ COLUMNS (
+ id FOR ORDINALITY,
+ id2 FOR ORDINALITY, -- allowed additional ordinality columns
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa'
+ )
+ ) jt
+ ON true;
+
+-- JSON_TABLE: Test backward parsing
+
+CREATE VIEW jsonb_table_view AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ id2 FOR ORDINALITY, -- allowed additional ordinality columns
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa',
+ NESTED PATH '$[1]' AS p1 COLUMNS (
+ a1 int,
+ NESTED PATH '$[*]' AS "p1 1" COLUMNS (
+ a11 text
+ ),
+ b1 text
+ ),
+ NESTED PATH '$[2]' AS p2 COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1" COLUMNS (
+ a21 text
+ ),
+ NESTED PATH '$[*]' AS p22 COLUMNS (
+ a22 text
+ )
+ )
+ )
+ );
+
+\sv jsonb_table_view
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
+
+-- JSON_TABLE: ON EMPTY/ON ERROR behavior
+SELECT *
+FROM
+ (VALUES ('1'), ('"err"')) vals(js),
+ JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$')) jt;
+
+SELECT *
+FROM
+ (VALUES ('1'), ('"err"')) vals(js)
+ LEFT OUTER JOIN
+ JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt
+ ON true;
+
+SELECT *
+FROM
+ (VALUES ('1'), ('"err"')) vals(js)
+ LEFT OUTER JOIN
+ JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
+ ON true;
+
+SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
+SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
+SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
+
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+
+-- 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;
+
+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 '[]', '$' AS a
+ COLUMNS (
+ a int
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$' AS a
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$'
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS b
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb '[]', '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ b int
+ ),
+ NESTED PATH '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+ )
+) jt;
+
+-- JSON_TABLE: plan validation
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', '$[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p1)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', '$[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', '$[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0 OUTER p3)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', '$[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0 UNION p1 UNION p11)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', '$[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0 OUTER (p1 CROSS p13))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', '$[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0 OUTER (p1 CROSS p2))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', '$[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0 OUTER ((p1 UNION p11) CROSS p2))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', '$[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0 OUTER ((p1 INNER p11) CROSS p2))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', '$[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', 'strict $[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ jsonb 'null', 'strict $[*]' -- without root path name
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))
+) jt;
+
+-- JSON_TABLE: plan execution
+
+CREATE TEMP TABLE jsonb_table_test (js jsonb);
+
+INSERT INTO jsonb_table_test
+VALUES (
+ '[
+ {"a": 1, "b": [], "c": []},
+ {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
+ {"a": 3, "b": [1, 2], "c": []},
+ {"x": "4", "b": [1, 2], "c": 123}
+ ]'
+);
+
+-- unspecified plan (outer, union)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ ) jt;
+
+-- default plan (outer, union)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (outer, union)
+ ) jt;
+
+-- specific plan (p outer (pb union pc))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p outer (pb union pc))
+ ) jt;
+
+-- specific plan (p outer (pc union pb))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p outer (pc union pb))
+ ) jt;
+
+-- default plan (inner, union)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (inner)
+ ) jt;
+
+-- specific plan (p inner (pb union pc))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p inner (pb union pc))
+ ) jt;
+
+-- default plan (inner, cross)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (cross, inner)
+ ) jt;
+
+-- specific plan (p inner (pb cross pc))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p inner (pb cross pc))
+ ) jt;
+
+-- default plan (outer, cross)
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (outer, cross)
+ ) jt;
+
+-- specific plan (p outer (pb cross pc))
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p outer (pb cross pc))
+ ) jt;
+
+
+select
+ jt.*, b1 + 100 as b
+from
+ json_table (jsonb
+ '[
+ {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]},
+ {"a": 2, "b": [10, 20], "c": [1, null, 2]},
+ {"x": "3", "b": [11, 22, 33, 44]}
+ ]',
+ '$[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on error,
+ nested path 'strict $.b[*]' as pb columns (
+ b text format json path '$',
+ nested path 'strict $[*]' as pb1 columns (
+ b1 int path '$'
+ )
+ ),
+ nested path 'strict $.c[*]' as pc columns (
+ c text format json path '$',
+ nested path 'strict $[*]' as pc1 columns (
+ c1 int path '$'
+ )
+ )
+ )
+ --plan default(outer, cross)
+ plan(p outer ((pb inner pb1) cross (pc outer pc1)))
+ ) jt;
+
+-- Should succeed (JSON arguments are passed to root and nested paths)
+SELECT *
+FROM
+ generate_series(1, 4) x,
+ generate_series(1, 3) y,
+ JSON_TABLE(jsonb
+ '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
+ 'strict $[*] ? (@[*] < $x)'
+ PASSING x AS x, y AS y
+ COLUMNS (
+ y text FORMAT JSON PATH '$',
+ NESTED PATH 'strict $[*] ? (@ >= $y)'
+ COLUMNS (
+ z int PATH '$'
+ )
+ )
+ ) jt;
+
+-- Should fail (JSON arguments are not passed to column paths)
+SELECT *
+FROM JSON_TABLE(
+ jsonb '[1,2,3]',
+ '$[*] ? (@ < $x)'
+ PASSING 10 AS x
+ COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)')
+ ) jt;
+
-- Extension: non-constant JSON path
SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
@@ -285,6 +861,8 @@ SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a');
SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
-- Should fail (invalid path)
SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error');
+-- Should fail (not supported)
+SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
-- Test parallel JSON_VALUE()
CREATE TABLE test_parallel_jsonb_value AS
@@ -300,4 +878,3 @@ SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value
EXPLAIN (COSTS OFF)
SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
-
0016-json_table-json-v15.patch (text/x-patch, 48.9K) download
diff --git a/src/backend/executor/nodeTableFuncscan.c b/src/backend/executor/nodeTableFuncscan.c
index dfae539..15d2d32 100644
--- a/src/backend/executor/nodeTableFuncscan.c
+++ b/src/backend/executor/nodeTableFuncscan.c
@@ -23,10 +23,12 @@
#include "postgres.h"
#include "nodes/execnodes.h"
+#include "catalog/pg_type.h"
#include "executor/executor.h"
#include "executor/nodeTableFuncscan.h"
#include "executor/tablefunc.h"
#include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
#include "utils/builtins.h"
#include "utils/jsonpath.h"
#include "utils/lsyscache.h"
@@ -164,7 +166,8 @@ ExecInitTableFuncScan(TableFuncScan *node, EState *estate, int eflags)
/* Only XMLTABLE and JSON_TABLE are supported currently */
scanstate->routine =
- tf->functype == TFT_XMLTABLE ? &XmlTableRoutine : &JsonbTableRoutine;
+ tf->functype == TFT_XMLTABLE ? &XmlTableRoutine :
+ exprType(tf->docexpr) == JSONBOID ? &JsonbTableRoutine : &JsonTableRoutine;
scanstate->perValueCxt =
AllocSetContextCreate(CurrentMemoryContext,
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index cb6407a..14fa2e6 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4731,12 +4731,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
jsexpr->returning.typid = exprType(contextItemExpr);
jsexpr->returning.typmod = -1;
- if (jsexpr->returning.typid != JSONBOID)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("JSON_TABLE() is not yet implemented for json type"),
- parser_errposition(pstate, func->location)));
-
break;
}
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index c469010..fa4346e 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -314,6 +314,7 @@ extern Datum JsonPathQuery(Datum json, JsonPath *jp, JsonWrapper wrapper,
extern Datum EvalJsonPathVar(void *cxt, bool *isnull);
+extern const TableFuncRoutine JsonTableRoutine;
extern const TableFuncRoutine JsonbTableRoutine;
#endif
diff --git a/src/test/regress/expected/json_sqljson.out b/src/test/regress/expected/json_sqljson.out
index fc26f27..4ef91d1 100644
--- a/src/test/regress/expected/json_sqljson.out
+++ b/src/test/regress/expected/json_sqljson.out
@@ -1042,10 +1042,978 @@ ERROR: new row for relation "test_json_constraints" violates check constraint "
DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]).
DROP TABLE test_json_constraints;
-- JSON_TABLE
-SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text));
-ERROR: JSON_TABLE() is not yet implemented for json type
-LINE 1: SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo ...
- ^
+-- Should fail (JSON_TABLE can be used only in FROM clause)
+SELECT JSON_TABLE('[]', '$');
+ERROR: syntax error at or near "("
+LINE 1: SELECT JSON_TABLE('[]', '$');
+ ^
+-- Should fail (no columns)
+SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
+ERROR: syntax error at or near ")"
+LINE 1: SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
+ ^
+-- NULL => empty table
+SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS (foo int)) bar;
+ foo
+-----
+(0 rows)
+
+-- invalid json => empty table
+SELECT * FROM JSON_TABLE('', '$' COLUMNS (foo int)) bar;
+ foo
+-----
+(0 rows)
+
+SELECT * FROM JSON_TABLE('' FORMAT JSON, '$' COLUMNS (foo int)) bar;
+ foo
+-----
+(0 rows)
+
+-- invalid json => error
+SELECT * FROM JSON_TABLE('' FORMAT JSON, '$' COLUMNS (foo int) ERROR ON ERROR) bar;
+ERROR: invalid input syntax for type json
+DETAIL: The input string ended unexpectedly.
+CONTEXT: JSON data, line 1:
+--
+SELECT * FROM JSON_TABLE('123' FORMAT JSON, '$'
+ COLUMNS (item int PATH '$', foo int)) bar;
+ item | foo
+------+-----
+ 123 |
+(1 row)
+
+SELECT * FROM JSON_TABLE(json '123', '$'
+ COLUMNS (item int PATH '$', foo int)) bar;
+ item | foo
+------+-----
+ 123 |
+(1 row)
+
+-- JSON_TABLE: basic functionality
+SELECT *
+FROM
+ (VALUES
+ ('1'),
+ ('[]'),
+ ('{}'),
+ ('[1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]'),
+ ('err')
+ ) vals(js)
+ LEFT OUTER JOIN
+-- JSON_TABLE is implicitly lateral
+ JSON_TABLE(
+ vals.js FORMAT json, 'lax $[*]'
+ COLUMNS (
+ id FOR ORDINALITY,
+ id2 FOR ORDINALITY, -- allowed additional ordinality columns
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa'
+ )
+ ) jt
+ ON true;
+ js | id | id2 | int | text | char(4) | bool | numeric | js | jb | jst | jsc | jsv | jsb | aaa | aaa1
+--------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+-----------+-----------+--------------+------+------+--------------+-----+------
+ 1 | 1 | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |
+ [] | | | | | | | | | | | | | | |
+ {} | 1 | 1 | | | | | | | | {} | {} | {} | {} | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | "2" | "2" | "2" | "2" | "2" | "2" | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | | | | | | | null | null | null | null | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | 0 | false | fals | f | | false | false | false | fals | fals | false | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 1 | true | true | t | | true | true | true | true | true | true | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | | | | | | | | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | 123 | 123
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | [1,2] | [1,2 | | | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | |
+ [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | "str" | "str | | | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | |
+ err | | | | | | | | | | | | | | |
+(14 rows)
+
+-- JSON_TABLE: Test backward parsing
+CREATE VIEW json_table_view AS
+SELECT * FROM
+ JSON_TABLE(
+ 'null' FORMAT JSON, 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ id2 FOR ORDINALITY, -- allowed additional ordinality columns
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa',
+ NESTED PATH '$[1]' AS p1 COLUMNS (
+ a1 int,
+ NESTED PATH '$[*]' AS "p1 1" COLUMNS (
+ a11 text
+ ),
+ b1 text
+ ),
+ NESTED PATH '$[2]' AS p2 COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1" COLUMNS (
+ a21 text
+ ),
+ NESTED PATH '$[*]' AS p22 COLUMNS (
+ a22 text
+ )
+ )
+ )
+ );
+\sv json_table_view
+CREATE OR REPLACE VIEW public.json_table_view AS
+ SELECT "json_table".id,
+ "json_table".id2,
+ "json_table"."int",
+ "json_table".text,
+ "json_table"."char(4)",
+ "json_table".bool,
+ "json_table"."numeric",
+ "json_table".js,
+ "json_table".jb,
+ "json_table".jst,
+ "json_table".jsc,
+ "json_table".jsv,
+ "json_table".jsb,
+ "json_table".aaa,
+ "json_table".aaa1,
+ "json_table".a1,
+ "json_table".b1,
+ "json_table".a11,
+ "json_table".a21,
+ "json_table".a22
+ FROM JSON_TABLE(
+ 'null'::text FORMAT JSON, '$[*]' AS json_table_path_1
+ PASSING
+ 1 + 2 AS a,
+ '"foo"'::json 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 '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc character(4) FORMAT JSON PATH '$',
+ jsv character varying(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa integer PATH '$."aaa"',
+ aaa1 integer PATH '$."aaa"',
+ NESTED PATH '$[1]' AS p1
+ COLUMNS (
+ a1 integer PATH '$."a1"',
+ b1 text PATH '$."b1"',
+ NESTED PATH '$[*]' AS "p1 1"
+ COLUMNS (
+ a11 text PATH '$."a11"'
+ )
+ ),
+ NESTED PATH '$[2]' AS p2
+ COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1"
+ COLUMNS (
+ a21 text PATH '$."a21"'
+ ),
+ NESTED PATH '$[*]' AS p22
+ COLUMNS (
+ a22 text PATH '$."a22"'
+ )
+ )
+ )
+ PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))
+ )
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM json_table_view;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "json_table"
+ Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".aaa, "json_table".aaa1, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
+ Table Function Call: JSON_TABLE('null'::text FORMAT JSON, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::json 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 '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb FORMAT JSON PATH '$', aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
+(3 rows)
+
+-- JSON_TABLE: ON EMPTY/ON ERROR behavior
+SELECT *
+FROM
+ (VALUES ('1'), ('err'), ('"err"')) vals(js),
+ JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$')) jt;
+ js | a
+-------+---
+ 1 | 1
+ "err" |
+(2 rows)
+
+SELECT *
+FROM
+ (VALUES ('1'), ('err'), ('"err"')) vals(js)
+ LEFT OUTER JOIN
+ JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt
+ ON true;
+ERROR: invalid input syntax for type json
+DETAIL: Token "err" is invalid.
+CONTEXT: JSON data, line 1: err
+SELECT *
+FROM
+ (VALUES ('1'), ('err'), ('"err"')) vals(js)
+ LEFT OUTER JOIN
+ JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
+ ON true;
+ERROR: invalid input syntax for integer: "err"
+SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
+ a
+---
+
+(1 row)
+
+SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
+ERROR: SQL/JSON member not found
+SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
+ERROR: no SQL/JSON item
+SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+ a
+---
+ 2
+(1 row)
+
+SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+ a
+---
+ 2
+(1 row)
+
+SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+ a
+---
+ 1
+(1 row)
+
+-- 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(
+ json '[]', '$' -- AS <path name> required here
+ COLUMNS (
+ foo int PATH '$'
+ )
+ PLAN DEFAULT (UNION)
+) jt;
+ERROR: invalid JSON_TABLE expression
+LINE 2: json '[]', '$'
+ ^
+DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used
+SELECT * FROM JSON_TABLE(
+ json '[]', '$' 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 (
+ ^
+DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used
+-- Should fail (column names anf path names shall be distinct)
+SELECT * FROM JSON_TABLE(
+ json '[]', '$' AS a
+ COLUMNS (
+ 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(
+ json '[]', '$' AS a
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c 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(
+ json '[]', '$'
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS b
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+ERROR: duplicate JSON_TABLE column name: b
+HINT: JSON_TABLE path names and column names shall be distinct from one another
+SELECT * FROM JSON_TABLE(
+ json '[]', '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ b int
+ ),
+ NESTED PATH '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+ )
+) jt;
+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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json 'null', 'strict $[*]' AS p0
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)))
+) jt;
+ bar | foo | baz
+-----+-----+-----
+(0 rows)
+
+SELECT * FROM JSON_TABLE(
+ json '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: json 'null', 'strict $[*]'
+ ^
+DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used
+-- JSON_TABLE: plan execution
+CREATE TEMP TABLE json_table_test (js text);
+INSERT INTO json_table_test
+VALUES (
+ '[
+ {"a": 1, "b": [], "c": []},
+ {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
+ {"a": 3, "b": [1, 2], "c": []},
+ {"x": "4", "b": [1, 2], "c": 123}
+ ]'
+);
+-- unspecified plan (outer, union)
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(11 rows)
+
+-- default plan (outer, union)
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (outer, union)
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(11 rows)
+
+-- specific plan (p outer (pb union pc))
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p outer (pb union pc))
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(11 rows)
+
+-- specific plan (p outer (pc union pb))
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p outer (pc union pb))
+ ) jt;
+ n | a | c | b
+---+----+----+---
+ 1 | 1 | |
+ 2 | 2 | 10 |
+ 2 | 2 | |
+ 2 | 2 | 20 |
+ 2 | 2 | | 1
+ 2 | 2 | | 2
+ 2 | 2 | | 3
+ 3 | 3 | | 1
+ 3 | 3 | | 2
+ 4 | -1 | | 1
+ 4 | -1 | | 2
+(11 rows)
+
+-- default plan (inner, union)
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (inner)
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(10 rows)
+
+-- specific plan (p inner (pb union pc))
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p inner (pb union pc))
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 2 | 2 | 1 |
+ 2 | 2 | 2 |
+ 2 | 2 | 3 |
+ 2 | 2 | | 10
+ 2 | 2 | |
+ 2 | 2 | | 20
+ 3 | 3 | 1 |
+ 3 | 3 | 2 |
+ 4 | -1 | 1 |
+ 4 | -1 | 2 |
+(10 rows)
+
+-- default plan (inner, cross)
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (cross, inner)
+ ) jt;
+ n | a | b | c
+---+---+---+----
+ 2 | 2 | 1 | 10
+ 2 | 2 | 1 |
+ 2 | 2 | 1 | 20
+ 2 | 2 | 2 | 10
+ 2 | 2 | 2 |
+ 2 | 2 | 2 | 20
+ 2 | 2 | 3 | 10
+ 2 | 2 | 3 |
+ 2 | 2 | 3 | 20
+(9 rows)
+
+-- specific plan (p inner (pb cross pc))
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p inner (pb cross pc))
+ ) jt;
+ n | a | b | c
+---+---+---+----
+ 2 | 2 | 1 | 10
+ 2 | 2 | 1 |
+ 2 | 2 | 1 | 20
+ 2 | 2 | 2 | 10
+ 2 | 2 | 2 |
+ 2 | 2 | 2 | 20
+ 2 | 2 | 3 | 10
+ 2 | 2 | 3 |
+ 2 | 2 | 3 | 20
+(9 rows)
+
+-- default plan (outer, cross)
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (outer, cross)
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 | 10
+ 2 | 2 | 1 |
+ 2 | 2 | 1 | 20
+ 2 | 2 | 2 | 10
+ 2 | 2 | 2 |
+ 2 | 2 | 2 | 20
+ 2 | 2 | 3 | 10
+ 2 | 2 | 3 |
+ 2 | 2 | 3 | 20
+ 3 | 3 | |
+ 4 | -1 | |
+(12 rows)
+
+-- specific plan (p outer (pb cross pc))
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan (p outer (pb cross pc))
+ ) jt;
+ n | a | b | c
+---+----+---+----
+ 1 | 1 | |
+ 2 | 2 | 1 | 10
+ 2 | 2 | 1 |
+ 2 | 2 | 1 | 20
+ 2 | 2 | 2 | 10
+ 2 | 2 | 2 |
+ 2 | 2 | 2 | 20
+ 2 | 2 | 3 | 10
+ 2 | 2 | 3 |
+ 2 | 2 | 3 | 20
+ 3 | 3 | |
+ 4 | -1 | |
+(12 rows)
+
+select
+ jt.*, b1 + 100 as b
+from
+ json_table (json
+ '[
+ {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]},
+ {"a": 2, "b": [10, 20], "c": [1, null, 2]},
+ {"x": "3", "b": [11, 22, 33, 44]}
+ ]',
+ '$[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on error,
+ nested path 'strict $.b[*]' as pb columns (
+ b text format json path '$',
+ nested path 'strict $[*]' as pb1 columns (
+ b1 int path '$'
+ )
+ ),
+ nested path 'strict $.c[*]' as pc columns (
+ c text format json path '$',
+ nested path 'strict $[*]' as pc1 columns (
+ c1 int path '$'
+ )
+ )
+ )
+ --plan default(outer, cross)
+ plan(p outer ((pb inner pb1) cross (pc outer pc1)))
+ ) jt;
+ n | a | b | b1 | c | c1 | b
+---+---+--------------+-----+------+----+-----
+ 1 | 1 | [1, 10] | 1 | 1 | | 101
+ 1 | 1 | [1, 10] | 1 | null | | 101
+ 1 | 1 | [1, 10] | 1 | 2 | | 101
+ 1 | 1 | [1, 10] | 10 | 1 | | 110
+ 1 | 1 | [1, 10] | 10 | null | | 110
+ 1 | 1 | [1, 10] | 10 | 2 | | 110
+ 1 | 1 | [2] | 2 | 1 | | 102
+ 1 | 1 | [2] | 2 | null | | 102
+ 1 | 1 | [2] | 2 | 2 | | 102
+ 1 | 1 | [3, 30, 300] | 3 | 1 | | 103
+ 1 | 1 | [3, 30, 300] | 3 | null | | 103
+ 1 | 1 | [3, 30, 300] | 3 | 2 | | 103
+ 1 | 1 | [3, 30, 300] | 30 | 1 | | 130
+ 1 | 1 | [3, 30, 300] | 30 | null | | 130
+ 1 | 1 | [3, 30, 300] | 30 | 2 | | 130
+ 1 | 1 | [3, 30, 300] | 300 | 1 | | 400
+ 1 | 1 | [3, 30, 300] | 300 | null | | 400
+ 1 | 1 | [3, 30, 300] | 300 | 2 | | 400
+ 2 | 2 | | | | |
+ 3 | | | | | |
+(20 rows)
+
+-- Should succeed (JSON arguments are passed to root and nested paths)
+SELECT *
+FROM
+ generate_series(1, 4) x,
+ generate_series(1, 3) y,
+ JSON_TABLE(json
+ '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
+ 'strict $[*] ? (@[*] < $x)'
+ PASSING x AS x, y AS y
+ COLUMNS (
+ y text FORMAT JSON PATH '$',
+ NESTED PATH 'strict $[*] ? (@ >= $y)'
+ COLUMNS (
+ z int PATH '$'
+ )
+ )
+ ) jt;
+ x | y | y | z
+---+---+-----------+---
+ 2 | 1 | [1,2,3] | 1
+ 2 | 1 | [1,2,3] | 2
+ 2 | 1 | [1,2,3] | 3
+ 3 | 1 | [1,2,3] | 1
+ 3 | 1 | [1,2,3] | 2
+ 3 | 1 | [1,2,3] | 3
+ 3 | 1 | [2,3,4,5] | 2
+ 3 | 1 | [2,3,4,5] | 3
+ 3 | 1 | [2,3,4,5] | 4
+ 3 | 1 | [2,3,4,5] | 5
+ 4 | 1 | [1,2,3] | 1
+ 4 | 1 | [1,2,3] | 2
+ 4 | 1 | [1,2,3] | 3
+ 4 | 1 | [2,3,4,5] | 2
+ 4 | 1 | [2,3,4,5] | 3
+ 4 | 1 | [2,3,4,5] | 4
+ 4 | 1 | [2,3,4,5] | 5
+ 4 | 1 | [3,4,5,6] | 3
+ 4 | 1 | [3,4,5,6] | 4
+ 4 | 1 | [3,4,5,6] | 5
+ 4 | 1 | [3,4,5,6] | 6
+ 2 | 2 | [1,2,3] | 2
+ 2 | 2 | [1,2,3] | 3
+ 3 | 2 | [1,2,3] | 2
+ 3 | 2 | [1,2,3] | 3
+ 3 | 2 | [2,3,4,5] | 2
+ 3 | 2 | [2,3,4,5] | 3
+ 3 | 2 | [2,3,4,5] | 4
+ 3 | 2 | [2,3,4,5] | 5
+ 4 | 2 | [1,2,3] | 2
+ 4 | 2 | [1,2,3] | 3
+ 4 | 2 | [2,3,4,5] | 2
+ 4 | 2 | [2,3,4,5] | 3
+ 4 | 2 | [2,3,4,5] | 4
+ 4 | 2 | [2,3,4,5] | 5
+ 4 | 2 | [3,4,5,6] | 3
+ 4 | 2 | [3,4,5,6] | 4
+ 4 | 2 | [3,4,5,6] | 5
+ 4 | 2 | [3,4,5,6] | 6
+ 2 | 3 | [1,2,3] | 3
+ 3 | 3 | [1,2,3] | 3
+ 3 | 3 | [2,3,4,5] | 3
+ 3 | 3 | [2,3,4,5] | 4
+ 3 | 3 | [2,3,4,5] | 5
+ 4 | 3 | [1,2,3] | 3
+ 4 | 3 | [2,3,4,5] | 3
+ 4 | 3 | [2,3,4,5] | 4
+ 4 | 3 | [2,3,4,5] | 5
+ 4 | 3 | [3,4,5,6] | 3
+ 4 | 3 | [3,4,5,6] | 4
+ 4 | 3 | [3,4,5,6] | 5
+ 4 | 3 | [3,4,5,6] | 6
+(52 rows)
+
+-- Should fail (JSON arguments are not passed to column paths)
+SELECT *
+FROM JSON_TABLE(
+ json '[1,2,3]',
+ '$[*] ? (@ < $x)'
+ PASSING 10 AS x
+ COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)')
+ ) jt;
+ERROR: could not find 'x' passed variable
-- Extension: non-constant JSON path
SELECT JSON_EXISTS(json '{"a": 123}', '$' || '.' || 'a');
json_exists
@@ -1081,3 +2049,8 @@ SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
SELECT JSON_QUERY(json '{"a": 123}', 'error' || ' ' || 'error');
ERROR: bad jsonpath representation
DETAIL: syntax error, unexpected IDENT_P at or near " "
+-- Should fail (not supported)
+SELECT * FROM JSON_TABLE(json '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
+ERROR: only string constants supported in JSON_TABLE path specification
+LINE 1: SELECT * FROM JSON_TABLE(json '{"a": 123}', '$' || '.' || 'a...
+ ^
diff --git a/src/test/regress/sql/json_sqljson.sql b/src/test/regress/sql/json_sqljson.sql
index 896941c..90c52c3 100644
--- a/src/test/regress/sql/json_sqljson.sql
+++ b/src/test/regress/sql/json_sqljson.sql
@@ -304,7 +304,590 @@ DROP TABLE test_json_constraints;
-- JSON_TABLE
-SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text));
+-- Should fail (JSON_TABLE can be used only in FROM clause)
+SELECT JSON_TABLE('[]', '$');
+
+-- Should fail (no columns)
+SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
+
+-- NULL => empty table
+SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS (foo int)) bar;
+
+-- invalid json => empty table
+SELECT * FROM JSON_TABLE('', '$' COLUMNS (foo int)) bar;
+SELECT * FROM JSON_TABLE('' FORMAT JSON, '$' COLUMNS (foo int)) bar;
+
+-- invalid json => error
+SELECT * FROM JSON_TABLE('' FORMAT JSON, '$' COLUMNS (foo int) ERROR ON ERROR) bar;
+
+--
+SELECT * FROM JSON_TABLE('123' FORMAT JSON, '$'
+ COLUMNS (item int PATH '$', foo int)) bar;
+
+SELECT * FROM JSON_TABLE(json '123', '$'
+ COLUMNS (item int PATH '$', foo int)) bar;
+
+-- JSON_TABLE: basic functionality
+SELECT *
+FROM
+ (VALUES
+ ('1'),
+ ('[]'),
+ ('{}'),
+ ('[1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]'),
+ ('err')
+ ) vals(js)
+ LEFT OUTER JOIN
+-- JSON_TABLE is implicitly lateral
+ JSON_TABLE(
+ vals.js FORMAT json, 'lax $[*]'
+ COLUMNS (
+ id FOR ORDINALITY,
+ id2 FOR ORDINALITY, -- allowed additional ordinality columns
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa'
+ )
+ ) jt
+ ON true;
+
+-- JSON_TABLE: Test backward parsing
+
+CREATE VIEW json_table_view AS
+SELECT * FROM
+ JSON_TABLE(
+ 'null' FORMAT JSON, 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ id2 FOR ORDINALITY, -- allowed additional ordinality columns
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$',
+ jsb jsonb FORMAT JSON PATH '$',
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa',
+ NESTED PATH '$[1]' AS p1 COLUMNS (
+ a1 int,
+ NESTED PATH '$[*]' AS "p1 1" COLUMNS (
+ a11 text
+ ),
+ b1 text
+ ),
+ NESTED PATH '$[2]' AS p2 COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1" COLUMNS (
+ a21 text
+ ),
+ NESTED PATH '$[*]' AS p22 COLUMNS (
+ a22 text
+ )
+ )
+ )
+ );
+
+\sv json_table_view
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM json_table_view;
+
+-- JSON_TABLE: ON EMPTY/ON ERROR behavior
+SELECT *
+FROM
+ (VALUES ('1'), ('err'), ('"err"')) vals(js),
+ JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$')) jt;
+
+SELECT *
+FROM
+ (VALUES ('1'), ('err'), ('"err"')) vals(js)
+ LEFT OUTER JOIN
+ JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt
+ ON true;
+
+SELECT *
+FROM
+ (VALUES ('1'), ('err'), ('"err"')) vals(js)
+ LEFT OUTER JOIN
+ JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
+ ON true;
+
+SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
+SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
+SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
+
+SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
+
+-- 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(
+ json '[]', '$' -- AS <path name> required here
+ COLUMNS (
+ foo int PATH '$'
+ )
+ PLAN DEFAULT (UNION)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ json '[]', '$' 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(
+ json '[]', '$' AS a
+ COLUMNS (
+ a int
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ json '[]', '$' AS a
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ json '[]', '$'
+ COLUMNS (
+ b int,
+ NESTED PATH '$' AS b
+ COLUMNS (
+ c int
+ )
+ )
+) jt;
+
+SELECT * FROM JSON_TABLE(
+ json '[]', '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ b int
+ ),
+ NESTED PATH '$'
+ COLUMNS (
+ NESTED PATH '$' AS a
+ COLUMNS (
+ c int
+ )
+ )
+ )
+) jt;
+
+-- JSON_TABLE: plan validation
+
+SELECT * FROM JSON_TABLE(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json '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(
+ json 'null', 'strict $[*]' -- without root path name
+ COLUMNS (
+ NESTED PATH '$' AS p1 COLUMNS (
+ NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+ NESTED PATH '$' AS p12 COLUMNS ( bar int )
+ ),
+ NESTED PATH '$' AS p2 COLUMNS (
+ NESTED PATH '$' AS p21 COLUMNS ( baz int )
+ )
+ )
+ PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))
+) jt;
+
+-- JSON_TABLE: plan execution
+
+CREATE TEMP TABLE json_table_test (js text);
+
+INSERT INTO json_table_test
+VALUES (
+ '[
+ {"a": 1, "b": [], "c": []},
+ {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
+ {"a": 3, "b": [1, 2], "c": []},
+ {"x": "4", "b": [1, 2], "c": 123}
+ ]'
+);
+
+-- unspecified plan (outer, union)
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ ) jt;
+
+-- default plan (outer, union)
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (outer, union)
+ ) jt;
+
+-- specific plan (p outer (pb union pc))
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'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
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'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
+ json_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (inner)
+ ) jt;
+
+-- specific plan (p inner (pb union pc))
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'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
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (cross, inner)
+ ) jt;
+
+-- specific plan (p inner (pb cross pc))
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'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
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+ )
+ plan default (outer, cross)
+ ) jt;
+
+-- specific plan (p outer (pb cross pc))
+select
+ jt.*
+from
+ json_table_test jtt,
+ json_table (
+ jtt.js FORMAT JSON,'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 (json
+ '[
+ {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]},
+ {"a": 2, "b": [10, 20], "c": [1, null, 2]},
+ {"x": "3", "b": [11, 22, 33, 44]}
+ ]',
+ '$[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on error,
+ nested path 'strict $.b[*]' as pb columns (
+ b text format json path '$',
+ nested path 'strict $[*]' as pb1 columns (
+ b1 int path '$'
+ )
+ ),
+ nested path 'strict $.c[*]' as pc columns (
+ c text format json path '$',
+ nested path 'strict $[*]' as pc1 columns (
+ c1 int path '$'
+ )
+ )
+ )
+ --plan default(outer, cross)
+ plan(p outer ((pb inner pb1) cross (pc outer pc1)))
+ ) jt;
+
+-- Should succeed (JSON arguments are passed to root and nested paths)
+SELECT *
+FROM
+ generate_series(1, 4) x,
+ generate_series(1, 3) y,
+ JSON_TABLE(json
+ '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
+ 'strict $[*] ? (@[*] < $x)'
+ PASSING x AS x, y AS y
+ COLUMNS (
+ y text FORMAT JSON PATH '$',
+ NESTED PATH 'strict $[*] ? (@ >= $y)'
+ COLUMNS (
+ z int PATH '$'
+ )
+ )
+ ) jt;
+
+-- Should fail (JSON arguments are not passed to column paths)
+SELECT *
+FROM JSON_TABLE(
+ json '[1,2,3]',
+ '$[*] ? (@ < $x)'
+ PASSING 10 AS x
+ COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)')
+ ) jt;
-- Extension: non-constant JSON path
SELECT JSON_EXISTS(json '{"a": 123}', '$' || '.' || 'a');
@@ -314,3 +897,5 @@ SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a');
SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
-- Should fail (invalid path)
SELECT JSON_QUERY(json '{"a": 123}', 'error' || ' ' || 'error');
+-- Should fail (not supported)
+SELECT * FROM JSON_TABLE(json '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
view thread (98+ messages) latest in thread
Message-ID: <78894d50-010d-0876-23cb-55cba86404e2@postgrespro.ru>
Permalink: ../78894d50-010d-0876-23cb-55cba86404e2@postgrespro.ru/
Also on: postgresql.org/message-id/78894d50-010d-0876-23cb-55cba86404e2@postgrespro.ru
copy link · copy postgr.es · copy git am
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: n.gluhov@postgrespro.ru, obartunov@gmail.com, andrew.dunstan@2ndquadrant.com, pavel.stehule@gmail.com
Subject: Re: SQL/JSON: JSON_TABLE
In-Reply-To: <78894d50-010d-0876-23cb-55cba86404e2@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox