Displaying JSON Data in a Form in ORACLE APEX

 Introduction

In Oracle APEX, it's common to work with JSON data—whether fetched from RESTful web services, stored in database columns, or retrieved from APIs. Displaying this JSON data in a user-friendly form layout enhances usability and allows for meaningful interaction with structured information. This blog explains how to display JSON data in an Oracle APEX form using built-in PL/SQL tools and declarative features, making your applications more dynamic and flexible.

How to Display JSON Data in a Form in Oracle APEX

To display JSON data in a form, you typically start by parsing the JSON and mapping it to individual APEX items (text fields, select lists, etc.). Here’s how to do it step by step:

Step 1: Store or Fetch JSON Data
You can either store JSON data in a table column (e.g., CLOB or VARCHAR2) or fetch it dynamically using apex_web_service.make_rest_request or a REST Data Source.

Example:

SELECT json_column 
INTO :P1_JSON_RAW 
FROM my_json_table 
WHERE id = :P1_ID;

Step 2: Parse the JSON Using APEX_JSON or JSON_VALUE
Use PL/SQL or SQL to extract values from the JSON and assign them to form page items.

PL/SQL using APEX_JSON:

BEGIN
  apex_json.parse(:P1_JSON_RAW);
  :P1_NAME := apex_json.get_varchar2(p_path => 'name');
  :P1_EMAIL := apex_json.get_varchar2(p_path => 'email');
  :P1_PHONE := apex_json.get_varchar2(p_path => 'contact.phone');
END;

Or using SQL in a process:

:P1_NAME := JSON_VALUE(:P1_JSON_RAW, '$.name');

Step 3: Create Page Items and Assign Values
On the page, create text fields for each JSON field you want to display, such as P1_NAME, P1_EMAIL, P1_PHONE. In a page load process or dynamic action, populate these items using the code above.

Step 4: Optional – Display Nested or Array Data
If your JSON includes nested arrays (e.g., list of orders), consider using JSON_TABLE to display them in an Interactive Report or Interactive Grid.

Example:

SELECT *
FROM JSON_TABLE(
  :P1_JSON_RAW,
  '$.orders[*]'
  COLUMNS (
    order_id    NUMBER PATH '$.id',
    order_total NUMBER PATH '$.total'
  )
);

Best Practices

• Always validate the JSON string using IS JSON or apex_json.is_json before parsing.

• Store large JSON in CLOB columns to avoid size limitations.

• Use apex_json.get_varchar2, get_number, or get_date depending on the expected data type.

• Use conditional display logic if some fields may be missing in the JSON.

Oracle APEX Documentation

• APEX_JSON Reference Guide

• JSON Functions in SQL

 

You can also display the parsed JSON data in form fields if needed. Here's how:

1. Create a Form:

• Create a new Form in APEX based on the USCG_DATA table.

2. Use a Query to Extract Data:

• Use a SQL query similar to the one above to extract the JSON values in the Form source. For example:

SELECT

    ID,

    JSON_VALUE(GEOMETRY, '$.type') AS geometry_type,

    JSON_VALUE(GEOMETRY, '$.coordinates[0]') AS longitude,

    JSON_VALUE(GEOMETRY, '$.coordinates[1]') AS latitude

FROM USCG_DATA

WHERE ID = :P1_ID; -- Assuming :P1_ID is the primary key item for the form

3. Map the Results to Form Fields:

• Map the extracted JSON values (e.g., geometry_type, longitude, latitude) to the appropriate form items (e.g., P1_GEOMETRY_TYPE, P1_LONGITUDE, P1_LATITUDE).

Handling Complex JSON Structures

If the "GEOMETRY" column contains more complex nested JSON data, you can use the JSON_QUERY function or JSON_TABLE to extract arrays or nested objects.

Example: Using JSON_QUERY for Nested JSON

If "GEOMETRY" contains an array of coordinates and you want to extract all of them, you could use JSON_QUERY:

SELECT

    ID,

    JSON_QUERY(GEOMETRY, '$.coordinates') AS coordinates

FROM USCG_DATA;

This query will return the entire coordinates array as a JSON object. You can then manipulate it further in APEX or process it with PL/SQL.

Conclusion
Displaying JSON data in Oracle APEX forms bridges the gap between modern APIs and user-friendly interfaces. With tools like APEX_JSON and SQL/JSON functions, developers can flexibly extract, parse, and display structured data efficiently. By following best practices for performance and error handling, your APEX applications can become powerful front-ends for JSON-driven backends.