CODEWITHSUNDEEP
jsonplsqloracle-apex
tako_tokyo
tako_tokyo

Reputation: 79

How to parse a JSON array inside an APEX web service request's body

The body of a certain REST request has the following JSON defined:

{
    "employees": [{
            "employee_id": 900,
            "first_name": "a",
            "last_name": "Sato",
            "email": "a.sato",
            "hire_date": "01-06-2018",
            "job_id": "AD_PRES"
        },
        {
            "employee_id": 901,
            "first_name": "b",
            "last_name": "Sato",
            "email": "aas.sato",
            "hire_date": "01-06-2018",
            "job_id": "AD_PRES"
        }
    ]
}

Is there a way to refer to the request body (i.e.JSON string above) and parse the it so I can save them to my database via PL/SQL.

APEX version is 5.1.1.00.08

Upvotes: 2

Views: 2632

Answers (1)

Vih Damarques
Vih Damarques

Reputation: 371

You can do this using the APEX_JSON package.

declare
  p_json clob := '
  {
    "employees": [{
            "employee_id": 900,
            "first_name": "a",
            "last_name": "Sato",
            "email": "a.sato",
            "hire_date": "01-06-2018",
            "job_id": "AD_PRES"
        },
        {
            "employee_id": 901,
            "first_name": "b",
            "last_name": "Sato",
            "email": "aas.sato",
            "hire_date": "01-06-2018",
            "job_id": "AD_PRES"
        }
    ]
  }';

  type t_employee is record (
    employee_id number
   ,first_name varchar2(255)
   ,last_name varchar2(255)
   ,email varchar2(255)
   ,hire_date date
   ,job_id varchar2(255)
  );
  type t_employees is table of t_employee index by pls_integer;

  l_employees t_employees;
begin
  apex_json.parse(p_json);

  for i in 1 .. apex_json.get_count(p_path => 'employees') loop
    l_employees(i).employee_id := apex_json.get_varchar2(p_path => 'employees[%d].employee_id', p0 => i);
    l_employees(i).first_name  := apex_json.get_varchar2(p_path => 'employees[%d].first_name',  p0 => i);
    l_employees(i).last_name   := apex_json.get_varchar2(p_path => 'employees[%d].last_name',   p0 => i);
    l_employees(i).email       := apex_json.get_varchar2(p_path => 'employees[%d].email',       p0 => i);
    l_employees(i).job_id      := apex_json.get_varchar2(p_path => 'employees[%d].job_id',      p0 => i);
    l_employees(i).hire_date   := to_date(apex_json.get_varchar2(p_path => 'employees[%d].hire_date', p0 => i), 'mm-dd-yyyy');
  end loop;

  -- Do what you want here with l_employees
  -- In this example I'm just outputing it.
  for i in 1 .. l_employees.count loop
    dbms_output.put_line (
      'employee_id(' || i || ') => ' || l_employees(i).employee_id || ' / ' ||
      'first_name(' || i || ') => ' || l_employees(i).first_name || ' / ' ||
      'last_name(' || i || ') => ' || l_employees(i).last_name || ' / ' ||
      'email(' || i || ') => ' || l_employees(i).email || ' / ' ||
      'job_id(' || i || ') => ' || l_employees(i).job_id || ' / ' ||
      'hire_date(' || i || ') => ' || l_employees(i).hire_date
    );
  end loop;
end;

If you're using 12c, you can also use JSON_TABLE which is a way better and faster way.

Upvotes: 3

Related Questions