Overview of SODA

1 Overview of 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.

There are separate SODA implementations for use with different languages and with the representational state transfer (REST) architectural style. SODA for REST can itself be accessed from almost any programming language. It maps SODA operations to Uniform Resource Locator (URL) patterns.

Note:

This book describes the features that are present in different SODA implementations. Some features described here may not be available for some implementations. In addition, different implementations can have different ways of providing some of the features. Please refer to the documentation for a particular implementation for detailed information about it.

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). You can also use any SODA implementation to query the content of JavaScript Object Notation (JSON) documents using pattern-matching: query-by-example (QBE). CRUD operations can be driven by document keys or by QBEs.

Oracle Database supports storing and querying JSON data natively. SODA document collections are backed by ordinary database tables and views. Because of this, you can take advantage of database features for use with the content of SODA documents. SODA CRUD and query operations are automatically mapped to SQL operations on the underlying database tables or views, and these operations are optimized.

You do not need knowledge of SQL, or database administrator (DBA) assistance, to develop or deploy a SODA application. However, database role SODA_APP must be granted to the database schema (user account) that you use to store collections.

The SQL standard defines a set of SQL/JSON operators that allow direct querying of JSON data. Database views based on these operators provide schema-on-read behavior that is immune to changes in the structure of your documents. If needed, developers with SQL knowledge can use SQL/JSON to perform advanced operations on your SODA data that make full use of the database. For example, a SQL developer can apply database analytics and reporting to your JSON data, and can include it in aggregation and join operations that involve other data. In addition, your SODA applications can use database transactions.

These SODA abstractions hide the complexities of SQL and client programming:

Collection
Document

A document collection contains documents. Collections are persisted in an Oracle Database schema (also known as a database user). In some SODA implementations a database schema is referred to as a SODA database.

A SODA collection is analogous to an Oracle Database table or view.

SODA is designed primarily for working with JSON documents, but a document can be of any Multipurpose Internet Mail Extensions (MIME) type.

In addition to its content, a document has other document components, including a unique identifier, called its key, a version, a media type (type of content), and the date and time that it was created and last modified. The key is typically assigned by SODA when a document is created, but client-assigned keys can also be used. Besides the content and key (if client-assigned), you can set the media type of a document. The other components are generated and maintained by SODA. All components other than content and key are optional.

A SODA document is analogous to, and is in fact backed by, a row of a database table or view. The row has one column for each document component: key, content, version, and so on.

In addition to the documents it contains, a collection also has associated collection metadata. This specifies various details about the collection, such as its storage, whether it should track version and time-stamp document components, how such components are generated, and whether the collection can contain only JSON documents.

In some contexts collection metadata is represented as a JSON document. This metadata document is sometimes called a collection specification. You can supply a custom collection specification when you create a collection, to provide metadata that differs from that provided by default.

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.

SODA APIs provide operations for collection management (create, drop, list) and document management (CRUD).

These are some of the actions you can perform using 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

Your applications use a database transaction when performing one or more such actions.^Foot 1

1.1 Overview of SODA Documents

SODA is designed primarily to manipulate JavaScript Object Notation (JSON) documents, that is, documents whose content is JSON data, but other kinds of documents can also be used. A document has other components, besides its content.

Here is a textual representation of the content of a simple JSON document:

{ "name"    : "Alexander",
  "address" : "1234 Main Street",
  "city"    : "Anytown",
  "state"   : "CA",
  "zip"     : "12345" }

You can set the following document components (with an application client, for example):

Key
Content
Media type

In a collection, each document must have a document key, which is unique for the collection. By default, collections are configured to automatically generate document keys for inserted documents. If you want to instead use your own, custom, keys for a collection then you must provide the key for a document when you create it.

The media type specifies the type of content for a document. For JSON documents the media type is "application/json".

The following document components are set and maintained automatically by SODA itself:

Version
Creation time stamp
Last-modified time stamp

