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 thejson_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.
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.
Parent topic: Use JSON Data with Oracle APEX
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.
Parent topic: Use JSON Data with Oracle APEX