From 9cef65304305f54d34cf08b2d0fc7e4988caf798 Mon Sep 17 00:00:00 2001 From: jian he Date: Tue, 2 Apr 2024 14:41:37 +0800 Subject: [PATCH v47 1/1] propagate passing clause to every json_table_column regardless of nesting level JSON_TABLE ( context_item, path_expression [ AS json_path_name ] [ PASSING { value AS varname } [, ...] ] COLUMNS ( json_table_column [, ...] ) [ { ERROR | EMPTY } ON ERROR ] ) as you can see the JSON_TABLE syntax, we can only have one passing clause in json_table on the top level. for all the json_table_column clauses (COLUMNS clause), we don't have a passing clause. but during JsonTableInitPathScan, for each output columns associated JsonTablePlanState we already initialized the PASSING arguments via `planstate->args = args;` also transformJsonTableColumn already has a passingArgs argument. technically we can use the jsonpath variable for every output columns regardless of columns nesting level. so we did it. now the passing clause transformed JsonPathVariable can be used for every column regardless of nesting level. I also did some minor miscellaneous fixes. --- doc/src/sgml/func.sgml | 2 +- src/backend/parser/parse_jsontable.c | 24 ++- .../ecpg/test/sql/sqljson_jsontable.pgc | 2 +- .../regress/expected/sqljson_jsontable.out | 168 +++++++++++++++++- src/test/regress/sql/sqljson_jsontable.sql | 82 ++++++++- 5 files changed, 265 insertions(+), 13 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 024655bd..b1845641 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18901,7 +18901,7 @@ DETAIL: Missing "]" after array dimensions. The rows produced by JSON_TABLE are laterally joined to the row that generated them, so you do not have to explicitly join the constructed view with the original table holding JSON - + Each NESTED PATH clause can generate one or more columns. Columns produced by NESTED PATHs at the diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c index ab1ffc1a..9a2f4ef3 100644 --- a/src/backend/parser/parse_jsontable.c +++ b/src/backend/parser/parse_jsontable.c @@ -42,6 +42,7 @@ typedef struct JsonTableParseContext static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, + List *passing_Args, JsonTablePathSpec *pathspec); static Node *transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, @@ -57,9 +58,11 @@ static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt, static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name); static char *generateJsonTablePathName(JsonTableParseContext *cxt); static Node *transformJsonTableChildPlan(JsonTableParseContext *cxt, - List *columns); + List *columns, + List *passing_Args); static Node *transformNestedJsonTableColumn(JsonTableParseContext *cxt, - JsonTableColumn *jtc); + JsonTableColumn *jtc, + List *passing_Args); static Node *makeJsonTableSiblingJoin(Node *lnode, Node *rnode); /* @@ -136,6 +139,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt) cxt.jt = jt; cxt.tf = tf; tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns, + jfe->passing, rootPathSpec); /* @@ -244,6 +248,7 @@ generateJsonTablePathName(JsonTableParseContext *cxt) */ static JsonTablePlan * transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, + List *passing_Args, JsonTablePathSpec *pathspec) { ParseState *pstate = cxt->pstate; @@ -315,7 +320,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, param->typeMod = -1; je = transformJsonTableColumn(rawc, (Node *) param, - NIL, errorOnError); + passing_Args, errorOnError); colexpr = transformExpr(pstate, je, EXPR_KIND_FROM_FUNCTION); assign_expr_collations(pstate, colexpr); @@ -329,7 +334,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, continue; default: - elog(ERROR, "unknown JSON_TABLE column type: %d", rawc->coltype); + elog(ERROR, "unknown JSON_TABLE column type: %d", (int) rawc->coltype); break; } @@ -343,7 +348,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns, colMax = list_length(tf->colvalexprs) - 1; /* Transform recursively nested columns */ - childplan = transformJsonTableChildPlan(cxt, columns); + childplan = transformJsonTableChildPlan(cxt, columns, passing_Args); return makeJsonTablePlan(pathspec, errorOnError, colMin, colMax, childplan); } @@ -420,7 +425,8 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr, */ static Node * transformJsonTableChildPlan(JsonTableParseContext *cxt, - List *columns) + List *columns, + List *passing_Args) { Node *plan = NULL; ListCell *lc; @@ -434,7 +440,7 @@ transformJsonTableChildPlan(JsonTableParseContext *cxt, if (col->coltype != JTC_NESTED) continue; - nested = transformNestedJsonTableColumn(cxt, col); + nested = transformNestedJsonTableColumn(cxt, col, passing_Args); /* join transformed node with previous sibling nodes */ if (plan) @@ -448,12 +454,14 @@ transformJsonTableChildPlan(JsonTableParseContext *cxt, static Node * transformNestedJsonTableColumn(JsonTableParseContext *cxt, - JsonTableColumn *jtc) + JsonTableColumn *jtc, + List *passing_Args) { if (jtc->pathspec->name == NULL) jtc->pathspec->name = generateJsonTablePathName(cxt); return (Node *) transformJsonTableColumns(cxt, jtc->columns, + passing_Args, jtc->pathspec); } diff --git a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc index 9cd1498b..a51adab8 100644 --- a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc +++ b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc @@ -29,7 +29,7 @@ main () NESTED PATH '$' AS p2 COLUMNS ( NESTED PATH '$' AS p21 COLUMNS ( baz int ) ) - ); + )); EXEC SQL DISCONNECT; diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index fb4e1da9..ef6a46a5 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -873,7 +873,6 @@ FROM 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]', @@ -881,9 +880,174 @@ FROM JSON_TABLE( PASSING 10 AS x COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)') ) jt; -ERROR: could not find jsonpath variable "x" + y +--- + 1 + 2 + 3 +(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}', '$' || '.' || '... ^ +drop table if exists s; +NOTICE: table "s" does not exist, skipping +create table s(js jsonb); +insert into s select '{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]}]},"c": 3}'; +insert into s select '{"a":{"za":[{"z1": [21,4222]},{"z21": [32, 134,1345]}]},"c": 10}'; +--passing clause apply to path_expression. +--expepct zero rows, does not meet the filter expression +SELECT sub.* FROM s, JSON_TABLE(js, '$.c ? (@ > $x)' PASSING 10 AS x COLUMNS( + xx text path '$' +))sub; + xx +---- +(0 rows) + +--passing clause apply to path_expression, expect return 2 row +SELECT sub.* FROM s, JSON_TABLE(js, '$.c ? (@ <= $x)' PASSING 10 AS x COLUMNS( + xx text path '$' +))sub; + xx +---- + 3 + 10 +(2 rows) + +--nested path with nested path +SELECT sub.* FROM s, + (values(23)) x(x), + generate_series(13, 13) y, + JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns (NESTED PATH '$.z21[*]' as z21 COLUMNS (a12 int path '$')), + NESTED PATH '$.a.za[0]' as n4 columns (NESTED PATH '$.z1[*]' as z1 COLUMNS (a13 int path '$'))) + )sub; + xx | a12 | a13 +----+------+------ + 3 | 22 | + 3 | 234 | + 3 | 2345 | + 3 | | 11 + 3 | | 2222 + 10 | 32 | + 10 | 134 | + 10 | 1345 | + 10 | | 21 + 10 | | 4222 +(10 rows) + +--should error +SELECT sub.* FROM s, + JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns + (NESTED PATH '$.z21[*]' as n2 COLUMNS (z21 int path '$?(@ >= $"x")' error on error)) + ))sub; +ERROR: no SQL/JSON item +SELECT sub.*, x, y FROM s, + (values(22)) x(x), + generate_series(234, 234) y, + JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns + (NESTED PATH '$.z21[*]' as n2 + COLUMNS (z21 int path '$?(@ == $"x" || @ == $"y" )' default 0 on empty)), + NESTED PATH '$.a.za[0]' as n4 columns + (NESTED PATH '$.z1[*]' as n3 + COLUMNS (z1 int path '$?(@ > $"y" + 1988)' default 0 on empty))) + )sub; + xx | z21 | z1 | x | y +----+-----+------+----+----- + 3 | 22 | | 22 | 234 + 3 | 234 | | 22 | 234 + 3 | 0 | | 22 | 234 + 3 | | 0 | 22 | 234 + 3 | | 0 | 22 | 234 + 10 | 0 | | 22 | 234 + 10 | 0 | | 22 | 234 + 10 | 0 | | 22 | 234 + 10 | | 0 | 22 | 234 + 10 | | 4222 | 22 | 234 +(10 rows) + +SELECT sub.* FROM s, + (values(234)) x(x), + generate_series(21, 21) y, + JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1].z21[*] ?(@ >= $"x")' as n1 columns (z21 int path '$'), + NESTED PATH '$.a.za[0].z1[*] ?(@ >= $"y")' as n2 columns (z1 int path '$')))sub; + xx | z21 | z1 +----+------+------ + 3 | 234 | + 3 | 2345 | + 3 | | 2222 + 10 | 1345 | + 10 | | 21 + 10 | | 4222 +(6 rows) + +SELECT sub.* FROM s, + JSON_TABLE(js, '$' PASSING 23 AS x, 234 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1].z21[*] ?(@ >= $"x")' as n1 + columns (z21 int path '$?(@ <= $"y")' default -1 on empty), + NESTED PATH '$.a.za[0].z1[*] ?(@ >= $"y")' as n4 + columns (z1 int path '$?(@ <= $"x" * 100)' default -1 on empty))) sub; + xx | z21 | z1 +----+-----+------ + 3 | 234 | + 3 | -1 | + 3 | | 2222 + 10 | 32 | + 10 | 134 | + 10 | -1 | + 10 | | -1 +(7 rows) + +SELECT sub.* FROM s, + JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns (NESTED PATH '$.z21[*]' as n2 COLUMNS (z21 int path '$?(@ >= $"x")')), + NESTED PATH '$.a.za[0]' as n4 columns (NESTED PATH '$.z1[*]' as n3 COLUMNS (z1 int path '$?(@ >= $"y")'))) + )sub; + xx | z21 | z1 +----+------+------ + 3 | | + 3 | 234 | + 3 | 2345 | + 3 | | + 3 | | 2222 + 10 | 32 | + 10 | 134 | + 10 | 1345 | + 10 | | 21 + 10 | | 4222 +(10 rows) + +SELECT sub.* FROM s, + JSON_TABLE(js, '$' PASSING 32 AS x, 2223 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns + (NESTED PATH '$.z21[*]' as n2 COLUMNS (z21 int path '$?(@ >= $"x")' default 0 on empty)), + NESTED PATH '$.a.za[0]' as n4 columns + (NESTED PATH '$.z1[*]' as n3 COLUMNS (z1 int path '$?(@ >= $"y")' default 0 on empty ))) + )sub; + xx | z21 | z1 +----+------+------ + 3 | 0 | + 3 | 234 | + 3 | 2345 | + 3 | | 0 + 3 | | 0 + 10 | 32 | + 10 | 134 | + 10 | 1345 | + 10 | | 0 + 10 | | 4222 +(10 rows) + +drop table s; diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 22629188..1be5ba0d 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -396,7 +396,6 @@ FROM ) ) jt; --- Should fail (JSON arguments are not passed to column paths) SELECT * FROM JSON_TABLE( jsonb '[1,2,3]', @@ -407,3 +406,84 @@ FROM JSON_TABLE( -- Should fail (not supported) SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)); + +drop table if exists s; +create table s(js jsonb); +insert into s select '{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]}]},"c": 3}'; +insert into s select '{"a":{"za":[{"z1": [21,4222]},{"z21": [32, 134,1345]}]},"c": 10}'; + +--passing clause apply to path_expression. +--expepct zero rows, does not meet the filter expression +SELECT sub.* FROM s, JSON_TABLE(js, '$.c ? (@ > $x)' PASSING 10 AS x COLUMNS( + xx text path '$' +))sub; + +--passing clause apply to path_expression, expect return 2 row +SELECT sub.* FROM s, JSON_TABLE(js, '$.c ? (@ <= $x)' PASSING 10 AS x COLUMNS( + xx text path '$' +))sub; + +--nested path with nested path +SELECT sub.* FROM s, + (values(23)) x(x), + generate_series(13, 13) y, + JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns (NESTED PATH '$.z21[*]' as z21 COLUMNS (a12 int path '$')), + NESTED PATH '$.a.za[0]' as n4 columns (NESTED PATH '$.z1[*]' as z1 COLUMNS (a13 int path '$'))) + )sub; + +--should error +SELECT sub.* FROM s, + JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns + (NESTED PATH '$.z21[*]' as n2 COLUMNS (z21 int path '$?(@ >= $"x")' error on error)) + ))sub; + +SELECT sub.*, x, y FROM s, + (values(22)) x(x), + generate_series(234, 234) y, + JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns + (NESTED PATH '$.z21[*]' as n2 + COLUMNS (z21 int path '$?(@ == $"x" || @ == $"y" )' default 0 on empty)), + NESTED PATH '$.a.za[0]' as n4 columns + (NESTED PATH '$.z1[*]' as n3 + COLUMNS (z1 int path '$?(@ > $"y" + 1988)' default 0 on empty))) + )sub; + +SELECT sub.* FROM s, + (values(234)) x(x), + generate_series(21, 21) y, + JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1].z21[*] ?(@ >= $"x")' as n1 columns (z21 int path '$'), + NESTED PATH '$.a.za[0].z1[*] ?(@ >= $"y")' as n2 columns (z1 int path '$')))sub; + +SELECT sub.* FROM s, + JSON_TABLE(js, '$' PASSING 23 AS x, 234 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1].z21[*] ?(@ >= $"x")' as n1 + columns (z21 int path '$?(@ <= $"y")' default -1 on empty), + NESTED PATH '$.a.za[0].z1[*] ?(@ >= $"y")' as n4 + columns (z1 int path '$?(@ <= $"x" * 100)' default -1 on empty))) sub; + +SELECT sub.* FROM s, + JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns (NESTED PATH '$.z21[*]' as n2 COLUMNS (z21 int path '$?(@ >= $"x")')), + NESTED PATH '$.a.za[0]' as n4 columns (NESTED PATH '$.z1[*]' as n3 COLUMNS (z1 int path '$?(@ >= $"y")'))) + )sub; + +SELECT sub.* FROM s, + JSON_TABLE(js, '$' PASSING 32 AS x, 2223 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' as n1 columns + (NESTED PATH '$.z21[*]' as n2 COLUMNS (z21 int path '$?(@ >= $"x")' default 0 on empty)), + NESTED PATH '$.a.za[0]' as n4 columns + (NESTED PATH '$.z1[*]' as n3 COLUMNS (z1 int path '$?(@ >= $"y")' default 0 on empty ))) + )sub; + +drop table s; \ No newline at end of file base-commit: 7aa00f1360e0c6938fdf32d3fbb8b847b6098b88 prerequisite-patch-id: 98e54cda59b4c1906dac45238e194573d0037d2c prerequisite-patch-id: 521030bebfaee72fe06021d0ca85739c7e95bacd -- 2.34.1