How to Handle Nested JSON Arrays from REST APIs in Oracle APEX ITEMS

One of the most common challenges when integrating external REST APIs with Oracle APEX is dealing with nested arrays of key value pairs. Instead of returning data in a flat JSON object with fixed properties, many APIs return a flexible array like this:

{

  "attributesList": [

    {

      "attributeName": "customer_id",

      "attributeValue": "CUST-1001"

    },

    {

      "attributeName": "account_number",

      "attributeValue": "ACC-778899"

    },

    {

      "attributeName": "order_reference",

      "attributeValue": "ORD-2026-001"

    },

    {

      "attributeName": "order_date",

      "attributeValue": "2026-01-15"

    }

  ]

}

This structure is very flexible the API can add new attributes without changing the JSON schema but it requires special configuration in APEX to extract the values into page items.

In this article, I will show you step-by-step how to:

1. Create a REST Data Source
2. Configure the Data Profile with the correct Row Selector
3. Write a PL/SQL process to read the array and assign values to page items

Step 1: Create the REST Data Source

1. Go to Shared Components → REST Data Sources
2. Click Create
3. Set the following:
   • Name: TEST
   • URL: Your REST API endpoint
   • Method: GET
   • Format: JSON

After creating it, click on the REST Data Source and go to Data Profile.

Step 2: Configure the Data Profile (Most Important Step 😲)

In the Data Profile settings:

• Name: MY_API_ATTRIBUTES
• Format: JSON
• Row Selector: attributesList (This tells APEX to treat each object inside the attributesList array as a separate row)
• Contains Single Row: Off (disabled)

Here is an example of how the Data Profile should look:

Now go to the Columns tab. APEX should automatically discover two columns:

• ATTRIBUTENAME (or ATTRIBUTE_NAME) – Type: Varchar2
• ATTRIBUTEVALUE (or ATTRIBUTE_VALUE) – Type: Varchar2

Make sure the Selector values are set correctly:

• For Name column → Selector: attributeName
• For Value column → Selector: attributeValue

Step 3: Create Page Items

Create page items to store the extracted values. For example:

• P2_CUSTOMER_ID
• P2_ACCOUNT_NUMBER
• P2_ORDER_REFERENCE
• P2_ORDER_DATE

You can add as many items as needed based on the attributes your API may return.

Step 4: Create the PL/SQL Page Process

Create a Page Process (best placed on After Header or Initialization) and use the following code:

declare l_context apex_exec.t_context; l_parameters apex_exec.t_parameters; l_name varchar2(255); l_value varchar2(4000); begin -- Add any input parameters required by your API (example) apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'order_id', -- change according to your API p_value => :P2_ORDER_ID ); -- Open the REST Data Source l_context := apex_exec.open_web_source_query( p_module_static_id => 'MY_API_ATTRIBUTES', -- Use the Static ID of your REST Data Source p_parameters => l_parameters ); -- Loop through each key-value pair in the attributesList while apex_exec.next_row(l_context) loop l_name := lower(trim(apex_exec.get_varchar2(l_context, 'ATTRIBUTENAME'))); l_value := apex_exec.get_varchar2(l_context, 'ATTRIBUTEVALUE'); -- Map attribute names to page items case l_name when 'customer_id' then :P2_CUSTOMER_ID := l_value; when 'account_number' then :P2_ACCOUNT_NUMBER := l_value; when 'order_reference' then :P2_ORDER_REFERENCE := l_value; when 'order_date' then :P2_ORDER_DATE := l_value; -- Add more attributes as needed -- when 'another_field' then :P2_ANOTHER_ITEM := l_value; else null; -- ignore unknown attributes end case; end loop; -- Close the context apex_exec.close(l_context); exception when others then if l_context is not null then apex_exec.close(l_context); end if; apex_error.add_error( p_message => 'REST Data Source Error: ' || sqlerrm, p_display_location => apex_error.c_inline_in_notification ); end;}

Thanks for reading💓