Experiencing JSON in Oracle Autonomous Database for Cloud Native Geeks in less than 15 Minutes


Oracle DB has been supporting JSON for quite some time, but you probably didn’t know it! Let’s se how easy is to start puting and geting json data in it.

The most quick way to get a JSON db up and running is creating an Autonomous Transaction Processing instance. Create the instance and forget there is a database in there. Backups, tuning, servers,…, all those stuff are transparent for you.

NOTE: If you need a cloud account sing out for one (free) here.

Creating an autonomous db in 2 minutes
The new database gets created in less than 3 minutes

Click on [Name]

Click on [Service Console]

Click on [Development]

Grab the SODA URL for later:

Launch SQL Developer Web

Execute the following in the worksheet:

EXEC ords.enable_schema;
 GRANT SODA_APP TO admin;
 BEGIN
   ords.delete_privilege_mapping(
     'oracle.soda.privilege.developer',
     '/soda/*');
   COMMIT;
 END;
SQL Developer Worksheet

Creating a Collection via REST

Execute the following in the command line to create an object called “movement” to store json data:

curl -X PUT -u 'admin:YOURPASSWORD' "https://nhihitemese6sbi-test.adb.eu-frankfurt-1.oraclecloudapps.com/ords/admin/soda/latest/movement"

Execute the following in Worksheet:

describe movement;
A database table has been created for our collection

Storing JSON data via REST

Execute the following from the command line, postman or whatever:

curl -X POST -u 'ADMIN:YOURPASSWORD' -H "Content-Type: application/json" --data '{"id": "00000000010023456443454545","store": "0601001","section": "99","movements": [{"to": "0601301","timestamp": "201909211212","items": [{"productCode": "90452800044","model": "NICESKIRT","quality": "10","color": "RED","size": "XXL","serialNumber": "99999999"}, {"productCode": "444","model": "PITILLOTROUSER","quality": "9","color": "BLUE","size": "XS","serialNumber": "529564545"}]}, {"to": "0301001","timestamp": "196802020101","items": [{"productCode": "90452800055","model": "VERYLONGSKOCKS","quality": "5","color": "WHITE","size": "M","serialNumber": "529554545"}]}]}' \
  "https://nhihitemese6sbi-test.adb.eu-frankfurt-1.oraclecloudapps.com/ords/admin/soda/latest/movement"
Notice that a JSON document has been stored with a loooong ID

Go to Worksheet and execute:

select * from movement;
JSON data is stored as BLOB in a table

Getting JSON data via REST

To get all documents:

curl -X GET -u 'admin:YOURPASSWORD' "https://nhihitemese6sbi-test.adb.eu-frankfurt-1.oraclecloudapps.com/ords/admin/soda/latest/movement?limit=5"

To get an object by ID:

curl -X GET -u 'admin:YOURPASSWORD' "https://nhihitemese6sbi-test.adb.eu-frankfurt-1.oraclecloudapps.com/ords/admin/soda/latest/movement/1D96C8569DC3402A9CBCA35354162921"

Getting JSON via REST with Query and filters

curl -X POST -u 'admin:YOURPASSWORD' --data-binary '{"store":"0601001"}' -H "Content-Type: application/json" "https://nhihitemese6sbi-test.adb.eu-frankfurt-1.oraclecloudapps.com/ords/admin/soda/latest/custom-actions/query/movement/"
The query condition is passed as QBE, more info here
Qery with Postman

Getting JSON data via SQL

select 
     mov.id, mov.json_document.store,
     mov.json_document.movements[1].timestamp timestamp,
     mov.json_document.movements[1].items[0],
     mov.json_document.movements[1].items[0].color color
 from movement mov where mov.json_document.store='0601001';
Note you can get simple fields and/or json sub elements as columns of data

There are more mechanisms for working with JSON in Oracle DB will show you in coming posts!

That’s all for today folks! Hope it helps 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.