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.
