Search code examples
cassandradatastaxdatastax-enterprise

How to use CONTAINS on TEXT column with SAI index?

According to this article: https://docs.datastax.com/en/storage-attached-index/6.8/sai/saiFaqs.html , the CONTAINS operation is supported for TEXT type. But I receive such error: com.datastax.driver.core.exceptions.InvalidQueryException: Cannot use CONTAINS on non-collection column

Can anyone explain how to use SAI indexes on TEXT column to have a possibility to use CONTAINS operation (like SQL LIKE operation) for search in this column?

P.S. I was able to use CONTAINS operation if SAI index is created on the column with List of TEXT values. But don't want to generate all combinations of substrings by myself. At least according to the documentation, it should be possible to use CONTAINS on TEXT...

DB schema:

CREATE TABLE IF NOT EXISTS some_table (
 id text,
 data text,
 list_for_search list<text>,
 PRIMARY KEY (id))
WITH CLUSTERING ORDER BY (id ASC);

CREATE CUSTOM INDEX IF NOT EXISTS some_table_search_idx
 ON some_table (data)
 USING 'StorageAttachedIndex' WITH OPTIONS = {'case_sensitive': 'false'};

DSE 6.8.3

Solution

  • The CONTAINS and it's variants CONTAINS KEY and CONTAINS VALUES are designed for searching the specific string value inside collections of text types (maps, sets, and lists) - it's not supported for searching the substring inside the text (that is the task of the LIKE that isn't supported (yet?)).

    Per documentation:

    Not supported: LIKE, IN, OR