How to Parse JSON Using APEX Data Parser in Oracle?

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 

You might like

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *