Simplifying JSON in Oracle APEX 24.2: A Step-by-Step Guide

No more parsing JSON in SQL!

Oracle APEX’s 24.2 new Shared Component, JSON Source, lets you directly connect and display JSON data in reports with minimal effort. Let’s dive into how this innovative feature redefines application development. 

Step 1: Create a JSON Table with Sequence and Trigger

To begin utilizing Oracle APEX's JSON Source feature, the first step is to create a table that stores JSON data. To make the process efficient, we’ll also include:

1. A Sequence to generate unique IDs.
2. A Trigger to automatically assign IDs and populate "who columns" for audit purposes (CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATED_DATE).

CREATE TABLE employee_json_t (

      employee_id          NUMBER PRIMARY KEY

    , employee_details  CLOB CHECK ( employee_details IS JSON )

    , status                     CHAR(1)

    , created_by             VARCHAR2(255)

    , creation_date         TIMESTAMP(6)

    , updated_by            VARCHAR2(255)

    , updated_date         TIMESTAMP(6)

);

/

CREATE SEQUENCE employee_id_seq 

      MINVALUE 0 

      MAXVALUE 999999999999999 

      INCREMENT BY 1 

      START WITH 1 

      NOCACHE 

      NOCYCLE 

      NOKEEP 

      NOSCALE;

/

CREATE OR REPLACE TRIGGER employee_json_biu BEFORE

    INSERT OR UPDATE ON employee_json_t

    FOR EACH ROW

BEGIN

    IF inserting THEN

        :new.employee_id := employee_id_seq.nextval;

        :new.status := 'Y';

        :new.created_by := nvl(v('APP_USER'), user);

        :new.creation_date := localtimestamp;

        :new.updated_by := nvl(v('APP_USER'), user);

        :new.updated_date := localtimestamp;

    END IF;

    IF updating THEN

        :new.updated_by := nvl(v('APP_USER'), user);

        :new.updated_date := localtimestamp;

    END IF;

END;

Step 2: Insert JSON Data into the Table

Here’s how to insert JSON data into the table. The trigger will handle the EMPLOYEE_ID and who columns:

INSERT INTO employee_json_t (

    employee_details

) VALUES (

    '{"name": "Jane Smith", "age": 25, "position": "Designer", "joining_date": "2022-12-01", "location": "San Francisco"}'

);

INSERT INTO employee_json_t (

    employee_details

) VALUES (

    '{"name": "Alice Johnson", "age": 28, "position": "Manager", "joining_date": "2021-07-20", "location": "Chicago"}'

);

INSERT INTO employee_json_t (

    employee_details

) VALUES (

    '{"name": "Bob Brown", "age": 35, "position": "Analyst", "joining_date": "2020-03-11", "location": "Boston"}'

);

INSERT INTO employee_json_t (

    employee_details

) VALUES (

    '{"name": "Charlie Davis", "age": 40, "position": "Architect", "joining_date": "2019-11-05", "location": "Seattle"}'

);

Step 3: Verify the Data

Query the table to verify that data has been inserted correctly and "who columns" are populated:

SELECT

    *

FROM

    employee_json_t;

Step 4: Create a JSON Source in Oracle APEX

To display JSON data in your application, start by creating a JSON Source in Shared Components. Follow these steps:

1. Navigate to JSON Sources

In Shared Components, go to Data Sources.

Click on JSON Sources.

Inside the JSON Sources page, click Create.

2. Configure the JSON Source

In the Create JSON Source tab, click Create.
Enter Name.
Choose Workspace in the Owner dropdown.

Select the table containing your JSON column (EMPLOYEE_JSON_T).

Click Next to continue.

Defining the foundation: Create your JSON Source effortlessly in Oracle APEX.

        

3. Select JSON Columns

Review the JSON columns detected in your table.

Select the required JSON columns you want to use.

Click Next.

Select and map JSON columns to unlock their full potential in reports.

4. Specify the Primary Key

Choose the Primary Key for your JSON Source (e.g., EMPLOYEE_ID).

Click Create to finalize the JSON Source.

Specify the primary key to ensure unique identification and data consistency.

Step 5: Create a Page to Display the JSON Data

Here’s how to create a page in Oracle APEX to display the JSON data using the JSON Source:

1. Start Creating a New Page

In the Application Builder, click Create and select Page.

From the available options, choose Report (Interactive Report or Classic Report).

Click Next to proceed.

2. Configure Page Details

Enter a Page Name, such as Employee JSON Report.

For the Data Source Type, select JSON Source.

From the dropdown, choose the JSON Source you created earlier (EmployeeJSONSource).

Click Create Page.

Transform your JSON data into a structured and dynamic interactive report.

  

Step 6: Customize the Report and Align the JSON Data

Once the page is created, you can refine the report by hiding unnecessary columns and aligning the data for better readability.

Final Output

Without Oracle APEX’s JSON Source feature, displaying JSON data in a report would require manually parsing the JSON stored in the table. Below is how it would typically be done:

Below is an example SQL query of Employee JSON Report that extracts specific fields from the JSON data while also displaying the WHO columns:

SELECT

    employee_id

  , JSON_VALUE(employee_details, '$.name')         AS name

  , JSON_VALUE(employee_details, '$.age')          AS age

  , JSON_VALUE(employee_details, '$.position')     AS position

  , JSON_VALUE(employee_details, '$.joining_date') AS joining_date

  , JSON_VALUE(employee_details, '$.location')     AS location

  , status

  , created_by

  , creation_date

  , updated_by

  , updated_date

FROM

    employee_json_t;

This approach works but involves additional coding effort to extract the data and build meaningful reports.

Wrapping Up!

With Oracle APEX’s JSON Source feature, working with JSON data has been easier. What once required manual SQL parsing and complex queries is now streamlined, efficient, and intuitive. By following the steps outlined in this blog, you’ve seen how to:

Create a table with JSON columns, sequences, and triggers.

Insert JSON data into the table.

Use the JSON Source feature to seamlessly integrate JSON data into your reports.

This innovative feature not only saves time but also empowers developers to focus on building dynamic, user-friendly applications without worrying about parsing JSON manually.

Say goodbye to parsing and hello to faster development with Oracle APEX’s JSON Source!

Thank You for Reading!

Feel free to share your thoughts, questions, or experiences in the comments section below. Let’s explore more features of Oracle APEX together in future blogs.

Stay tuned!