7 Working with SODA Collections in MLE JavaScript Code

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.

SODA APIs exist for different programming languages and include support for MLE JavaScript. 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.

This chapter covers JavaScript in the database, based on Multilingual Engine (MLE) as opposed to the client-side node-oracledb driver. Whenever JavaScript is mentioned in this chapter it implicitly refers to MLE JavaScript.

See Also:

Oracle Database Introduction to Simple Oracle Document Access (SODA) for a complete introduction to SODA

Topics

7.1 High-Level Introduction to Working with SODA for In-Database JavaScript

The SODA API is part of the MLE JavaScript SQL driver. Interaction with collections and documents requires you to establish a connection with the database first, before a SODA database object can be obtained.

The SODA database is the top-level abstraction object when working with the SODA API.

Figure 7-1 demonstrates the standard control flow.

Figure 7-1 SODA for In-Database JavaScript Basic Workflow



Applications that aren't ported from client-side Node.js or Deno can benefit from coding aids available in the MLE JavaScript SQL driver, such as a number of frequently used variables that are available in the global scope. For a complete list of available global variables and types, see Working with the MLE JavaScript Driver.

For SODA applications the most important global variable is the soda object, which represents the SodaDatabase object. The availability of the soda object in the global scope reduces the need for writing boilerplate code.

7.2 SODA Objects

Objects used with the SODA API.

The following objects are at the core of the SODA API:
  • SodaDatabase: The top-level object for 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. With the MLE JavaScript SQL driver, the soda object is available as a global variable, which represents the SodaDatabase object and reduces the need for writing boilerplate code.

  • SodaCollection: Represents a collection of SODA documents. By default, collections allow JSON documents to be stored, and they add a default set of metadata to each document. This is recommended for most 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.

  • 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.

When working with collections and documents stored therein, you will make use of the following objects:
  • SodaDocumentCursor: A cursor object representing the result of the getCursor() method from a find() operation. It can be iterated over to access each SodaDocument.

  • SodaOperation: An internal object used with 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.

See Also:

Server-Side JavaScript API Documentation for information about using SODA objects with mle-js-oracledb

7.3 Using SODA for In-Database JavaScript

How to access SODA for In-Database JavaScript is described, as well as how to use it to perform create, read (retrieve), update, and delete (CRUD) operations on collections.

This section describes SODA for MLE JavaScript. Code snippets in this section are sometimes abridged for readability. Care has been taken to ensure that JavaScript functions are listed in their entirety, but they aren’t runnable on their own. Embedding the function definition into a JavaScript module and importing the MLE JavaScript SQL driver will convert these code examples to valid JavaScript code for Oracle Database 23c.

Topics

7.3.1 Getting Started with SODA for In-Database JavaScript

How to access SODA for In-Database JavaScript is described, as well as how to use it to create a database collection, insert a document into a collection, and retrieve a document from a collection.

Before you can get started working with SODA for MLE JavaScript, the account used for storing collections must be granted the SODA_APP roles:

grant soda_app to soda_user
Accessing SODA functionality requires the use of the MLE JavaScript SQL driver. Because the database session exists by the time the code is invoked, no additional connection handling is necessary. Example 7-1 demonstrates how to:
  • Create a SODA collection,

  • Insert a JSON document into it, and

  • Iterate over all SODA Documents in the collection, printing their contents on screen

Each concept presented by Example 7-1 - creating collections, adding and modifying documents, and dropping collections - is addressed in more detail later in this chapter.

Example 7-1 SODA with MLE JavaScript General Workflow

This example demonstrates the general workflow using SODA collections with MLE JavaScript.

CREATE OR REPLACE MLE MODULE intro_soda_mod 
LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

export function testSODA(dropCollection) {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // Create a collection with the name "MyJSONCollection".
    // This creates a database table, also named "MyJSONCollection",
    // to store the collection. If a collection with the same name
    // exists it will be opened
    const col = db.createCollection("MyJSONCollection");

    // Create a JSON document (based on the HR.EMPLOYEES table for employee 100)
    const doc = {
        "employee_id": 100,
        "job_id": "AD_PRES",
        "last_name": "King",
        "first_name": "Steven",
        "email": "SKING",
        "manager_id": null,
        "department_id": 90
    };

    // Insert the document into a collection.
    col.insertOne(doc);

    // Find all documents in the collection and print them on screen
    try {

        // use a cursor to iterate over all documents in the collection
        const c = col.find().getCursor();
    
        let resultDoc;
        while (resultDoc = c.getNext()) {
            const content = resultDoc.getContent();

            console.log(`
                ------------------------------------------
                key:           ${resultDoc.key}
                content (select fields):
                - employee_id  ${content.employee_id}
                - job_id       ${content.job_id}
                - name         ${content.first_name} ${content.last_name}
                version:       ${resultDoc.version}
                last modified: ${resultDoc.lastModified}
                created on:    ${resultDoc.createdOn}
                media type:    ${resultDoc.mediaType}`
            );
        }

        // it is very important to close the SODADocumentCursor to free resources
        c.close();
    } catch (err) {
        throw err;
    }

    // commit open transactions
    connection.commit();

    // optionally drop the collection
    if (dropCollection) {
        col.drop();
    }
}
/

7.3.2 Creating a Document Collection with SODA for In-Database JavaScript

