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

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;

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;

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"

Go to Worksheet and execute:

select * from movement;

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/"

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';

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 🙂