APEX: JSON Based Facets

In Oracle APEX, we can have Faceted Search Region.

However, it can be a challenge when we need to fetch values from a child table. We can easily achieve this with JSON Based Facets.

In this blog, we want to display a Faceted Search Region on table DEPT, however, we want to be able to filter those depts given selected Employees in table EMP.

In this example, we create a Faceted Search Region with a Query like this

SELECT d.*,
      ( SELECT JSON_ARRAYAGG(e.ename) FROM emp e WHERE e.deptno = d.deptno ) Employees
  FROM dept d

(Optional) Change the P3_EMPLOYEES Facet to HIDDEN.

Run the page. As you can see the Employees Facet looks scruffy with JSON content

Finally, change the Facet's attributes to JSON Array and run the page...

This is ridiculously easy to convert a JSON array to working Facets.