Simple Oracle Document Access (SODA)

Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the documents are stored in the database. SODA is designed primarily for working with JSON documents, but a document can be of any Multipurpose Internet Mail Extensions (MIME) type.

SODA APIs are document-centric. You can use any SODA implementation to perform create, read, update, and delete (CRUD) operations on documents of nearly any kind (including video, image, sound, and other binary content). Documents can be fetched from the database by key lookup or by using query-by-example (QBE) pattern-matching.

SODA uses a SQL schema to store documents but you do not need to know SQL or how the documents are stored. However, access via SQL does allow use of advanced Oracle Database functionality such as analytics for reporting.

SODA for Java is stable, well-documented, and has a comprehensive test suite and is built on top of native JSON support in the Oracle Database.

SODA provides CRUD operations on documents. JSON documents can additionally be queried, using query-by-example (QBE) patterns, also known as filter specifications. A filter specification is itself a JSON object.

The drivers needed for different SODA implementations (languages) are as follows:

• Java
• REST
• C
• Node.js
• Python
• Oracle SQL cl (SQL Developer Command Line)

The performance of SODA QBEs can sometimes be improved by using indexes. You define a SODA index with an index specification, which is a JSON object that specifies how particular QBE patterns are to be indexed for quicker matching.

Below are some of the actions which can be performed SODA:

• Create collections
• Open existing collections
• Drop collections
• List all existing collections
• Create documents
• Insert documents into a collection
• Save documents into a collection (insert new or update existing)
• Truncate a collection (empty it, deleting all of its documents)
• Find a document in a collection, by key or by key and version
• Find all documents in a collection
• Find documents in a collection, by keys or by QBE
• Find documents in a collection whose content matches a text search
• Replace (update) a document in a collection, by key or by key and version (optimistic locking)
• Remove a document from a collection, by key or by key and version (optimistic locking)
• Remove documents from a collection, by keys or by QBE
• Index the documents in a collection (to improve query performance)
• Create a JSON data guide for a collection, which summarizes document structural and type information
• Create a relational view from a JSON data guide