A SODA document is an abstract object that encapsulates its components, including its content — it is a carrier of content. A SODA create-document operation creates such a programmatic document object, and a document object is returned by some SODA operations, such as find operations.^Foot 2

A document is stored in Oracle Database as a row in a table or view, with each component in its own column.

In a client application, a SODA document is represented in a way that is appropriate to the particular SODA implementation used. For example:

In SODA for Java, a document is represented as a Java interface.
In SODA for PL/SQL, a document is represented as a PL/SQL object type.
In SODA for C, a document is represented as an Oracle Call Interface (OCI) handle.

In all cases, there are methods or functions to create documents and access their components.

To write content to SODA collections and read content from them, you use create-document, write, and read operations:

You use a SODA create-document operation to create a document object with content that you provide. (The content can be JSON data or something else.)
You use a SODA write operation (such as insert), to store the document persistently in Oracle Database. (The document content is written to a database table.)
You use a SODA read operation (such as find), to fetch the document back from Oracle Database. You use specific getter operations to read specific document components (such as key and content).

Related Topics

SODA Feature Support

1.2 Overview of SODA Document Collections

A SODA collection is a set of documents that is backed by an Oracle Database table or view.

By default, creating a SODA document collection creates the following in Oracle Database:

Persistent default collection metadata.
A table for storing the collection, in the database schema to which your SODA client is connected.

All SODA implementations provide a get-metadata operation, which returns the metadata for a collection, represented in JSON. Example 1-1 shows an example of the default collection metadata, which is returned for a default collection.

The default metadata specifies a collection that tracks five components for each document: key, content, version, last-modified time stamp, and created-on time stamp. These are specified in JSON by fields keyColumn, contentcolumn, versionColumn, lastModifiedColumn, and creationTimeColumn, respectively. Each of these components is stored in a separate column in the table or view that backs the collection in Oracle Database. The metadata further specifies various details about these components and the database columns that back them.

In Example 1-1, for the key component: the column name is "ID", the column type is "VARCHAR2", the maximum key length is 255, and the key generation method used is "UUID".

In a client application, a document collection is represented in a way that is appropriate to the particular SODA implementation used. For example:

In SODA for Java, a collection is represented as a Java interface.
In SODA for PL/SQL, a collection is represented as a PL/SQL object type.
In SODA for C, a collection is represented as an Oracle Call Interface (OCI) handle.

When a collection is created, the create-collection operation returns a Java or PL/SQL object or an OCI handle, which you can use to perform various collection read and write operations.^Foot 3

Note:

In the SODA for REST URI syntax, after the version component, you can use custom-actions, metadata-catalog, or a particular collection name. When you use custom-actions or metadata-catalog, the next segment in the URI, if there is one, is a collection name.

Because of this syntax flexibility, you cannot have a collection named either custom-actions or metadata-catalog. An error is raised if you try to create a collection with either of those names using SODA for REST.

In other SODA implementations, besides SODA for REST, nothing prevents you from creating and using a collection named custom-actions or metadata-catalog. But for possible interoperability, best practice calls for not using these names for collections.

When you create a collection you can specify things such as the following:

Storage details, such as the name of the table that stores the collection and the names and data types of its columns.
The presence or absence of columns for creation time stamp, last-modified time stamp, and version.
Whether the collection can store only JSON documents (the media type for the column).
Methods of document-key generation, and whether document keys are client-assigned or generated automatically.
Methods of version generation.

This configurability also lets you map a new collection to an existing database table or view.

To configure a collection in a nondefault way, you must define custom collection metadata and pass it to the create-collection operation. This metadata is represented as JSON data.

Note:

You can customize collection metadata to obtain different behavior from that provided by default. Just what you can change depends on the database you are using. If you are using an Oracle Autonomous Database — Autonomous JSON Database (AJD), Autonomous Transaction Processing (ATP), or Autonomous Data Warehouse (ADW) — then the only metadata you can customize are the document-key generation method and the content media type. In particular, you cannot change the SQL data type of the column used to store JSON content (the content column).

