hi.
now you can
SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS
(foo int PATH '$'));
previously, top level path expression (in the above example ('$' || '.' || 'a'))
is hard coded as String A_Const. see gram.y:
if (!IsA($5, A_Const) ||
castNode(A_Const, $5)->val.node.type != T_String)
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("only string constants are supported in JSON_TABLE
path specification"),
parser_errposition(@5));
with the attached patch, this restriction is removed.
but JSON_TABLE COLUMN path expression is still required as String constant.
(maybe we can do the same but that requires grammar changes).
In transformJsonTable:
``tf->docexpr = transformExpr(pstate, (Node *) jfe, EXPR_KIND_FROM_FUNCTION);``
already did the parse analytics job for top level path expression, the same way
as JSON_QUERY.
we just need to take care of initializing and evaluating the top level path
expression.
--
jian
https://www.enterprisedb.com/
From c15c852e0997c32a53fe1a7291af51885d0efa79 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 28 Nov 2025 11:38:57 +0800
Subject: [PATCH v1 1/1] extend JSON_TABLE top level path expression
now you can:
SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int PATH '$'));
Discussion: https://postgr.es/m/
---
src/backend/nodes/makefuncs.c | 2 +-
src/backend/nodes/nodeFuncs.c | 2 +-
src/backend/parser/gram.y | 17 +-
src/backend/parser/parse_jsontable.c | 68 +++++---
src/backend/utils/adt/jsonpath_exec.c | 55 ++++--
src/backend/utils/adt/ruleutils.c | 2 +-
src/include/nodes/parsenodes.h | 2 +-
.../regress/expected/sqljson_jsontable.out | 158 ++++++++++++------
src/test/regress/sql/sqljson_jsontable.sql | 19 ++-
9 files changed, 227 insertions(+), 98 deletions(-)
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index e2d9e9be41a..a072fb2ec98 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -1008,7 +1008,7 @@ makeJsonTablePathSpec(char *string, char *name, int string_location,
JsonTablePathSpec *pathspec = makeNode(JsonTablePathSpec);
Assert(string != NULL);
- pathspec->string = makeStringConst(string, string_location);
+ pathspec->expr = makeStringConst(string, string_location);
if (name != NULL)
pathspec->name = pstrdup(name);
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index d228318dc72..b5a2e698526 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4202,7 +4202,7 @@ raw_expression_tree_walker_impl(Node *node,
}
break;
case T_JsonTablePathSpec:
- return WALK(((JsonTablePathSpec *) node)->string);
+ return WALK(((JsonTablePathSpec *) node)->expr);
case T_NullTest:
return WALK(((NullTest *) node)->arg);
case T_BooleanTest:
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c3a0a354a9c..09810419683 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -14435,17 +14435,16 @@ json_table:
')'
{
JsonTable *n = makeNode(JsonTable);
- char *pathstring;
+ JsonTablePathSpec *rootPathSpec = makeNode(JsonTablePathSpec);
n->context_item = (JsonValueExpr *) $3;
- if (!IsA($5, A_Const) ||
- castNode(A_Const, $5)->val.node.type != T_String)
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("only string constants are supported in JSON_TABLE path specification"),
- parser_errposition(@5));
- pathstring = castNode(A_Const, $5)->val.sval.sval;
- n->pathspec = makeJsonTablePathSpec(pathstring, $6, @5, @6);
+
+ rootPathSpec->expr = $5;
+ rootPathSpec->name = $6;
+ rootPathSpec->name_location = @6;
+ rootPathSpec->location = @5;
+ n->pathspec = rootPathSpec;
+
n->passing = $7;
n->columns = $10;
n->on_error = (JsonBehavior *) $12;
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 13d533b83f3..6ffb9a46353 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -20,12 +20,14 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
#include "parser/parse_clause.h"
+#include "parser/parse_coerce.h"
#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
#include "parser/parse_type.h"
#include "utils/fmgrprotos.h"
#include "utils/json.h"
+#include "utils/builtins.h"
#include "utils/lsyscache.h"
/* Context for transformJsonTableColumns() */
@@ -41,7 +43,8 @@ typedef struct JsonTableParseContext
static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt,
List *columns,
List *passingArgs,
- JsonTablePathSpec *pathspec);
+ JsonTablePathSpec *pathspec,
+ bool isTopLevel);
static JsonTablePlan *transformJsonTableNestedColumns(JsonTableParseContext *cxt,
List *passingArgs,
List *columns);
@@ -50,6 +53,7 @@ static JsonFuncExpr *transformJsonTableColumn(JsonTableColumn *jtc,
List *passingArgs);
static bool isCompositeType(Oid typid);
static JsonTablePlan *makeJsonTablePathScan(JsonTablePathSpec *pathspec,
+ bool isTopLevel,
bool errorOnError,
int colMin, int colMax,
JsonTablePlan *childplan);
@@ -68,7 +72,8 @@ static JsonTablePlan *makeJsonTableSiblingJoin(JsonTablePlan *lplan,
* (jt->context_item) and the column-generating expressions (jt->columns) to
* populate TableFunc.docexpr and TableFunc.colvalexprs, respectively. Also,
* the PASSING values (jt->passing) are transformed and added into
- * TableFunc.passingvalexprs.
+ * TableFunc.passingvalexprs. Top level path expression is being transformed and
+ * added into TableFunc.rowexpr too.
*/
ParseNamespaceItem *
transformJsonTable(ParseState *pstate, JsonTable *jt)
@@ -80,9 +85,6 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
bool is_lateral;
JsonTableParseContext cxt = {pstate};
- Assert(IsA(rootPathSpec->string, A_Const) &&
- castNode(A_Const, rootPathSpec->string)->val.node.type == T_String);
-
if (jt->on_error &&
jt->on_error->btype != JSON_BEHAVIOR_ERROR &&
jt->on_error->btype != JSON_BEHAVIOR_EMPTY &&
@@ -121,7 +123,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
jfe = makeNode(JsonFuncExpr);
jfe->op = JSON_TABLE_OP;
jfe->context_item = jt->context_item;
- jfe->pathspec = (Node *) rootPathSpec->string;
+ jfe->pathspec = rootPathSpec->expr;
jfe->passing = jt->passing;
jfe->on_empty = NULL;
jfe->on_error = jt->on_error;
@@ -137,15 +139,20 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
cxt.tf = tf;
tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns,
jt->passing,
- rootPathSpec);
+ rootPathSpec,
+ true);
/*
* Copy the transformed PASSING arguments into the TableFunc node, because
* they are evaluated separately from the JsonExpr that we just put in
* TableFunc.docexpr. JsonExpr.passing_values is still kept around for
* get_json_table().
+ *
+ * Copy the transformed top level path expression into the TableFunc node
+ * for the same reason as we copy the PASSING arguments.
*/
je = (JsonExpr *) tf->docexpr;
+ tf->rowexpr = copyObject(je->path_spec);
tf->passingvalexprs = copyObject(je->passing_values);
tf->ordinalitycol = -1; /* undefine ordinality column number */
@@ -248,7 +255,8 @@ generateJsonTablePathName(JsonTableParseContext *cxt)
static JsonTablePlan *
transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
List *passingArgs,
- JsonTablePathSpec *pathspec)
+ JsonTablePathSpec *pathspec,
+ bool isTopLevel)
{
ParseState *pstate = cxt->pstate;
JsonTable *jt = cxt->jt;
@@ -363,7 +371,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns);
/* Create a "parent" scan responsible for all columns handled above. */
- return makeJsonTablePathScan(pathspec, errorOnError, colMin, colMax,
+ return makeJsonTablePathScan(pathspec, isTopLevel, errorOnError, colMin, colMax,
childplan);
}
@@ -416,7 +424,7 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
JS_ENC_DEFAULT,
-1));
if (jtc->pathspec)
- pathspec = (Node *) jtc->pathspec->string;
+ pathspec = (Node *) jtc->pathspec->expr;
else
{
/* Construct default path as '$."column_name"' */
@@ -474,7 +482,7 @@ transformJsonTableNestedColumns(JsonTableParseContext *cxt,
jtc->pathspec->name = generateJsonTablePathName(cxt);
nested = transformJsonTableColumns(cxt, jtc->columns, passingArgs,
- jtc->pathspec);
+ jtc->pathspec, false);
if (plan)
plan = makeJsonTableSiblingJoin(plan, nested);
@@ -494,23 +502,39 @@ transformJsonTableNestedColumns(JsonTableParseContext *cxt,
* thus computed by 'childplan'.
*/
static JsonTablePlan *
-makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError,
+makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool isTopLevel, bool errorOnError,
int colMin, int colMax,
JsonTablePlan *childplan)
{
JsonTablePathScan *scan = makeNode(JsonTablePathScan);
- char *pathstring;
- Const *value;
-
- Assert(IsA(pathspec->string, A_Const));
- pathstring = castNode(A_Const, pathspec->string)->val.sval.sval;
- value = makeConst(JSONPATHOID, -1, InvalidOid, -1,
- DirectFunctionCall1(jsonpath_in,
- CStringGetDatum(pathstring)),
- false, false);
+
+ if (isTopLevel)
+ {
+ /*
+ * The transformed top level path expression is stored in the TableFunc
+ * node, but the original path name need preserved for deparsing
+ */
+ scan->path = makeNode(JsonTablePath);
+ scan->path->value = NULL;
+ scan->path->name = pathspec->name;
+ }
+ else
+ {
+ char *pathstring;
+ Const *value;
+
+ Assert(IsA(pathspec->expr, A_Const));
+ pathstring = castNode(A_Const, pathspec->expr)->val.sval.sval;
+
+ value = makeConst(JSONPATHOID, -1, InvalidOid, -1,
+ DirectFunctionCall1(jsonpath_in,
+ CStringGetDatum(pathstring)),
+ false, false);
+
+ scan->path = makeJsonTablePath(value, pathspec->name);
+ }
scan->plan.type = T_JsonTablePathScan;
- scan->path = makeJsonTablePath(value, pathspec->name);
scan->errorOnError = errorOnError;
scan->child = childplan;
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 8156695e97e..85efbc13796 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -355,6 +355,7 @@ static void JsonTableInitOpaque(TableFuncScanState *state, int natts);
static JsonTablePlanState *JsonTableInitPlan(JsonTableExecContext *cxt,
JsonTablePlan *plan,
JsonTablePlanState *parentstate,
+ NullableDatum *pathexpr,
List *args,
MemoryContext mcxt);
static void JsonTableSetDocument(TableFuncScanState *state, Datum value);
@@ -4105,8 +4106,8 @@ GetJsonTableExecContext(TableFuncScanState *state, const char *fname)
* JsonTableInitOpaque
* Fill in TableFuncScanState->opaque for processing JSON_TABLE
*
- * This initializes the PASSING arguments and the JsonTablePlanState for
- * JsonTablePlan given in TableFunc.
+ * This initializes the PASSING arguments, top-level path expression and the
+ * JsonTablePlanState for JsonTablePlan given in TableFunc.
*/
static void
JsonTableInitOpaque(TableFuncScanState *state, int natts)
@@ -4118,10 +4119,23 @@ JsonTableInitOpaque(TableFuncScanState *state, int natts)
JsonTablePlan *rootplan = (JsonTablePlan *) tf->plan;
JsonExpr *je = castNode(JsonExpr, tf->docexpr);
List *args = NIL;
+ NullableDatum pathexpr;
cxt = palloc0(sizeof(JsonTableExecContext));
cxt->magic = JSON_TABLE_EXEC_CONTEXT_MAGIC;
+ /*
+ * Evaluate JSON_TABLE() top-level path expression and save the value to
+ * pathexpr
+ */
+ if (state->rowexpr != NULL)
+ {
+ ExprState *pathexprstate = state->rowexpr;
+
+ pathexpr.value = ExecEvalExpr(pathexprstate, ps->ps_ExprContext,
+ &pathexpr.isnull);
+ }
+
/*
* Evaluate JSON_TABLE() PASSING arguments to be passed to the jsonpath
* executor via JsonPathVariables.
@@ -4163,7 +4177,7 @@ JsonTableInitOpaque(TableFuncScanState *state, int natts)
* Initialize plan for the root path and, recursively, also any child
* plans that compute the NESTED paths.
*/
- cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, NULL, args,
+ cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, NULL, &pathexpr, args,
CurrentMemoryContext);
state->opaque = cxt;
@@ -4193,6 +4207,7 @@ JsonTableDestroyOpaque(TableFuncScanState *state)
static JsonTablePlanState *
JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan,
JsonTablePlanState *parentstate,
+ NullableDatum *pathexpr,
List *args, MemoryContext mcxt)
{
JsonTablePlanState *planstate = palloc0(sizeof(*planstate));
@@ -4205,7 +4220,20 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan,
JsonTablePathScan *scan = (JsonTablePathScan *) plan;
int i;
- planstate->path = DatumGetJsonPathP(scan->path->value->constvalue);
+ /*
+ * The top-level path expression has already been evaluated and stored
+ * in pathexpr; see makeJsonTablePathScan and JsonTableInitOpaque also.
+ */
+ if (pathexpr)
+ {
+ if (pathexpr->isnull)
+ planstate->path = NULL;
+ else
+ planstate->path = DatumGetJsonPathP(pathexpr->value);
+ }
+ else
+ planstate->path = DatumGetJsonPathP(scan->path->value->constvalue);
+
planstate->args = args;
planstate->mcxt = AllocSetContextCreate(mcxt, "JsonTableExecContext",
ALLOCSET_DEFAULT_SIZES);
@@ -4218,15 +4246,15 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan,
cxt->colplanstates[i] = planstate;
planstate->nested = scan->child ?
- JsonTableInitPlan(cxt, scan->child, planstate, args, mcxt) : NULL;
+ JsonTableInitPlan(cxt, scan->child, planstate, NULL, args, mcxt) : NULL;
}
else if (IsA(plan, JsonTableSiblingJoin))
{
JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan;
- planstate->left = JsonTableInitPlan(cxt, join->lplan, parentstate,
+ planstate->left = JsonTableInitPlan(cxt, join->lplan, parentstate, NULL,
args, mcxt);
- planstate->right = JsonTableInitPlan(cxt, join->rplan, parentstate,
+ planstate->right = JsonTableInitPlan(cxt, join->rplan, parentstate, NULL,
args, mcxt);
}
@@ -4264,11 +4292,14 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item)
oldcxt = MemoryContextSwitchTo(planstate->mcxt);
- res = executeJsonPath(planstate->path, planstate->args,
- GetJsonPathVar, CountJsonPathVars,
- js, scan->errorOnError,
- &planstate->found,
- true);
+ if (planstate->path == NULL)
+ res = jperNotFound;
+ else
+ res = executeJsonPath(planstate->path, planstate->args,
+ GetJsonPathVar, CountJsonPathVars,
+ js, scan->errorOnError,
+ &planstate->found,
+ true);
MemoryContextSwitchTo(oldcxt);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..589ac2bd291 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12229,7 +12229,7 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit)
appendStringInfoString(buf, ", ");
- get_const_expr(root->path->value, context, -1);
+ get_rule_expr(jexpr->path_spec, context, showimplicit);
appendStringInfo(buf, " AS %s", quote_identifier(root->path->name));
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d14294a4ece..d08621c2074 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1874,7 +1874,7 @@ typedef struct JsonTablePathSpec
{
NodeTag type;
- Node *string;
+ Node *expr;
char *name;
ParseLoc name_location;
ParseLoc location; /* location of 'string' */
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 458c5aaa5b0..7f42a2c5c11 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -309,7 +309,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view2 AS
"numeric",
domain
FROM JSON_TABLE(
- 'null'::jsonb, '$[*]' AS json_table_path_0
+ 'null'::jsonb, '$[*]'::jsonpath AS json_table_path_0
PASSING
1 + 2 AS a,
'"foo"'::json AS "b c"
@@ -330,7 +330,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view3 AS
jsc,
jsv
FROM JSON_TABLE(
- 'null'::jsonb, '$[*]' AS json_table_path_0
+ 'null'::jsonb, '$[*]'::jsonpath AS json_table_path_0
PASSING
1 + 2 AS a,
'"foo"'::json AS "b c"
@@ -349,7 +349,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view4 AS
aaa,
aaa1
FROM JSON_TABLE(
- 'null'::jsonb, '$[*]' AS json_table_path_0
+ 'null'::jsonb, '$[*]'::jsonpath AS json_table_path_0
PASSING
1 + 2 AS a,
'"foo"'::json AS "b c"
@@ -366,7 +366,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view5 AS
exists2,
exists3
FROM JSON_TABLE(
- 'null'::jsonb, '$[*]' AS json_table_path_0
+ 'null'::jsonb, '$[*]'::jsonpath AS json_table_path_0
PASSING
1 + 2 AS a,
'"foo"'::json AS "b c"
@@ -385,7 +385,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
ta,
jba
FROM JSON_TABLE(
- 'null'::jsonb, '$[*]' AS json_table_path_0
+ 'null'::jsonb, '$[*]'::jsonpath AS json_table_path_0
PASSING
1 + 2 AS a,
'"foo"'::json AS "b c"
@@ -399,43 +399,43 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
)
)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table"
Output: "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS ("int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$'))
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]'::jsonpath AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS ("int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$'))
(3 rows)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table"
Output: "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES))
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]'::jsonpath AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES))
(3 rows)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table"
Output: "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"'))
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]'::jsonpath AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"'))
(3 rows)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table"
Output: "json_table".exists1, "json_table".exists2, "json_table".exists3
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR))
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]'::jsonpath AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR))
(3 rows)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view6;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table"
Output: "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES))
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]'::jsonpath AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES))
(3 rows)
-- JSON_TABLE() with alias
@@ -448,11 +448,11 @@ SELECT * FROM
"int" int PATH '$',
"text" text PATH '$'
)) json_table_func;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table" json_table_func
Output: id, "int", text
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, "int" integer PATH '$', text text PATH '$'))
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]'::jsonpath AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, "int" integer PATH '$', text text PATH '$'))
(3 rows)
EXPLAIN (COSTS OFF, FORMAT JSON, VERBOSE)
@@ -464,21 +464,21 @@ SELECT * FROM
"int" int PATH '$',
"text" text PATH '$'
)) json_table_func;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- [ +
- { +
- "Plan": { +
- "Node Type": "Table Function Scan", +
- "Parallel Aware": false, +
- "Async Capable": false, +
- "Table Function Name": "json_table", +
- "Alias": "json_table_func", +
- "Disabled": false, +
- "Output": ["id", "\"int\"", "text"], +
- "Table Function Call": "JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '\"foo\"'::jsonb AS \"b c\" COLUMNS (id FOR ORDINALITY, \"int\" integer PATH '$', text text PATH '$'))"+
- } +
- } +
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Table Function Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Table Function Name": "json_table", +
+ "Alias": "json_table_func", +
+ "Disabled": false, +
+ "Output": ["id", "\"int\"", "text"], +
+ "Table Function Call": "JSON_TABLE('null'::jsonb, '$[*]'::jsonpath AS json_table_path_0 PASSING 3 AS a, '\"foo\"'::jsonb AS \"b c\" COLUMNS (id FOR ORDINALITY, \"int\" integer PATH '$', text text PATH '$'))"+
+ } +
+ } +
]
(1 row)
@@ -703,11 +703,71 @@ FROM JSON_TABLE(
(3 rows)
--- Should fail (not supported)
-SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
-ERROR: only string constants are supported in JSON_TABLE path specification
-LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
- ^
+-----extension of top level path_expression
+SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int PATH '$'));
+ foo
+-----
+ 123
+(1 row)
+
+CREATE TABLE jstable (a jsonb, b text, c jsonpath, d text, e int);
+INSERT INTO jstable VALUES ('123', '$', '$', NULL, NULL), (NULL, '$', '$', NULL, NULL), ('{"a":123}', '$', '$.a', 'junk', NULL);
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, '$.a' COLUMNS (x int PATH '$')) bar;
+ a | x
+------------+-----
+ {"a": 123} | 123
+(1 row)
+
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, NULL COLUMNS (item int PATH '$')) bar;
+ a | item
+---+------
+(0 rows)
+
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, NULL::jsonb COLUMNS (item int PATH '$')) bar; --error
+ERROR: JSON path expression must be of type jsonpath, not of type jsonb
+LINE 1: ...ECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, NULL::json...
+ ^
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, b COLUMNS (item int PATH '$')) bar;
+ a | item
+------------+------
+ 123 | 123
+ {"a": 123} |
+(2 rows)
+
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, c COLUMNS (item int PATH '$')) bar;
+ a | item
+------------+------
+ 123 | 123
+ {"a": 123} | 123
+(2 rows)
+
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, d COLUMNS (item int PATH '$')) bar WHERE d <> 'junk';
+ a | item
+---+------
+(0 rows)
+
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, d COLUMNS (item int PATH '$')) bar; --error, 'junk' is not valid jsonpath expression
+ERROR: syntax error at end of jsonpath input
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, e COLUMNS (item int PATH '$')) bar; --error
+ERROR: JSON path expression must be of type jsonpath, not of type integer
+LINE 1: ...ECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, e COLUMNS ...
+ ^
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, '$' COLUMNS (item int PATH b)) bar; --error
+ERROR: syntax error at or near "b"
+LINE 1: ...ble t1, JSON_TABLE(t1.a, '$' COLUMNS (item int PATH b)) bar;
+ ^
+CREATE VIEW jstable_v1 AS SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, d COLUMNS (item int PATH '$')) bar;
+\sv jstable_v1
+CREATE OR REPLACE VIEW public.jstable_v1 AS
+ SELECT t1.a,
+ bar.item
+ FROM jstable t1,
+ LATERAL JSON_TABLE(
+ t1.a, t1.d::jsonpath AS json_table_path_0
+ COLUMNS (
+ item integer PATH '$'
+ )
+ ) bar
-- JsonPathQuery() error message mentioning column name
SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
ERROR: JSON path expression for column "b" must return single item when no wrapper is requested
@@ -885,7 +945,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS
a21,
a22
FROM JSON_TABLE(
- 'null'::jsonb, '$[*]' AS json_table_path_0
+ 'null'::jsonb, '$[*]'::jsonpath AS json_table_path_0
PASSING
1 + 2 AS a,
'"foo"'::json AS "b c"
@@ -1071,7 +1131,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view7 AS
( VALUES (23)) x(x),
generate_series(13, 13) y(y),
LATERAL JSON_TABLE(
- s.js, '$' AS c1
+ s.js, '$'::jsonpath AS c1
PASSING
x.x AS x,
y.y AS y
@@ -1140,7 +1200,7 @@ CREATE VIEW json_table_view8 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a t
CREATE OR REPLACE VIEW public.json_table_view8 AS
SELECT a
FROM JSON_TABLE(
- '"a"'::text, '$' AS json_table_path_0
+ '"a"'::text, '$'::jsonpath AS json_table_path_0
COLUMNS (
a text PATH '$'
)
@@ -1150,7 +1210,7 @@ CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a t
CREATE OR REPLACE VIEW public.json_table_view9 AS
SELECT a
FROM JSON_TABLE(
- '"a"'::text, '$' AS json_table_path_0
+ '"a"'::text, '$'::jsonpath AS json_table_path_0
COLUMNS (
a text PATH '$'
) ERROR ON ERROR
@@ -1162,7 +1222,7 @@ CREATE VIEW json_table_view8 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a t
CREATE OR REPLACE VIEW public.json_table_view8 AS
SELECT a
FROM JSON_TABLE(
- '"a"'::text, '$' AS json_table_path_0
+ '"a"'::text, '$'::jsonpath AS json_table_path_0
COLUMNS (
a text PATH '$'
)
@@ -1172,7 +1232,7 @@ CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a t
CREATE OR REPLACE VIEW public.json_table_view9 AS
SELECT a
FROM JSON_TABLE(
- '"a"'::text, '$' AS json_table_path_0
+ '"a"'::text, '$'::jsonpath AS json_table_path_0
COLUMNS (
a text PATH '$'
)
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 154eea79c76..e6b45c97a92 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -317,8 +317,23 @@ FROM JSON_TABLE(
COLUMNS (a text FORMAT JSON PATH '$ ? (@ < $y)')
) jt;
--- Should fail (not supported)
-SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
+-----extension of top level path_expression
+SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int PATH '$'));
+CREATE TABLE jstable (a jsonb, b text, c jsonpath, d text, e int);
+INSERT INTO jstable VALUES ('123', '$', '$', NULL, NULL), (NULL, '$', '$', NULL, NULL), ('{"a":123}', '$', '$.a', 'junk', NULL);
+
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, '$.a' COLUMNS (x int PATH '$')) bar;
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, NULL COLUMNS (item int PATH '$')) bar;
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, NULL::jsonb COLUMNS (item int PATH '$')) bar; --error
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, b COLUMNS (item int PATH '$')) bar;
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, c COLUMNS (item int PATH '$')) bar;
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, d COLUMNS (item int PATH '$')) bar WHERE d <> 'junk';
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, d COLUMNS (item int PATH '$')) bar; --error, 'junk' is not valid jsonpath expression
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, e COLUMNS (item int PATH '$')) bar; --error
+SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, '$' COLUMNS (item int PATH b)) bar; --error
+
+CREATE VIEW jstable_v1 AS SELECT t1.a, bar.* FROM jstable t1, JSON_TABLE(t1.a, d COLUMNS (item int PATH '$')) bar;
+\sv jstable_v1
-- JsonPathQuery() error message mentioning column name
SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
--
2.34.1
