If your database version is Oracle 12c, you can utilize json_table as shown below:
(Keep in mind that a parent tag is required. In this example, "row" is used)
SELECT jsn.*
FROM JSON_TABLE('{"row":[{"type": "Campaign","currentStatus": "Active","id": "206","depth": "complete","folderId": "1428","name": "Car Loan","isReadOnly": "false"},{"type": "Debate","currentStatus": "Active","id": "207","depth": "pending","folderId": "1429","name": "House Loan","isReadOnly": "true"}] }'
, '$.row[*]'
COLUMNS (type VARCHAR2(50 CHAR) PATH '$.type',
currentStatus VARCHAR2(50 CHAR) PATH '$.currentStatus',
id VARCHAR2(50 CHAR) PATH '$.id',
depth VARCHAR2(50 CHAR) PATH '$.depth',
folderId VARCHAR2(50 CHAR) PATH '$.folderId',
name VARCHAR2(50 CHAR) PATH '$.name',
isReadOnly VARCHAR2(50 CHAR) PATH '$.isReadOnly')) jsn;
If your database version is not Oracle 12c, you can opt for apex_json. You can use a pipeline function for reporting purposes like:
-- Define the necessary types to support the table function.
DROP TYPE t_tf_tab;
DROP TYPE t_tf_row;
CREATE TYPE t_tf_row AS OBJECT (
type varchar2(50),
currentStatus varchar2(50),
id varchar2(50),
depth varchar2(50),
folderId varchar2(50),
name varchar2(50),
isReadOnly varchar2(50)
);
/
CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/
-- Create the table function.
CREATE OR REPLACE FUNCTION get_tab_tf RETURN t_tf_tab AS
l_tab t_tf_tab := t_tf_tab();
l_json CLOB := '{"row": [ {
"type": "Campaign",
"currentStatus": "Active",
"id": "206",
"depth": "complete",
"folderId": "1428",
"name": "Car Loan",
"isReadOnly": "false"
},
{
"type": "Debate",
"currentStatus": "Active",
"id": "207",
"depth": "pending",
"folderId": "1429",
"name": "House Loan",
"isReadOnly": "true"
}
] }';
j apex_json.t_values;
l_path VARCHAR2(100);
BEGIN
apex_json.parse(l_json);
FOR i IN 1..apex_json.get_count(p_path => 'row')
LOOP
l_tab.extend;
l_tab(l_tab.last) := t_tf_row( apex_json.get_varchar2( p_path => 'row[%d].type', p0 => i),
apex_json.get_varchar2( p_path => 'row[%d].currentStatus', p0 => i),
apex_json.get_varchar2( p_path => 'row[%d].id', p0 => i),
apex_json.get_varchar2( p_path => 'row[%d].depth', p0 => i),
apex_json.get_varchar2( p_path => 'row[%d].folderId', p0 => i),
apex_json.get_varchar2( p_path => 'row[%d].name', p0 => i),
apex_json.get_varchar2( p_path => 'row[%d].isReadOnly', p0 => i)
);
END LOOP;
RETURN l_tab;
END;
/
-- Test the table function.
SELECT *
FROM TABLE(get_tab_tf)
ORDER BY id DESC;