How to use SODA for In-Database JavaScript to create a new document collection is explained.

Collections allow you to logically group documents. Before a collection can be created or accessed, a few more steps must be completed. Begin by creating a connection object. The connection object is the starting point for all SODA interactions in the MLE JavaScript module:

// get a connection handle to the database session
const connection = oracledb.defaultConnection();

Once the connection is obtained, you can use it to call Connection.getSodaDatabase(), a prerequisite for creating the collection:

// get a SODA database
const db = connection.getSodaDatabase();

With the SODA database available, the final step is to create the collection. Note that collection names are case-sensitive:

// Create a collection with the name "MyJSONCollection".
// This creates a database table, also named "MyJSONCollection",
// to store the collection. If a collection with the same name
// exists, it will be opened
const col = db.createCollection("MyJSONCollection");

The preceding statement creates a collection that, by default, allows JSON documents to be stored. If the collection name passed to SodaDatabase.createCollection() is that of an existing collection, it will simply be opened. You can alternatively open a known, existing collection using SodaDatabase.openCollection().

Unless custom metadata (as seen in Example 7-2) is provided to SodaDatabase.createCollection(), default collection metadata will be supplied. The default metadata has the following characteristics:

  • Each document in the collection has these components:
    • Key
    • Content
    • Creation timestamp
    • Last-modified timestamp
    • Version
  • The collection can store only JSON documents.
  • Document keys and version information are generated automatically using a Universally Unique Identifier (UUID) function.

Optional collection metadata can be provided to the call to createCollection(), however, the default collection configuration is recommended in most cases.

If a collection with the same name already exists, it is simply opened and its object is returned. If custom metadata is passed to the method and does not match that of the existing collection, the collection is not opened and an error is raised. To match, all metadata fields must have the same values.

See Also:

Oracle Database Introduction to Simple Oracle Document Access (SODA) for more details about collection metadata.

Example 7-2 Creating a Collection with Custom Metadata

This example demonstrates how to create a collection with custom metadata. Rather than storing the key as an automatically created UUID value stored in a VARCHAR2 column, the key is stored as a numeric field.

export function createCustomCollection() {
    
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // create a collection with custom metadata. Unless a custom
    // metadata field (keyColumn, versionColumn) is specified it 
    // will not be used/present in the collection. The metadata 
    // argument is mostly based on the defaults except for the 
    // client-assigned key column
    const col = db.createCollection(
        "myCustomCollection", 
        {
            "metaData": {
                "keyColumn": {
                    "name": "ID",
                    "sqlType": "NUMBER",
                    "assignmentMethod": "CLIENT"
                },
                "versionColumn": {
                    "name": "VERSION",
                    "method": "UUID"
                },
                "lastModifiedColumn": {
                    "name": "LAST_MODIFIED"
                },
                "creationTimeColumn": {
                    "name": "CREATED_ON"
                }
            }
        }
    );
    console.log(`
        Custom collection created.
        Assignment method: ${col.metaData.keyColumn.assignmentMethod}`
    );
}

Although the keyColumn is the only field whose value deviates from the defaults, it is mandatory to add versionColumn, lastModifiedColumn, and creationTimeColumn or they won't be used in the collection.

7.3.3 Opening an Existing Document Collection with SODA for In-Database JavaScript

You can use the method SodaDatabase.openCollection() to open an existing document collection or to test whether a given name names an existing collection.

Example 7-3 Opening an Existing Document Collection

This example opens the collection named collectionName. It is very important to check that the collection object returned by SodaDatabase.openCollection() is not null. Rather than throwing an error, the method will return a null value should the requested collection not exist.

export function openCollection(collectionName) {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // perform a lookup. If a connection cannot be found by that
    // name no exception nor error are thrown, but the resulting
    // collection object will be null
    const col = db.openCollection(collectionName);
    if (col === null) {
        throw `No such collection ${collectionName}`;
    }

    // do something with the collection
} 

7.3.4 Checking Whether a Given Collection Exists with SODA for In-Database JavaScript

You can use SodaDatabase.openCollection() to check for the existence of a given collection. It returns null if the collection argument does not name an existing collection; otherwise, it opens the collection having that name.

In Example 7-3, if collectionName does not name an existing collection then col is assigned the value null.

7.3.5 Discovering Existing Collections with SODA for In-Database JavaScript

You can use SodaDatabase.getCollectionNames() to fetch the names of all existing collections for a given SodaDatabase object.

If the number of collections is very large, you can limit the number of names returned. Additionally, the lookup can be limited to collections starting with a user-defined string as demonstrated by Example 7-5.

Example 7-4 Fetching All Existing Collection Names

This example prints the names of all existing collections using the method getCollectionNames().

export function printCollectionNames() {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // loop over all collection names
    const allCollections = db.getCollectionNames();
    for (const col of allCollections) {
        console.log(`- ${col}`);
    }
}
/

Example 7-5 Filtering the List of Returned Collections

This example limits the results of getCollectionNames() by only printing the names of collections that begin with a user-defined string, startWith.

export function printSomeCollectionNames(numHits, startWith) {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // loop over all collection names
    const allCollections = db.getCollectionNames(
        {
            limit: numHits,
            startsWith: startWith
        }
    );
    for (const col of allCollections) {
        console.log(`- ${col}`);
    }
}

