APEX 24.1: REST Data Sources and nested JSON responses

APEX 24.1 introduces a major change to REST Data Sources: Support for hierarchical (nested) JSON responses. In previous releases, APEX could only extract a flat structure from a REST API response —starting with APEX 24.1, APEX understands complex and nested REST API responses as well. This blog posting describes the new functionality and how to use it in APEX applications. 

Consider a REST API returning the response shown in Fig. 1. This JSON response contains a nested array: First we have an array of purchase orders (denoted by the "items" attribute"), and within each purchase order, there is an array of order line items. Up to APEX 23.2, a REST Data Source is only able to extract the outer array of purchase orders - the inner array of line items was inaccessible to REST Data Sources.

Fig. 1: REST API response containing a nested structure

REST API returning the structure depicted in Fig. 1: https://apex.oracle.com/pls/apex/carstenczarski/orders/purchaseorders/

Create and Review the REST Data Source

This changes with APEX 24.1—after the REST Data Source is created, components will have access to the full JSON response, including all the array columns. So, let's first create a REST Data Source on this REST API. Navigate to Shared Components, REST Data Sources, and create a new REST Data Source. The actual wizard is rather unchanged compared to previous versions.

Fig. 2: Create a new REST Data Source - Step 1

Fig. 3: Create REST Data Source - last step "Discovery Results"

After the REST Data Source has been created, navigate to its details and click the Edit Data Profile column - here, we can see the new support for hierarchical JSON structures in action. Fig. 4 shows that the Data Profile now has an understanding of a Parent Column:

• The PO_LINEITEMS column is of the Array type.
• Five columns (PART_UPCCODE, PART_UNITPRICE, PART_DESCRIPTION, QUANTITY and ITEMNUMBER) have the PO_LINEITEMS as their parent column.

Fig. 4: The Data Profile of the new REST Data Source

Create a Report and access nested JSON structures

Let's now start using this REST Data Source in APEX Components. Create a new page, and navigate to Page Designer. We'll start with creating an Interactive Report on top of the new REST Data Source. For starters, simply create the region and pick the REST Data Source. Fig. 5 shows that, Page Designer populates region columns from the REST Data Source, as usual. We can see all the top-level columns, and the new PO_LINEITEMS column.

Fig. 5: Create an Interactive Report on top of the new REST Data Source

For now, make no further changes, then save and run the page.

Fig. 6: The Array column, which represents the nested JSON structure comes up as a JSON fragment.

Fig. 6 shows that the Interactive Report does contain the nested JSON elements, but they only appear in JSON format. This is because we only created a report on a REST Data Source, as in earlier APEX versions. We did not tell APEX anything about what to do with the PO_LINEITEMS array column from the data profile, so it's being returned in JSON format. Now, go back to Page Designer, and look up the Array Column attribute within the Data Profile section (Fig. 7).

Fig. 7: After picking the Array Column for this region, nested columns become accessible.

Pick the PO_LINEITEMS array column from the Array Column select list and watch how the region columns change. The PO_LINEITEMS column (which referenced the JSON fragment) goes away, and the individual nested columns are added now. Before running the page again, consider cleaning up some of the (unwanted) other columns, then run the page again. Fig. 8 shows that the result will be quite interesting.

Fig. 8: The Interactive Report can access nested JSON elements now.

The Interactive Report now shows one row per (nested) order line item, and no longer one row per purchase order - APEX 24.1 allows to access nested array elements fully with declarative means. If the LineItems JSON attribute within the response JSON had nested arrays as well, these would - again - first show up as JSON "fragment". Once picked as the Array Column for the APEX region, the nested columns become available.

How does APEX do that? Enabling Debug mode and reviewing debug output shows that APEX simply uses functionality of the Oracle Database. JSON is being parsed using the JSON_TABLE SQL function, and nested structures are accessed using the NESTED PATH clause.

Fig. 9: Nested JSON structures are processed using the JSON_TABLE SQL function.

Use Post Processing SQL

Every APEX region, whether it's a report, chart, faceted search or a plug-in can now be created on top of a REST Data Source - and an Array column within its data profile. When utilizing Post Processing SQL, we can even do calculations on the individual line item values - consider the following post processing query.

select PO_USER,
       PO_PONUMBER,
       PO_REFERENCE,
       PO_COSTCENTER,
       SUM(QUANTITY * PART_UNITPRICE) as ORDER_TOTAL
  from #APEX$SOURCE_DATA#
 group by PO_USER,
          PO_PONUMBER,
          PO_REFERENCE,
          PO_COSTCENTER

This query will aggregate the line items for each purchase order to compute the the order total - so the region will now - again - return one row per purchase order. However, we used Post Processing SQL in order to compute a value which is not available within the response JSON from the REST API.

Fig. 10: Editing the Post Processing SQL Query in Page Designer

Run the page again - now we can see that the report shows the order total, computed by the Post Processing SQL Query (Fig. 11).

Fig. 11: Interactive Report results on a REST Data Source, with nested JSON structures and a Post Processing Query.

Other components

Each APEX component which supports REST Data Source, also supports Array Columns in APEX 24.1. This includes:

• Classic Report, Interactive Report and Interactive Grid
• Map Regions and JET Charts
• Calendar
• Region Plug-Ins
• Automations
• Shared Lists Of Values
• Search Configurations
• Report Queries

Fig. 12 shows how an Automation can be created on top of a REST Source with nested structures - other shared components work similar.

Fig. 12: Use the Create Automation wizard with REST Sources and Array columns.

Array Columns can also be used for REST Source Synchronizations - however, each REST Data Source still can have only one synchronization. So one has to decide which array column to create the synchronization for (Fig. 13).

Fig. 13: Create a REST Synchronization and configure the array column to synchronize.

Programmatic access: APEX_EXEC

The APEX_EXEC package contains procedures to access a REST Data Source programmatically. The OPEN_REST_SOURCE_QUERY has been enhanced to support array columns as well. The following PL/SQL block invokes the REST Data Source and returns data for the PO_LINEITEMS array column. Note that this code block requires an APEX session to be existent; so you might run APEX_SESSION.CREATE_SESSION to create one beforehand.

DECLARE
    l_context       apex_exec.t_context;

    l_porefidx      pls_integer;

    l_itemnoodx     pls_integer;

    l_itempridx     pls_integer;

    l_itemquidx     pls_integer;

BEGIN

    l_context := apex_exec.open_rest_source_query(

        p_static_id           => 'purchase_orders',           

        --

        -- use the p_array_column_name to pass in the Array Column which this query

        -- should operate on.

        p_array_column_name   => 'PO_LINEITEMS',

        p_max_rows            => 100 );

 

    l_porefidx  := apex_exec.get_column_position( l_context, 'PO_REFERENCE' );

    l_itemnoodx := apex_exec.get_column_position( l_context, 'ITEMNUMBER' );

    l_itempridx := apex_exec.get_column_position( l_context, 'PART_UNITPRICE' );

    l_itemquidx := apex_exec.get_column_position( l_context, 'QUANTITY' );

 

    dbms_output.put_line(    '|' || lpad( 'REFERENCE', 20, ' ' )

                          || ' ' || lpad( 'ITEM#',     20, ' ' )

                          || ' ' || lpad( 'PRICE',     10, ' ' )

                          || ' ' || lpad( 'QUANTITY',   10, ' ' ) );

    dbms_output.put_line(    '|'

                          || lpad( '-', 63, '-' ));

 

    while apex_exec.next_row( l_context ) LOOP

 

        dbms_output.put_line(    '|'  || lpad( apex_exec.get_varchar2( l_context, l_porefidx  ), 20, ' ' )

                              ||  ' ' || lpad( apex_exec.get_varchar2( l_context, l_itemnoodx ), 20, ' ' )

                              ||  ' ' || lpad( apex_exec.get_varchar2( l_context, l_itempridx ), 10, ' ' )

                              ||  ' ' || lpad( apex_exec.get_varchar2( l_context, l_itemquidx ), 10, ' ' ) );

 

     end loop;

 

     apex_exec.close( l_context );

exception

     when others then

         apex_exec.close( l_context );

     raise;

end;

Running this block should yield the following response:

APEX 24.1 introduces way more functions for dealing with nested JSON responses to the APEX_EXEC package. Instead of "flattening out" the result as above, one can also execute the REST Source query on the top level, and then walk through the nested JSON hierarchy programmatically - as the following block highlights.

DECLARE
    l_context       apex_exec.t_context;

BEGIN

    l_context := apex_exec.open_rest_source_query(

        p_static_id           => 'purchase_orders',           

        p_max_rows            => 10 );

 

    while apex_exec.next_row( l_context ) LOOP

 

        dbms_output.put_line( '| PO: ' ||  apex_exec.get_varchar2( l_context, 'PO_REFERENCE' ) );

 

        apex_exec.open_array( l_context, 'PO_LINEITEMS' );

 

        while apex_exec.next_array_row( l_context ) loop

 

            dbms_output.put_line(    '|       #'

                                  || apex_exec.get_varchar2( l_context, 'ITEMNUMBER' )

                                  || ': '

                                  || apex_exec.get_varchar2( l_context, 'QUANTITY' )

                                  || ' x '

                                  || apex_exec.get_varchar2( l_context, 'PART_DESCRIPTION' )

                                  || ', '

                                  || to_char( apex_exec.get_number( l_context, 'PART_UNITPRICE' ), 'FM99990D00' )

                                  );

 

        end loop;

 

        dbms_output.put_line( null );

 

        apex_exec.close_array( l_context );

 

     end loop;

 

     apex_exec.close( l_context );

exception

     when others then

         apex_exec.close( l_context );

     raise;

end;

The result output clearly illustrates how the code walked through the purchase orders, and then for each line item per purchase order.

Summary

REST Data Sources in the new APEX release 24.1 are way more powerful than in previous releases: With the support of nested JSON responses, APEX components can use any REST API, returning any JSON response. This support includes page components, shared components, REST Source synchronizations as well as programmatic access with the APEX_EXEC package.