hi. https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-TABLE JSON_TABLE ( context_item, path_expression [ AS json_path_name ] [ PASSING { value AS varname } [, ...] ]
I quickly realized I didn't have tests that path_expression is not String A_Const and PASSING clause is not NULL. so I changed existing tests to cover this scenario. The rest of the patch remains the same as v1. -- jian https://www.enterprisedb.com/
From 62bbb8adedae52842c87ff459a84cb97090282f1 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Fri, 28 Nov 2025 12:31:22 +0800 Subject: [PATCH v2 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/cacjufxfvupppztsfdfof4akbsyhgadlhejuijpitbj+vcth...@mail.gmail.com --- 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 | 168 ++++++++++++------ src/test/regress/sql/sqljson_jsontable.sql | 29 ++- 9 files changed, 239 insertions(+), 106 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..6e28a67322f 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) @@ -692,7 +692,7 @@ FROM JSON_TABLE( SELECT * FROM JSON_TABLE( jsonb '[1,2,3]', - '$[*] ? (@ < $x)' + '$[*] ? (@ < $x)'::jsonpath PASSING 10 AS x, 3 AS y COLUMNS (a text FORMAT JSON PATH '$ ? (@ < $y)') ) jt; @@ -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 @@ -814,13 +874,14 @@ from (11 rows) -- PASSING arguments are passed to nested paths and their columns' paths -SELECT * +SELECT x.*, y.*, jt.* FROM generate_series(1, 3) x, generate_series(1, 3) y, + (VALUES ('strict $[*] ? (@[*] <= $x)'::jsonpath)) zzz(zzz), JSON_TABLE(jsonb '[[1,2,3],[2,3,4,5],[3,4,5,6]]', - 'strict $[*] ? (@[*] <= $x)' + zzz.zzz PASSING x AS x, y AS y COLUMNS ( y text FORMAT JSON PATH '$', @@ -885,7 +946,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" @@ -988,7 +1049,8 @@ SELECT sub.* FROM s, SELECT sub.* FROM s, (VALUES (23)) x(x), generate_series(13, 13) y, - JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y + (VALUES ('$'::jsonpath)) z(z), + JSON_TABLE(js, z.z AS c1 PASSING x AS x, y AS y COLUMNS ( xx1 int PATH '$.c', NESTED PATH '$.a.za[1]' @@ -1071,7 +1133,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 +1202,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 +1212,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 +1224,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 +1234,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..f7842e6ba49 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -312,13 +312,28 @@ FROM JSON_TABLE( SELECT * FROM JSON_TABLE( jsonb '[1,2,3]', - '$[*] ? (@ < $x)' + '$[*] ? (@ < $x)'::jsonpath PASSING 10 AS x, 3 AS y 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)); @@ -407,13 +422,14 @@ from -- PASSING arguments are passed to nested paths and their columns' paths -SELECT * +SELECT x.*, y.*, jt.* FROM generate_series(1, 3) x, generate_series(1, 3) y, + (VALUES ('strict $[*] ? (@[*] <= $x)'::jsonpath)) zzz(zzz), JSON_TABLE(jsonb '[[1,2,3],[2,3,4,5],[3,4,5,6]]', - 'strict $[*] ? (@[*] <= $x)' + zzz.zzz PASSING x AS x, y AS y COLUMNS ( y text FORMAT JSON PATH '$', @@ -491,7 +507,8 @@ SELECT sub.* FROM s, SELECT sub.* FROM s, (VALUES (23)) x(x), generate_series(13, 13) y, - JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y + (VALUES ('$'::jsonpath)) z(z), + JSON_TABLE(js, z.z AS c1 PASSING x AS x, y AS y COLUMNS ( xx1 int PATH '$.c', NESTED PATH '$.a.za[1]' -- 2.34.1
