Locally Filtering and Sorting Remote Data

If the REST API you use doesn’t let you filter or sort the data, you can do it in the local APEX database instead. Since these follow-up actions occur after the APEX engine retrieves the JSON response from the REST API, it’s known as Local Post-Processing.

When you set this property of your region or List of Values to Where/Order By Clause, then additional Where Clause and Order by Type properties appear. If you choose a Static Value type of order by clause, then can just type it in yourself. Otherwise the order by clause is determined by the Order by Item you configure.

The WHERE clause and ORDER BY clauses you configure for Local Post-Processing are added to the outermost SQL query in the APEX engine’s JSON_TABLE() statement. As a consequence, these clauses can only reference column names that are included in that outermost SELECT list. If you need to filter on or order by a column that you didn’t initially include in a region, add the additional column and leave it hidden.

For example, to present a list of Employees REST Data Source hired after October 1981, ordered descending by hire date, set the Local Post-Processing WHERE clause to:

HIREDATE > DATE'1981-10-31'

Then set the Static Value type ORDER BY clause to:

HIREDATE DESC

The result is an augmented JSON_TABLE() SQL statement that looks like this:

select "EMPNO",
       "ENAME",
       "HIREDATE"
from (
    /* Rest of APEX JSON_TABLE() query omitted for brevity */
)
where **HIREDATE > DATE'1981-10-31'**
order by **HIREDATE DESC**

This additional configuration results in retrieving the original set of Employees from the remote REST API, but then displaying only those in the desired hire date range, ordered as indicated.