16. API: SODA

Oracle Database Simple Oracle Document Access (SODA) allows documents to be inserted, queried, and retrieved from Oracle Database using a set of NoSQL-style python-oracledb methods. By default, documents are JSON strings. See the user manual for examples.

Note

SODA is only supported in python-oracledb Thick mode. See Enabling python-oracledb Thick mode.

16.1. SODA Requirements

To use SODA, the role SODA_APP must be granted to the user. To create collections, users need the CREATE TABLE privilege. These can be granted by a DBA:

SQL> grant soda_app, create table to myuser;

Advanced users who are using Oracle sequences for keys will also need the CREATE SEQUENCE privilege.

SODA requires Oracle Database 18.1 and later.

Note

SODA APIs are only supported in python-oracledb Thick mode. See Enabling python-oracledb Thick mode.

If you are using Oracle Database 21c (or later) and create new collections you need to do one of the following:

Use Oracle Client libraries 21c (or later)

Or explicitly use collection metadata when creating collections and set the data storage type to BLOB, for example:

{
    "keyColumn": {
        "name":"ID"
    },
    "contentColumn": {
        "name": "JSON_DOCUMENT",
        "sqlType": "BLOB"
    },
    "versionColumn": {
        "name": "VERSION",
        "method": "UUID"
    },
    "lastModifiedColumn": {
        "name": "LAST_MODIFIED"
    },
    "creationTimeColumn": {
        "name": "CREATED_ON"
    }
}

Or set the database initialization parameter compatible to 19.0.0 or lower

Otherwise, you may get errors such as ORA-40842: unsupported value JSON in the metadata for the field sqlType or ORA-40659: Data type does not match the specification in the collection metadata.

16.2. SodaDatabase Class

class oracledb.SodaDatabase

A SodaDatabase object is returned by Connection.getSodaDatabase().

This object is an extension to the DB API definition.

16.2.1. SodaDatabase Methods

SodaDatabase.createCollection(name: str, metadata: str | dict | None = None, mapMode: bool = False) → SodaCollection

Creates a SODA collection with the given name and returns a new SODA collection object. If you try to create a collection, and a collection with the same name and metadata already exists, then that existing collection is opened without error.

If metadata is specified, it is expected to be a string containing valid JSON or a dictionary that will be transformed into a JSON string. This JSON permits you to specify the configuration of the collection including storage options; specifying the presence or absence of columns for creation timestamp, last modified timestamp and version; whether the collection can store only JSON documents; and methods of key and version generation. The default metadata creates a collection that only supports JSON documents and uses system generated keys.

If the mapMode parameter is set to True, the new collection is mapped to an existing table instead of creating a table. If a collection is created in this way, dropping the collection will not drop the existing table either.

SodaDatabase.createDocument(content: Any, key: str | None = None, mediaType: str = 'application/json') → SodaDocument

Creates a SODA document usable for SODA write operations. You only need to use this method if your collection requires client-assigned keys or has non-JSON content; otherwise, you can pass your content directly to SODA write operations. SodaDocument attributes createdOn, lastModified, and version will be None.

The content parameter can be a dictionary or list which will be transformed into a JSON string and then UTF-8 encoded. It can also be a string which will be UTF-8 encoded or it can be a bytes object which will be stored unchanged. If a bytes object is provided and the content is expected to be JSON, note that SODA only supports UTF-8, UTF-16LE, and UTF-16BE encodings.

The key parameter should only be supplied if the collection in which the document is to be placed requires client-assigned keys.

The mediaType parameter should only be supplied if the collection in which the document is to be placed supports non-JSON documents and the content for this document is non-JSON. Using a standard MIME type for this value is recommended but any string will be accepted.

SodaDatabase.getCollectionNames(startName: str | None = None, limit: int = 0) → list[str]

Returns a list of the names of collections in the database that match the criteria, in alphabetical order.

If the startName parameter is specified, the list of names returned will start with this value and also contain any names that fall after this value in alphabetical order.

If the limit parameter is specified and is non-zero, the number of collection names returned will be limited to this value.

SodaDatabase.openCollection(name: str) → SodaCollection: Opens an existing collection with the given name and returns a new SODA collection object. If a collection with that name does not exist, None is returned.

16.3. SodaCollection Class

