DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > Relational to JSON With APEX_JSON

Relational to JSON With APEX_JSON

APEX_JSON was primarily intended to be used by APEX developers — but there are some hooks that can allow it to be used as a standalone PL/SQL package.

Dan McGhan user avatar by
Dan McGhan
·
Oct. 20, 17 · Database Zone · Tutorial
Like (1)
Save
Tweet
8.50K Views

Join the DZone community and get the full member experience.

Join For Free

APEX_JSON is a PL/SQL API included with Oracle Application Express (APEX) 5.0 that provides utilities for parsing and generating JSON. While APEX_JSON was primarily intended to be used by APEX developers, there are some hooks that can allow it to be used as a standalone PL/SQL package.

Solution

The following solution uses APEX_JSON to create the JSON that represents a department in the HR schema. APEX_JSON basically writes JSON content to a buffer. By default, the buffer used is the HTP buffer in the database, as that's what APEX reads. But as you can see with line 27, it's possible to redirect the output to a CLOB buffer instead. Once we've redirected the output, we can make API calls to open/close objects and arrays and write values to them. When we're done writing out the JSON we can make a call to get_clob_output to get the JSON contents. I've highlighted some of the relevant lines...

create or replace function get_dept_apex_json(
   p_dept_id in departments.department_id%type
)

   return clob

is

   cursor manager_cur (
      p_manager_id in employees.employee_id%type
   )
   is
      select *
      from employees
      where employee_id = manager_cur.p_manager_id;

   l_date_format    constant varchar2(20) := 'DD-MON-YYYY';
   l_dept_rec       departments%rowtype;
   l_dept_json_clob clob;
   l_loc_rec        locations%rowtype;
   l_country_rec    countries%rowtype;
   l_manager_rec    manager_cur%rowtype;
   l_job_rec        jobs%rowtype;

begin

   apex_json.initialize_clob_output;

   select *
   into l_dept_rec
   from departments
   where department_id = get_dept_apex_json.p_dept_id;

   apex_json.open_object(); --department

   apex_json.write('id', l_dept_rec.department_id);
   apex_json.write('name', l_dept_rec.department_name);

   select *
   into l_loc_rec
   from locations
   where location_id = l_dept_rec.location_id;

   apex_json.open_object('location');

   apex_json.write('id', l_loc_rec.location_id);
   apex_json.write('streetAddress', l_loc_rec.street_address);
   apex_json.write('postalCode', l_loc_rec.postal_code);

   select *
   into l_country_rec
   from countries cou
   where cou.country_id = l_loc_rec.country_id;

   apex_json.open_object('country');

   apex_json.write('id', l_country_rec.country_id);
   apex_json.write('name', l_country_rec.country_name);
   apex_json.write('regionId', l_country_rec.region_id);

   apex_json.close_object(); --country

   apex_json.close_object(); --location

   open manager_cur(l_dept_rec.manager_id);
   fetch manager_cur into l_manager_rec;

   if manager_cur%found
   then
      apex_json.open_object('manager');

      apex_json.write('id', l_manager_rec.employee_id);
      apex_json.write('name', l_manager_rec.first_name || ' ' || l_manager_rec.last_name);
      apex_json.write('salary', l_manager_rec.salary);

      select *
      into l_job_rec
      from jobs job
      where job.job_id = l_manager_rec.job_id;

      apex_json.open_object('job');

      apex_json.write('id', l_job_rec.job_id);
      apex_json.write('title', l_job_rec.job_title);
      apex_json.write('minSalary', l_job_rec.min_salary);
      apex_json.write('maxSalary', l_job_rec.max_salary);

      apex_json.close_object(); --job

      apex_json.close_object(); --manager
   else
      apex_json.write('manager', '', p_write_null => true);
   end if;

   close manager_cur;

   apex_json.open_array('employees');

   for emp_rec in (
      select *
      from employees
      where department_id = l_dept_rec.department_id
   )
   loop
      apex_json.open_object(); --employee

      apex_json.write('id', emp_rec.employee_id);
      apex_json.write('name', emp_rec.first_name || ' ' || emp_rec.last_name);
      apex_json.write('isSenior', emp_rec.hire_date < to_date('01-jan-2005', 'dd-mon-yyyy'));
      apex_json.write('commissionPct', emp_rec.commission_pct, p_write_null => true);

      apex_json.open_array('jobHistory');

      for jh_rec in (
         select job_id,
            department_id,
            start_date,
            end_date
         from job_history
         where employee_id = emp_rec.employee_id
      )
      loop
          apex_json.open_object(); --job

          apex_json.write('id', jh_rec.job_id);
          apex_json.write('departmentId', jh_rec.department_id);
          apex_json.write('startDate', to_char(jh_rec.start_date, l_date_format));
          apex_json.write('endDate', to_char(jh_rec.end_date, l_date_format));

          apex_json.close_object(); --job
      end loop;

      apex_json.close_array(); --jobHistory

      apex_json.close_object(); --employee
   end loop;

   apex_json.close_array(); --employees
   apex_json.close_object(); --department

   l_dept_json_clob := apex_json.get_clob_output;

   apex_json.free_output;

   return l_dept_json_clob;

exception

   when others
   then      
      if manager_cur%isopen
      then
         close manager_cur;
      end if;

      raise;

end get_dept_apex_json;

Output

When passed a departmentId of 10 , the function returns a CLOB populated with JSON that matches the goal 100%.

{
  "id": 10,
  "name": "Administration",
  "location": {
    "id": 1700,
    "streetAddress": "2004 Charade Rd",
    "postalCode": "98199",
    "country": {
      "id": "US",
      "name": "United States of America",
      "regionId": 2
    }
  },
  "manager": {
    "id": 200,
    "name": "Jennifer Whalen",
    "salary": 4400,
    "job": {
      "id": "AD_ASST",
      "title": "Administration Assistant",
      "minSalary": 3000,
      "maxSalary": 6000
    }
  },
  "employees": [
    {
      "id": 200,
      "name": "Jennifer Whalen",
      "isSenior": true,
      "commissionPct": null,
      "jobHistory": [
        {
          "id": "AD_ASST",
          "departmentId": 90,
          "startDate": "17-SEP-1995",
          "endDate": "17-JUN-2001"
        },
        {
          "id": "AC_ACCOUNT",
          "departmentId": 90,
          "startDate": "01-JUL-2002",
          "endDate": "31-DEC-2006"
        }
      ]
    }
  ]
}

Summary

I really enjoyed working with APEX_JSON — it's my new "go-to" for PL/SQL based JSON generation. APEX_JSON has a very light footprint (it's just a single package) and it takes a minimalistic approach. Rather than compose objects as one would do with PL/JSON, you simply use the package to write JSON to a buffer.

This approach yields some performance benefits, as well. In a basic test where I generated the JSON for every department in the HR schema 100 times in a loop, the APEX_JSON-based solution finished at around 3.5 seconds whereas the PL/JSON based solution took around 17 seconds. That means APEX_JSON about 3.8 times faster than PL/JSON when it comes generating JSON and converting it to a CLOB.

Unfortunately, APEX_JSON is only included with APEX 5.0+. Upgrading your database's APEX instance seems a little extreme if all you want to do is work with JSON (though it is free and doesn't take too long), but if you already have APEX 5.0, then it's a very nice tool to be able to leverage.

JSON

Published at DZone with permission of Dan McGhan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Real-Time Supply Chain With Apache Kafka in the Food and Retail Industry
  • Role of Development Team in an Agile Environment
  • How to Optimize MySQL Queries for Speed and Performance
  • Stupid Things Orgs Do That Kill Productivity w/ Netflix, FloSports & Refactoring.club

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo