9. API: SodaCollection Class

SODA can be used with Oracle Database 18.3 and above, when node-oracledb uses Oracle Client 18.5 or Oracle Client 19.3, or later. The SODA bulk insert methods sodaCollection.insertMany() and sodaCollection.insertManyAndGet() are in Preview status.

Note

In this release, SODA is only supported in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

9.1. SodaCollection Properties

Each SodaCollection object contains read-only properties:

sodaCollection.metaData

Added in version 3.0.

This read-only property is an object which contains the metaData of the current collection. See SODA Client-Assigned Keys and Collection Metadata.

Its type was changed to Object in node-oracledb 4.0.

sodaCollection.name: Added in version 3.0.

This read-only property is a string which specifies the name of the current collection.

9.2. SodaCollection Methods

sodaCollection.createIndex()

Added in version 3.0.

Promise:

promise = createIndex(Object indexSpec);

Creates an index on a SODA collection, to improve the performance of SODA query-by-examples (QBE) or enable text searches. See Creating and Dropping SODA Indexes for information on indexing.

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

If oracledb.autoCommit is true, and createIndex() succeeds, then any open user transaction is committed. Note SODA DDL operations do not commit an open transaction the way that SQL always does for DDL statements.

The parameters of the sodaCollection.createIndex() method are:

Table 9.1 sodaCollection.createIndex() Parameters
Parameter	Data Type	Description
`indexSpec`	Object	An object with fields as shown in the SODA Index Specifications (Reference) manual.

Callback:

If you are using the callback programming style:

createIndex(Object indexSpec, function(Error error){});

See sodaCollection.createIndex() Parameters for information on the parameters.

The parameters of the callback function function(Error error) are:

Callback Function Parameter	Description
Error `error`	If `createIndex()` succeeds, `error` is NULL. If an error occurs, then `error` contains the error message.

See Creating and Dropping SODA Indexes for more information.

sodaCollection.drop()

Added in version 3.0.

Promise:

promise = drop();

Drops the current collection.

An error such as ORA-40626 will be returned and the collection will not be dropped if there are uncommitted writes to the collection in the current transaction.

If the collection was created with mode oracledb.SODA_COLL_MAP_MODE, then drop() will not physically delete the database storage containing the collection, and won’t drop SODA indexes. Instead it will simply unmap the collection, making it inaccessible to SODA operations.

If oracledb.autoCommit is true, and drop() succeeds, then any open user transaction is committed. Note SODA operations do not commit an open transaction the way that SQL always does for DDL statements.

If the collection was created with custom metadata changing the key assignment method to SEQUENCE, the drop() method will not delete the underlying Oracle sequence. This is in case it was created outside SODA. To drop the sequence, use the SQL command DROP SEQUENCE after drop() has completed.

Note you should never use SQL DROP TABLE command on the database table underlying a collection. This will not clean up SODA’s metadata. If you do accidentally execute DROP SQL, you should cleanup the metadata with drop() or execute the SQL statement: SELECT DBMS_SODA.DROP_COLLECTION('myCollection') FROM DUAL;.

Callback:

If you are using the callback programming style:

drop(function(Error error, Object result){});

The parameters of the callback function function(Error error, Object result) are:

Callback Function Parameter

Description

Error error

If drop() succeeds, error is NULL. It is not an error if the collection does not exist. If an error occurs, then error contains the error message.

Object result

The result object contains one attribute:

Boolean dropped

If the drop operation succeeded, dropped will be true. If no collection was found, dropped will be false.

sodaCollection.dropIndex()

Added in version 3.0.

Promise:

promise = dropIndex(String indexName [, Object options]);

Drops the specified index.

If oracledb.autoCommit is true, and dropIndex() succeeds, then any open user transaction is committed. Note SODA operations do not commit an open transaction the way that SQL always does for DDL statements.

The parameters of the sodaCollection.dropIndex() method are:

