JSON_TABLE

The JSON_TABLE function transforms JSON data into a relational database table with given SQL columns.

Purpose

The JSON_TABLE function creates a relational view of JSON data. It maps parts of a JSON document into the rows and columns of a new virtual table. You can then use this virtual table to insert JSON data into an existing table, or you can use SQL to work with the data in the table.

A common use of JSON_TABLE is to create a non-materialized view of JSON data. You can use such a view just as you would use any table or view. Applications can operate on JSON data without consideration of JSON syntax or path expressions.

You specify the JSON_TABLE function in the FROM clause of a SELECT statement. The function generates a row of virtual relational table data for each JSON value selected by a row path expression (row pattern). Since the JSON value that matches a row path expressions generates a row of relational data, it is called a row source.

There is a required COLUMNS clause for the JSON_TABLE function. This clause defines the columns of the virtual table to be created. The COLUMNS clause evaluates the row source, finds specific JSON values within the row source, and returns those JSON values as SQL values in individual columns of a row of relational data. For details, see COLUMNS Clause of JSON_TABLE in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

There are two levels of error handling for JSON_TABLE, corresponding to the two levels of path expressions, row-level and column-level. When present, a column-level error handler overrides a row-level error handler. The default error handler for both levels is NULL ON ERROR.

Syntax

JSON_TABLE (expr, JSON_basic_path_expression 
[JSON_table_on_error_clause] [JSON_table_on_empty_clause] 
[JSON_table_on_mismatch_clause] JSON_columns_clause

expr

expr::= CHAR|NCHAR|VARCHAR2|NVARCHAR2|BLOB|CLOB|NCLOB|JSON

This is a required argument to the JSON_TABLE function.

The argument is a SQL expression, which can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. The result of evaluating the expression serves as the context item for evaluating the row path expression.

The argument is a SQL expression that returns an instance of a SQL data type that contains JSON data. The SQL expression accepts as input data types that include the following: CHAR, NCHAR, VARCHAR2, NVARCHAR2, BLOB, CLOB, NCLOB, and JSON.

If expr is null, then JSON_TABLE returns SQL NULL. If expr is not a text literal of well-formed JSON data using strict or lax syntax, then the function returns SQL NULL by default. You can use the JSON_table_on_error_clause to override this default behavior.

See JSON_TABLE Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

JSON_basic_path_expression

This is a required argument to the JSON_TABLE function.

For specific syntax, see JSON_EXISTS Condition in this book. For complete details about syntax and semantics, see SQL/JSON Path Expressions and JSON_TABLE Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

JSON_table_on_error_clause

{NULL | ERROR} ON ERROR

This optional clause applies to row-level error handling.

You can specify one of the following clauses or take the default:

  • NULL ON ERROR: Returns SQL NULL when an error occurs at the row-level. This is the default row-level error handling behavior.

  • ERROR ON ERROR: Returns the appropriate error when an error occurs at the row-level.

JSON_table_on_empty_clause

{NULL | ERROR} ON EMPTY

This optional clause applies to row-level error handling.

Use this clause to specify the value returned by this function if no match is found. This clause allows you to specify a different outcome for this type of error than the outcome specified with the JSON_table_on_error_clause. For example, if there is a no match found error, and you specify both the JSON_table_on_empty_clause and the JSON_table_on_error_clause, JSON_TABLE uses the JSON_table_on_empty_clause. Otherwise, JSON_TABLE uses the JSON_table_on_error_clause.

You can specify one of the following clauses or take the default:

  • NULL ON EMPTY: Returns SQL NULL when there is a no match error at the row-level. This is the default row-level handling behavior.

  • ERROR ON EMPTY: Returns the appropriate error when there is a no match error at the row-level.

JSON_table_on_mismatch_clause

({IGNORE|ERROR|NULL} ON MISMATCH [((MISSING DATA)|(EXTRA DATA)|(TYPE ERROR))])...

This optional clause applies to row-level mismatch errors. This error handles cases where a targeted JSON field type does not match the return type or if there is missing or extra data.

For details, see JSON_TABLE Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide

Here is a summary:

Mismatch errors can be of three types:

  • MISSING DATA: There is no result for the path expression.

  • EXTRA DATA: There is more data than expected.

  • TYPE ERROR: There is a data type mismatch.

For all three types of mismatch, you can specify the following:

  • IGNORE: Behaves the same as NULL ON MISMATCH. Use the NULL ON MISMATCH clause instead.

  • ERROR: Returns the appropriate error when a mismatch occurs.

  • NULL: Returns SQL NULL when a mismatch occurs.

If you specify ERROR ON MISMATCH or NULL ON MISMATCH, the same error handling applies to all three types of mismatch. The default is NULL ON MISMATCH.

This clause allows you to specify a different outcome for this type of error than the outcome specified with the JSON_table_on_error_clause. For example, if there is a mismatch error, and you specify both the JSON_table_on_mismatch_clause and the JSON_table_on_error_clause, JSON_TABLE uses the JSON_table_on_mismatch_clause. Otherwise, JSON_TABLE uses the JSON_table_on_error_clause.

JSON_columns_clause

COLUMNS (JSON_column_definition [, JSON_column_definition] ... )

The JSON_columns_clause is a required clause that defines the columns in the virtual relational table returned by the JSON_TABLE function.

JSON_column_definition

JSON_column_definition::= JSON_exists_column| JSON_query_column| 
JSON_value_column| JSON_nested_path | JSON_ordinality_column

The JSON_column_definition clause provides different ways for searching and evaluating JSON data. See the following sections for details.

JSON_exists_column

This clause evaluates JSON data in the same manner as the JSON_EXISTS condition. See JSON_EXISTS Condition in this book. For syntax and usage details, see JSON_TABLE Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

JSON_query_column

This clause evaluates JSON data in the same manner as the JSON_QUERY function. See JSON_QUERY in this book. For syntax and usage details, see JSON_TABLE Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

JSON_value_column

This clause evaluates JSON data in the same manner as the JSON_VALUE function. See JSON_VALUE in this book. For syntax and usage details, see JSON_TABLE Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

JSON_nested_path

Use this clause to flatten JSON values in a nested JSON object or JSON array into individual columns in a single row along with JSON values from the parent object or array. You can use this clause recursively to project data from multiple layers of nested objects or arrays into a single row.

For syntax and usage details, see JSON_TABLE Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

JSON_ordinality_column

JSON_ordinality_column::= column_name FOR ORDINALITY

This clause returns a column of generated row numbers. You can specify at most one JSON_ordinality_column.

For usage details, see JSON_TABLE Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

See Also