Until recently, Oracle APEX, via functionality such as REST Data Sources, could only extract a flat JSON structure. From release 24.1, however, APEX can now understand and transform more complex hierarchical or nested JSON responses.
In this blog, I will demonstrate some of this new functionality and its use, for example, in Interactive Reports.
The image below illustrates a snippet of JSON output from an Oracle REST Data Service for department-related data that incorporates nested arrays. Not including the highest-level array (departments), there are three further arrays in the JSON, two at the first level down (employees and locations) and one at the second level down (customers).
Up until APEX release 23.2, a REST Data Source would only be able to extract the highest-level array (departments). Any lower-level arrays would not be extracted.
The procedure for creating a REST Data Source in your APEX application is unchanged. Access the Create wizard via Shared Components > Data Sources > REST Data Source and click the Create button. The wizard, too, is unchanged. As you Discover the Data profile, it isn't immediately clear that APEX is doing anything other than extracting a flat structure, as previously, only the five non-array attributes associated with departments are listed:
Clicking the Data Profile tab in Discover provides more clues that changes are afoot. More columns are listed here, including some identified with an 'array' type:
Once you have created the data source in the wizard, click the Create REST Data Source button.
Once your REST Data Source has been created, click on the link under the REST Source Name column in the REST Data Source page to view and edit the definition for it.
On the definition page, clicking the 'Edit Data Profile' provides more evidence of changes. Here, you can see that some columns are defined as array types, and others have parent columns associated with those array-type columns.
Now, we can utilise the defined REST Data Source in our application. Create a new page based on an Interactive Report for which the Data Source is the REST Data Source we have just defined.
Once the page and report have been created, in the report's region in page designer, you will find that initially, by default, the report is defined with seven columns, including two first-level array types (EMPLOYEES and LOCATIONS).
Let's run the report.
Not good. We get a 'character string buffer too small' error.
I noticed that, in the report definition, all the columns, including the two array columns (employees and locations), are defined as 'Plain Text' types. I potentially have a lot of data within the 'employees' array. I set the 'employees' column as a hidden column and reran the report. Thankfully the report runs successfully, but it's displaying the 'locations' array column data as a JSON-format string.
Essentially, in our REST Data Source definition, APEX has identified these columns as arrays, but we haven't described how we want to deal with these array columns, so they are just being returned as plain text in JSON format.
In Page Designer, under the report's region properties, there is a new attribute, 'Nested Rows', under 'Data Profile'. This attribute has defaulted to 'None'.
If, for this attribute, we select '3. Locations', on selection, both array type columns (employees and locations) are removed as columns from the report definition. Instead, the report includes columns for each of the attributes included in the locations JSON array. At this stage, columns are not created for each attribute included in the other arrays in the JSON (employees, customers).
Let's run the report again, and once it's run, use the feature of the IR (Actions > Columns) to ensure all columns are included in the report. Now, we can see that APEX is successfully interpreting the individually nested columns. Notice, though, that we are now displaying a report row for each nested location, no longer one for each department. That's not a great problem – we can use one of the features of the IR, Control Breaks, to overcome that.
It is worth noting that only one Nested Rows column can be selected at a time in the IR report definition. If the 'employees' column is selected rather than the 'locations' column, then only the columns of the employees' nested row will be included in the report and not those for 'locations'. However, all columns of all parents will also become available as part of the flat table structure. Therefore, if 'customers' is selected as the Nested Rows column, all the nested columns of employees and customers (but not locations) become available.
By using the pre-existing Local Post Processing property of the IR report definition, we can perform calculations on individual line items.
I set my Nested Row to be 'employees' and entered the following code snippet into the report's Local Post Processing property.
I made some basic IR format changes and then ran the report, which now shows one row per department with calculated employee-related data (employee count and salary total) determined by the post-processing SQL.
Before APEX release 24.1, the APEX_EXEC package provided functions and procedures to process REST Data Sources programmatically. Since release 24.1, the package has been extended to accommodate nested JSON array columns. This includes the addition of new functions and procedures, including (but not limited to):
Or extending existing functions and procedures to cater for array columns, for example:
For example, the following snippet makes use of the APEX_EXEC package in the following ways:
This way, the code processes each department and every location associated with each department. Execution of the procedure generates the following output:
Since APEX release 24.1, Oracle has greatly enhanced APEX REST Data Sources by enabling support for hierarchical (nested) JSON.
Every APEX component that supports REST Data Source now also supports array columns within the data profile, including:
With similarly extended programmatic support, this is a welcome enhancement to APEX REST Data Source's capabilities.
For more information, check out our Oracle APEX Services, and if you liked this blog, check out our other APEX blogs here.
Contact us today and one of our expert developers will be in touch.