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:

• Oracle AI Database Introduction to Simple Oracle Document Access (SODA) for an overview of using SODA indexing

• Oracle AI Database Introduction to Simple Oracle Document Access (SODA) for information about SODA index specifications

• Oracle AI Database JSON Developer’s Guide for information about JSON search indexes

• Oracle AI Database JSON Developer’s Guide for information about persistent data-guide information as part of a JSON search index

Example 8-18 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 employeesCollection (created in Example 8-8).

export function createBTreeIndex(){

  // open the collection
  const col = soda.openCollection('employeesCollection');
  if(col === null){
    throw new Error("'employeesCollection' does not exist");
  }

  // define the index...
  const indexSpec = {
    "name": "DEPARTMENTS_IDX",
    "fields": [
      {
        "path": "departmentId",
        "datatype": "number",
        "order": "asc"
      }
    ]
  };

  //... and create it
  try{
    col.createIndex(indexSpec);
  } catch(err){
      throw new Error(
        `could not create the index: ${err}`
      )
  }
}

Example 8-19 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 employeesCollection (created in Example 8-8). 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 8-18).

export function createSearchIndex(){

  // open the collection
  const col = soda.openCollection("employeesCollection");
  if(col === null){
    throw new Error("'employeesCollection' does not exist");
  }

  // define the index properties...
  cost indexSpec = {
    "name": "SEARCH_AND_DATA_GUIDE_IDX",
    "dataguide": "on",
    "search_on": "text_value"
  }

  //...and create it
  try{
    col.createIndex(indexSpec);
  } catch(err){
      throw new Error(
        `could not create the search and Data Guide index: ${err}`
      );
  }
}

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 8-20 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.

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

export function dropIndex(indexName){

  // open the collection
  const col = soda.openCollection("employeesCollection");
  if(col === null){
    throw new Error("'employeesCollection' 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.