Table 9.2 sodaCollection.dropIndex() Parameters
Parameter	Data Type	Description
`indexName`	String	Name of the index to be dropped.
`options`	Object	The `options` parameter can have the following attribute: Boolean force Setting `force` to true forces dropping of a JSON Search index or Spatial index if the underlying Oracle Database domain index does not permit normal dropping. See DROP INDEX.

Callback:

If you are using the callback programming style:

dropIndex(String indexName [, Object options], function(Error error, Object result){});

See sodaCollection.dropIndex() Parameters for information on the indexName and options parameters.

The parameters of the callback function function(Error error, Object result) are:

Callback Function Parameter	Description
Error `error`	If `dropIndex()` succeeds, `error` is NULL. It is not an error if the index does not exist. If an error occurs, then `error` contains the error message.
Object `result`	If dropping the index succeeded, `dropped` will be true. If no index was found, `dropped` will be false.

See Creating and Dropping SODA Indexes for an example.

sodaCollection.find()

Added in version 3.0.

find()

The synchronous find() method is used to locate and order a set of SODA documents for retrieval, replacement, or removal. It creates and returns a SodaOperation object which is used via method chaining with non-terminal and terminal methods described below. Note that SodaOperation is an internal object whose attributes should not be accessed directly.

Returns a SodaOperation object.

Example

documents = await collection.find().filter({"address.city": "Melbourne", "salary": {"$gt": 500000}}).getDocuments();

See Simple Oracle Document Access (SODA) for more examples.

sodaCollection.getDataGuide()

Added in version 3.0.

Promise:

promise = getDataGuide();

Infers the schema of a collection of JSON documents at the current time. A JSON data guide shows details like the JSON property names, data types and lengths. It is useful for exploring the schema of a collection. The data guide is represented as JSON content in a SodaDocument.

This method is supported for JSON-only collections which have a JSON Search index where the “dataguide” option is “on”. An error will be returned if a data guide cannot be created.

A data guide is a best effort heuristic and should not be used as a schema to validate new JSON documents. The data guide is always additive, and does not update itself when documents are deleted. There are some limits such as the maximum number of children under one node, and the maximum length of a path.

If oracledb.autoCommit is true, and getDataGuide() succeeds, then any open user transaction is committed.

Callback:

If you are using the callback programming style:

getDataGuide(function(Error error, SodaDocument document){});

The parameters of the callback function function(Error error, SodaDocument document) are:

Callback Function Parameter	Description
Error `error`	If `getDataGuide()` succeeds, `error` is NULL. It is not an error if no document is replaced. If an error occurs, then `error` contains the error message.
SodaDocument `document`	The SodaDocument containing JSON content which can be accessed from the document as normal with `sodaDocument.getContents()`, `sodaDocument.getContentAsString()`, or `sodaDocument.getContentAsBuffer()`.

sodaCollection.insertMany()

Added in version 4.0.

Promise:

promise = insertMany(Array newDocumentContentArray);
promise = insertMany(Array newSodaDocumentArray);

This is similar to insertOne() however it accepts an array of the Objects or SodaDocuments that insertOne() accepts. When inserting multiple documents, using insertMany() is recommended in preference to insertOne().

If an error occurs, the offset attribute on the Error objects will contain the number of documents that were successfully inserted. Subsequent documents in the input array will not be inserted.

This method is in Preview status and should not be used in production.

It requires Oracle Client 18.5 or higher.

Callback:

If you are using the callback programming style:

insertMany(Array newDocumentContentArray, function(Error error){});
insertMany(Array newSodaDocumentArray, function(Error error){});

sodaCollection.insertManyAndGet()

Added in version 4.0.

Promise:

promise = insertManyAndGet(Array newDocumentContentArray [, Object options ]);
promise = insertManyAndGet(Array newSodaDocumentArray [, Object options ]);

Similar to sodaCollection.insertMany() but also returns an array of the inserted documents so system managed properties, such as the keys (in default collections), can be found. Content itself is not returned for performance reasons. When inserting multiple documents, using insertManyAndGet() is recommended in preference to insertOneAndGet().

