11 min readOct 11, 2021

Create an Apex application using collection {JSON} ,relationa data, spatial data, and blockchain

In the last Italian JSON Event (here the video-recorder), I showed an application written with apex for the University use-case, where I mixed together different kinds of Data:

{JSON} Data from document /collection
data from a relational table
Spatial Data
Blockchain Collection

The name of the Italian event was “NoSQL or SQL, that’s the question? Not with Oracle!” because with Oracle Database we can use the benefit of both without the need to renounce one of them.

The scope of this application was to show how powerful it is to use the schemaless NoSQL approach together with schema SQL Approach using different type of data.

The application was made only for educational purposes and surely could be written better, and with all the checks that a real application should have (this is not the case of this application ).

The application shows how you can use a collection (created by API ) with different structures for each document (as you can see on the right of this picture, where there is a collection of the points of interest for studens within a campus, for example a canteen or a gym), but it is also possible to use inside this schemaless document, a key that is bonded with a key in a relational table. In this way you avoid replicating data each time for each document and also you can guarantee the consistency of the information, for example, if you need to change the value of an attribute of the university like a phone contact or an email, you have to change this information inside all documents at the same time and it could be even more complex, for example if you have other collections with university information, like collection of students or professors…)

And also with Oracle, you can mix other types of data (not only {JSON}), to build modern applications and create value from different information like APEX or blockchain table.

This post is just to share with you the application presented in that event and show all the steps to recreate it in your environment. Let me remind you that you can leverage Oracle cloud with always free to have an oracle database (autonomous database) and apex and object storage to reproduce the application without any payment, but if you prefer to have Oracle Xe and Apex on your private laptop you can follow the steps of my guide to recreate the environment.

I use Oracle cloud free tier for my environment and the steps above are based on this environment.

Create Autonomous Database

After creating an account for the free tier we have to create an autonomous database, to do that, use the hamburger menu on the left and select Oracle Database, there you will find several choices including Autonomous Databases.

Using the Autonomous Databases link, we land in a page where there is a button to create an autonomous database

Set the name and the type of the workload

and after setting always free option

and the password we are ready to create the autonomous database

In a couple of minutes, the autonomous database is up and ready

Install Application

First of all, get all files you need from this link. Inside the zip, you will find these files

Now we can create a user inside the database and to do so, we can use the database action in the tools tab of Autonomous Database Menu

and use the login and password chosen during the creating of the Autonomous database step

In the Administration section click on the DataBase Users box

and create a user like above

Now we can use apex, which is already installed in our autonomous database, we only need to get to the autonomous page, and in the tools tab click on the Open Apex button on the right

Connect using the same password of admin user

at this point, we have to create a workspace for our application using the button in the top right corner

To create a Workspace you need to define a database user , so we can use the user created before by DataBase Users box

After that, we need to connect to that workspace

After having logged in the workspace we can import the application using the import box from the App Builder tab

upload the file app_evento_italiano_json.sql from the zip file

leave the default setting and with the next button install the application JSON WorkShopItaly_bk_28092021

Don’t run the application because the installation creates the objects inside the schema, but does not load the dataset inside the collection, so we have to load the documents into that

Load The {JSON} Document

Please note that all datasets are invented, built using a free test data generator (https://mockaroo.com/)

Student collection

From the OCI cloud interface select storage on the hamburger menu and then choose the Buckets in the Object Storage section

create a bucket and upload the student’s dataset

After creating the bucket, select it and upload in the bucket the file UNI_Student_cordinates.json

in order to read this file from autonomous database we need the URL path of the bucket that is made in this way

https://object_storage.<region>.oraclecloud.com/n/<Namespace>/b/<Bucket_name>/o/

a fast way to get this information is to use the menu on the right of your file and choose “view object detail “

here you will find the information about path URL , and you need to delete only the name of the file from it

in my case

https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/fry2iu6gzngg/b/JSON_Bucket/o/

At this point we have to create a credential inside the autonomous database to load the documents inside the collection .

To do it we need some information :

The username that we use to log in to the OCI Cloud
an authorization token

To have this information from the Identity & Security menu from the hamburger menu, you have to choose the users section. In the user page you will find the username (if you are using a federate user the output will be slightly different )

and for the token on the bottom of the page , there is a menu with auth token voice , and with that you can create a new token

Keep in mind that you have to copy the token because later it will not be recoverable.

Now, we have all information to create the credential inside the autonomous database to access the bucket.

Get back to the autonomous database page and from the tools tab choose the database action, At this point, we need to use login and password for the user created before

form the menu choose SQL

and execute the following code (you need to modify it with your information) , the information about my personal user are not longer exist :-)

BEGINDBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'universita_objectstore',username => 'luca.bindi@gmail.com',password => '07mVp{XkUXLQX}I7Iifa');END;/

If you want to be able to read the files in your bucket from the database you can execute this query changing the path URL

select object_name, bytes from dbms_cloud.list_objects('universita_objectstore','https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/fry2iu6gzngg/b/JSON_Bucket/o/');

ok it seems to work!!

At this point we can load the documents into students’ collection with this command ( also here you have to modify it with your information )

BEGINDBMS_CLOUD.COPY_COLLECTION(collection_name => 'UNI_STUDENT',credential_name => 'universita_objectstore',file_uri_list => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/fry2iu6gzngg/b/JSON_Bucket/o/UNI_Student_cordinates.json',format => '{"recorddelimiter" : "0x''01''", "unpackarrays" : "TRUE", "maxdocsize" : "10240000"}');END;/Check if you have the records inside your collection

Create a view on this collection , in this way some operation in apex will be more simple

DECLAREdg CLOB;BEGINSELECT json_dataguide(JSON_DOCUMENT, DBMS_JSON.FORMAT_HIERARCHICAL,DBMS_JSON.GEOJSON+DBMS_JSON.PRETTY)INTO dgFROM UNI_STUDENT ;DBMS_JSON.create_view('UNI_STUDENT_VIEW','UNI_STUDENT', 'JSON_DOCUMENT', dg);END;/

Point of Interest Collection

For this collection I will change the method of loading the document , just to show you another way among several methods to load the {JSON} document into Oracle

Get back to our Autonomous database page using the Database action and using RESTFUL SERVICES AND SODA button Copy the URL

in my case https://g4bd44xxxxxxxxxxxxxxxxxxkfurt-1.oraclecloudapps.com/ords/

we will use this URL to load the Point of interest collection directly from our laptop without using the object storage , just using the following command :

curl -X POST -u 'json:DataBase##11' --data-binary @Uni_POI_upper.json -H "Content-Type: application/json" https://https://g4bd44xxxxxxxxxxxxxxxxxxxxxkfurt-1.oraclecloudapps.com/ords/json/soda/latest/UNI_POI?action=insert

You have to modify user and password with your information used in the creation user time and adding at the end the URL some other information like this

/<utente_name>/soda/latest/UNI_POI?action=insert

With the same URL , using a web browser we will see what is inside the collection UNI_POI

https://g4bd449xxxxxxxxxxxxxxxkfurt-1.oraclecloudapps.com/ords/json/soda/latest/UNI_POI

GeoSpatial function

to leverage the spatial function inside apex , like to find the distance between two points in space, we have to create 3 spatial indexes for our collections and table.

So from the SQL console execute these command

-- drop index uni_student_SX;CREATE INDEX uni_student_SX ON uni_student (json_value(json_document, '$.GEOMETRY' RETURNING SDO_GEOMETRY) )INDEXTYPE IS mdsys.spatial_index;-- drop index uni_poi_SX;CREATE INDEX uni_poi_SX ON uni_poi (json_value(json_document, '$.GEOMETRY' RETURNING SDO_GEOMETRY) )INDEXTYPE IS mdsys.spatial_index;INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)VALUES ('UNIVERSITY', 'UNIVERSITY_GEOMETRY',SDO_DIM_ARRAY (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5), SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),8307);commit;-- drop index universiti_SX;CREATE INDEX universiti_SX ON university (university_geometry )INDEXTYPE IS mdsys.spatial_index;

