How Do I use A_TABLE for Flattening Nested Arrays in ORACLE APEX

Introduction
In Oracle APEX, dealing with JSON arrays—especially nested ones—is a common task when integrating with RESTful APIs or handling structured data stored in tables. Oracle SQL provides powerful tools to flatten these arrays into relational rows for display or processing. One such tool is the use of A_TABLE or more precisely, JSON_TABLE with an alias that acts like a virtual table. This blog will walk through how to use JSON_TABLE to flatten nested JSON arrays, making them accessible for reports, forms, and logic in Oracle APEX.

How to Use A_TABLE (JSON_TABLE) for Flattening Nested Arrays in Oracle APEX

Step 1: Understand Your JSON Structure
Suppose you have a JSON payload like this stored in a CLOB column or retrieved from a REST source:

{
  "employee": {
    "name": "John",
    "skills": [
      { "type": "Database", "level": "Advanced" },
      { "type": "APEX", "level": "Intermediate" },
      { "type": "JavaScript", "level": "Basic" }
    ]
  }
}

Step 2: Flatten Using JSON_TABLE
You can use JSON_TABLE in SQL to extract and flatten the array into rows:

SELECT *
FROM JSON_TABLE(
  :P1_JSON, 
  '$.employee.skills[*]' 
  COLUMNS (
    skill_type  VARCHAR2(50) PATH '$.type',
    skill_level VARCHAR2(50) PATH '$.level'
  )
) skills_table;

This transforms each object in the skills array into a row with skill_type and skill_level.

Step 3: Use in a Report Region
Create a Classic or Interactive Report region and use the SQL above as the source. You can bind :P1_JSON to a page item or use a subquery to fetch it from a table.

Step 4: Handling JSON from a Table
If your JSON data is stored in a table (e.g., EMPLOYEE_DATA.JSON_DOC), your SQL would look like:

SELECT e.id, jt.*
FROM EMPLOYEE_DATA e,
     JSON_TABLE(
       e.json_doc,
       '$.employee.skills[*]'
       COLUMNS (
         skill_type  VARCHAR2(50) PATH '$.type',
         skill_level VARCHAR2(50) PATH '$.level'
       )
     ) jt;

Best Practices

• Always validate JSON data using IS JSON or ensure the structure with constraints.

• Use CLOB data type for large or nested JSON structures.

• Index JSON data with functional indexes using JSON_VALUE() or JSON_TABLE paths if querying frequently.

• In APEX, prefer REST Data Sources and Collections for performance and reusability if data is dynamic.

Oracle APEX Documentation Links

• JSON_TABLE in Oracle SQL

• Working with JSON in APEX

If the "GEOMETRY" JSON column contains a more complex nested array or object, you might want to use JSON_TABLE to flatten the array into rows.

For example:

SELECT *

FROM USCG_DATA,

     JSON_TABLE(

         GEOMETRY,

         '$.coordinates[*]' COLUMNS (

             longitude NUMBER PATH '$[0]',

             latitude NUMBER PATH '$[1]'

         )

     ) jt;

This query will flatten the coordinates array in the JSON into individual rows, displaying each coordinate's longitude and latitude as separate rows in the result.

 

Conclusion
Flattening nested arrays with JSON_TABLE in Oracle APEX allows developers to convert complex JSON structures into usable tabular formats. Whether you're reporting on REST data, storing user preferences, or integrating external services, mastering A_TABLE techniques gives you the power to work efficiently with JSON in any APEX application. With good structure, validation, and performance practices, your JSON-powered features will be both reliable and scalable.