For example:ĬOLUMNS (linenum FOR ORDINALITY, ProductID) It consists of the keyword COLUMNS followed by the following entries enclosed in parentheses:Īt most one entry in the COLUMNS clause can be a column name followed by the keywords FOR ORDINALITY, which specifies a column of generated row numbers (SQL data type NUMBER). The mandatory COLUMNS clause defines the columns of the virtual table to be created by json_table. The default error handler for both levels is NULL ON ERROR. When present, a column error handler overrides row-level error handling. There are two levels of error handling for json_table, corresponding to the two levels of path expressions: row and column. )Īnd in cases where the row path expression is only '$', which targets the entire document, you can omit that second argument altogether. Json_table(t.j.ShippingInstructions.Phone. Json_table(t.j, '$.ShippingInstructions.Phone'. For example, these two queries are equivalent: (You can still use an error clause, and the COLUMNS clause is still required.) Dot notation specifies a table or view column together with a simple path to the targeted JSON data. (There is no RETURNING clause.)Īs an alternative, you can use the simple dot-notation syntax instead of the first argument and the row path expression. The second argument to json_table is the SQL/JSON row path expression followed by an optional error clause for handling the row and the (required) COLUMNS clause. The result of evaluating the expression is used as the context item for evaluating the row path expression. It can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. The first argument to json_table is a SQL expression. json_table generates zero or more new rows, as determined by evaluating the row path expression against the input document. Typically a json_table invocation is laterally joined, implicitly, with a source table in the FROM list, whose rows each contain a JSON document that is used as input to the function. The columns of each generated row are defined by the column path expressions of the COLUMNS clause. It is a row source: it generates a row of virtual-table data for each JSON value selected by a row path expression (row pattern). To change the schema, just redefine the view - no need to reorganize the underlying JSON data. The view exposes only data that conforms to the mapping (schema) that defines the view. Such a schema (mapping) imposes no restriction on the kind of JSON documents that can be stored in the database (other than being well-formed JSON data). This mapping is after the fact: the underlying JSON data can be defined and created without any regard to a schema or any particular pattern of use. This lets applications, tools, and programmers operate on JSON data without consideration of the syntax of JSON or JSON path expressions.ĭefining a view over JSON data in effect maps a kind of schema onto that data. You can use such a view just as you would use any table or view. You can then insert this virtual table into a pre-existing database table, or you can query it using SQL - in a join expression, for example.Ī common use of json_table is to create a view of JSON data.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |