10. API: SodaOperation Class
You can chain together SodaOperation methods to specify read or write operations against a collection.
Note
In this release, SODA is only supported in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.
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.
Non-terminal SodaOperation methods return the same object on which they are invoked, allowing them to be chained together.
A terminal SodaOperation method always appears at the end of a method chain to execute the operation.
A SodaOperation object is an internal object. You should not directly modify its properties.
10.1. Non-terminal SodaOperation Methods
Non-terminal SodaOperation methods are chained together to set criteria that documents must satisfy. At the end of the chain, a single terminal method specifies the operation to be performed on the matching documents.
When a non-terminal method is repeated, the last one overrides the
earlier one. For example if find().key("a").key("b")...
was used,
then only documents with the key “b” are matched. If
find().keys(["a","b"]).key("c")...
is used, then only the document
with the key “c” is matched.
- sodaOperation.fetchArraySize()
Added in version 5.0.
fetchArraySize(Number size)
Sets the size of an internal buffer used for fetching documents from a collection with the terminal SodaOperation methods
getCursor()
andgetDocuments()
. Changingsize
may affect performance but does not affect how many documents are returned.If
fetchArraySize()
is not used, the size defaults to the current value oforacledb.fetchArraySize
.For node-oracledb examples, see SODA Query-by-Example Searches for JSON Documents.
It requires Oracle Client 19.5 or later, and Oracle Database 18.3 or later.
- sodaOperation.filter()
Added in version 3.0.
filter(Object filterSpec)
Sets a filter specification for the operation, allowing for complex document queries and ordering of JSON documents. Filter specifications can include comparisons, regular expressions, logical, and spatial operators, among others. See Overview of SODA Filter Specifications (QBEs) and SODA Filter Specifications (Reference).
For node-oracledb examples, see SODA Query-by-Example Searches for JSON Documents.
- sodaOperation.hint()
Added in version 5.2.
hint(String hint)
The
hint()
value can be used to pass an Oracle hint to terminal SodaOperation Methods. It is string in the same format as a SQL hint but without any comment characters, for examplehint("MONITOR")
. Pass 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 21.3 or higher (or Oracle Client 19 from 19.11).
- sodaOperation.key()
Added in version 3.0.
key(String value)
Sets the key value to be used to match a document for the operation. Any previous calls made to this method or
keys()
will be ignored.SODA document keys are unique.
- sodaOperation.keys()
Added in version 3.0.
keys(Array value)
Sets the keys to be used to match multiple documents for the operation. Any previous calls made to this method or
sodaOperation.key()
will be ignored.SODA document keys are unique.
A maximum of 1000 keys can be used.
- sodaOperation.limit()
Added in version 3.0.
limit(Number n)
Sets the maximum number of documents that a terminal method will apply to. The value of
n
must be greater than 0. The limit is applied to documents that match the other SodaOperation criteria. Thelimit()
method only applies to SodaOperation read operations likegetCursor()
andgetDocuments()
. If a filter$orderby
is not used, the document order is internally defined.The
limit()
method cannot be used in conjunction withcount()
.
- sodaOperation.lock()
Added in version 6.2.
lock()
Locks the documents fetched from the collection.
Using
lock()
allows for pessimistic locking, that is, only the current user that performed the lock can modify the documents in the collection. Other users can only perform operations on these documents once they are unlocked. The functionality of this method is equivalent to theSELECT FOR UPDATE
clause.The documents can be unlocked with an explicit call to
commit()
orrollback()
on the connection. Ensure that theoracledb.autoCommit
is set to false for the connection. Otherwise, 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 methodsskip()
andlimit()
.If this method is specified in conjunction with a write operation, then this method is ignored.
This method requires Oracle Client 21.3 or later (or Oracle Client 19 from 19.11).
- sodaOperation.skip()
Added in version 3.0.
skip(Number n)
Sets the number of documents that will be skipped before the terminal method is applied. The value of
n
must be greater or equal to 0. The skip applies to documents that match the other SodaOperation criteria.If a filter
$orderby
is not used, the document order (and hence which documents are skipped) is internally defined.The
skip()
method only applies to SodaOperation read operations likegetDocuments()
. It cannot be used withcount()
.
- sodaOperation.version()
Added in version 3.0.
version(String value)
Sets the document version that documents must have.
This is typically used in conjunction with a key, for example
collection.find().key("k").version("v").replaceOne(doc)
.Using
version()
allows for optimistic locking, so that the subsequent SodaOperation terminal method does not affect a document that someone else has already modified. If the requested document version is not matched, then your terminal operation will not impact any document. The application can then query to find the latest document version and apply any desired change.
10.2. Terminal SodaOperation Methods
A terminal SodaOperation method operates on the set of documents that satisfy the criteria specified by previous non-terminal methods in the method chain. Only one terminal method can be used in each chain.
- sodaOperation.count()
Added in version 3.0.
Promise:
promise = count();
Finds the number of documents matching the given SodaOperation query criteria.
If
skip()
orlimit()
are set, thencount()
will return an error.If
oracledb.autoCommit
is true, andcount()
succeeds, then any open transaction on the connection is committed.Callback:
If you are using the callback programming style:
count(function (Error error, Object result){});
The parameters of the callback function
function (Error error, Object result)
are:Callback Function Parameter
Description
Error
error
If
count()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.Object
result
The result object contains one attribute:
Number count
The number of documents matching the SodaOperation criteria.
Due to Node.js type limitations, the largest
count
value will be 232 - 1, even if more rows exist. Larger values will wrap.
- sodaOperation.getCursor()
Added in version 3.0.
Promise:
promise = getCursor()
Returns a SodaDocumentCursor for documents that match the SodaOperation query criteria. The cursor can be iterated over with
sodaDocumentCursor.getNext()
to access each SodaDocument.When the application has completed using the cursor it must be closed with
sodaDocumentCursor.close()
.If the number of documents is known to be small, it is recommended to use
sodaOperation.getDocuments()
instead.If
oracledb.autoCommit
is true, andgetCursor()
succeeds, then any open transaction on the connection is committed.Callback:
If you are using the callback programming style:
getCursor(function(Error error, SodaDocumentCursor cursor){});
The parameters of the callback function
function(Error error, SodaDocumentCursor cursor)
are:Callback Function Parameter
Description
Error
error
If
getCursor()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.SodaDocumentCursor
cursor
A cursor that can be iterated over to access SodaDocument objects matching the SodaOperation search criteria.
- sodaOperation.getDocuments()
Added in version 3.0.
Promise:
promise = getDocuments();
Gets an array of SodaDocuments matching the SodaOperation query criteria. An empty array will be returned when no documents match.
Where the number of matching documents is known to be small, this API should be used in preference to
sodaOperation.getCursor()
.If
oracledb.autoCommit
is true, andgetDocuments()
succeeds, then any open transaction on the connection is committed.Callback:
If you are using the callback programming style:
getDocuments(function(Error error, Array documents){});
The parameters of the callback function
function(Error error, Array documents)
are:Callback Function Parameter
Description
Error
error
If
getDocuments()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.Array
documents
An array of SodaDocuments that match the SodaOperation query criteria.
- sodaOperation.getOne()
Added in version 3.0.
Promise:
promise = getOne();
Obtains one document matching the SodaOperation query criteria. If the criteria match more than one document, then only the first is returned.
Typically
getone()
should be used withkey(k)
orkey(k).version(v)
to ensure only one document is matched.If
oracledb.autoCommit
is true, andgetOne()
succeeds, then any open transaction on the connection is committed.Callback:
If you are using the callback programming style:
getOne(function(Error error, SodaDocument document){});
The parameters of the callback function
function(Error error, SodaDocument document)
are:Callback Function Parameter
Description
Error
error
If
getOne()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.SodaDocument
document
One SodaDocument that matches the sodaOperation query criteria. If no document is found, then
document
will be undefined.
- sodaOperation.remove()
Added in version 3.0.
Promise:
promise = remove();
Removes a set of documents matching the SodaOperation query criteria.
Note settings from
skip()
andlimit()
non-terminals are ignored because they only apply to read operations.If
oracledb.autoCommit
is true, andremove()
succeeds, then removal and any open transaction on the connection is committed.Callback:
If you are using the callback programming style:
remove(function(Error error, Object result){});
The parameters of the callback function
function(Error error, Object result)
are:Callback Function Parameter
Description
Error
error
If
remove()
succeeds,error
is NULL. If an error occurs, thenerror
contains the error message.Object
result
The result object contains one attribute:
result.count
The number of documents removed from the collection.
Due to Node.js type limitations, the largest
count
value will be 232 - 1, even if Oracle Database removed more rows. Larger values will wrap.
- sodaOperation.replaceOne()
Added in version 3.0.
Promise:
promise = replaceOne(Object newDocumentContent); promise = replaceOne(SodaDocument newSodaDocument);
Replaces a document in a collection. The input document can be either a JavaScript object representing the data content, or it can be an existing SodaDocument.
The
mediaType
document component and content of the document that matches the SodaOperation query criteria will be replaced by the content and anymediaType
document component of the new document. Any other document components will not be affected. ThelastModified
andversion
document components of the replaced document will be updated.The
key()
non-terminal must be used when usingreplaceOne()
.No error is reported if the operation criteria do not match any document.
Note settings from
skip()
andlimit()
non-terminals are ignored because they only apply to read operations.If
oracledb.autoCommit
is true, andreplaceOne()
succeeds, then any open transaction on the connection is committed.The parameters of the
sodaOperation.replaceOne()
method are:Table 10.1 sodaOperation.replaceOne() Parameters Parameter
Data Type
Description
newDocumentContent
ornewSodaDocument
Object or SodaDocument
The new document. See
sodaCollection.insertOne()
, which has the same semantics for the document.Callback:
If you are using the callback programming style:
replaceOne(Object newDocumentContent, function(Error error, Object result){}); replaceOne(SodaDocument newSodaDocument, function(Error error, Object result){});
See sodaOperation.replaceOne() Parameters for information on the parameters.
The parameters of the callback function
function(Error error, Object result)
are:Callback Function Parameter
Description
Error
error
If
replaceOne()
succeeds,error
is NULL. It is not an error if no document is replaced. If an error occurs, thenerror
contains the error message.Object
result
The result object contains one attribute:
result.replaced
This attribute will be true if the document was successfully replaced, false otherwise.
- sodaOperation.replaceOneAndGet()
Added in version 3.0.
Promise:
promise = replaceOneAndGet(Object newDocumentContent); promise = replaceOneAndGet(SodaDocument newSodaDocument);
Replaces a document in a collection similar to
sodaOperation.replaceOne()
, 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. The result document has new values for components that are updated as part of the replace operation (such as version, last-modified timestamp, and media type)If
oracledb.autoCommit
is true, andreplaceOneAndGet()
succeeds, then any open transaction on the connection is committed.The parameters of the
sodaOperation.replaceOneAndGet()
method are:Table 10.2 sodaOperation.replaceOneAndGet() Parameters Parameter
Data Type
Description
newDocumentContent
ornewSodaDocument
Object or SodaDocument
The new document. See
sodaCollection.insertOne()
, which has the same semantics for the document.Callback:
If you are using the callback programming style:
replaceOneAndGet(Object newDocumentContent, function(Error error, SodaDocument updatedDocument){}); replaceOneAndGet(SodaDocument newSodaDocument, function(Error error, SodaDocument updatedDocument){});
See sodaOperation.replaceOneAndGet() Parameters for information on the parameters.
The parameters of the callback function
function(Error error, SodaDocument updatedDocument)
are:Callback Function Parameter
Description
Error
error
If
replaceOneAndGet()
succeeds,error
is NULL. It is not an error if no document is replaced. If an error occurs, thenerror
contains the error message.SodaDocument
updatedDocument
The updated SodaDocument if replacement was successful, otherwise
updatedDocument
will be undefined.The
lastModified
andversion
attributes of the stored SodaDocument will be updated. ThemediaType
attribute and the content will be replaced. Other attributes ofnewSodaDocument
are ignored.Note for performance reasons,
updatedDocument
will not have document content and cannot itself be passed directly to SODA insert or replace methods.