From de3600452b61d1bc3967e9e37e86db8956c8f577 Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Thu, 4 Apr 2024 19:57:08 +0900 Subject: Add basic JSON_TABLE() functionality MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit JSON_TABLE() allows JSON data to be converted into a relational view and thus used, for example, in a FROM clause, like other tabular data. Data to show in the view is selected from a source JSON object using a JSON path expression to get a sequence of JSON objects that's called a "row pattern", which becomes the source to compute the SQL/JSON values that populate the view's output columns. Column values themselves are computed using JSON path expressions applied to each of the JSON objects comprising the "row pattern", for which the SQL/JSON query functions added in 6185c9737cf4 are used. To implement JSON_TABLE() as a table function, this augments the TableFunc and TableFuncScanState nodes that are currently used to support XMLTABLE() with some JSON_TABLE()-specific fields. Note that the JSON_TABLE() spec includes NESTED COLUMNS and PLAN clauses, which are required to provide more flexibility to extract data out of nested JSON objects, but they are not implemented here to keep this commit of manageable size. 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/parser/parse_expr.c | 53 ++++++++++++++++++++++++++++++++++++++++- 1 file changed, 52 insertions(+), 1 deletion(-) (limited to 'src/backend/parser/parse_expr.c') diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 73c83cea4ac..81e66020858 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4245,7 +4245,8 @@ transformJsonSerializeExpr(ParseState *pstate, JsonSerializeExpr *expr) } /* - * Transform JSON_VALUE, JSON_QUERY, JSON_EXISTS functions into a JsonExpr node. + * Transform JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_TABLE functions into + * a JsonExpr node. */ static Node * transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) @@ -4269,6 +4270,9 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) func_name = "JSON_VALUE"; default_format = JS_FORMAT_DEFAULT; break; + case JSON_TABLE_OP: + func_name = "JSON_TABLE"; + break; default: elog(ERROR, "invalid JsonFuncExpr op %d", (int) func->op); break; @@ -4350,6 +4354,42 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) jsexpr->returning->typmod = -1; } + /* JSON_TABLE() COLUMNS can specify a non-boolean type. */ + if (jsexpr->returning->typid != BOOLOID) + { + Node *coercion_expr; + CaseTestExpr *placeholder = makeNode(CaseTestExpr); + int location = exprLocation((Node *) jsexpr); + + /* + * We abuse CaseTestExpr here as placeholder to pass the + * result of evaluating JSON_EXISTS to the coercion + * expression. + */ + placeholder->typeId = BOOLOID; + placeholder->typeMod = -1; + placeholder->collation = InvalidOid; + + coercion_expr = + coerce_to_target_type(pstate, (Node *) placeholder, BOOLOID, + jsexpr->returning->typid, + jsexpr->returning->typmod, + COERCION_EXPLICIT, + COERCE_IMPLICIT_CAST, + location); + + if (coercion_expr == NULL) + ereport(ERROR, + (errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot cast type %s to %s", + format_type_be(BOOLOID), + format_type_be(jsexpr->returning->typid)), + parser_coercion_errposition(pstate, location, (Node *) jsexpr))); + + if (coercion_expr != (Node *) placeholder) + jsexpr->coercion_expr = coercion_expr; + } + jsexpr->on_error = transformJsonBehavior(pstate, func->on_error, JSON_BEHAVIOR_FALSE, jsexpr->returning); @@ -4414,6 +4454,17 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) jsexpr->returning); break; + case JSON_TABLE_OP: + if (!OidIsValid(jsexpr->returning->typid)) + { + jsexpr->returning->typid = exprType(jsexpr->formatted_expr); + jsexpr->returning->typmod = -1; + } + jsexpr->on_error = transformJsonBehavior(pstate, func->on_error, + JSON_BEHAVIOR_EMPTY, + jsexpr->returning); + break; + default: elog(ERROR, "invalid JsonFuncExpr op %d", (int) func->op); break; -- cgit v1.2.3