How to Parse Json Using APEX Data Parser in Oracle?
Sample JSON
{
"items":[
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7782"
},
"empno":7782,
"ename":"CLARK"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7839"
},
"empno":7839,
"ename":"KING"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7934"
},
"empno":7934,
"ename":"MILLER"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7876"
},
"empno":7876,
"ename":"ADAMS"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7902"
},
"empno":7902,
"ename":"FORD"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7566"
},
"empno":7566,
"ename":"JONES"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7788"
},
"empno":7788,
"ename":"SCOTT"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7369"
},
"empno":7369,
"ename":"SMITH"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7499"
},
"empno":7499,
"ename":"ALLEN"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7698"
},
"empno":7698,
"ename":"BLAKE"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7900"
},
"empno":7900,
"ename":"JAMES"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7654"
},
"empno":7654,
"ename":"MARTIN"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7844"
},
"empno":7844,
"ename":"TURNER"
},
{
"uri":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/7521"
},
"empno":7521,
"ename":"WARD"
}
],
"first":{
"$ref":"https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employeesfeed/"
}
}
Parsing Query
SELECT line_number,
col001,
col002,
col003
FROM TABLE (apex_data_parser.parse (p_content => (SELECT file_content
FROM loader_files
WHERE id = 3),
p_file_name => 'data.json',
p_skip_rows => 0));
file_content holds the whole JSON data.
Output