In addition, changing some components requires familiarity with Oracle Database concepts, such as SQL data types. Oracle recommends that you do not change such components unless you have a compelling reason. Because SODA collections are implemented on top of Oracle Database tables (or views), many collection configuration components are related to the underlying table configuration.

Reasons you might want to use custom metadata include:

To configure SecureFiles LOB storage.
To configure a collection to store documents other than JSON (a heterogeneous collection).
To map an existing Oracle Database table or view to a new collection.
To specify that a collection mapping to an existing table is read-only.
To use a VARCHAR2 column for JSON content, and to increase the default maximum length of data allowed in the column.

You might want to increase the maximum allowed data length if your database is configured with extended data types, which extends the maximum length of these data types to 32767 bytes. For more information about extended data types, see Oracle Database SQL Language Reference.

Note:

You can use online redefinition to change the metadata of an existing collection.

An important use case for this arises when you upgrade your database so that initialization parameter compatible is at least 20. Then the default collection metadata specifies JSON type for the content storage and UUID for the method used to generate version values.

To take advantage of JSON type for the content storage of an existing collection, use online redefinition to change metadata field contentColumn.sqlType to "JSON". To use UUID as the version column generation method, use online redefinition to change metadata field versionColumn.method to "UUID".

See Redefining a SODA Collection in Oracle Database SODA for PL/SQL Developer's Guide for how to do this.

You can perform read and write operations on a collection only if it is open. Opening a collection amounts to obtaining an object (in Java and PL/SQL) or a handle (in C) that represents the collection. Creating a collection opens it automatically: the create-collection operation returns a collection object or handle. There is also an open operation, to open an existing collection. It too returns a collection object or handle. If you try to create a collection, and a collection with the same name already exists, then that existing collection is simply opened.

Note:

By default, the table name for a collection is derived from the collection name, but it can also be explicitly provided in the custom collection metadata that you pass to the create-collection operation. If this table name (derived or explicitly provided) matches an existing table in the currently connected database schema (user), the create-collection operation tries to use that existing table to back the collection.^Foot 4

You must therefore ensure that the existing table matches the collection metadata. For example, if the collection metadata specifies that the collection has three columns, for key, content, and version, then the underlying table must have these same columns, and the column types must match those specified in the collection metadata. The create-collection operation performs minimal validation of the existing table, to check that it matches collection metadata. If this check determines that the table and metadata do not match then the create-collection operation raises an error.

Caution:

Do not use SQL to drop the database table that underlies a collection. Dropping a collection involves more than just dropping its database table. In addition to the documents that are stored in its table, a collection has metadata, which is also persisted in Oracle Database. Dropping the table underlying a collection does not also drop the collection metadata.

Example 1-1 Default Collection Metadata

This example shows the default metadata used for an Oracle Database that is not an Oracle Autonomous Database (Autonomous Transaction Processing or Autonomous Data Warehouse). The default metadata for an autonomous database is described in Default Collection Metadata.

If database initialization parameter compatible is at least 20, then SODA uses JSON data type by default for JSON content, and the version method is UUID. Here is the default metadata for this case:

{
   "schemaName" : "mySchemaName",
   "tableName" : "myTableName",
   "keyColumn" :
   {
      "name" : "ID",
      "sqlType" : "VARCHAR2",
      "maxLength" : 255,
      "assignmentMethod" : "UUID"
   },
   "contentColumn" :
   {
      "name" : "JSON_DOCUMENT",
      "sqlType" : "JSON"
   },
   "versionColumn" :
   {
     "name" : "VERSION",
     "method" : "UUID"
   },
   "lastModifiedColumn" :
   {
     "name" : "LAST_MODIFIED"
   },
   "creationTimeColumn" :
   {
      "name" : "CREATED_ON"
   },
   "readOnly" : false
}

If initialization parameter compatible is less than 20, then SODA uses "BLOB" textual data by default for JSON content — the data is character data. Here is the default metadata for this case:

