From bb766cde63b4f624d029b34c9cdd3d0a94fd5b46 Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Mon, 8 Apr 2024 15:58:58 +0900 Subject: JSON_TABLE: Add support for NESTED paths and columns MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit A NESTED path allows to extract data from nested levels of JSON objects given by the parent path expression, which are projected as columns specified using a nested COLUMNS clause, just like the parent COLUMNS clause. Rows comprised from a NESTED columns are "joined" to the row comprised from the parent columns. If a particular NESTED path evaluates to 0 rows, then the nested COLUMNS will emit NULLs, making it an OUTER join. NESTED columns themselves may include NESTED paths to allow extracting data from arbitrary nesting levels, which are likewise joined against the rows at the parent level. Multiple NESTED paths at a given level are called "sibling" paths and their rows are combined by UNIONing them, that is, after being joined against the parent row as described above. Author: Nikita Glukhov Author: Teodor Sigaev Author: Oleg Bartunov Author: Alexander Korotkov Author: Andrew Dunstan Author: Amit Langote Author: Jian He Reviewers have included (in no particular order): Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera, Jian He Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com --- src/backend/utils/adt/ruleutils.c | 60 ++++++++++++++++++++++++++++++++++++--- 1 file changed, 56 insertions(+), 4 deletions(-) (limited to 'src/backend/utils/adt/ruleutils.c') diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 466d9576a21..52bf87ac55b 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -524,8 +524,13 @@ static char *flatten_reloptions(Oid relid); static void get_reloptions(StringInfo buf, Datum reloptions); static void get_json_path_spec(Node *path_spec, deparse_context *context, bool showimplicit); -static void get_json_table_columns(TableFunc *tf, deparse_context *context, +static void get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan, + deparse_context *context, bool showimplicit); +static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan, + deparse_context *context, + bool showimplicit, + bool needcomma); #define only_marker(rte) ((rte)->inh ? "" : "ONLY ") @@ -11626,11 +11631,44 @@ get_xmltable(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, JsonTablePlan *plan, + deparse_context *context, bool showimplicit, + bool needcomma) +{ + if (IsA(plan, JsonTablePathScan)) + { + JsonTablePathScan *scan = castNode(JsonTablePathScan, plan); + + if (needcomma) + appendStringInfoChar(context->buf, ','); + + appendStringInfoChar(context->buf, ' '); + appendContextKeyword(context, "NESTED PATH ", 0, 0, 0); + get_const_expr(scan->path->value, context, -1); + appendStringInfo(context->buf, " AS %s", quote_identifier(scan->path->name)); + get_json_table_columns(tf, scan, context, showimplicit); + } + else if (IsA(plan, JsonTableSiblingJoin)) + { + JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan; + + get_json_table_nested_columns(tf, join->lplan, context, showimplicit, + needcomma); + get_json_table_nested_columns(tf, join->rplan, context, showimplicit, + true); + } +} + /* * get_json_table_columns - Parse back JSON_TABLE columns */ static void -get_json_table_columns(TableFunc *tf, deparse_context *context, +get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan, + deparse_context *context, bool showimplicit) { StringInfo buf = context->buf; @@ -11663,7 +11701,16 @@ get_json_table_columns(TableFunc *tf, deparse_context *context, typmod = lfirst_int(lc_coltypmod); colexpr = castNode(JsonExpr, lfirst(lc_colvalexpr)); - if (colnum > 0) + /* Skip columns that don't belong to this scan. */ + if (scan->colMin < 0 || colnum < scan->colMin) + { + colnum++; + continue; + } + if (colnum > scan->colMax) + break; + + if (colnum > scan->colMin) appendStringInfoString(buf, ", "); colnum++; @@ -11711,6 +11758,10 @@ get_json_table_columns(TableFunc *tf, deparse_context *context, get_json_expr_options(colexpr, context, default_behavior); } + if (scan->child) + get_json_table_nested_columns(tf, scan->child, context, showimplicit, + scan->colMin >= 0); + if (PRETTY_INDENT(context)) context->indentLevel -= PRETTYINDENT_VAR; @@ -11774,7 +11825,8 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit) context->indentLevel -= PRETTYINDENT_VAR; } - get_json_table_columns(tf, context, showimplicit); + get_json_table_columns(tf, castNode(JsonTablePathScan, tf->plan), context, + showimplicit); if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY) get_json_behavior(jexpr->on_error, context, "ERROR"); -- cgit v1.2.3