Use JSON Data with Oracle APEX

You can use Oracle APEX to create applications with JSON data. You must first create a view to extract the required attributes from the JSON data and maps them into columns of a relational view.

• Create a View from JSON Data Guide
  Oracle APEX interprets data in relational format. Creating a view extracts required attributes from the JSON data and maps them into columns of a relational view.
• Create a View with JSON_TABLE Function
  You can create views of JSON data using the json_table SQL/JSON function.

Create a View from JSON Data Guide

Oracle APEX interprets data in relational format. Creating a view extracts required attributes from the JSON data and maps them into columns of a relational view.

For creating a view of JSON data that is stored in SODA collections, you can use SODA APIs and JSON Data Guide. The following PL/SQL code uses SODA APIs to create a Data Guide view on JSON Data stored in SODA Collections.

Run the following code in Oracle APEX SQL Workshop to create a view named myview:

-- Fetch the data guide and create a view
DECLARE    
    coll  SODA_Collection_T;
    dg    CLOB;
    n     NUMBER;
BEGIN    -- Fetch the data guide from the collection or create one with hierarchical format
    coll := dbms_soda.open_Collection('mycollection');
    dg := coll.get_Data_Guide;
    dbms_output.put_line(JSON_QUERY(dg, '$' pretty));
    -- User can modify the data guide as needed
    n := coll.create_View_From_DG('myview', dg);
    dbms_output.put_line('Status: ' || n);
    dbms_lob.freeTemporary(dg);
END;
/

Use the following command to check if the view has been created:

select count(1) from user_views where view_name = 'myview';

Use the following command to see the structure of the view:

describe myview;

See Create View using JSON Data Guide for more information on creating a view using JSON Data Guide.

Create a View with JSON_TABLE Function

You can create views of JSON data using the json_table SQL/JSON function.

The json_table SQL/JSON function projects specific JSON data to columns of various SQL data types. You can use the json_table function to map parts of a JSON document into the rows and columns of a new, virtual table, which you can also think of as an inline view.

See Create View on JSON Data for more information on creating views over JSON Data.