{
   "schemaName" : "mySchemaName",
   "tableName" : "myTableName",
   "keyColumn" :
   {
      "name" : "ID",
      "sqlType" : "VARCHAR2",
      "maxLength" : 255,
      "assignmentMethod" : "UUID"
   },
   "contentColumn" :
   {
      "name" : "JSON_DOCUMENT",
      "sqlType" : "BLOB",
      "compress" : "NONE",
      "cache" : true,
      "encrypt" : "NONE",
      "validation" : "STANDARD"
   },
   "versionColumn" :
   {
     "name" : "VERSION",
     "method" : "SHA256"
   },
   "lastModifiedColumn" :
   {
     "name" : "LAST_MODIFIED"
   },
   "creationTimeColumn" :
   {
      "name" : "CREATED_ON"
   },
   "readOnly" : false
}

Related Topics

Parent topic: Overview of SODA

1.3 Default Naming of a Collection Table

By default, the name of the database table that underlies a document collection is derived from the collection name.

If you want a different table name from that provided by default then use custom collection metadata to explicitly provide the name.

The default table name is derived from the collection name you provide, as follows:

Each ASCII control character and double quotation mark character (") in the collection name is replaced by an underscore character (_).
If all of the following conditions apply, then all letters in the name are converted to uppercase, to provide the table name. In this case, you need not quote the table name in SQL code; otherwise, you must quote it.
- The letters in the name are either all lowercase or all uppercase.
- The name begins with an ASCII letter.
- Each character in the name is alphanumeric ASCII, an underscore (_), a dollar sign ($), or a number sign (#).
  
  Note:
  
  Oracle recommends that you do not use dollar-sign characters ($) or number-sign characters (#) in Oracle identifier names.

For example:

Collection names "col" and "COL" both result in a table named "COL". When used in SQL, the table name is interpreted case-insensitively, so it need not be enclosed in double quotation marks (").
Collection name "myCol" results in a table named "myCol". When used in SQL, the table name is interpreted case-sensitively, so it must be enclosed in double quotation marks (").

Related Topics

Table or View

Parent topic: Overview of SODA

1.4 A View of Your SODA Collections

Oracle Database static data dictionary view USER_SODA_COLLECTIONS lists the basic features of all of your SODA collections, that is, all SODA collections created by the database user (database schema) that you are currently connected to the database as.

The view includes, for each collection, its metadata and its underlying database information, in particular, the collection name and the name and database schema of the table or view that backs the collection.

Similarly, for database administrators there is view DBA_SODA_COLLECTIONS, which lists collection information for all users (database schemas). It has the additional column OWNER, which specifies the collection owner. You need the SELECT privilege on this view to select data from it. This privilege is granted to role SELECT_CATALOG_ROLE.

View DBA_SODA_COLLECTIONS

You can use Structured Query Language (SQL) to select data for one or more of your collections.

Note:

Oracle Database Reference for complete information about data dictionary views USER_SODA_COLLECTIONS and DBA_SODA_COLLECTIONS. View DBA_SODA_COLLECTIONS is available only for Autonomous Oracle Database 19c.

Example 1-2 Selecting Collection Data From USER_SODA_COLLECTIONS

This example selects all columns from the row of view USER_SODA_COLLECTIONS that corresponds to collection name myCol.

SELECT * FROM USER_SODA_COLLECTIONS
  WHERE URI_NAME = 'myCol';

Parent topic: Overview of SODA

1.5 Monitoring SODA Operation Performance

You can turn on performance monitoring of the SQL operations that underlie a SODA read or write operation, by adding a SQL hint to the SODA operation.

The hint you provide to the SODA operation uses the Oracle SQL hint syntax (that is, the actual hint text, without the enclosing SQL comment syntax /*+...*/).

Refer to the documentation for a particular SODA implementation (language) for how to provide a hint for a given operation.

Use only hint MONITOR (turn on monitoring) or NO_MONITOR (turn off monitoring). You can use this to pass any SQL hints, but MONITOR and NO_MONITOR are the useful ones for SODA, and an inappropriate hint can cause the optimizer to produce a suboptimal query plan.