class oracledb.SodaCollection

A SODA Collection object is used to represent SODA collections and is created by SodaDatabase.createCollection() and SodaDatabase.openCollection().

This object is an extension to the DB API definition.

16.3.1. SodaCollection Methods

SodaCollection.createIndex(spec: dict | str) → None

Creates an index on a SODA collection.

The spec parameter is expected to be a dictionary or a JSON-encoded string.

Note that a commit should be performed before attempting to create an index.

See also

Overview of SODA Indexing

SodaCollection.drop() → bool

Drops the collection from the database, if it exists. Note that if the collection was created with mapMode set to True, the underlying table will not be dropped.

A boolean value is returned indicating if the collection was actually dropped.

SodaCollection.dropIndex(name: str, force: bool = False) → bool

Drops the index with the specified name, if it exists.

The force parameter, if set to True, can be used to force the dropping of an index that the underlying Oracle Database domain index does not normally permit. This is only applicable to spatial and JSON search indexes.

A boolean value is returned indicating if the index was actually dropped.

See also

DROP INDEX statement

SodaCollection.find() → SodaOperation: Begins an operation that will act upon documents in the collection. It creates and returns a SodaOperation object which is used to specify the criteria and the operation that will be performed on the documents that match that criteria.

See also

SodaOperation object

SodaCollection.getDataGuide() → SodaDocument: Returns a SODA document object containing property names, data types, and lengths inferred from the JSON documents in the collection. It can be useful for exploring the schema of a collection. Note that this method is only supported for JSON-only collections where a JSON search index has been created with the ‘dataguide’ option enabled. If there are no documents in the collection, None is returned.

See also

SODA document object

SodaCollection.insertMany(docs: list) → None

Inserts a list of documents into the collection at one time. Each of the input documents can be a dictionary or list or an existing SODA document object.

This method requires Oracle Client 18.5 (or later) and is available only as a preview.

See also

SODA document object.

SodaCollection.insertManyAndGet(docs: list, hint: str | None = None) → list[SodaDocument]

Similar to SodaCollection.insertMany(), this method inserts a list of documents into the collection at one time. The only difference is that it returns a list of SODA Document objects. Note that for performance reasons the returned documents do not contain the content.

The hint parameter, if specified, supplies a hint to the database when processing the SODA operation. This is expected to be a string in the same format as SQL hints but without any comment characters, for example hint=”MONITOR”. While you could use this to pass any SQL hint, the hints MONITOR (turn on monitoring) and NO_MONITOR (turn off monitoring) are the most useful. Use of the hint parameter requires Oracle Client 21.3 or later (or Oracle Client 19 from 19.11).

This method requires Oracle Client 18.5 (or later).

SodaCollection.insertOne(doc: Any) → None: Inserts a given document into the collection. The input document can be a dictionary or list or an existing SODA document object.

See also

SODA document object

SodaCollection.insertOneAndGet(doc: Any, hint: str | None = None) → SodaDocument

Similar to insertOne(), this method inserts a given document into the collection. The only difference is that it returns a SODA Document object. Note that for performance reasons the returned document does not contain the content.

The hint parameter, if specified, supplies a hint to the database when processing the SODA operation. This is expected to be a string in the same format as SQL hints but without any comment characters, for example hint=”MONITOR”. While you could use this to pass any SQL hint, the hints MONITOR (turn on monitoring) and NO_MONITOR (turn off monitoring) are the most useful. Use of the hint parameter requires Oracle Client 21.3 or later (or Oracle Client 19 from 19.11).

SodaCollection.listIndexes() → list

Returns a list of specifications for the indexes found on the collection.

This method requires Oracle Client 21.3 or later (or Oracle Client 19 from 19.13).

Added in version 1.4.0.

SodaCollection.save(doc: Any) → None

Saves a document into the collection. This method is equivalent to insertOne() except that if client-assigned keys are used, and the document with the specified key already exists in the collection, it will be replaced with the input document.

This method requires Oracle Client 19.9 (or later) in addition to the usual SODA requirements.

SodaCollection.saveAndGet(doc: Any, hint: str | None = None) → SodaDocument

Saves a document into the collection. This method is equivalent to insertOneAndGet() except that if client-assigned keys are used, and the document with the specified key already exists in the collection, it will be replaced with the input document.

