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 --- doc/src/sgml/func.sgml | 334 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 334 insertions(+) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 192959ebc11..ff6901138d9 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18859,6 +18859,340 @@ DETAIL: Missing "]" after array dimensions. + + + JSON_TABLE + + json_table + + + + JSON_TABLE is an SQL/JSON function which + queries JSON data + and presents the results as a relational view, which can be accessed as a + regular SQL table. You can use JSON_TABLE inside + the FROM clause of a SELECT, + UPDATE, or DELETE and as data source + in a MERGE statement. + + + + Taking JSON data as input, JSON_TABLE uses a JSON path + expression to extract a part of the provided data to use as a + row pattern for the constructed view. Each SQL/JSON + value given by the row pattern serves as source for a separate row in the + constructed view. + + + + To split the row pattern into columns, JSON_TABLE + provides the COLUMNS clause that defines the + schema of the created view. For each column, a separate JSON path expression + can be specified to be evaluated against the row pattern to get an SQL/JSON + value that will become the value for the specified column in a given output + row. + + + + 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 + data. + + + + The syntax is: + + + +JSON_TABLE ( + context_item, path_expression AS json_path_name PASSING { value AS varname } , ... + COLUMNS ( json_table_column , ... ) + { ERROR | EMPTY } ON ERROR +) + + +where json_table_column is: + + name FOR ORDINALITY + | name type + FORMAT JSON ENCODING UTF8 + PATH path_expression + { WITHOUT | WITH { CONDITIONAL | UNCONDITIONAL } } ARRAY WRAPPER + { KEEP | OMIT } QUOTES ON SCALAR STRING + { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON EMPTY + { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT expression } ON ERROR + | name type EXISTS PATH path_expression + { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR + + + + Each syntax element is described below in more detail. + + + + + + context_item, path_expression AS json_path_name PASSING { value AS varname } , ... + + + + The input data to query (context_item), + the JSON path expression defining the query (path_expression) + with an optional name (json_path_name), and an + optional PASSING clause, which can provide data values + to the path_expression. The result of the input + data evaluation using the aforementioned elements is called the + row pattern, which is used as the source for row + values in the constructed view. + + + + + + + COLUMNS ( json_table_column , ... ) + + + + + The COLUMNS clause defining the schema of the + constructed view. In this clause, you can specify each column to be + filled with an SQL/JSON value obtained by applying a JSON path expression + against the row pattern. json_table_column has + the following variants: + + + + + + name FOR ORDINALITY + + + + Adds an ordinality column that provides sequential row numbering starting + from 1. + + + + + + + name type + FORMAT JSON ENCODING UTF8 + PATH path_expression + + + + Inserts an SQL/JSON value obtained by applying + path_expression against the row pattern into + the view's output row after coercing it to specified + type. + + + Specifying FORMAT JSON makes it explicit that you + expect the value to be a valid json object. It only + makes sense to specify FORMAT JSON if + type is one of bpchar, + bytea, character varying, name, + json, jsonb, text, or a domain over + these types. + + + Optionally, you can specify WRAPPER and + QUOTES clauses to format the output. Note that + specifying OMIT QUOTES overrides + FORMAT JSON if also specified, because unquoted + literals do not constitute valid json values. + + + Optionally, you can use ON EMPTY and + ON ERROR clauses to specify whether to throw the error + or return the specified value when the result of JSON path evaluation is + empty and when an error occurs during JSON path evaluation or when + coercing the SQL/JSON value to the specified type, respectively. The + default for both is to return a NULL value. + + + + This clause is internally turned into and has the same semantics as + JSON_VALUE or JSON_QUERY. + The latter if the specified type is not a scalar type or if either of + FORMAT JSON, WRAPPER, or + QUOTES clause is present. + + + + + + + + name type + EXISTS PATH path_expression + + + + Inserts a boolean value obtained by applying + path_expression against the row pattern + into the view's output row after coercing it to specified + type. + + + The value corresponds to whether applying the PATH + expression to the row pattern yields any values. + + + The specified type should have a cast from the + boolean type. + + + Optionally, you can use ON ERROR to specify whether to + throw the error or return the specified value when an error occurs during + JSON path evaluation or when coercing SQL/JSON value to the specified + type. The default is to return a boolean value + FALSE. + + + + This clause is internally turned into and has the same semantics as + JSON_EXISTS. + + + + + + + + + In each variant of json_table_column described + above, if the PATH clause is omitted, path expression + $.name is used, where + name is the provided column name. + + + + + + + + + AS json_path_name + + + + + The optional json_path_name serves as an + identifier of the provided path_expression. + The name must be unique and distinct from the column names. + + + + + + + { ERROR | EMPTY } ON ERROR + + + + + The optional ON ERROR can be used to specify how to + handle errors when evaluating the top-level + path_expression. Use ERROR + if you want the errors to be thrown and EMPTY to + return an empty table, that is, a table containing 0 rows. Note that + this clause does not affect the errors that occur when evaluating + columns, for which the behavior depends on whether the + ON ERROR clause is specified against a given column. + + + + + + Examples + + + In the examples that follow, the following table containing JSON data + will be used: + + +CREATE TABLE my_films ( js jsonb ); + +INSERT INTO my_films VALUES ( +'{ "favorites" : [ + { "kind" : "comedy", "films" : [ + { "title" : "Bananas", + "director" : "Woody Allen"}, + { "title" : "The Dinner Game", + "director" : "Francis Veber" } ] }, + { "kind" : "horror", "films" : [ + { "title" : "Psycho", + "director" : "Alfred Hitchcock" } ] }, + { "kind" : "thriller", "films" : [ + { "title" : "Vertigo", + "director" : "Alfred Hitchcock" } ] }, + { "kind" : "drama", "films" : [ + { "title" : "Yojimbo", + "director" : "Akira Kurosawa" } ] } + ] }'); + + + + + The following query shows how to use JSON_TABLE to + turn the JSON objects in the my_films table + to a view containing columns for the keys kind, + title, and director contained in + the original JSON along with an ordinality column: + + +SELECT jt.* FROM + my_films, + JSON_TABLE (js, '$.favorites[*]' COLUMNS ( + id FOR ORDINALITY, + kind text PATH '$.kind', + title text PATH '$.films[*].title' WITH WRAPPER, + director text PATH '$.films[*].director' WITH WRAPPER)) AS jt; + + + + id | kind | title | director +----+----------+--------------------------------+---------------------------------- + 1 | comedy | ["Bananas", "The Dinner Game"] | ["Woody Allen", "Francis Veber"] + 2 | horror | ["Psycho"] | ["Alfred Hitchcock"] + 3 | thriller | ["Vertigo"] | ["Alfred Hitchcock"] + 4 | drama | ["Yojimbo"] | ["Akira Kurosawa"] +(4 rows) + + + + + The following is a modified version of the above query to show the + usage of PASSING arguments in the filter specified in + the top-level JSON path expression and the various options for the + individual columns: + + +SELECT jt.* FROM + my_films, + JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)' + PASSING 'Alfred Hitchcock' AS filter, 'Vertigo' AS filter2 + COLUMNS ( + id FOR ORDINALITY, + kind text PATH '$.kind', + title text FORMAT JSON PATH '$.films[*].title' OMIT QUOTES, + director text PATH '$.films[*].director' KEEP QUOTES)) AS jt; + + + + id | kind | title | director +----+----------+---------+-------------------- + 1 | horror | Psycho | "Alfred Hitchcock" + 2 | thriller | Vertigo | "Alfred Hitchcock" +(2 rows) + + + + -- cgit v1.2.3