Finding Documents in Collections with SODA for In-Database JavaScript

Previous Next JavaScript must be enabled to correctly display this content

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 8-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 8-9 Finding a Document by Key

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

export function findDocByKey(searchKey){

  const collectionName = 'MyCollection';

  // open the collection in preparation of a document lookup
  const col = soda.openCollection(collectionName);
  if (col === null){
    throw new Error(`${collectionName} does not exist`);
  }

  try{
    // perform a lookup of a document with the key provided as a 
    // parameter to this function. Keys are like primary keys,
    // the lookup therefore can only return 1 document max
    const doc = col.find()
                   .key(searchKey)
                   .getOne();
    console.log(`
      document found for key ${searchKey}
      contents: ${doc.getContentAsString()}`
    );
  } catch(err){
      throw new Error(
        `error retrieving document with key ${searchKey} (${err})`
      );
  }
}

Note:

Keys need to be enclosed in 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 8-10 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.

See Example 8-8 for details about how to create employeesCollection, used in this example.

export function findDocByKeys(searchKeys){
  
  if(!Array.isArray(searchKeys)){
    throw new Error('please provide an array of search keys');
  }

  // open a collection in preparation of a document lookup
  const col = soda.openCollection('employeesCollection');
  if (col === null){
    throw new Error('employeesCollection does not exist');
  }

  try{
    // perform a lookup of a set of documents using
    // the "keys" array provided
    const docCursor =
      col.find()
         .keys(searchKeys)
         .getCursor();

    let doc
      while((doc = docCursor.getNext())){
        console.log(`
          document found for key ${doc.key}
          contents: ${doc.getContentAsString()}`
        );
      }
      docCursor.close();
  } catch(err){
      // there is no error thrown if one/all of the keys aren't found
      // this error handler is generic
      throw new Error(
        `error retrieving documents with keys ${searchKeys} (${err})`
      );
  }
}

Rather than failing with an error, 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 8-11 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.

See Example 8-8 for details about how to create employeesCollection, used in this example.

export function findDocByFiltering(){

  // 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 = soda.openCollection('employeesCollection');
  if(col === null){
    throw new Error(`employeesCollection does not exist`);
  }

  // find all employees with an employee_id > 100 and
  // last name beginning with M
  const filterCondition = {
    "$and": [
      { "lastName": { "$upper": { "$startsWith": "M" } } },
      { "_id": { "$gt": 100 } }
    ]  
  };

  try{
    
    // perform the lookup operation using the QBE defined earlier
    const docCursor = col.find()
                         .filter(filterCondition)
                         .getCursor();
    let doc;
    while ((doc = docCursor.getNext())){
      console.log(`
        ------------------------------------
        document found matching the search criteria
        - key:           ${doc.key}
        - _id:           ${doc.getContent()._id}
        - name:          ${doc.getContent().lastName}`
      );
    }

    docCursor.close();
  } catch(err){
      throw new Error(`error looking up documents using a QBE: ${err}`);
  }
}

See Also:

Oracle AI Database Introduction to Simple Oracle Document Access (SODA) for an introduction to SODA filter specifications
Oracle AI Database Introduction to Simple Oracle Document Access (SODA) for reference information about SODA filter specifications

Example 8-12 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.

See Example 8-8 for details about how to create employeesCollection, used in this example.

export function paginationExample(){

  // 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 = soda.openCollection('employeesCollection');
  if(col === null){
    throw new Error ('employeesCollection does not exist, aborting');
  }

  // find all employees with an employee_id > 100 and
  // last name beginning with E
  const filterCondition = {
    "$and": [
      { "lastName": { "$upper": { "$startsWith": "M" } } },
      { "_id": { "$gt": 100 } }
    ]
  };

  try{

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

    docCursor.close();
  } catch(err){
      throw new Error(
        `error looking up documents by QBE (${err})`
      );
  }
}

Example 8-13 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.

See Example 8-8 for details about how to create employeesCollection, used in this example.

export function versioningExample(searchKey, version){

  // 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 = soda.openCollection("employeesCollection");

  try{
    // perform a lookup of a document using the provided key and version
    const doc = col
      .find()
      .key(searchKey)
      .version(version)
      .getOne();
    console.log(`
      document found for key ${doc.key}
      contents: ${doc.getContentAsString()}`
    );
  } catch(err){
      throw new Error(
        `${err} during lookup. Key: ${searchKey}, version: ${version}`
      );
  }
}

If SODA cannot find the document matching the key and version tag, an ORA-01403: no data found error is thrown.

Example 8-14 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.

See Example 8-8 for details about how to create employeesCollection, used in this example.

export function countingExample(){

  // 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 = soda.openCollection("employeesCollection");
  if(col === null){
    throw new Error('employeesCollection does not exist');
  }

  try{

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

Parent topic: Using SODA for In-Database JavaScript