7.3.6 Dropping a Document Collection with SODA for In-Database JavaScript

You use SodaCollection.drop() to drop an existing collection.

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.

Note:

Day-to-day use of a typical application that makes use of SODA does not require that you drop and re-create collections. But if you need to do that for any reason then this guideline applies.

Do not drop a collection and then re-create it with different metadata if there is any application running that uses the collection in any way. Shut down any such applications before re-creating the collection, so that all live SODA objects are released.

There is no problem just dropping a collection. Any read or write operation on a dropped collection raises an error. And there is no problem dropping a collection and then re-creating it with the same metadata. But if you re-create a collection with different metadata, and if there are any live applications using SODA objects, then there is a risk that a stale collection is accessed, and no error is raised in this case.

In SODA implementations that allow collection metadata caching, such as SODA for Java, this risk is increased if such caching is enabled. In that case, a (shared or local) cache can return an entry for a stale collection object even if the collection has been dropped.

Note:

Commit all writes to a collection before using SodaCollection.drop(). For the method to succeed, all uncommitted writes to the collection must first be committed. Otherwise, an exception is raised.

Example 7-6 Dropping a Collection

This example drops collection col.

CREATE OR REPLACE MLE MODULE drop_col_mod 
LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

export function openAndDropCollection(collectionName) {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // look the collection up
    const col = db.openCollection(collectionName);
    if (col === null) {
        throw `No such collection ${collectionName}`;
    }

    // drop the collection - POTENTIALLY DANGEROUS
    col.drop();
}
/

7.3.7 Creating Documents with SODA for In-Database JavaScript

Creation of documents by SODA for In-Database JavaScript is described.

The SodaDocument class represents SODA documents. Although its focus is on JSON documents, it supports other content types as well. A SodaDocument stores both the actual document's contents as well as metadata.

JavaScript is especially well-suited to work with JSON by design, giving it an edge over other programming languages.

Here is an example of a simple JSON document:

// Create a JSON document (based on the HR.EMPLOYEES table for employee 100)
const doc = {
    "employee_id": 100,
    "job_id": "AD_PRES",
    "last_name": "King",
    "first_name": "Steven",
    "email": "SKING",
    "manager_id": null,
    "department_id": 90
};

Note:

In SODA, JSON content must conform to RFC 4627.

SodaDocument objects can be created in three ways:

  • As a result of sodaDatabase.createDocument(). This is a proto-SodaDocument object usable for SODA insert and replace methods. The SodaDocument will have content and media type components set. Attributes like createdOn will not be defined. Optional attributes not specified when calling SodaDocument.createDocument() will not be defined either.
  • As a result of a read operation from the database, such as calling sodaOperation.getOne(), or from sodaDocumentCursor.getNext() after a sodaOperation.getCursor() call. These return complete SodaDocument objects containing the document content and attributes, such as time stamps.
  • As a result of sodaCollection.insertOneAndGet(), sodaOperation.replaceOneAndGet(), or sodaCollection.insertManyAndGet() methods. These return SodaDocuments that contain all attributes except the document content itself. They are useful for finding document attributes such as system generated keys, and versions of new and updated documents.

A document has these components:

  • Key

  • Content

  • Creation time stamp

  • Last-modified time stamp

  • Version

  • Media type ("application/json" for JSON documents)

Example 7-7 Creating SODA Documents

export function createJSONDoc() {
    
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // define a payload
    const payload = {
        "employee_id": 100,
        "job_id": "AD_PRES",
        "last_name": "King",
        "first_name": "Steven",
        "email": "SKING",
        "manager_id": null,
        "department_id": 90
    };

    // create a SODA document using the default key
    const docDefaultKey = db.createDocument(payload);
    console.log(`
        ------------------------------------------
        SODA Document using default key
        content (select fields):
        - employee_id  ${docDefaultKey.getContent().employee_id}
        - job_id       ${docDefaultKey.getContent().job_id}
        - first name   ${docDefaultKey.getContent().first_name}
        media type:    ${docDefaultKey.mediaType}`
    );

    // create a SODA document using a custom key (requires that the
    // collection this document is inserted to uses a user-assigned key)
    const docCustomKey = db.createDocument(
        payload, 
        {
            key: "12345"
        }
    );

    // print details about the new SODA Document
    console.log(`
        ------------------------------------------
        SODA Document using custom key
        content (select fields):
        - employee_id  ${docCustomKey.getContent().employee_id}
        - job_id       ${docCustomKey.getContent().job_id}
        - first name   ${docCustomKey.getContent().first_name}
        key:           ${docCustomKey.key}
        media type:    ${docCustomKey.mediaType}`
    );
}

Creating SodaDocument instances as shown in this example is the exception rather than the norm. In most cases, developers use SodaCollection.insertOne() or SodeCollection.insertOneAndGet(). The use of SodaCollection.insertOne() is demonstrated in Example 7-8.

7.3.8 Inserting Documents into Collections with SODA for In-Database JavaScript

SodaCollection.insertOne() or a related call such as sodaCollection.insertOneAndGet() offers convenient ways to add documents to a collection. These methods create document keys automatically, unless the collection is configured with client-assigned keys and the input document provides the key.

SodaCollection.insertOne() simply inserts the document into the collection, whereas SodaCollection.insertOneAndGet() additionally returns a result document. The resulting document contains the document key and any other generated document components, except for the actual document’s content (this is done to improve performance).

Both methods automatically set the values of the creation time stamp, last-modified time stamp, and version, unless the collection has been created with custom metadata. Custom metadata might not include all the default metadata. When querying attributes not defined by the collection a null value is returned.

Note:

If the collection is configured with client-assigned document keys (which is not the default case), and the input document provides a key that identifies an existing document in the collection, then these methods throw an exception. If you want the input document to replace the existing document instead of causing an exception, see Saving Documents into Collections with SODA for In-Database JavaScript.

Example 7-8 Inserting a SODA Document into a Collection

This example demonstrates how to insert a document into a collection using SodaCollection.insertOne().

export function insertOneExample() {
    
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // create/open a SODA collection
    const col = db.createCollection("MyJSONCollection");

    // define a payload
    const payload = {
        "employee_id": 100,
        "first_name": "Steven",
        "last_name": "King"
    };

    // insert the document
    col.insertOne(payload);
}

Example 7-9 Inserting an Array of Documents into a Collection

This example demonstrates the use of SodaCollection.insertMany() to insert multiple documents with one command.

export function insertManyExample() {
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // create/open a SODA collection
    const col = db.createCollection("MyJSONCollection");

    // define a payload (an array of objects)
    const payload = [
        {
            "employee_id": 100,
            "first_name": "Steven",
            "last_name": "King"
        },
        {
            "employee_id": 101,
            "first_name": "Neena",
            "last_name": "Kochhar"
        },
        {
            "employee_id": 102,
            "first_name": "Lex",
            "last_name": "De Haan"
        },
        {
            "employee_id": 103,
            "first_name": "Alexander",
            "last_name": "Hunold"
        },
        {
            "employee_id": 104,
            "first_name": "Bruce",
            "last_name": "Ernst"
        },
        {
            "employee_id": 105,
            "first_name": "David",
            "last_name": "Austin"
        }
    ]

    // insert the document
    col.insertMany(payload);
}

7.3.9 Saving Documents into Collections with SODA for In-Database JavaScript

You use SodaCollection.save() and saveAndGet() to save documents into collections.

These methods are similar to methods insertOne() and insertOneAndGet() except that, if the collection is configured with client-assigned document keys, and the input document provides a key that already identifies a document in the collection, then the input document replaces the existing document. In contrast, methods insertOne() and insertOneAndGet() throw an exception in that case.

Note:

By default, collections are configured with automatically generated document keys. Therefore, for a default collection, methods save() and saveAndGet() are equivalent to methods insertOne() and insertOneAndGet(), respectively.

Example 7-10 Saving a Document into a Collection

This example shows how to use SodaCollection.saveAndGet(). The JavaScript code stores a SODA document in a collection defined with custom metadata to ensure save() and saveAndGet() work as described. The key column specifically is user-managed, which is why you see the SodaDocument constructor mention a key.

CREATE OR REPLACE MLE MODULE save_doc_mod 
LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

export function saveJSONDoc() {
    
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // create/open a collection using custom metadata
    const col = db.openCollection("myCustomCollection");
    if ( col === null ) {
        throw `'myCustomCollection' does not exist`;
    }

    // create the SODA document, use the employee_id as its key
    let docCustomKey = db.createDocument(
        {
        "employee_id": 100,
        "job_id": "AD_PRES",
        "last_name": "King",
        "first_name": "Steven",
        "email": "SKING",
        "manager_id": null,
        "department_id": 90
        },
        {
            "key": "100"
        }
    );

    // insert the document into the custom collection
    let savedDoc = col.saveAndGet(docCustomKey);

    // get some meta information about the new document
    console.log(`Document saved for the 1st time: 
    - department id: ${docCustomKey.getContent().department_id}
    - key:           ${savedDoc.key}
    - version:       ${savedDoc.version}
    - last modified: ${savedDoc.lastModified}
    - created on:    ${savedDoc.createdOn}`);

    // correct the previous mistake - the document should have
    // been inserted with a department_id of 30
    // - create the new document (important to use the same key)
    docCustomKey = db.createDocument(
        {
        "employee_id": 100,
        "job_id": "AD_PRES",
        "last_name": "King",
        "first_name": "Steven",
        "email": "SKING",
        "manager_id": null,
        "department_id": 30
        },
        {
            "key": "100"
        }
    );

    // - update the existing document
    savedDoc = col.saveAndGet(docCustomKey);

    // - show the changed metadata. Cannot refer to the content
    //   as it is not returned by saveAndGet() for performance
    //   reasons
    console.log(`Updated document saved successfully:
    - department id: ${docCustomKey.getContent().department_id}
    - key:           ${savedDoc.key}
    - version:       ${savedDoc.version}
    - last modified: ${savedDoc.lastModified}
    - created on:    ${savedDoc.createdOn}`);
}
/

After the initial document has been saved to the collection, it turns out that the value for the document's department_id was wrong and needs correcting. The mistake is corrected and the document saved again, overwriting the previous contents and effectively setting the department_id to the correct value of 30.

7.3.10 SODA for In-Database JavaScript Read and Write Operations

The primary way you specify read and write operations (other than insert and save) is to use methods provided by the SodaOperation class. You can chain together SodaOperation methods to specify read or write operations against a collection.

