Oracle JSON_TABLE() Function

Oracle JSON_TABLE() is a built-in function that creates a relational view of JSON data, converting the JSON data into rows and columns in a table.

Oracle `JSON_TABLE()` Syntax

Here is the syntax for the Oracle JSON_TABLE() function:

JSON_TABLE
  ( expr [ FORMAT JSON ] [ , path ]
    [ JSON_table_on_error_clause ] [ JSON_table_on_empty_clause ]
    JSON_columns_clause )

Parameters

expr

Required. Specifies the JSON data to be queried. For expr, specify an expression that evaluates to a text literal. If expr is a column, the column must be of data type VARCHAR2, CLOB, or BLOB. If expr is empty, the function returns null.

FORMAT JSON

Optional. If expr is a column of data type BLOB, FORMAT JSON must be specified.

path

Optional. Specifies a SQL/JSON path expression. This function evaluates expr using the path expression and looks for scalar JSON values that match or satisfy the path expression. The path expression must be a text literal.

JSON_table_on_error_clause

Optional. You can use this clause to specify the value returned by the function when an error occurs. Errors include: input that is not well-formed JSON text, or no match is found when evaluating row path expressions, or other errors. You can use the following clauses:

NULL ON ERROR
ERROR ON ERROR

JSON_table_on_empty_clause

Optional. You can use this clause to specify the value returned by the function when no match is found when evaluating the JSON data using a SQL/JSON path expression. You can specify the following clauses:

NULL ON EMPTY - returns null when no match is found.
ERROR ON EMPTY - returns an appropriate Oracle error when no match is found.
DEFAULT literal ON EMPTY - returns literal when no match is found. The data type of literal must match the data type of the value returned by the function.

JSON_columns_clause

Required. This clause is used to define the columns in the virtual relational table returned by the JSON_TABLE function.

Return Value

The Oracle JSON_TABLE() function returns a result set that is derived from the given JSON data.

Oracle `JSON_TABLE()` Examples

Here are some examples demonstrating the usage of the Oracle JSON_TABLE() function.

Example 1

The following example converts a JSON object into a relational view:

SELECT *
FROM JSON_TABLE('{x:1, y:2}', '$' COLUMNS(x, y));

输出：

X    Y
____ ____
1    2

Example 2

The following example converts a JSON array containing multiple JSON objects into a relational view:

SELECT * FROM JSON_TABLE('[{x:1, y:2},{x:3, y:4}]', '$[*]' COLUMNS(x, y));

Output:

outputCopy code

`X Y

1 2 3 4`

Example 3

You can use a WHERE statement to filter the rows returned by the JSON_TABLE() function:

SELECT *
FROM JSON_TABLE('[{x:1, y:2},{x:3, y:4}]', '$[*]' COLUMNS(x, y));

输出：

X    Y
____ ____
1    2
3    4

Conclusion