Use analyzers and non-tokenizing filters

The analyzer operator (:) is used to query analyzed or filtered columns in SAI indexes.

To enable the analyzer operator, create an SAI index with index_analyzer or a non-tokenizing filter in the WITH OPTIONS clause.

After you create an analyzed or filtered SAI index, use the analyzer operator (:) in your CQL queries in the same way that you use other operators:

SELECT * FROM animals.sharks
  WHERE analyzed_column : 'search_term';

The analyzer operator has the following restrictions:

Only SAI indexes support the : operator.
The : operator requires an SAI index with index_analyzer, case_sensitive, normalize, or ascii.
The analyzed column cannot be part of the primary key, including the partition key and clustering columns.
The : operator is only compatible with SELECT statements. It cannot be used to find rows for INSERT, UPDATE, or DELETE statements.
The : operator cannot be used with light-weight transactions, such as a condition for an IF clause.

SAI queries recognize both : and = operators, but = is deprecated for SAI indexes because it can be confused with the equality operator (also represented by =) for non-analyzed columns. By design, queries on analyzed indexes cannot be as deterministic as true equality matches on non-analyzed columns because the analyzed index processes column values according to the analyzer configuration.

The following examples cover built-in analyzers, tokenizers, filters, and non-tokenizing filters. Complete examples of every option and use case are outside the scope of this document. For more information, see SAI options and the Apache Lucene™ documentation.

These examples use the following table:

CREATE TABLE IF NOT EXISTS cycling.comments_vs (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  comment_vector VECTOR <FLOAT, 5>,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);

Standard analyzer

Built-in analyzers are preconfigured combinations on tokenizers and filters. This example uses the standard analyzer.

Create an index on the comment column with the standard analyzer.

CREATE CUSTOM INDEX comment_standard_idx ON cycling.comments_vs (comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {'index_analyzer': 'STANDARD'};

Query the table to find the matches for the word rain in the comment column using the standard analyzer.

SELECT comment,comment_vector,commenter FROM cycling.comments_vs
  WHERE comment : 'Rain';

This query returns the rows with the word rain or Rain in the comment column, but not raining. The standard analyzer splits the text into case-independent terms, but does not perform stemming. It is also case-insensitive so that both uppercase and lowercase are returned.

 comment                                | comment_vector                | commenter
----------------------------------------+-------------------------------+-----------
              rain, rain,rain, go away! |  [0.9, 0.54, 0.12, 0.1, 0.95] |      John
      Glad you ran the race in the rain |  [0.3, 0.34, 0.2, 0.78, 0.25] |       Amy
                    Rain like a monsoon |  [0.9, 0.54, 0.12, 0.1, 0.95] |      Jane

(3 rows)

Standard tokenizer with filters

To create a custom analyzer, specify the tokenizer and optional filters and charFilters. Depending on your chosen tokenizers and filters, additional fields might be required. See the Apache Lucene documentation for configuration details.

Standard tokenizer with lowercase filter

The lowercase filter enables case insensitivity. It is an important filter when the dataset contains mixed-case text.

CREATE CUSTOM INDEX comment_standard_lowercase_idx ON cycling.comments_vs (comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
  "tokenizer" : { "name" : "standard", "args" : {} },
  "filters" : [ { "name" : "lowercase", "args": {} } ],
  "charFilters" : []
  }'
};

Queries on the comment column return all rows containing a given string, regardless of the letter case. For example:

SELECT comment,comment_vector,commenter FROM cycling.comments_vs
  WHERE comment : 'rain';

 comment                                | comment_vector                | commenter
----------------------------------------+-------------------------------+-----------
              rain, rain,rain, go away! |  [0.9, 0.54, 0.12, 0.1, 0.95] |      John
      Glad you ran the race in the rain |  [0.3, 0.34, 0.2, 0.78, 0.25] |       Amy
                    Rain like a monsoon |  [0.9, 0.54, 0.12, 0.1, 0.95] |      Jane

(3 rows)

Standard tokenizer with stemming filter

The porterstem filter enables matches with stemming, such as rain, raining, and rained.

CREATE CUSTOM INDEX comment_stemming_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
    "tokenizer" : {"name" : "standard"},
    "filters" : [{"name" : "porterstem"}]
  }'
};

Queries on the comment column return all rows containing a given string with various stems. However, case sensitivity still applies because the lowercase filter is not used. For example, queries on rain don’t match Rain or Raining because they are mixed case.

SELECT comment,comment_vector,commenter FROM cycling.comments_vs
  WHERE comment : 'rain';

All results are lowercase:

 comment                           | comment_vector               | commenter