Nonterminal 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.

Note:

A SodaOperation object is an internal object. You should not directly modify its properties.

Unless the node-oracledb documentation for a method says otherwise, you can chain together any nonterminal methods and you can end the chain with any terminal method. However, not all combinations make sense. For example, it does not make sense to chain method version() together with a method that does not uniquely identify the document, such as keys().

Table 7-1 Overview of Nonterminal Methods for Read Operations

Method Description
key() Find a document that has the specified document key.
keys() Find documents that have the specified document keys.
filter() Find documents that match a filter specification (a query-by-example expressed in JSON).
version() Find documents that have the specified version. This is typically used with key().
headerOnly() Exclude document content from the result.
skip() Skip the specified number of documents in the result.
limit() Limit the number of documents in the result to the specified number.

Table 7-2 Overview of Terminal Methods for Read Operations

Method Description
getOne() Create and execute an operation that returns at most one document. For example, an operation that includes an invocation of nonterminal method key().
getCursor() Get a cursor over read operation results.
count() Count the number of documents found by the operation.
getDocuments() Gets an array of documents matching the query criteria.

Table 7-3 Overview of Terminal Methods for Write Operations

Method Description
replaceOne() Replace one document.
replaceOneAndGet() Replace one document and return the result document.
remove() Remove documents from a collection.

See Also:

7.3.11 Finding Documents in Collections with SODA for In-Database JavaScript

To find documents in a collection, you invoke SodaCollection.find(). It creates and returns a SodaOperation object which is used via method chaining with nonterminal and terminal methods.

To execute the query, obtain a cursor for its results by invoking SodaOperation.getCursor(). Then use the cursor to visit each document in the result list. This is illustrated by Example 7-1 and other examples. It is important not to forget to close the cursor, to save resources.

However, this is not the typical workflow when searching for documents in a collection. It is more common to chain multiple methods provided by the SodaOperation class together.

Example 7-11 Finding a Document by Key

This example shows how to look up a document by its key using the methods find(), key(), and getOne().

There is an assumption that customCollection has been created as outlined in Example 7-2.

CREATE OR REPLACE MLE MODULE find_doc_mod 
LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

export function findDocByKey() {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open a collection in preparation of a document
    // lookup
    const col = db.openCollection("myCustomCollection");
    if (col === null) {
        throw `myCustomCollection does not exist`;
    }

    try {
        // perform a lookup of a document with key "100"
        const doc = col.find().key("100").getOne();
        console.log(`
            document found for key ${doc.key}
            contents: ${doc.getContentAsString()}`
        );
    } catch (err) {
        throw `No document found in 'myCustomCollection' with key 100`;
    }
}
/

Note:

Keys need to be enclosed in double quotation marks even if they should be in numeric format.

In case the search for a given key fails, the database throws an ORA-01403 (no data found) exception. It is good practice to handle exceptions properly. In this example, the caller of the function has the responsibility to ensure the error is trapped and dealt with according to the industry's best-known methods.

Example 7-12 Looking up Documents Using Multiple Keys

This example uses the methods find(), keys(), getCursor(), and getNext() to search for multiple keys provided in an array.

export function findDocByKeys() {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open a collection in preparation of a document
    // lookup
    const col = db.openCollection("myCustomCollection");
    if (col === null) {
        throw `myCustomCollection does not exist`;
    }

    try {
        // perform a lookup of a document with keys ["100","101"]
        const docCursor = col.find().keys(["100", "101"]).getCursor();

        let doc
        while (( doc = docCursor.getNext())) {
            console.log(`
                document found for key ${doc.key}
                contents: ${doc.getContentAsString()}`
            );
        }
        docCursor.close();
    } catch (err) {
        throw `No document found in 'myCustomCollection' with either key`;
    }
}

Rather than failing with an ORA-1403 (no data found exception), the find() operation simply doesn't return any data for a key not found in a collection. If none of the keys are found, nothing is returned.

Example 7-13 Using a QBE to Filter Documents in a Collection

This example uses filter() to locate documents in a collection. The nonterminal SodaOperation.filter() method provides a powerful way to filter JSON documents in a collection, allowing for complex document queries and ordering of JSON documents. Filter specifications can include comparisons, regular expressions, logical and spatial operators, among others.

The search expression defined in filterCondition matches all employees with an employee ID greater than 110 working in department 30.

export function findDocByFiltering() {
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open a collection in preparation of a document
    // lookup. This particular collection contains all the
    // rows from the HR.employees table converted to SODA
    // documents
    const col = db.openCollection("employees_collection");
    if (col === null) {
        throw `employees_collection does not exist`;
    }

    // find all employees with an employee_id > 110 working
    // in department 30
    const filterCondition = {
        "employee_id": { "$gt": 110 },
        "department_id": "30"
    }

    try {

        // perform the lookup operation using the QBE
        const docCursor = col.find().filter(filterCondition).getCursor();
        let doc;
        while (( doc = docCursor.getNext())) {
            console.log(`
                ------------------------------------
                document found matching the search criteria
                - key:           ${doc.key}
                - employee id:   ${doc.getContent().employee_id}
                - department ID: ${doc.getContent().department_id}
                - name:          ${doc.getContent().last_name}`
            );
        }

        docCursor.close();
    } catch (err) {
        throw `No document found in 'myCustomCollection' with either key`;
    }
}

