In Oracle Database, the JSON_TABLE() function creates a relational view of JSON data. It allows you to present the values in a JSON document in table format – as rows and columns.

Syntax

The syntax goes like this:

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

Where:

• expr is the JSON document you want to query
• JSON_basic_path_expression is the SQL/JSON path expression.
• JSON_query_on_error_clause specifies the value returned when certain errors occur.
• JSON_query_on_empty_clause specifies the value returned if no match is found.
• JSON_columns_clause defines the columns in the virtual relational table returned by the function.

The JSON_TABLE() function can only be used in the FROM clause of a SELECT statement.

Example

Here’s an example to demonstrate how it works:

SELECT *
FROM JSON_TABLE('{a:1, b:2, c:3}', '$' COLUMNS(a, b, c));

Result:

   A    B    C 
____ ____ ____ 
1    2    3    

Here it is with slightly more data:

SELECT *
FROM JSON_TABLE(
    '[{a:1, b:2, c:3}, {a:4, b:5, c:6}, {a:7, b:8, c:9}]', 
    '$[*]' 
    COLUMNS(a, b, c)
    );

Result:

   A    B    C 
____ ____ ____ 
1    2    3    
4    5    6    
7    8    9   

Less Columns

Here’s what happens if we reduce the number of columns specified in the COLUMNS clause:

SELECT *
FROM JSON_TABLE(
    '[{a:1, b:2, c:3}, {a:4, b:5, c:6}, {a:7, b:8, c:9}]', 
    '$[*]' 
    COLUMNS(a, b)
    );

Result:

   A    B 
____ ____ 
1    2    
4    5    
7    8    

We can achieve the same outcome by specifying selected columns in the SELECT list:

SELECT a, b
FROM JSON_TABLE(
    '[{a:1, b:2, c:3}, {a:4, b:5, c:6}, {a:7, b:8, c:9}]', 
    '$[*]' 
    COLUMNS(a, b, c)
    );

Result:

   A    B 
____ ____ 
1    2    
4    5    
7    8    

Select Specific Rows

We can use clauses like WHERE to select only those rows that satisfy a certain criteria:

SELECT *
FROM JSON_TABLE(
    '[{a:1, b:2, c:3}, {a:4, b:5, c:6}, {a:7, b:8, c:9}]', 
    '$[*]' 
    COLUMNS(a, b, c)
    )
WHERE b = 5;

Result:

   A    B    C 
____ ____ ____ 
4    5    6    

Error Handling

We can use the following clauses to deal with errors:

• NULL ON ERROR
  • If the input is not well-formed JSON, no more rows are returned from the point at which the error is detected.
  • If no match is found when the row path expression is evaluated, no rows are returned.
  • Sets the default error behaviour for all column expressions to NULL ON ERROR
• ERROR ON ERROR
  • If the input is not well-formed JSON, an error is raised.
  • If no match is found when the row path expression is evaluated, an error will be raised
  • Sets the default error behaviour for all column expressions to ERROR ON ERROR

Here’s an example of NULL ON ERROR:

SELECT *
FROM JSON_TABLE(
    '[{a:1, b:2, c:3}, {a:4, b:5, c:6}, a:7, b:8, c:9]', 
    '$[*]'
    NULL ON ERROR
    COLUMNS(a, b, c)
    );

Result:

   A    B    C 
____ ____ ____ 
1    2    3    
4    5    6    

Since the JSON_TABLE() function supports streaming evaluation, rows can be returned prior to encountering the portion of the input with the error. That is exactly what we can see with this example – we got the first two rows, but not the third (due to that part of the JSON not being well-formed JSON).

Here it is with the ERROR ON ERROR clause:

SELECT *
FROM JSON_TABLE(
    '[{a:1, b:2, c:3}, {a:4, b:5, c:6}, a:7, b:8, c:9]', 
    '$[*]'
    ERROR ON ERROR
    COLUMNS(a, b, c)
    );

Result:

Error report -
ORA-40441: JSON syntax error

Handling Empty Results

You can also use a clause to specify the value returned 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 error clauses.

These clauses are:

• NULL ON EMPTY – Returns null when no match is found.
• ERROR ON EMPTY – Returns the 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 this function.

See the Oracle documentation for more information about the JSON_TABLE() function.