You can use Oracle Application Express 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 Application Express 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.
-- 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:
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.
json_table SQL/JSON function projects specific JSON data to
columns of various SQL data types. You can use the
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.