The options object can have one string property hint. Hints are strings without SQL comment characters, for example { hint: "MONITOR" }. Use only the hint "MONITOR" (turn on monitoring) or "NO_MONITOR" (turn off monitoring). See the Oracle Database SQL Tuning Guide documentation MONITOR and NO_MONITOR Hints and Monitoring Database Operations for more information.

This method is in Preview status and should not be used in production.

It requires Oracle Client 18.5 or higher. Use of the hint property requires Oracle Client 21.3 or higher (or Oracle Client 19 from 19.11).

This method accepts an options parameter from node-oracledb 5.2 onwards.

Callback:

If you are using the callback programming style:

insertManyAndGet(Array newDocumentContentArray [, Object options ], function(Error error, Array SodaDocuments){});
insertManyAndGet(Array newSodaDocumentArray [, Object options ], function(Error error, Array SodaDocuments){});

sodaCollection.insertOne()

Added in version 3.0.

Promise:

promise = insertOne(Object newDocumentContent);
promise = insertOne(SodaDocument newSodaDocument);

Inserts a given document to the collection. The input document can be either a JavaScript object representing the data content, or it can be an existing SodaDocument.

If oracledb.autoCommit is true, and insertOne() succeeds, then the new document and any open transaction on the connection is committed.

The following examples are equivalent:

newDocumentContent = {name: "Alison"};
await sodaCollection.insertOne(newDocumentContent);

and:

newDocumentContent = {name: "Alison"};
doc = sodaDatabase.createDocument(newDocumentContent);
await sodaCollection.insertOne(doc);

The parameters of the sodaCollection.insertOne() method are:

Table 9.3 sodaCollection.insertOne() Parameters
Parameter	Data Type	Description
`newDocumentContent` or `newSodaDocument`	Object or SodaDocument	The document to insert. Passed as a simple JavaScript object, the value is interpreted as JSON document content. Other document components (key, version, etc.) will be auto-generated by SODA during insert. The media type will be set to “application/json”. Alternatively, a SodaDocument can be passed. The `content` and `mediaType` supplied in the SodaDocument will be used. The `key`, if set, will also be used if collection has client-assigned keys. Other components in the input SodaDocument, such as version and last-modified, will be ignored and auto-generated values will be used instead.

Callback:

If you are using the callback programming style:

insertOne(Object newDocumentContent, function(Error error){});
insertOne(SodaDocument newSodaDocument, function(Error error){});

See sodaCollection.insertOne() Parameters for information on the newDocumentContent or SodaDocument parameters.

The parameters of the callback function function(Error error) are:

Callback Function Parameter	Description
Error `error`	If `insertOne()` succeeds, `error` is NULL. If an error occurs, then `error` contains the error message.

sodaCollection.insertOneAndGet()

Added in version 3.0.

Promise:

promise = insertOneAndGet(Object newDocumentContent [, Object options ]);
promise = insertOneAndGet(SodaDocument newSodaDocument [, Object options ]);

Inserts a document in a collection similar to sodaCollection.insertOne(), but also returns the result document which contains all SodaDocument components (key, version, etc.) except for content. Content itself is not returned for performance reasons.

If you want to insert the document again, use the original newDocumentContent or newSodaDocument. Alternatively construct a new object from the returned document and add content.

The options object can have one string property hint. Hints are strings without SQL comment characters, for example { hint: "MONITOR" }. Use only the hint "MONITOR" (turn on monitoring) or "NO_MONITOR" (turn off monitoring). See the Oracle Database SQL Tuning Guide documentation MONITOR and NO_MONITOR Hints and Monitoring Database Operations for more information.

If oracledb.autoCommit is true, and insertOneAndGet() succeeds, then any open transaction on the connection is committed.

This method accepts an options parameter from node-oracledb 5.2 onwards. Use of the hint property requires Oracle Client 21.3 or higher (or Oracle Client 19 from 19.11).

The parameters of the sodaCollection.insertOneAndGet() method are:

Table 9.4 sodaCollection.insertOneAndGet() Parameters
Parameter	Data Type	Description
`newDocumentContent` or `newSodaDocument`	Object or SodaDocument	The document to insert. For related documentation, see `sodaCollection.insertOne()`.

Callback:

If you are using the callback programming style:

insertOneAndGet(Object newDocumentContent [, Object options ], function(Error error, SodaDocument document){});
insertOneAndGet(SodaDocument newSodaDocument [, Object options ], function(Error error, SodaDocument document){});

See sodaCollection.insertOneAndGet() Parameters for information on the newDocumentContent or newSodaDocument parameter.

The parameters of the callback function function(Error error, SodaDocument document) are:

Callback Function Parameter

Description

Error error

If insertOne() succeeds, error is NULL. If an error occurs, then error contains the error message.

SodaDocument document

A result SodaDocument that is useful for finding the system generated key and other metadata of the newly inserted document.

Note for performance reasons, document will not have document content and cannot itself be passed directly to SODA insert or replace methods.

sodaCollection.listIndexes()

Added in version 6.2.

Promise::

promise = listIndexes();

Retrieves all the indexes from a SODA collection. This method returns an array of objects that contains the index specifications.

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

Callback:

If you are using the callback programming style:

listIndexes(function(Error error, Array listIndexes){});

The parameters of the callback function function(Error error, Array listIndexes) are:

Callback Function Parameter	Description
Error `error`	If `listIndexes()` succeeds, `error` is NULL. If an error occurs, then `error` contains the error message.
Array `listIndexes`	An array of objects, each containing the index specifications of the SODA collection.

See Retrieving All Index Specifications for an example.

sodaCollection.save()

Added in version 5.0.

Promise:

promise = save(SodaDocument newSodaDocument);

This method behaves like sodaCollection.insertOne() with the exception that if a document with the same key already exists, then it is updated instead.

The collection must use client-assigned keys keys, which is why save() accepts only a SodaDocument, unlike insertOne(). If the collection is not configured with client-assigned keys, then the behavior is exactly the same as sodaCollection.insertOne().

It requires Oracle Client 19.9 or later, and Oracle Database 18.3 or later.

Callback:

If you are using the callback programming style:

save(SodaDocument newSodaDocument, function(Error error){});

sodaCollection.saveAndGet()

Added in version 5.0.

Promise:

promise = saveAndGet(SodaDocument newSodaDocument [, Object options ]);

This method behaves like sodaCollection.insertOneAndGet() with the exception that if a document with the same key already exists, then it is updated instead.

The collection must use client-assigned keys keys, which is why saveAndGet() accepts only a SodaDocument, unlike insertOneAndGet(). If the collection is not configured with client-assigned keys, then the behavior is exactly the same as sodaCollection.insertOneAndGet().

The options object can have one string property hint. Hints are strings without SQL comment characters, for example { hint: "MONITOR" }. Use only the hint "MONITOR" (turn on monitoring) or "NO_MONITOR" (turn off monitoring). See the Oracle Database SQL Tuning Guide documentation MONITOR and NO_MONITOR Hints and Monitoring Database Operations for more information.

It requires Oracle Client 19.9 or later, and Oracle Database 18.3 or later. Use of the hint property requires Oracle Client 21.3 or higher (or Oracle Client 19 from 19.11).

This method accepts an options parameter from node-oracledb 5.2 onwards.

Callback:

If you are using the callback programming style:

saveAndGet(SodaDocument newSodaDocument [, Object options ], function(Error error, SodaDocument document){});

sodaCollection.truncate()

Added in version 5.0.

Promise

promise = truncate();

Truncates a collection, removing all documents. The collection will not be deleted.

It requires Oracle Client 20 or later, and Oracle Database 18.3 or later.

Callback:

If you are using the callback programming style:

truncate(function(Error error) {});

The parameters of the callback function function(Error error) are:

Callback Function Parameter	Description
Error `error`	If `truncate()` succeeds, `error` is NULL. If an error occurs, then `error` contains the error message.