See Also:

Example 7-14 Using skip() and limit() in a Pagination Query

If the number of rows becomes too large, you may choose to paginate and or limit the number of documents returned. This example demonstrates using skip() and limit() in this type of circumstance.

export function paginationExample() {
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open a collection in preparation of a document
    // lookup. This particular collection contains all the
    // rows from the HR.employees table converted to SODA
    // documents
    const col = db.openCollection("employees_collection");
    if (col === null) {
        throw `employees_collection does not exist`;
    }

    // find all employees with an employee_id > 110 working
    // in department 80
    const filterCondition = {
        "employee_id": { "$gt": 110 },
        "department_id": "80"
    }

    try {

        // perform the lookup operation using the QBE, skipping the first
        // 10 documents and limiting the result set to 10 documents
        const docCursor = 
            col.find().filter(filterCondition).skip(10).limit(10).getCursor();
        let doc
        while (( doc = docCursor.getNext())) {
            console.log(`
                ------------------------------------
                document found matching the search criteria
                - key:           ${doc.key}
                - employee id:   ${doc.getContent().employee_id}`
            );
        }

        docCursor.close();
    } catch (err) {
        throw `No document found in 'myCustomCollection' with either key`;
    }
}

Example 7-15 Specifying Document Versions

This example uses the nonterminal version() method to specify a particular document version. This is useful for implementing optimistic locking, when used with the terminal methods for write operations.

export function versioningExample() {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open a collection in preparation of a document
    // lookup
    const col = db.openCollection("myCustomCollection");
    if (col === null) {
        throw `myCustomCollection does not exist`;
    }

    try {
        // perform a lookup of a document with key "100" and version "v1"
        const doc = col.find().key("100").version("v1").getOne();
        console.log(`
            document found for key ${doc.key}
            contents: ${doc.getContentAsString()}`
        );
    } catch (err) {
        throw `No document found for key 100 and version "v1"`;
    }
} 

Example 7-16 Counting the Number of Documents Found

This example shows how to count the number of documents found in a collection using the find(), filter(), and count() methods. The filter() expression limits the result to all employees working in department 30.

export function countingExample() {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open a collection in preparation of a document lookup
    const col = db.openCollection("employees_collection");
    if (col === null) {
        throw `employees_collection does not exist`;
    }

    try {
        // perform a lookup operation identifying all employees working
        // in department 30 limiting the result to headers only
        const filterCondition = {"department_id": "30" };
        const numDocs = col.find().filter(filterCondition).count();
        console.log(`there are ${numDocs} documents matching the filter`);
    } catch (err) {
        throw `No document found in 'employees_collection' matching the filter`;
    }
} 

7.3.12 Replacing Documents in a Collection with SODA for In-Database JavaScript

To replace the content of one document in a collection with the content of another, you start by looking up the document to be modified using its key. Because SodaOperation.key() is a nonterminal operation, the easiest way to replace the contents is to chain SodaOperation.key() to SodaOperation.replaceOne() or SodaOperation.replaceOneAndGet().

SodaOperation.replaceOne() merely replaces the document, whereas SodaOperation.replaceOneAndGet() replaces it and provides the resulting new document to the caller.

Both methods update the values of the last modified time stamp and the version. Replacement does not change the document key or the creation time stamp.

The difference between SodaOperation.replace() and SodaOperation.save() is that the latter performs an insert in case the key doesn't already exist in the collection. The replace operation requires an existing document to be found by the lookup via the SodaOperation.key() method.

Note:

Some version-generation methods, including the default method, generate hash values of the document content. In such a case, if the document content does not change then neither does the version.

Example 7-17 Replacing a Document in a Collection and Returning the Result Document

This example shows how to replace a document in a collection, returning a reference to the changed document.

CREATE OR REPLACE MLE MODULE rep_ret_doc_mod 
LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

export function replaceExample() {
    
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open myCustomCollection
    const col = db.openCollection("myCustomCollection");
    if ( col === null ) {
        throw "'myCustomCollection' does not exist";
    }

    // create the changed SODA document, change department_id to 80
    const newDoc = db.createDocument(
        {
            "employee_id": 100,
            "job_id": "AD_PRES",
            "last_name": "King",
            "first_name": "Steven",
            "email": "SKING",
            "manager_id": null,
            "department_id": 80
        }
    );;  

    // perform a lookup of the document about to be changed
    try {
        const resultDoc = col.find().key("100").replaceOneAndGet(newDoc);

        // print some metadata (note that content is not returned for 
        // performance reasons)
        console.log(`Document updated successfully:
        - key:           ${resultDoc.key}
        - version:       ${resultDoc.version}
        - last modified: ${resultDoc.lastModified}
        - created on:    ${resultDoc.createdOn}`);
    } catch (err) {
        throw err;
    }
}
/

Note:

Trying to read the changed contents will result in an error as the actual document's contents aren't returned, for performance reasons.

7.3.13 Removing Documents from a Collection with SODA for In-Database JavaScript

Removing documents from a collection is similar to replacing. The first step is to perform a lookup operation, usually based on the document's key or by using a search expression in SodaOperation.filter(). The call to SodaOperation.remove() is a terminal operation, in other words the last operation in the chain.

Example 7-18 Removing a Document from a Collection Using a Document Key

