PL/SQL JSON_TABLE - Oracle PL/SQL Tutorial

JSON_TABLE is a function in Oracle PL/SQL that allows you to extract data from a JSON document and display it in a tabular format. This function was introduced in Oracle Database 12c Release 2 and is used to convert JSON data into relational data for easy analysis.

JSON_TABLE allows you to convert JSON data into relational format, making it easier to work with in a traditional relational database environment. It provides a way to map the structure of a JSON document to the structure of database tables, allowing you to extract data from JSON documents and insert it into relational tables.

The basic syntax of the JSON_TABLE function is as follows:

JSON_TABLE (
  json_data CLOB,
  path_expression VARCHAR2,
  columns expression FOR ORDINALITY
    ON ERROR {ON ERROR | NULL ON ERROR}
    {column_definitions}
)

Let’s break down the components:

json_data: This is the input JSON data that you want to query. It can be a JSON document or a JSON array.

path_expression: This is a SQL/JSON path expression that defines the structure of the input JSON data. It specifies the elements or properties to be extracted from the JSON data. The path expression is used to navigate through the JSON hierarchy.

columns: This specifies the columns to be extracted from the JSON data. Each column is defined using the column_definitions clause. You can extract scalar values, nested JSON objects, or arrays.

expression FOR ORDINALITY: This clause is optional and is used to generate an ordinal number for each row returned by the JSON_TABLE function. It can be useful when dealing with arrays in JSON data.

ON ERROR {ON ERROR | NULL ON ERROR}: This clause is optional and specifies how the function should handle errors during the processing of JSON data. It can be set to either ON ERROR to raise an error, or NULL ON ERROR to return NULL for the entire JSON_TABLE invocation if an error occurs.

Here is an example of how to use the JSON_TABLE function in Oracle PL/SQL:

SELECT *
FROM JSON_TABLE('{
    "employees": [
        { "id": "1", "name": "John Doe", "title": "Manager" },
        { "id": "2", "name": "Jane Smith", "title": "Developer" },
        { "id": "3", "name": "Bob Johnson", "title": "Developer" }
    ]
}', '$.employees[*]'
COLUMNS (
    emp_id PATH '$.id',
    emp_name PATH '$.name',
    emp_title PATH '$.title'
));

In this example, we have a JSON document with an array of employee objects. We use the JSON_TABLE function to extract the data from the employees array and create a table with columns for emp_id, emp_name, and emp_title.

The PATH clause in the COLUMNS definition specifies the JSON path expression to extract the data for each column.

The output of this query will be a table with three columns, emp_id, emp_name, and emp_title, with one row for each employee object in the JSON data.

Overall, the JSON_TABLE function is a useful tool for working with JSON data in Oracle PL/SQL, as it allows you to easily convert complex JSON data structures into relational tables for analysis and manipulation.

It allows for the seamless integration of JSON and relational data, making it easier for developers to work with diverse data formats within the database environment. Whether you’re dealing with JSON documents representing complex structures or simple arrays, JSON_TABLE provides a flexible and efficient way to transform and query your data.

Learn PL/SQL