Custom JSON in APEX 5

I attended the excellent Tech 15 this December and was lucky enough to be able to speak about integrating external technologies with APEX. One of the things I demonstrated was my implementation of a Google Pie Chart in APEX in which I used the APEX_JSON package to output JSON in the exact format that Google wanted it. My previous blog talked through the simple way to generate JSON from the database to use on your page and in this blog I will show you how I implemented the Google Pie Chart.

This is the chart I created, utilising the charting API documented here. As I wanted to populate the chart with JSON data from the database, I looked at the JSON examples found here.

The JSON structure here doesn’t match what is returned when I simply pass the chart query to the APEX_JSON package so I decided to output the data myself using APEX_JSON.OPEN_OBJECT, APEX_JSON.OPEN_ARRAY and APEX_JSON.WRITE.

Because I only needed static labels for this chart, I just constructed the exact JSON I needed using function calls and string values. These label values could be dynamically selected from the database if needed, and I used my cursor to do just that for the data:

If I loop through my data I can write the JSON data in exactly the format that Google is expecting it. Once the loop is finished we can set the value of an APEX hidden item to be the JSON data and then use that within out call to the API:

This code fires on page load and draws the chart in a HTML region drawn on the page with the ID piechart.

This is a really simple way of getting JSON data from the database in exactly the format you want it and into an application item. This is really useful whenever you are incorporating an external technology from the online documentation and examples provided.

Author: Craig Sykes 

Job Title: Senior Oracle Development Consultant

Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.