This example removes the document whose document key is "100".

CREATE OR REPLACE MLE MODULE rm_doc_mod 
LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

export function removeByKey() {
    
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open myCustomCollection
    const col = db.openCollection("myCustomCollection");
    if ( col === null ) {
        throw "'myCustomCollection' does not exist";
    }

    // perform a lookup of the document about to be removed and 
    // ultimately remove it. 
    const result = col.find().key("100").remove();
    if ( result.count === 0) {
        throw "Document was not deleted - is this an invalid key?"
    }
}
/

Example 7-19 Removing JSON Documents from a Collection Using a Filter

This example uses a filter to remove the JSON documents whose department_id is 70. It then prints the number of documents removed.

export function removeByFilter() {
    
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open employees_collection
    const col = db.openCollection("employees_collection");
    if ( col === null ) {
        throw "'employees_collection' does not exist";
    }

    // perform a lookup based on a filter expression and remove the 
    // documents matching the filter
    const result = col.find().filter({"department_id": "70"}).remove();
    
    return result.count;
}

7.3.14 Indexing the Documents in a Collection with SODA for In-Database JavaScript

Indexes can speed up data access, regardless of whether you use the NoSQL style SODA API or a relational approach. You index documents in a SODA collection using SodaCollection.createIndex(). Its IndexSpec parameter is a textual JSON index specification.

Existing indexes can be dropped using SodaCollection.dropIndex().

A JSON search index is used for full-text and ad hoc structural queries, and for persistent recording and automatic updating of JSON data-guide information.

See Also:

Example 7-20 Creating a B-Tree Index for a JSON Field with SODA for In-Database JavaScript

This example creates a B-tree non-unique index for numeric field department_id of the JSON documents in collection employees_collection.

CREATE OR REPLACE MLE MODULE b_tree_mod 
LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

export function createBTreeIndex() {
    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open the collection
    const col = db.openCollection("employees_collection");
    if ( col === null ) {
        throw "'employees_collection' does not exist";
    }

    const indexSpec = {
        "name": "departments_idx",
        "fields": [
            {
                "path": "department_id",
                "datatype": "number",
                "order": "asc"
            }
        ]
    };

    col.createIndex(indexSpec);

    console.log(`B-Tree index successfully created`);
}
/

Example 7-21 Creating a JSON Search Index with SODA for In-Database JavaScript

This example shows how to create a JSON search index for indexing the documents in collection employees_collection. It can be used for ad hoc queries and full-text search (queries using QBE operator $contains). It automatically accumulates and updates data-guide information about your JSON documents (aggregate structural and type information). The index specification has only field name (no field fields unlike the B-tree index in Example 7-20).

export function createSearchIndex() {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open the collection
    const col = db.openCollection("employees_collection");
    if ( col === null ) {
        throw "'employees_collection' does not exist";
    }

    const indexSpec = {
        "name": "SEARCH_AND_DATA_GUIDE_IDX",
        "dataguide": "on",
        "search_on": "text_value"
    }

    col.createIndex(indexSpec);

    console.log(`Search & Data Guide index successfully created`);
}

If you only wanted to speed up ad hoc (search) indexing, you should specify a value of "off" for field dataguide. The dataguide indexing feature can be turned off in the same way if it is not required.

Example 7-22 Dropping an Index with SODA for In-Database JavaScript

This example shows how you can drop an existing index on a collection using SodaCollection.dropIndex() and the force option.

export function dropIndex(indexName) {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open the collection
    const col = db.openCollection("employees_collection");
    if ( col === null ) {
        throw "'employees_collection' does not exist";
    }

    // drop the index
    const result = col.dropIndex(indexName, {"force": true});
    if (! result.dropped ) {
        throw `Could not drop SODA index ${indexName}`;
    }
}

SodaCollection.dropIndex() returns a result object containing a single field: dropped. Its value is true if the index has been dropped, otherwise its value is false. The method succeeds either way.

An optional parameter object can be supplied to the method. Setting force to true forces dropping of a JSON index if the underlying Oracle Database domain index does not permit normal dropping.

7.3.15 Getting a Data Guide for a Collection with SODA for In-Database JavaScript

A data guide is a summary of the structural and type information contained in a set of JSON documents. It records metadata about the fields used in those documents. They provide great insights into JSON documents and are invaluable for getting an overview of a data set.

You can create a data guide using SodaCollection.getDataGuide(). 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 is returned each subsequent time getDataGuide() is called.

Example 7-23 Generating a Data Guide for a Collection

This example gets a data guide for the collection employees_collection using the method getDataGuide() and then prints the contents as a string using the method getContentAsString().

CREATE OR REPLACE MLE MODULE data_guide_mod
LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

export function createDataGuide() {

    // get a connection handle to the database session
    const connection = oracledb.defaultConnection();

    // get a SODA database
    const db = connection.getSodaDatabase();

    // open the collection
    const col = db.openCollection("employees_collection");
    if ( col === null ) {
        throw "'employees_collection' does not exist";
    }

    // generate a Data Guide
    const doc = col.getDataGuide();
    console.log(doc.getContentAsString());
}
/

7.3.16 Handling Transactions with SODA for In-Database JavaScript

