15. Working with Simple Oracle Document Access (SODA)

Oracle Database Simple Oracle Document Access (SODA) documents can be inserted, queried, and retrieved from Oracle Database through NoSQL-style APIs. By default, documents are JSON strings but can be nearly any kind, including video, image, sound, and other binary content. Create, read, update and delete operations can be performed via document key lookups, or by query-by-example (QBE) pattern-matching.

Note

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

SODA internally uses a SQL schema to store documents but you do not need to know SQL or how the documents are stored. However, optional access via SQL does allow use of advanced Oracle Database functionality such as analytics for reporting. Applications that access a mixture of SODA objects and relational objects (or access SODA objects via SQL) are supported. Because SODA APIs internally use SQL, tuning the Statement Cache can be beneficial.

Oracle SODA implementations are also available in Python, Java, PL/SQL, Oracle Call Interface and via REST. The Simple Oracle Document Access homepage contains much information relevant to using SODA.

15.1. Node-oracledb SODA Objects

Node-oracledb uses the following objects for SODA:

SodaDatabase: The top level object for node-oracledb SODA operations. This is acquired from an Oracle Database connection. A ‘SODA database’ is an abstraction, allowing access to SODA collections in that ‘SODA database’, which then allow access to documents in those collections. A SODA database is analogous to an Oracle Database user or schema. A collection is analogous to a table. A document is analogous to a table row with one column for a unique document key, a column for the document content, and other columns for various document attributes.
SodaCollection: Represents a collection of SODA documents. By default, collections allow JSON documents to be stored. This is recommended for most SODA users. However optional metadata can set various details about a collection, such as its database storage, whether it should track version and time stamp document components, how such components are generated, and what document types are supported. See Collection Metadata for more information. By default, the name of the Oracle Database table storing a collection is the same as the collection name. Note: do not use SQL to drop the database table, since SODA metadata will not be correctly removed. Use the sodaCollection.drop() method instead.
SodaDocument: Represents a document. Typically the document content will be JSON. The document has properties including the content, a key, timestamps, and the media type. By default, document keys are automatically generated. See SodaDocument Class for the forms of SodaDocument.
SodaDocumentCursor: A cursor object representing the result of the getCursor() method from a sodaCollection.find() operation. It can be iterated over to access each SodaDocument.
SodaOperation: An internal object used with sodaCollection.find() to perform read and write operations on documents. Chained methods set properties on a SodaOperation object which is then used by a terminal method to find, count, replace, or remove documents. This is an internal object that should not be directly accessed.

15.2. Committing SODA Work

The general recommendation for SODA applications is to turn on oracledb.autoCommit globally:

oracledb.autoCommit = true;

If your SODA document write operations are mostly independent of each other, this removes the overhead of application transaction management and the need for explicit connection.commit() calls.

When deciding how to commit transactions, beware of transactional consistency and performance requirements. If you are using individual SODA calls to insert or update a large number of documents with individual calls, you should turn autoCommit off and issue a single, explicit connection.commit() after all documents have been processed. Also consider using sodaCollection.insertMany() or sodaCollection.insertManyAndGet() which have performance benefits.

If you are not autocommitting, and one of the SODA operations in your transaction fails, then previous uncommitted operations will not be rolled back. Your application should explicitly roll back the transaction with connection.rollback() to prevent any later commits from committing a partial transaction.

Note:

SODA DDL operations do not commit an open transaction the way that SQL always does for DDL statements.
When oracledb.autoCommit is true, most SODA methods will issue a commit before successful return.
SODA provides optimistic locking, see sodaOperation.version().
SODA provides pessimistic locking, see sodaOperation.lock().
When mixing SODA and relational access, any commit or rollback on the connection will affect all work.

15.3. Node-oracledb SODA Requirements

SODA is available to Node.js applications using 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.

To execute SODA operations, Oracle Database users require the SODA_APP role granted to them by a DBA:

GRANT SODA_APP TO hr;

The CREATE TABLE system privilege is also needed. Advanced users who are using Oracle sequences for keys will also need the CREATE SEQUENCE privilege.

Note: if you are using Oracle Database 21 (or later) and you create new collections, then you need to do one of the following:

Use Oracle Client libraries 21 (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 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.

If you use Oracle Client libraries 19 with Oracle Database 21 and accidently create a collection with unusable metadata, then you can drop the collection by running a command like SELECT DBMS_SODA.DROP_COLLECTION('myCollection') FROM DUAL; in SQL*Plus.

15.4. Creating and Dropping SODA Collections

The following examples use Node.js 8’s Async/Await syntax. However, callbacks can also be used. There are runnable examples in the GitHub examples directory.

Collections can be created like:

oracledb.autoCommit = true;

try {
    const soda = connection.getSodaDatabase();
    const collection = await soda.createCollection("mycollection");
    const indexSpec = { "name": "CITY_IDX",
                        "fields": [ {
                            "path": "address.city",
                            "datatype": "string",
                            "order": "asc" } ] };
    await collection.createIndex(indexSpec);
} catch(err) {
    console.error(err);
}

This example creates a collection that, by default, allows JSON documents to be stored. A non-unique B-tree index is created on the address.city path to improve search performance.

If the collection name passed to sodaDatabase.createCollection() already exists, it will simply be opened. Alternatively you can open a known, existing collection with sodaDatabase.openCollection().

Collections will be visible as tables in your Oracle Database schema. Do not use DROP TABLE to drop these database tables, since SODA metadata will not be correctly removed. Use the sodaCollection.drop() method instead. If you accidentally execute DROP SQL, you should call sodaCollection.drop() or execute the SQL statement SELECT DBMS_SODA.DROP_COLLECTION('myCollection') FROM dual;

See SODA Client-Assigned Keys and Collection Metadata for how to create a collection with custom metadata.

15.5. Creating and Accessing SODA Documents

To insert a document into an opened collection, a JavaScript object that is the document content can be used directly. In the following example, it is the object myContent:

try {
    const myContent = {name: "Sally", address: {city: "Melbourne"}};
    const newDoc = await collection.insertOneAndGet(myContent);
    // a system generated key is created by default
    console.log("The key of the new SODA document is: ", newDoc.key);
} catch(err) {
    console.error(err);
}

See sodaCollection.insertOne() for more information.

For many users, passing your document content directly to the sodaCollection.insertOne(), sodaCollection.insertOneAndGet(), sodaCollection.save(), sodaCollection.saveAndGet(), sodaOperation.replaceOne(), sodaOperation.replaceOneAndGet(), sodaCollection.insertMany(), or sodaCollection.insertManyAndGet() methods will be fine. System generated values for the key and other document components will be added to the stored SODA document. For cases where you want to insert Buffers or Strings, or when you need more control over the SodaDocument, such as to use a client-assigned key, then you can call the sodaDatabase.createDocument() method and pass its result to an insert or replace method, for example:

try {
    myContent = {name: "Sally", address: {city: "Melbourne"}};
    newDoc = soda.createDocument(myContent, {key: "123"});
    await collection.insertOne(myContent);
} catch(err) {
    console.error(err);
}

Note: to use client-assigned keys, collections must be created with custom metadata, see SODA Client-Assigned Keys and Collection Metadata.

Collections with client-assigned keys can be used for ‘upsert’ operations using sodaCollection.save() and sodaCollection.saveAndGet(). These methods are similar to the insertion methods, however if an existing document with the same key already exists in the collection, it is replaced.

To extract documents from a collection, the find() method can be used to build a SodaOperation object specifying the keys of desired documents, or searches can be performed on JSON documents using query-by-example (QBE) methods. Each document has a unique key. If the key for a document is “k1”, the document can be fetched like:

const myKey = "k1";
try {
    const soda = connection.getSodaDatabase();
    const collection = await soda.openCollection("mycollection");
    const doc = await collection.find().key(myKey).getOne(); // A SodaDocument
    const content = doc.getContent();  // A JavaScript object
    console.log("Name: " + content.name); // Sally
    console.log("Lives in: " + content.address.city);  // Melbourne
} catch(err) {
    console.error(err);
}

The content of queried SodaDocument objects is only accessible via one of the accessor methods getContent(), getContentAsBuffer() or getContentAsString(). Which one to use depends on the media type, and how you want to use it in the application. By default, the media type is ‘application/json’.

The SodaCollection.find() method creates a SodaOperation object used with method chaining to specify desired properties of documents that a terminal method like getOne() or remove() then applies to.

Other examples of chained read and write operations include:

To see if a document exists:

c = await col.find().key("k1").getOne();
if (c) then { . . .}

To return a cursor that can be iterated over to get documents with keys “k1” and “k2”:

docCursor = await collection.find().keys(["k1", "k2"]).getCursor();
let myDocument;
while ((myDocument = await docCursor.getNext())) {
    console.log(myDocument.getContent());
}
await docCursor.close();

To remove the documents matching the supplied keys:

await collection.find().keys(["k1", "k2"]).remove();

To remove the document with the key ‘k1’ and version ‘v1’:
```
await collection.find().key("k1").version("v1").remove();
```
The version field is a value that automatically changes whenever the document is updated. By default it is a hash of the document’s content. Using version() allows optimistic locking, so that the find() terminal method (which is remove() in this example) does not affect a document that someone else has already modified. If the requested document version is not matched, then the terminal operation will not impact any documents. The application can then query to find the latest document version and apply any desired change.

To update a document with a given key and version. The new document content will be the newContent object:

newContent = {name: "Fred", address: {city: "Melbourne"}};
await collection.find().key("k1").version("v1").replaceOne(newContent);

To find the new version of an updated document:

const newContent = {name: "Fred", address: {city: "Melbourne"}};
const updatedDoc = await collection.find().key("k1").version("v1").replaceOneAndGet(newContent);
console.log('New version is: ' + updatedDoc.version);

To count all documents, no keys are needed:
```
const n = collection.find().count();
```
To lock the documents in a collection:
```
collection.find().lock();
```
Using lock() allows pessimistic locking, so that the find() terminal method does not allow anyone else to modify the documents in the collection other than the current user.

After all the documents have been modified, explicitly call commit() in your application to unlock the documents. Also, an explicit call to rollback() will unlock the documents in the collection.
When using getCursor() and getDocuments() to return a number of documents, performance of document retrieval can be tuned by setting oracledb.fetchArraySize or using the find() non-terminal fetchArraySize(). For example, to get all documents in a collection:
```
const documents = await coll.find().fetchArraySize(500).getDocuments();
```

The sodaCollection.find() operators that return documents produce complete SodaDocument objects that can be used for reading document content and attributes such as the key. They can also be used for passing to methods like sodaCollection.insertOne(), sodaCollection.insertOneAndGet(), sodaCollection.save(), sodaCollection.saveAndGet(), sodaCollection.insertMany(), sodaCollection.insertManyAndGet(), sodaOperation.replaceOne(), and sodaOperation.replaceOneAndGet().

Note that for efficiency, the SodaDocuments returned from sodaCollection.insertOneAndGet(), sodaCollection.saveAndGet(), sodaOperation.replaceOneAndGet(), and sodaCollection.insertManyAndGet() cannot be passed to SODA insert methods, since they do not contain any document content. These SodaDocuments are useful for getting other document components such as the key and version. If you need a complete SodaDocument, then create a JavaScript object using the desired attribute values, or use sodaDatabase.createDocument(), or use a SodaDocument returned by a sodaCollection.find() query.

From node-oracledb 6.4, you can asynchronously iterate over SodaDocumentCursor object:

const soda = connection.getSodaDatabase();
// Create a SODA collection
const collection = await soda.createCollection("mycollection");
const data = [
    { name: "John", age: 57 },
    { name: "Sally", age: 53 }
];
await collection.insertMany(data);
const docCursor = await collection.find().getCursor();
// Use the asyncIterator for the SodaDocumentCursor object
for await (const doc of docCursor) {
    console.log(doc.getContent());
}
await docCursor.close();
await collection.drop();
await connection.close();

See soda2.js for a runnable example.

15.6. SODA Query-by-Example Searches for JSON Documents

JSON documents stored in SODA can easily be searched using query-by-example (QBE) syntax with collection.find().filter(). Filtering and ordering easily allows subsets of documents to be retrieved, replaced or removed. Filter specifications can include comparisons, regular expressions, logical, and spatial operators, among others. See Overview of SODA Filter Specifications (QBEs)

Some QBE examples are:

To find the number of documents where ‘age’ is less than 30, the city is San Francisco and the salary is greater than 500000:

const n = await collection.find().filter({"age": {"$lt": 30},
                                          "address.city": "San Francisco",
                                          "salary": {"$gt": 500000}}).count();
console.log(n);

To return all documents that have an age less than 30, an address in San Francisco, and a salary greater than 500000:

const docCursor = await collection.find().filter({"age": {"$lt": 30},
                                                  "address.city": "San Francisco",
                                                  "salary": {"$gt": 500000}}).getCursor();
let myDocument;
while ((myDocument = await docCursor.getNext())) {
    console.log(myDocument.getContent());
}
await docCursor.close();

Same as the previous example, but allowing for pagination of results by only getting 10 documents:

const docCursor = await collection.find().filter({"age": {"$lt": 30},
                                                  "address.city": "San Francisco",
                                                  "salary": {"$gt": 500000}}).skip(0).limit(10).getCursor();

To get the next 10 documents, the QBE could be repeated with the skip() value set to 10.

To get JSON documents with an “age” attribute with values greater than 60, and where either the name is “Max” or where tea or coffee is drunk.

const filterSpec = {"$and": [{"age": {"$gt": 60} },
                      {"$or": [{"name": "Max"},
                               {"drinks": {"$in": ["tea", "coffee"]}}]}]; };
const docCursor = await collection.find().filter(filterSpec).getCursor();

The $orderby specification can be used to order any returned documents:
```
const filterSpec = {"$query": {"salary": {$between [10000, 20000]}},
                    "$orderby": {"age": -1, "name": 2}};
const docCursor = await collection.find().filter(filterSpec).getCursor();
```
This ‘orderby abbreviated syntax’ returns documents within a particular salary range, sorted by descending age and ascending name. Sorting is done first by age and then by name, because the absolute value of -1 is less than the absolute value of 2 - not because -1 is less than 2, and not because field age appears before field name in the $orderby object.

An alternate $orderby syntax allows specifying the data types and maximum number of string characters to be used for comparison. See Overview of QBE Operator $orderby.

Documents that contain a GeoJSON geometry can be searched. For example if the collection contained documents of the form:

{"location": {"type": "Point", "coordinates": [33.7243, -118.1579]}}

Then a Spatial QBE like the following could be used to find documents within a 50 km range of a specified point:

const filterSpec = {"location" :
  {"$near" :
    {"$geometry": {"type": "Point", "coordinates": [34.0162, -118.2019]},
      "$distance" : 50,
      "$unit"     : "KM"}}};
const docCursor = await collection.find().filter(filterSpec).getCursor();

See Overview of QBE Spatial Operators.

15.7. Creating and Dropping SODA Indexes

Indexing can improve the performance of SODA query-by-examples (QBE) or enable text searches. An index is defined by a specification, which is a JSON object that specifies how particular QBE patterns are to be indexed for quicker matching.

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

Each index specification is uniquely identified by the name field. The different index types that you can specify are:

B-tree: Used to speed up query-by-example (QBE) sodaOperation.filter() searches. For this index type, you must specify the fields field in the index specification.
GeoSpatial: Used for speeding up QBEs that do GeoJSON queries. For this index type, you must specify the spatial field in the index specification.
JSON search: Required for text searches using the $contains operator in QBEs. Also, improves QBE filter operation performance. For this index type, you must not specify the fields and spatial fields in the index specification. Note that a B-tree index will perform better for non-text searches.

See Overview of SODA Indexing.

As an example, if a collection has these documents:

{"name": "Chris"}
{"name": "Venkat"}
{"name": "Srinath"}

You must first specify the type of index that you want by creating a SODA index specification. For example, to create a B-tree index specification, you need to specify the fields field:

indexSpec = {name: "myIndex", fields: [{path: "name"}]};

Then use that index specification to create the B-tree index using sodaCollection.createIndex():

await collection.createIndex(indexSpec);

This index would improve the performance of QBEs like:

d = await collection.find().filter({name: "Venkat"}).getOne();

To drop a specific index on a SODA collection, use sodaCollection.dropIndex():

await collection.dropIndex("myIndex");

15.7.1. Retrieving All Index Specifications in a Collection

You can retrieve all the index specifications defined for the documents in a collection using sodaCollection.listIndexes(). For example:

// Create a new SODA collection
const collection = await soda.createCollection("mycollection");

// Create new index specifications
const indexArr = [
  {
    "name": "HOME_IDX",
    "fields": [
      {
        "path": "home",
        "datatype": "string",
        "order": "asc"
      }
    ]
  },
  {
    "name": "OFFICE_IDX",
    "fields": [
      {
        "path": "office",
        "datatype": "string",
        "order": "asc"
      }
    ]
  }
];

To create new indexes for each of the index specifications in IndexArr:

await collection.createIndex(indexArr[0]);
await collection.createIndex(indexArr[1]);

To retrieve all the index specifications in the collection:

// Retrieve list of indexes in a collection
const fetchedIndexArr  = await collection.listIndexes();

//  Sort the index specification names in alphabetical order
fetchedIndexArr.sort(function(a, b) {
  return a.name.localeCompare(b.name);
});

console.log ("fetchIndexArr-0 " + JSON.stringify(fetchedIndexArr[0]));
console.log ("fetchIndexArr-1 " + JSON.stringify(fetchedIndexArr[1]));

This prints an output such as:

fetchIndexArr-0 {"name":"HOME_IDX","schema":"SCOTT","tableName":"MYCOLLECTION","tableSchemaName":"SCOTT","indexNulls":false,"unique":false,"lax":false,"scalarRequired":false,"fields":[{"path":"home","dataType":"VARCHAR2","maxLength":2000,"order":"ASC"}]}
fetchIndexArr-1 {"name":"OFFICE_IDX","schema":"SCOTT","tableName":"MYCOLLECTION","tableSchemaName":"SCOTT","indexNulls":false,"unique":false,"lax":false,"scalarRequired":false,"fields":[{"path":"office","dataType":"VARCHAR2","maxLength":2000,"order":"ASC"}]}

15.8. SODA Text Searches

To perform text searches through documents, a JSON search index must be defined. For example:

await collection.createIndex({"name": "mySearchIdx"});

See SODA Index Specifications (Reference) for information on SODA indexing.

Documents in the indexed collection can be searched by running a filter (QBE) using the $contains operator:

let documents = await collection.find().filter({item : { $contains : "books"}}).getDocuments();

This example will find all documents that have an item field containing the string “books” (case-insensitive). For example, a document that contained {item : "Books by Brothers Grimm"} would be returned.

15.9. SODA Client-Assigned Keys and Collection Metadata

Default collections support JSON documents and use system generated document keys. Various storage options are also configured which should suit most users. Overriding the default configuration is possible by passing custom metadata when a collection is created with sodaDatabase.createCollection(). Metadata specifies things such as:

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.
Methods of document key generation, and whether document keys are client- assigned or generated automatically.
Methods of version generation.

Note that changing storage options should only be done with care.

The metadata attributes are described in SODA Collection Metadata Components.

Collection metadata in SODA is represented as a JavaScript object.

The default collection metadata specifies that a collection stores five components for each document: key, JSON content, version, last-modified timestamp, and a created-on timestamp. An example of default metadata with Oracle Database 19c is:

{
    "schemaName": "mySchemaName",
    "tableName": "myCollectionName",
    "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
}

With Oracle Database 21, default metadata might be like:

{
    "schemaName": "mySchemaName",
    "tableName": "myCollectionName",
    "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
}

See Overview of SODA Document Collections for more information on collections and their metadata.

The following example shows how to create a collection that supports keys supplied by the application, instead of being system generated. Here, numeric keys will be used. The metadata used when creating the collection will be the same as the above default metadata with the keyColumn object changed. Here the type becomes NUMBER and the assignment method is noted as client-assigned:

const mymetadata = { . . . };   // the default metadata shown above

// update the keyColumn info
mymetadata.keyColumn =
{
 "name": "ID",
 "sqlType": "NUMBER",
 "assignmentMethod": "CLIENT"
};

// Set schemaName to the connected user
mymetadata.schemaName = 'HR';

This custom metadata is then used when creating the collection:

oracledb.autoCommit = true;

try {
    const soda = connection.getSodaDatabase();
    const collection = await soda.createCollection("mycollection", { metaData: mymetadata});
    const indexSpec = { "name": "CITY_IDX",
                        "fields": [ {
                            "path": "address.city",
                            "datatype": "string",
                            "order": "asc" } ] };
    await collection.createIndex(indexSpec);
} catch(err) {
    console.error(err);
}

To insert a document into the collection, a key must be supplied by the application. Note it is set to a string:

try {
    const myContent = {name: "Sally", address: {city: "Melbourne"}};
    const newDoc = soda.createDocument(myContent, {key: "123"});
    await collection.insertOne(newDoc);
} catch(err) {
    console.error(err);
}

15.10. JSON Data Guides in SODA

SODA exposes Oracle Database’s JSON data guide feature. This lets you discover information about the structure and content of JSON documents by giving details such as property names, data types and data lengths. In SODA, it can be useful for exploring the schema of a collection.

To get a data guide in SODA, the collection must be JSON-only and have a JSON Search index where the "dataguide" option is "on". Data guides are returned from sodaCollection.getDataGuide() as JSON content in a SodaDocument. The data guide is inferred from the collection as it currently is. As a collection grows and documents change, a new data guide may be returned each subsequent time getDataGuide() is called.

As an example, suppose a collection was created with default settings, meaning it can store JSON content. If the collection contained these documents:

{"name": "max", "country": "ukraine"}
{"name": "chris", "country": "australia"}
{"name": "venkat" , "country": "india"}
{"name": "anthony", "country": "canada"}

Then the following code:

const await createIndex({"name": "myIndex"});  // dataguide is "on" by default
const doc = await sodaCollection.getDataGuide();
const dg = doc.getContentAsString();
console.log(dg);

Will display the data guide:

{"type":"object","properties":{
 "name":{"type":"string","o:length":8,"o:preferred_column_name":"JSON_DOCUMENT$name"},
 "country":{"type":"string","o:length":16,"o:preferred_column_name":"JSON_DOCUMENT$country"}}}

This indicates that the collection documents are JSON objects, and currently have “name” and “country” fields. The types (“string” in this case) and lengths of the values of these fields are listed. The “preferred_column_name” fields can be helpful for advanced users who want to define SQL views over JSON data. They suggest how to name the columns of a view.

15.11. Using the SODA Metadata Cache

SODA metadata can be cached to improve the performance of sodaDatabase.createCollection() and sodaDatabase.openCollection() by reducing round-trips to the database. Caching is available when using node-oracledb 5.2 (or later) with Oracle Client version 21.3 (or later). It is also available in Oracle Client 19 from 19.11 onwards. Note: if the metadata of a collection is changed, the cache can get out of sync.

Caching can be enabled for pooled connections but not standalone connections. Each pool has its own cache. Applications using standalone connections should retain and reuse the collection returned from sodaDatabase.createCollection() or sodaDatabase.openCollection() wherever possible, instead of making repeated calls to those methods.

The metadata cache can be turned on with sodaMetadataCache when creating a connection pool:

await oracledb.createPool({
    user              : "hr",
    password          : mypw,               // mypw contains the hr schema password
    connectString     : "localhost/FREEPDB1",
    sodaMetaDataCache : true
});

If the metadata of a collection is changed externally, the cache can get out of sync. If this happens, the cache can be cleared by calling pool.reconfigure({ sodaMetadataCache: false }). A second call to reconfigure() should then be made to re-enable the cache.

Note the cache is not used by soda.createCollection() when explicitly passing metadata. In this case, instead of using only soda.createCollection() and relying on its behavior of opening an existing collection like:

const mymetadata = { . . . };
const collection = await soda.createCollection("mycollection", mymetadata);  // open existing or create new collection
await collection.insertOne(mycontent);

you may find it more efficient to use logic similar to:

let collection = await soda.openCollection("mycollection");
if (!collection) {
    const mymetadata = { . . . };
    collection = await soda.createCollection("mycollection", mymetadata);
}
await collection.insertOne(mycontent);