If you are using the newest version of Apex, can happen that some objects that the import tool should create automatically, doesn’t it work as aspected , so please check if the table university has some records , otherwise use those simple commands to populate it.

Create table University (
university_id number(10) primary key,
university_name varchar2(80),
university_city varchar2(80),
university_geometry SDO_GEOMETRY,
contact_emeail varchar2(80),
contact_phone varchar2(80)
);insert into University values (1,'SAPIENZA','Roma',
  SDO_GEOMETRY(2001, 8307,
  SDO_POINT_TYPE(12.5102275,41.9070269,NULL), NULL, NULL),
  'info.sapienza@sapienza.it','063458345');
insert into University values (2,'TORVERGATA','Roma',
  SDO_GEOMETRY(2001, 8307, 
  SDO_POINT_TYPE(12.62921,41.85310,NULL), NULL, NULL),
  'info.torvergata@torvergata.it','063448225');
insert into University values (3,'ALMA MATER ','Bologna',
  SDO_GEOMETRY(2001, 8307,
  SDO_POINT_TYPE(11.35415,44.49632,NULL), NULL, NULL),
  'info.alma@alma.it','05104056');
insert into University values (4,'BOCCONI','Milano',
  SDO_GEOMETRY(2001, 8307,
  SDO_POINT_TYPE(9.18706,45.45056,NULL), NULL, NULL),
  'info.bocconi@milano.it','02343009433');
insert into University values (5,'FEDERICO SECONDO','Napoli',
  SDO_GEOMETRY(2001, 8307,
  SDO_POINT_TYPE(14.25708,40.84463,NULL), NULL, NULL),
  'info.federicosecondo@napoli.it','0819078564');
insert into University values (6,'UST','Torino',
  SDO_GEOMETRY(2001, 8307,
  SDO_POINT_TYPE(7.68881,45.06978,NULL), NULL, NULL),
  'info.ust@torino.it','0112311978');commit;

Run The Apex Application

At this point, all documents are loaded into the collections and we can run the application

Here is a quick video to show some features within the app as :

Show the student’s address on the map
Show points of interest of universities
Use spatial function to find distance between student and university
Show Heat map of the student distribution on the territory
Create a link from student and university
Insert an exam grade

If you want data on the heatmap you can partially update the documents connecting each student with the closer university using spatial functions like the command below

UPDATE uni_student j SET json_document =  json_mergepatch( json_document, '{"UNIVERSITY_ID":"'||(select university_id from university wheresdo_nn (university_geometry , json_value(j.json_document, '$.GEOMETRY' RETURNING SDO_GEOMETRY), 'sdo_num_res=1',1)= 'TRUE')||'"}');
be sure to have the data inside USER_SDO_GEOM_METADATA like belowselect * from USER_SDO_GEOM_METADATA;
TABLE_NAME  COLUMN_NAME                          DIMINFO                         SRID 
----------- ------------------------------------ ------------------------------- ---- 
UNIVERSITY  UNIVERSITY_GEOMETRY                  [object Object],[object Object] 8307 
UNI_POI     JSON_VALUE(JSON_DOCUMENT,$.GEOMETRY) [object Object],[object Object] 4326 
UNI_STUDENT JSON_VALUE(JSON_DOCUMENT,$.GEOMETRY) [object Object],[object Object] 4326

Blockchain Collection

When you connect a student with a university, apex will automatically create a new collection based on the blockchain table, in the application I use that blockchain collection to simulate a professor that inserts the exam grade and no one can change it anymore :-)

the partial update to change the information in the document into UNI_STUDENT collection

The university information inside the document

Nones can change the data inside the collection

As I told you the application is made for educational purposes and this demo surely could be written better, all checks for a stable application haven’t been run and consequently there is no any support to it.

I hope it’s useful and … have fun

Ciao.
Luca

Disclaimer

The views expressed in this paper are my own and do not necessarily reflect the views of Oracle.

useful link  (thanks Beda and Chris)
https://www.youtube.com/watch?v=CrzncrKzsWo