PL/SQL JSON_TABLE

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.

The basic syntax of the JSON_TABLE function is as follows:

JSON_TABLE(json_data, path_expression, columns_definition)

Where json_data is the JSON data to be converted, path_expression is the JSON path expression to identify the data to extract, and columns_definition is the list of columns to create in the output table.

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.