The hint parameter, if specified, supplies a hint to the database when processing the SODA operation. This is expected to be a string in the same format as SQL hints but without any comment characters, for example hint=”MONITOR”. While you could use this to pass any SQL hint, the hints MONITOR (turn on monitoring) and NO_MONITOR (turn off monitoring) are the most useful. Use of the hint parameter requires Oracle Client 21.3 or later (or Oracle Client 19 from 19.11).

This method requires Oracle Client 19.9 (or later) in addition to the usual SODA requirements.

SodaCollection.truncate() → None: Removes all of the documents in the collection, similarly to what is done for rows in a table by the TRUNCATE TABLE statement.

16.3.2. SodaCollection Attributes

property SodaCollection.metadata: dict: This read-only attribute returns a dictionary containing the metadata that was used to create the collection.

See also

Oracle Database SODA Collection Metadata Reference

property SodaCollection.name: str: This read-only attribute returns the name of the collection.

16.4. SodaDocument Class

class oracledb.SodaDocument

A SODA Document object is returned by SodaDatabase.createDocument(), SodaOperation.getDocuments(), and SodaOperation.getOne() as well as by iterating over SODA document cursors.

This object is an extension to the DB API definition.

16.4.1. SodaDocument Methods

SodaDocument.getContent() → dict | list: Returns the content of the document as a dictionary or list. This method assumes that the content is application/json and will raise an exception if this is not the case. If there is no content, however, None will be returned.

SodaDocument.getContentAsBytes() → bytes: Returns the content of the document as a bytes object. If there is no content, however, None will be returned.

SodaDocument.getContentAsString() → str: Returns the content of the document as a string. If the document encoding is not known, UTF-8 will be used. If there is no content, however, None will be returned.

16.4.2. SodaDocument Attributes

property SodaDocument.createdOn: str: This read-only attribute returns the creation time of the document in ISO 8601 format. Documents created by SodaDatabase.createDocument() or fetched from collections where this attribute is not stored will return None.

See also

ISO 8601

property SodaDocument.key: str: This read-only attribute returns the unique key assigned to this document. Documents created by SodaDatabase.createDocument() may not have a value assigned to them and return None.

property SodaDocument.lastModified: str: This read-only attribute returns the last modified time of the document in ISO 8601 format. Documents created by SodaDatabase.createDocument() or fetched from collections where this attribute is not stored will return None.

See also

ISO 8601

property SodaDocument.mediaType: str: This read-only attribute returns the media type assigned to the document. By convention this is expected to be a MIME type but no checks are performed on this value. If a value is not specified when calling SodaDatabase.createDocument() or the document is fetched from a collection where this component is not stored, the string “application/json” is returned.

property SodaDocument.version: str: This read-only attribute returns the version assigned to this document. Documents created by SodaDatabase.createDocument() or fetched from collections where this attribute is not stored will return None.

16.5. SodaDocCursor Class

class oracledb.SodaDocCursor

A SodaDocCursor object is returned by SodaOperation.getCursor() and implements the iterator protocol. Each iteration will return a SODA document object.

This object is an extension to the DB API definition.

16.5.1. SodaDocCursor Methods

SodaDocCursor.close() → None: Closes the cursor now, rather than whenever __del__ is called. The cursor will be unusable from this point forward; an Error exception will be raised if any operation is attempted with the cursor.

16.6. SodaOperation Class

class oracledb.SodaOperation(collection: SodaCollection)

A SODA Operation object represents an operation that will be performed on all or some of the documents in a SODA collection. This object is created by SodaCollection.find().

This object is an extension to the DB API definition.

16.6.1. SodaOperation Methods

SodaOperation.count() → int: Returns a count of the number of documents in the collection that match the criteria. If skip() or limit() were called on this object, an exception is raised.

SodaOperation.fetchArraySize(value: int) → Self

This is a tuning method to specify the number of documents that are internally fetched in batches by calls to getCursor() and getDocuments(). It does not affect how many documents are returned to the application.

If fetchArraySize() is not used, or the value parameter is 0, the array size will default to 100.

As a convenience, the SodaOperation object is returned so that further criteria can be specified by chaining methods together.

This method is only available when using Oracle Client 19.5, or later.