Unlike the client-side JavaScript SQL driver, the MLE JavaScript SQL driver does not provide an autoCommit feature. You need to commit or roll your transactions back, either in the PL/SQL layer in case of module calls, or directly in the JavaScript code by calling connection.commit() or connection.rollback().

Caution:

If any uncommitted operation raises an error, and you do not explicitly roll back the transaction, the incomplete transaction might leave the relevant data in an inconsistent state (uncommitted, partial results).

7.3.17 Summary Example of Using SODA for In-Database JavaScript

An end-to-end example is included to demonstrate using SODA for In-Database JavaScript from start to finish.

Example 7-24 Use SODA for In-Database JavaScript

This example includes the following steps to use SODA for in-database JavaScript end-to-end:
  1. Define a JavaScript module to interact with the employeesCol collection.
  2. Create a JavaScript function to persist a JSON document in the collection.
  3. Define another JavaScript function to list all the employees found in the collection.
  4. Provide a call specification to each function within a PL/SQL package.

Define the JavaScript Module

This module makes use of the variables available in the global scope to limit the amount of coding required.

CREATE OR REPLACE MLE MODULE employees_module
LANGUAGE JAVASCRIPT AS
 
/// <reference types="mle-js" />
 
const colName = "employeesCol";
 
/**
* add an employee document to the employeesCol collection
*
* @param {json} employeeDoc - the new employee document to be added
*/
export function addEmployee(employeeDoc) {
 
    // this code example uses the SODA database object (soda)
    // available in the global scope
    const col = soda.createCollection(colName);
    col.insertOne(employeeDoc);
}
 
/**
* return a JSON document containing all the employees from the collection
*
* @returns {json} a document listing employee_id, last name, and department_name
*/
export function findAllEmployees() {
 
    // this array will contain all the employee documents
    // shortened to include employee ID, last name and 
    // department name. It is safer to assume the array is
    // empty at first
    let employeeList = [];
 
    const col = soda.openCollection(colName);
    if ( col === null ) {
        return employeeList;
    }
 
    // iterate over all documents in the collection
    const allDocs = col.find().getDocuments();
    for ( let doc of allDocs ) {
        const payload = doc.getContent();
        // add the relevant fields to a JSON document before
        // pushing it to the employeeList array
        const employeeDetail = {
            "employee_id": payload.employee_id,
            "last_name": payload.last_name,
            "department_name": payload.department_name
        };
        employeeList.push(employeeDetail);
    };
 
    // return the list of employees found in the collection
    return employeeList;
}
/

Provide a Call Specification

Call specifications for both functions defined in the JavaScript module employees_module are provided by the following package. Note that the package is defined with invoker's rights rather than definer's rights. The invoker of the package must be granted the SODA_APP role or else an error is thrown.

CREATE OR REPLACE PACKAGE employees_pkg 
AUTHID current_user
AS

    PROCEDURE add_employee(employee JSON)
    AS MLE MODULE employees_module
    SIGNATURE 'addEmployee';
 
    FUNCTION find_all_employees
    RETURN JSON
    AS MLE MODULE employees_module
    SIGNATURE 'findAllEmployees';
 
END employees_pkg;
/

Add Sample Data to the Collection

The following anonymous PL/SQL block converts two rows from HR.EMPLOYEES to a JSON document and inserts them into the collection by invoking the JavaScript code externalized by the call specification created earlier.

DECLARE
    l_employee  JSON;
BEGIN
    SELECT
        JSON{
            'employee_id' IS e.employee_id,
            'first_name' IS e.first_name,
            'last_name' IS e.last_name,
            'email' IS e.email,
            'phone_number' IS e.phone_number,
            'hire_date' IS e.hire_date,
            'job_id' IS e.job_id,
            'salary' IS e.salary,
            'commission_pct' IS e.commission_pct,
            'manager_id' IS e.manager_id,
            'department_name' IS d.department_name
        }
    INTO
        l_employee
    FROM
        hr.employees e 
        JOIN hr.departments d ON (e.department_id = d.department_id)
    WHERE
        e.employee_id = 100;
    
    employees_pkg.add_employee(l_employee);
 
    SELECT
        JSON{
            'employee_id' IS e.employee_id,
            'first_name' IS e.first_name,
            'last_name' IS e.last_name,
            'email' IS e.email,
            'phone_number' IS e.phone_number,
            'hire_date' IS e.hire_date,
            'job_id' IS e.job_id,
            'salary' IS e.salary,
            'commission_pct' IS e.commission_pct,
            'manager_id' IS e.manager_id,
            'department_name' IS d.department_name
        }
    INTO
        l_employee
    FROM
        hr.employees e 
        JOIN hr.departments d ON (e.department_id = d.department_id)
    WHERE
        e.employee_id = 101;
    
    employees_pkg.add_employee(l_employee);
END;
/

Find All Employees in the Collection

Finally, EMPLOYEES_PKG.find_all_employees() can be used to provide a shortened list of employees stored in the collection.

SELECT 
  JSON_SERIALIZE(
    employees_pkg.find_all_employees
    PRETTY
  ) AS all_employees;
/

Result:

ALL_EMPLOYEES
--------------------------------------------------
[
  {
    "employee_id" : 100,
    "last_name" : "King",
    "department_name" : "Executive"
  },
  {
    "employee_id" : 101,
    "last_name" : "Yang",
    "department_name" : "Executive"
  }
]