-----------------------------------+------------------------------+-----------
         rain, rain,rain, go away! | [0.9, 0.54, 0.12, 0.1, 0.95] |      John
 Glad you ran the race in the rain | [0.3, 0.34, 0.2, 0.78, 0.25] |       Amy

(2 rows)

To get all stemmed matches regardless of case, create the index with porterstem and lowercase filters:

CREATE CUSTOM INDEX comment_stemming_lowercase_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
    "tokenizer" : {"name" : "standard"},
    "filters" : [{"name" : "lowercase"}, {"name" : "porterstem"}]
  }'
};

Repeating the previous query on rain now returns all rows containing rain regardless of case or stem, including Rain and Raining:

 comment                                | comment_vector                | commenter
----------------------------------------+-------------------------------+-----------
              rain, rain,rain, go away! |  [0.9, 0.54, 0.12, 0.1, 0.95] |      John
 Raining too hard should have postponed | [0.45, 0.09, 0.01, 0.2, 0.11] |      Alex
      Glad you ran the race in the rain |  [0.3, 0.34, 0.2, 0.78, 0.25] |       Amy
                    Rain like a monsoon |  [0.9, 0.54, 0.12, 0.1, 0.95] |      Jane

(4 rows)

You can chain conditions on the same analyzed column with the AND operator. For example, this query finds rows where the comment column contains both rain and race:

SELECT comment,comment_vector,commenter FROM cycling.comments_vs
  WHERE comment : 'rain' AND comment : 'race';

Standard tokenizer with lowercase and language stem filter

You can use language-specific stem filters for non-English text. This example uses the czechstem filter to search for the Czech word pánové with accents:

CREATE CUSTOM INDEX comment_language_lowercase_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
  "tokenizer" : {"name" : "standard"},
  "filters" : [{"name" : "lowercase"}, {"name" : "czechstem"}]
  }'
};

Query the table for a Czech string:

SELECT comment,comment_vector,commenter FROM cycling.comments_vs
  WHERE comment : 'pánové';

+ The lowercase filter enforces case insensitivity, allowing matches on pánové and Pánové:

 comment             | comment_vector | commenter
---------------------+----------------+-----------
 pánové, to je dobré |           null |    Michal
 Pánové, to je dobré |           null |   Martina

(2 rows)

Standard tokenizer with character filter

Character filters are applied to the text before tokenization. This example uses the mapping character filter to map the strings happy and sad to emojis:

CREATE CUSTOM INDEX comment_mapping_lowercase_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer' : '{
  "tokenizer" : {"name" : "standard"},
  "filters" : [{"name" : "lowercase"}],
  "charFilters" : [ { "name" :"mapping", "args": {"mapping" : "\":)\" => \"_happy_\", \":(\" => \"_sad_\"" } }]
  }'
};

Query the table on the mapped string (happy or sad):

SELECT comment FROM cycling.comments_vs WHERE comment : '_sad_';

The results include rows with the emoji mapped to sad:

 comment
-------------------------------------------
 LATE RIDERS SHOULD NOT DELAY THE START :(

(1 rows)

Querying on the actual emoji also works because the character filter is an additional preprocessing step that maps the emoji to the corresponding string, but doesn’t drop the original emoji from the text.

SELECT comment FROM cycling.comments_vs WHERE comment : ':(';

 comment
-------------------------------------------
 LATE RIDERS SHOULD NOT DELAY THE START :(

(1 rows)

Whitespace tokenizer

When you use the whitespace tokenizer, the text is split into terms whenever the analyzer encounters any whitespace character, but not other characters like commas:

CREATE CUSTOM INDEX comment_whitespace_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
    'index_analyzer': '{
      "tokenizer": {"name" : "whitespace"}
    }'
  };

Query the table:

SELECT comment,comment_vector,commenter FROM cycling.comments_vs
  WHERE comment : 'rain';

The results include the row with rain surrounded by whitespace, but not the row with rain, rain,rain where each rain is adjacent to a non-whitespace character.

 comment                           | comment_vector               | commenter
-----------------------------------+------------------------------+-----------
 Glad you ran the race in the rain | [0.3, 0.34, 0.2, 0.78, 0.25] |       Amy

(1 rows)

The whitespace tokenizer can be combined with filters, such as porterstem, to modify the terms before indexing. However, repeating the same query still won’t match rain, rain,rain because porterstem doesn’t affect punctuation.

CREATE CUSTOM INDEX comment_whitespace_stemming_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
  "tokenizer":{"name" : "whitespace"},
  "filters":[{"name" : "porterstem"}]
  }'
};

The lowercase filter is always useful when you don’t need to enforce case sensitivity. With lowercase and porterstem, querying on rain matches rain, raining, and Rain. rain, rain,rain still doesn’t match due to the punctuation.

CREATE CUSTOM INDEX comment_whitespace_lowercase_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
  "tokenizer":{"name" : "whitespace"},
  "filters":[{"name" : "porterstem"}, {"name" : "lowercase"}]
  }'
};

The whitespace tokenizer can be useful when you need to split strictly on whitespace but not on other characters. Otherwise, the standard tokenizer is more useful.

Whitespace analyzer

Creating an index with the whitespace analyzer is simpler than the whitespace tokenizer, but it isn’t configurable. For example, you can’t add lowercase or porterstem filters to the whitespace analyzer.

CREATE CUSTOM INDEX comment_whitespace_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {'index_analyzer':'whitespace'};

N-gram tokenizer

The ngram tokenizer divides text into terms whenever it encounters any whitespace character, then returns n-grams of each word. N-grams are contiguous sequences of characters of a specified length extracted from a word, such as bigrams (2-grams) or trigrams (3-grams). For example, rain could be [ra, ai, in].

CREATE CUSTOM INDEX comment_ngram_lowercase_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
    "tokenizer" : {"name" : "ngram", "args" : {"minGramSize":"2", "maxGramSize":"3"}},
    "filters" : [{"name" : "lowercase"}]
  }'
};

The query now requires only a few characters to match:

SELECT comment,comment_vector,commenter FROM cycling.comments_vs
  WHERE comment : 'ra';

All rows containing ra in the comment column are returned, including rain, raining, and race:

 comment                                | comment_vector                | commenter
----------------------------------------+-------------------------------+-----------
              rain, rain,rain, go away! |  [0.9, 0.54, 0.12, 0.1, 0.95] |      John
 Raining too hard should have postponed | [0.45, 0.09, 0.01, 0.2, 0.11] |      Alex
      Glad you ran the race in the rain |  [0.3, 0.34, 0.2, 0.78, 0.25] |       Amy
                 THE RACE WAS FABULOUS! |  [0.3, 0.34, 0.2, 0.78, 0.25] |       Amy
                    Rain like a monsoon |  [0.9, 0.54, 0.12, 0.1, 0.95] |      Jane

(5 rows)

The ngram tokenizer enables partial string matching that can be excluded by other tokenizers (like whitespace), but it can generate an excessive number of terms for each word and return too many unrelated results.

Simple pattern tokenizer

Use the simplepattern tokenizer to search for a pattern in the text. This example searches for sequences of three digits in the comment column.

CREATE CUSTOM INDEX comment_pattern_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
  "tokenizer" : {"name" : "simplepattern", "args" : {"pattern":"[0123456789]{3}"}}
  }'
};

Query the table with a three-digit sequence:

SELECT comment,comment_vector,commenter FROM cycling.comments_vs
  WHERE comment: '335';

 comment          | comment_vector | commenter
------------------+----------------+-----------
 fd-786-335-552-x |           null |       Amy
 fd-786-335-514-x |           null |       Amy
 fd-333-335-514-x |           null |       Amy

(3 rows)

Keyword tokenizer with filters

Use the keyword tokenizer to search for a keyword in the text.

HTML strip character filter

The htmlstrip character filter drops HTML elements from the text before indexing.

CREATE CUSTOM INDEX comment_keyword_htmlstrip_idx ON cycling.comments_vs(comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
  "tokenizer" : {"name" : "keyword"},
  "charFilters" : [{"name" : "htmlstrip"}]
  }'
};

Query the table for a string without HTML elements:

SELECT comment,comment_vector,commenter FROM cycling.comments_vs
  WHERE comment : 'The gift certificate for winning was the best';

This query returns two rows, one with the phrase gift certificate and one with the same phrase wrapped in bold HTML tags. The htmlstrip filter enabled matches on the plain text and the HTML-wrapped text.

 comment                                              | comment_vector                | commenter
------------------------------------------------------+-------------------------------+-----------
        The gift certificate for winning was the best | [0.13, 0.8, 0.35, 0.17, 0.03] |       Amy
 The <B>gift certificate</B> for winning was the best | [0.13, 0.8, 0.35, 0.17, 0.03] |       Amy

(2 rows)

Synonym and lowercase filters

This example uses the keyword tokenizer with the synonym and lowercase filters to enable matches on rain and the synonym drizzle:

CREATE CUSTOM INDEX comment_synonym_lowercase_idx ON cycling.comments_vs (comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
  "tokenizer": {"name" : "keyword"},
  "filters" : [ {"name" : "lowercase"},
  {"name" : "synonym", "args" : {"synonyms" : "rain, drizzle => rain"}}]
  }'
};

Query the table using either rain or drizzle:

SELECT comment, comment_vector, commenter FROM cycling.comments_vs
  WHERE comment : 'drizzle';

Due to the synonym mapping, the query returns rows with both rain and drizzle regardless of the string used in the query:

 comment                           | comment_vector               | commenter
-----------------------------------+------------------------------+-----------
 Glad you ran the race in the rain | [0.3, 0.34, 0.2, 0.78, 0.25] |       Amy
               Rain like a monsoon | [0.9, 0.54, 0.12, 0.1, 0.95] |      Jane
  Boy, was it a drizzle out there! | [0.3, 0.34, 0.2, 0.78, 0.25] |      Jane

(3 rows)

Synonym filter without lowercase

This example uses the keyword tokenizer with the synonym filter to match on Alex, alex, Alexander, and alexander in the commenter column. Because the lowercase filter is not used, synonym matching is case sensitive, and all variants in case must be included in the synonym mapping.

CREATE CUSTOM INDEX commenter_synonym_keyword_idx ON cycling.comments_vs(commenter)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {
  'index_analyzer': '{
  "tokenizer": {"name" : "keyword"},
  "filters" : [
  {"name" : "synonym", "args" : {"synonyms" : "Alex, alex, Alexander, alexander => Alex"}}]
  }'
};

Query the table on any of the synonym strings:

SELECT * FROM cycling.comments_vs WHERE commenter : 'alexander';

The results include Alex as a match for alexander due to the synonym mapping:

 id                                   | created_at                      | comment                                | comment_vector                | commenter | record_id
--------------------------------------+---------------------------------+----------------------------------------+-------------------------------+-----------+--------------------------------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.160000+0000 | LATE RIDERS SHOULD NOT DELAY THE START |  [0.9, 0.54, 0.12, 0.1, 0.95] |      Alex | 700ebed0-4084-11ef-893f-0fd142cea825
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-03-21 21:11:09.999000+0000 |      Second rest stop was out of water |  [0.99, 0.5, 0.99, 0.1, 0.34] |      Alex | 700d8650-4084-11ef-893f-0fd142cea825
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-02-14 20:43:20.000000+0000 | Raining too hard should have postponed | [0.45, 0.09, 0.01, 0.2, 0.11] |      Alex | 6fe2f3e0-4084-11ef-893f-0fd142cea825
 e7ae5cf3-d358-4d99-e900-85902fda9bb0 | 2017-04-01 14:33:02.160000+0000 |      LATE RIDERS SHOULD NOT BE ALLOWED |  [0.9, 0.54, 0.12, 0.1, 0.95] | alexander | c812c9f0-4084-11ef-893f-0fd142cea825

(4 rows)

Non-tokenizing filters

The non-tokenizing filters, case_sensitive, normalize, and ascii, cannot be combined with an index_analyzer, but they can be chained in a pipeline with other non-tokenizing filters. For explanations of the filters, see SAI options.

These examples demonstrate the case_sensitive non-tokenizing filter.

On the comment column, create a case-insensitive index by setting the case_sensitive filter set to false:

CREATE CUSTOM INDEX comment_case_insensitive_idx ON cycling.comments_vs (comment)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = { 'case_sensitive': false };

On the commenter column, create a case-sensitive index by setting the case_sensitive filter set to true:

CREATE CUSTOM INDEX commenter_case_sensitive_idx ON cycling.comments_vs (commenter)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = { 'case_sensitive': true};

Use the SAI analyzer operator : on the comment column with the query 'RAIN' (uppercase):

SELECT comment,commenter FROM cycling.comments_vs
  WHERE comment : 'RAIN';

Because the 'comment' column index is case-insensitive, it matches all uppercase and lowercase variants of rain:

 comment                           | commenter
-----------------------------------+-----------
 Glad you ran the race in the rain |       Amy
               Rain like a monsoon |      Jane
  Boy, was it a drizzle out there! |      Jane

(3 rows)

Use the SAI analyzer operator : on the commenter column with the query 'jane' (lowercase):
```
SELECT comment,commenter FROM cycling.comments_vs
  WHERE commenter : 'jane';
```
Because the commenter column index is case-sensitive, it only matches the exact case of the query. No results are returned because there are no rows where jane is entirely lowercase.
```
comment     | commenter
------------+-----------

(0 rows)
```

Query the commenter column for 'Jane' (uppercase J):

SELECT comment,commenter FROM cycling.comments_vs
  WHERE commenter : 'Jane';

Matches are found because the case of the query matches the case of the indexed values in the commenter column:

 comment                           | commenter
-----------------------------------+-----------
               Rain like a monsoon |      Jane
  Boy, was it a drizzle out there! |      Jane

(2 rows)