Apex - Load Data from Json File to Table

     This blog provides an example of loading data from Json file into Table    

     Sample Json:-

{

  "department": {

    "department_number": 10,

    "department_name": "ACCOUNTING",

    "employees": [

      {

        "employee_number": 7782,

        "employee_name": "CLARK"

      },

      {

        "employee_number": 7839,

        "employee_name": "KING"

      },

      {

        "employee_number": 7934,

        "employee_name": "MILLER"

      }

    ]

  }

}

This file is stored in the below table in the column “order_document”

CREATE TABLE json_order (ID NUMBER NOT NULL,

                         order_document CLOB

                        )

Data loaded to table:

create table Json_emp (E_no      number,

     E_name varchar2(100),

     D_no      number,

     d_name varchar2(100)

 )

Sample code:

DECLARE

   l_json_text   CLOB;

   l_count       PLS_INTEGER;

   l_members     wwv_flow_t_varchar2;

   l_paths       apex_t_varchar2;

   l_exists      BOOLEAN;

BEGIN

   SELECT order_document

     INTO l_json_text

     FROM json_order;

   apex_json.parse (l_json_text);

   l_count := apex_json.get_count (p_path => 'department.employees');

   FOR i IN 1 .. l_count

   LOOP

      INSERT INTO json_emp

                  (e_no,

                   e_name,

                   d_no,

                   d_name

                  )

           VALUES (apex_json.get_number

                        (p_path      => 'department.employees[%d].employee_number',

                         p0          => i

                        ),

                   apex_json.get_varchar2

                          (p_path      => 'department.employees[%d].employee_name',

                           p0          => i

                          ),

                   apex_json.get_number

                                     (p_path      => 'department.department_number'),

                   apex_json.get_varchar2

                                       (p_path      => 'department.department_name')

                  );

   END LOOP;

   COMMIT;

END;

/