SodaOperation.filter(value: dict | str) → Self

Sets a filter specification for complex document queries and ordering of JSON documents. Filter specifications must be provided as a dictionary or JSON-encoded string and can include comparisons, regular expressions, logical and spatial operators, among others.

As a convenience, the SodaOperation object is returned so that further criteria can be specified by chaining methods together.

See also

Overview of SODA filter specifications

SodaOperation.getCursor() → SodaDocCursor: Returns a SodaDocCursor object that can be used to iterate over the documents that match the criteria.

See also

SODA Document Cursor object

SodaOperation.getDocuments() → list[SodaDocument]: Returns a list of SodaDocument objects that match the criteria.

See also

SODA Document objects

SodaOperation.getOne() → SodaDocument | None: Returns a single SodaDocument object that matches the criteria. Note that if multiple documents match the criteria only the first one is returned.

See also

SODA Document object

SodaOperation.hint(value: str) → Self

Specifies a hint that will be provided to the SODA operation when it is performed. This is expected to be a string in the same format as SQL hints but without any comment characters. While you could use this to pass any SQL hint, the hints MONITOR (turn on monitoring) and NO_MONITOR (turn off monitoring) are the most useful. Use of this method requires Oracle Client 21.3 or later (or Oracle Client 19 from 19.11).

As a convenience, the SodaOperation object is returned so that further criteria can be specified by chaining methods together.

See also

Oracle AI Database SQL Tuning Guide documentation MONITOR and NO_MONITOR Hints and Monitoring Database Operations

SodaOperation.key(value: str) → Self

Specifies that the document with the specified key should be returned. This causes any previous calls made to this method and keys() to be ignored.

As a convenience, the SodaOperation object is returned so that further criteria can be specified by chaining methods together.

SodaOperation.keys(value: list) → Self

Specifies that documents that match the keys found in the supplied sequence should be returned. This causes any previous calls made to this method and key() to be ignored.

As a convenience, the SodaOperation object is returned so that further criteria can be specified by chaining methods together.

SodaOperation.limit(value: int) → Self

Specifies that only the specified number of documents should be returned. This method is only usable for read operations such as getCursor() and getDocuments(). For write operations, any value set using this method is ignored.

As a convenience, the SodaOperation object is returned so that further criteria can be specified by chaining methods together.

SodaOperation.lock() → Self

Specifies whether the documents fetched from the collection should be locked (equivalent to SQL “select for update”). Use of this method requires Oracle Client 21.3 or later (or Oracle Client 19 from 19.11).

The next commit or rollback on the connection made after the operation is performed will “unlock” the documents. Ensure that the connection is not in autocommit mode or the documents will be unlocked immediately after the operation is complete.

This method should only be used with read operations (other than count()) and should not be used in conjunction with non-terminal methods skip() and limit().

If this method is specified in conjunction with a write operation, this method is ignored.

As a convenience, the SodaOperation object is returned so that further criteria can be specified by chaining methods together.

Added in version 1.4.0.

SodaOperation.remove() → int: Removes all of the documents in the collection that match the criteria. The number of documents that have been removed is returned.

SodaOperation.replaceOne(doc: Any) → bool

Replaces a single document in the collection with the specified document. The input document can be a dictionary or list or an existing SODA document object. A boolean indicating if a document was replaced or not is returned.

Currently, the method key() must be called before this method can be called.

See also

SODA document object

SodaOperation.replaceOneAndGet(doc: Any) → SodaDocument: Similar to replaceOne(), this method replaces a single document in the collection with the specified document. The only difference is that it returns a SodaDocument object. Note that for performance reasons the returned document does not contain the content.

See also

SODA document object

SodaOperation.skip(value: int) → Self

Specifies the number of documents that match the other criteria that will be skipped. This method is only usable for read operations such as getOne(), getCursor(), and getDocuments(). For write operations, any value set using this method is ignored.

As a convenience, the SodaOperation object is returned so that further criteria can be specified by chaining methods together.

SodaOperation.version(value: str) → Self

Specifies that documents with the specified version should be returned. Typically this is used with key() to implement optimistic locking, so that the write operation called later does not affect a document that someone else has modified.

As a convenience, the SodaOperation object is returned so that further criteria can be specified by chaining methods together.