Simple Oracle Document Access (SODA) in the Autonomous JSON Database

“I ordered a soda – caffeine-free, low sodium, no artificial flavors. They brought me a glass of water.” – Robert E. Murray

SODA is a set of NoSQL-style APIs that let you create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the data in the documents is stored in the database.

Oracle database stores, manages, and indexes JSON documents, and developers can access these via document-oriented APIs in a NoSQL style.

A recent white paper written by Vlad Kamys, Francesc Mas and Sai Penumuru explained how to modernize your applications and increase business resilience and security with JSON on Oracle Database, and new cloud-based Oracle Autonomous JSON Database.

Here are few examples on how to use JSON and SODA in the Oracle Autonomous Database.

From the Tools tab, start “Database Actions”, and then select “SQL”:

The following below are typical SODA commands:

(1) list the collections

(2) create the EMP collection

(5), (6) and (7) insert three JSON documents into the collection

(10) gets all documents where the name is “Francesc”

(11) gets all documents where the salary is greater than 300

(12) gets all documents where the jobs starts with “D”

(13) gets all documents where the jobs contains the string “play”

Here is the output from command extracting all documents where the jobs starts with “D”:

If we do not have a text index on “job”, then we get an ORA-40467:

We can also run standard SQL on the EMP table which gets created as a result of creating the collection:

Here is how to get the output from EMP by using JSON_VALUE:

You can think of SODA as a programming bridge between the NoSQL model and the relational model.

There is also the DBMS_SODA package in the database. You can drop the EMP collection simply with “soda drop emp” but you can also run select DBMS_SODA.DROP_COLLECTION(’emp’) from dual; The function will return 1 when it succeeds and 0 when it fails.

Work with JSON Documents in Autonomous Database provides examples of how Java code opens a SODA collection of cart documents, how to use SQL with a SODA collection, etc.

Finally, there is JSON DB/SODA DB Health-Check Script that is a tool developed by Oracle Support Services. The tool, also known as jsonsodadb_hc, is used to check the environment in which a single SQL statement runs, checking for the current status of JSON DB and SODA DB components, makes recommendations based on current settings and checks if the components are being used.

Source: juliandontcheff.wordpress.com