Indexes and Indexing

      +

      Description — Couchbase mobile database indexing concepts
      Related Content — Databases | Documents | Indexing |

      Introduction

      Querying documents using a pre-existing database index is much faster because an index narrows down the set of documents to examine — see: the Query Troubleshooting topic.

      When planning the indexes you need for your database, remember that while indexes make queries faster, they may also:

      • Make writes slightly slower, because each index must be updated whenever a document is updated

      • Make your Couchbase Lite database slightly larger.

      Too many indexes may hurt performance. Optimal performance depends on designing and creating the right indexes to go along with your queries.

      Constraints
      Couchbase Lite for ios does not currently support partial value indexes; indexes with non-property expressions. You should only index with properties that you plan to use in the query.

      Creating a new index

      You can use SQL++ or QueryBuilder syntaxes to create an index

      Example 2 creates a new index for the type and name properties, shown in this data model:

      Example 1. Data Model
      {
          "_id": "hotel123",
          "type": "hotel",
          "name": "The Michigander",
          "overview": "Ideally situated for exploration of the Motor City and the wider state of Michigan. Tripadvisor rated the hotel ...",
          "state": "Michigan"
      }

      SQL++

      The code to create the index will look something like this:

      Example 2. Create index
      
      CBLValueIndexConfiguration* config = [[CBLValueIndexConfiguration alloc]
                                            initWithExpression: @[@"type", @"name"]];
      
      [collection createIndexWithName:@"TypeNameIndex" config:config error: &error];

      QueryBuilder

      See the QueryBuilder topic to learn more about QueryBuilder.

      The code to create the index will look something like this:

      Example 3. Create index with QueryBuilder
      CBLValueIndexItem *type = [CBLValueIndexItem property:@"type"];
      CBLValueIndexItem *name = [CBLValueIndexItem property:@"name"];
      CBLIndex *index = [CBLIndexBuilder valueIndexWithItems:@[type, name]];
      [collection createIndex:index name:@"TypeNameIndex" error:&error];

      Array Indexing

      Couchbase Lite 3.2.1 introduces functionality to optimize querying arrays. Array UNNEST to unpack arrays within a document to allow joins with the parent object, and array indexes for indexing unnested array’s values to allow more efficient queries with UNNEST.

      The Array Index

      An array index is a new type of the index for indexing nested array’s properties to allow querying with the UNNEST more efficiently.

      Below is an example array index configuration:

      CBLArrayIndexConfiguration* config = [[CBLArrayIndexConfiguration alloc] initWithPath: @"contacts" expressions: nil];

      Array Index Syntax

      The syntax for array index configuration is shown below:

      Name Is Optional? Description

      path

      no

      Path to the array to be indexed. Use [] to represent a property that is an array of each nested array level. For a single array or the last level array, the [] is optional. For instance contacts[].phones to specify an array of phones within each contact.

      expressions

      yes

      An optional array of strings where each string represents values within the array to be indexed. In N1QL/SQL++ syntax, these expressions are separated by commas. In JSON syntax, as supported by Couchbase Lite for C, the expressions are represented as a JSON array. If the array specified by the path contains scalar values, the expressions should be left unset or set to null.

      Using Array Indexes with UNNEST

      For the following examples, you can assume we are querying results from the following document, shown below:

      {
         "Name":"Sam",
         "contacts":[
           {
             "type":"primary",
             "address":{"street":"1 St","city":"San Pedro","state":"CA"},
             "phones":[
               {"type":"home","number":"310-123-4567"},
               {"type":"mobile","number":"310-123-6789"}
             ]
           },
           {
             "type":"secondary",
             "address":{"street":"5 St","city":"Seattle","state":"WA"},
             "phones":[
               {"type":"home","number":"206-123-4567"},
               {"type":"mobile","number":"206-123-6789"}
             ]
           }
         ],
         "likes":["soccer","travel"]
       }

      Using the document above you can perform queries on a single nested array like so:

      SELECT name, interest FROM _ UNNEST likes as like WHERE like = "travel"

      The query above produces the following output from the document:

      {"name": "Sam", "like": "travel"}

      You can also perform the same operation using array indexes like so:

      CBLArrayIndexConfiguration* config = [[CBLArrayIndexConfiguration alloc] initWithPath: @"likes" expressions: @[@"type"]];
      [collection createIndexWithName:@"singleArray" config: config error: &error];

      You can perform similar operations on nested arrays:

      SELECT name, contact.type, phone.number
      FROM profiles
      UNNEST contacts as contact
      UNNEST contact.phones as phone
      WHERE phone.type = "mobile"

      The query produces the following output:

      {"name": "Sam", "type": "primary", "number": "310-123-6789"}
      {"name": "Sam", "type": "secondary", "number": "206-123-6789"}

      The output demonstrates retrieval of both primary and secondary contact numbers listed as type "mobile".

      Here’s an example of creating an array index on a nested array containing dictionary values:

      CBLArrayIndexConfiguration* config = [[CBLArrayIndexConfiguration alloc] initWithPath: @"contacts[].phones" expressions: @[@"type"]];
      [collection createIndexWithName:@"nestedArray" config: config error: &error];

      The above snippet creates an array index to allow you to iterate through contacts[].phones[].type in the document, namely "home" and "mobile".

      Array literals are not supported in CBL 3.2.1. Attempting to create a query with array literals will return an error.