Query with SAI

SAI indexes can be used to query composite partition key columns, clustering columns, and non-primary key columns of almost any type.

DSE supports the following query operators for tables with SAI indexes:

  • Analyzers: :

  • Numerics: =, <, >, <=, >=, AND, IN

  • Strings: =, AND, IN

  • Collections: =, CONTAINS, CONTAINS KEY

    CONTAINS clauses cannot be used on FULL() map indexes.

The LIKE operator isn’t supported for SAI indexes.

The following examples demonstrate queries on different column types using SAI indexes and various supported operators. Each example includes the table and index definitions because this information is crucial for understanding the context of the queries. The keyspace used for these examples is named cycling. Keyspace configuration options aren’t relevant to these examples.

Prerequisites

Single index match on a column (WHERE)

This example uses the following table and indexes:

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);
CREATE CUSTOM INDEX commenter_idx ON cycling.comments_vs (commenter)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {'case_sensitive': 'true', 'normalize': 'true', 'ascii': 'true'};
CREATE CUSTOM INDEX created_at_idx ON cycling.comments_vs (created_at)
  USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX comment_ann_idx ON cycling.comments_vs(comment_vector)
  USING 'StorageAttachedIndex';

The column commenter is not the partition key in this table, so an index is required to query on it.

Query for a match on that column:

SELECT * FROM cycling.comments_vs WHERE commenter = 'Alex';
 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 | 3d3d9921-37da-11ef-81ed-f92c3c7170c3
 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 | 3d3c3991-37da-11ef-81ed-f92c3c7170c3
 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 | 3d390540-37da-11ef-81ed-f92c3c7170c3

(3 rows)

A failure will occur if you try this query before creating the index.

Multiple index matching with operators (WHERE, AND)

Use the AND operator to append multiple indexed column conditions to a single query. There is no limit on the number of index columns that can be used in a single query. However, the sai_indexes_per_table_failure_threshold setting in cassandra.yaml controls the maximum number of SAI indexes allowed in a single table.

Querying against multiple indexed columns incurs performance costs related to the increased number of index components processed. When evaluating multiple indexed columns in a query, SAI performs a workflow to traverse, merge, and intersect the index results that ultimately coalesces data from multiple memtables and SSTables. AND queries can process two SAI indexes concurrently. If a query uses more than two SAI indexes, SAI performs post-filtering on the remaining clauses.

DataStax performance testing has observed as much as double the latency and two-thirds of throughput when the number of index columns queried doubles. The relative impact on performance is largely dependent on your data model and distribution. DataStax recommends that you test specific use cases in your development environment.

These examples use the following table and indexes. Several indexes are created for the table to demonstrate how to query for matches on more than one column.

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);
CREATE CUSTOM INDEX commenter_idx ON cycling.comments_vs (commenter)
  USING 'StorageAttachedIndex'
  WITH OPTIONS = {'case_sensitive': 'true', 'normalize': 'true', 'ascii': 'true'};
CREATE CUSTOM INDEX created_at_idx ON cycling.comments_vs (created_at)
  USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX comment_ann_idx ON cycling.comments_vs(comment_vector)
  USING 'StorageAttachedIndex';

  • AND: Query for matches on more than one column where both columns must match:

    SELECT * FROM cycling.comments_vs
  WHERE created_at = '2017-03-21 21:11:09.999'
  AND commenter = 'Alex';
     id                                   | created_at                      | comment                           | comment_vector               | commenter | record_id
--------------------------------------+---------------------------------+-----------------------------------+------------------------------+-----------+--------------------------------------
 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

(1 rows)

  • IN: Query for matches from a list of values:

    SELECT * FROM cycling.comments_vs
  WHERE created_at IN ('2017-03-21 21:11:09.999','2017-03-22 01:16:59.001');
     id                                   | created_at                      | comment                           | comment_vector               | commenter | record_id
--------------------------------------+---------------------------------+-----------------------------------+------------------------------+-----------+--------------------------------------
 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
 c7fceba0-c141-4207-9494-a29f9809de6f | 2017-03-22 01:16:59.001000+0000 |     Great snacks at all reststops |  [0.1, 0.4, 0.1, 0.52, 0.09] |       Amy | 70143d10-4084-11ef-893f-0fd142cea825

(2 rows)

Set collections

Create an index on a set to find all the cyclists that have been on a particular team:

  1. Create a table called cycling.cyclist-career-teams if it doesn’t already exist.

    CREATE TABLE IF NOT EXISTS cycling.cyclist_career_teams (
  id UUID PRIMARY KEY,
  lastname text,
  teams set<text>
);

  2. Create a SAI index on the teams column.

    CREATE CUSTOM INDEX IF NOT EXISTS teams_idx ON cycling.cyclist_career_teams (teams) USING 'StorageAttachedIndex';

  3. Query the table using the teams column with a CONTAINS phrase to get the rows where a set contains a particular value.

    SELECT lastname, teams FROM cycling.cyclist_career_teams
  WHERE teams CONTAINS 'Rabobank-Liv Giant';
     lastname | teams
----------+----------------------------------------------------------------------------------------------------
    BRAND | {'AA Drink - Leontien.nl', 'Leontien.nl', 'Rabobank-Liv Giant', 'Rabobank-Liv Woman Cycling Team'}

(1 rows)

List collections

Create an index on a list to find all the cyclists that have a particular sponsor:

  1. Create a table called cycling.race_sponsors if it doesn’t already exist.

    CREATE TABLE IF NOT EXISTS cycling.race_sponsors (
  race_year int,
  race_name text,
  sponsorship list<text>,
  PRIMARY KEY (race_year, race_name)
);

  2. Create a SAI index on the sponsorship list column.

    CREATE CUSTOM INDEX sponsorship_idx ON cycling.race_sponsors (sponsorship)
  USING 'StorageAttachedIndex';

  3. Query the table using the sponsorship column with a CONTAINS phrase to get the rows where a list contains a particular value.

    SELECT * FROM cycling.race_sponsors WHERE sponsorship CONTAINS 'Carrefour';
     race_year | race_name      | sponsorship
-----------+----------------+-------------------------------------------------
      2018 | Tour de France | ['LCL', 'Carrefour', 'Skoda', 'Vittel', 'Krys']

(1 rows)

Map collections (KEYS)

For map collections, create an index on the map key, map value, or map entry.

Create an index on a map key to find all cyclist/team combinations for a particular year:

  1. Create a table called cycling.cyclist_teams if it doesn’t already exist.

    CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  id uuid PRIMARY KEY,
  firstname text,
  lastname text,
  teams map<int, text>
);

  2. Create a SAI index on the teams map column.

    CREATE CUSTOM INDEX IF NOT EXISTS team_keys_idx
  ON cycling.cyclist_teams ( KEYS (teams) ) USING 'StorageAttachedIndex';

  3. Query the table using the teams column with a CONTAINS KEY phrase to get the rows where a map contains a particular key.

    SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams CONTAINS KEY 2015;
     firstname | lastname   | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'}
  Marianne |        VOS |                                                                                                                                   {2015: 'Rabobank-Liv Woman Cycling Team'}

(2 rows)

    Not all map columns are designed for useful searches on map keys. In some cases, indexing on map values or map entries produces more useful results. The following example demonstrates how to query with a map key index, but the map key index used in this example doesn’t produce useful results.

Create a table called cycling.birthday if it doesn’t already exist.

+

CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  cyclist_name text PRIMARY KEY,
  blist map<text, text>
);

  1. Create a SAI index on the blist map column.

    CREATE CUSTOM INDEX IF NOT EXISTS blist_keys_idx
  ON cycling.birthday_list ( KEYS(blist) )
  USING 'StorageAttachedIndex';

  2. Query the table using the blist column with a CONTAINS KEY phrase to get the rows where a map contains a particular key.

    SELECT * FROM cycling.birthday_list WHERE blist CONTAINS KEY 'age';
     cyclist_name     | blist
------------------+--------------------------------------------------------------
   Claudio HEINEN |     {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'}
 Claudio VANDELLI |       {'age': '54', 'bday': '27/07/1961', 'nation': 'ITALY'}
    Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
      Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}
      Allan DAVIS |   {'age': '35', 'bday': '27/07/1980', 'nation': 'AUSTRALIA'}
 Laurence BOURQUE |      {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

(6 rows)

    In this case, the map key is not a useful index because the map key is not a unique identifier; it has the same value for all rows.

Map collections (VALUES)

For map collections, create an index on the map key, map value, or map entry.

Create an index on the map values and find cyclists whose teams column has a particular value found in the specified map:

  1. Create a table called cycling.cyclist_teams if it doesn’t already exist.

    CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  id uuid PRIMARY KEY,
  firstname text,
  lastname text,
  teams map<int, text>
);

  2. Create a SAI index on the teams map column.

    CREATE CUSTOM INDEX IF NOT EXISTS team_values_idx
  ON cycling.cyclist_teams ( VALUES (teams) ) USING 'StorageAttachedIndex';

  3. Now that you have created the table and index, query using a value for the map column.

    SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams CONTAINS 'Team Garmin - Cervelo';
     firstname | lastname   | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Jamie |    BENNETT |                                                                     {2011: 'Team Garmin - Cervelo', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team'}
 Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'}

(2 rows)

    Because the values of a map column are often unique, querying on a map value can be more useful than querying on map keys. This is demonstrated in the following example.

  4. Create a table called cycling.birthday if it doesn’t already exist.

    CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  cyclist_name text PRIMARY KEY,
  blist map<text, text>
);

  5. Create a SAI index on the blist map column.

    CREATE CUSTOM INDEX IF NOT EXISTS blist_values_idx
  ON cycling.birthday_list ( VALUES(blist) )
  USING 'StorageAttachedIndex';

  6. Now that you have created the table and index, query using a value for the map column.

    SELECT * FROM cycling.birthday_list WHERE blist CONTAINS 'NETHERLANDS';
     cyclist_name  | blist
---------------+--------------------------------------------------------------
 Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
   Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}

(2 rows)

Map collections (ENTRIES)

For map collections, create an index on the map key, map value, or map entry.

Create an index on the map entries and find cyclists who are the same age. An index using ENTRIES is only valid for maps.

  1. Create a table called cycling.birthday if it doesn’t already exist.

    CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  cyclist_name text PRIMARY KEY,
  blist map<text, text>
);

  2. Create a SAI index on the blist map column.

    CREATE CUSTOM INDEX IF NOT EXISTS blist_entries_idx
  ON cycling.birthday_list ( ENTRIES(blist) )
  USING 'StorageAttachedIndex';

Now that you have created the table and index, query using a key and a value for the map column:

Example: SELECT 1

Query the table using both a key and a value to get the rows where a map contains a particular entry:

SELECT * FROM cycling.birthday_list WHERE blist[ 'age' ] = '23';
 cyclist_name     | blist
------------------+----------------------------------------------------------
   Claudio HEINEN | {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'}
 Laurence BOURQUE |  {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

(2 rows)
Example: SELECT 2

Query the table using both a key and a value to get the rows where a map contains cyclists from the same country in the entry for each:

SELECT * FROM cycling.birthday_list WHERE blist[ 'nation' ] = 'NETHERLANDS';
 cyclist_name  | blist
---------------+--------------------------------------------------------------
 Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
   Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}

(2 rows)
Example: DATE

Any number of different queries can be made using the ENTRIES index, such as selecting a matching entry with a DATE data type:

SELECT * FROM cycling.birthday_list WHERE blist [ 'bday' ] = '27/07/1992';
 cyclist_name     | blist
------------------+----------------------------------------------------------
   Claudio HEINEN | {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'}
 Laurence BOURQUE |  {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

(2 rows)
Example: AND

Two different matches can be made using AND and the ENTRIES index:

SELECT * FROM cycling.birthday_list
  WHERE blist[ 'nation' ] = 'CANADA' AND blist[ 'age' ] = '23';
 cyclist_name     | blist
------------------+---------------------------------------------------------
 Laurence BOURQUE | {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

(1 rows)

Queries on a map entry where the key is a unique identifier are structured differently, as shown in the following example:

  1. Create a table called cycling.cyclist-teams if it doesn’t already exist.

    CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  id uuid PRIMARY KEY,
  firstname text,
  lastname text,
  teams map<int, text>
);

  2. Create a SAI index on the teams map column.

    CREATE CUSTOM INDEX IF NOT EXISTS team_entries_idx
  ON cycling.cyclist_teams ( ENTRIES (teams) ) USING 'StorageAttachedIndex';

Now that you have created the table and index, query using a key and a value for the map column:

Example: SELECT 1

Query the table using both a key and a value to get the rows where a map contains a particular entry:

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams[2014] = 'Boels:Dolmans Cycling Team';
 firstname | lastname   | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Jamie |    BENNETT |                                                                     {2011: 'Team Garmin - Cervelo', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team'}
 Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'}

(2 rows)
Example: AND

Different matches can be made using AND and the ENTRIES index:

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams[2014] = 'Boels:Dolmans Cycling Team'
    AND teams[2015] = 'Boels:Dolmans Cycling Team';
 firstname | lastname   | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'}

(1 rows)

Frozen collections

Create an index on the full content of a FROZEN list. The table in this example stores the number of Pro wins, Grand Tour races, and Classic races that a cyclist has competed in. The SELECT statement finds any cyclist who has 39 Pro race wins, 7 Grand Tour starts, and 14 Classic starts.

The FROZEN keyword is required to index the full content of a frozen collection and applies to any collection type (set, list, or map).

  1. Create a table called race_starts if it does not yet exist.

    CREATE TABLE IF NOT EXISTS cycling.race_starts (
  cyclist_name text PRIMARY KEY,
  rnumbers FROZEN<LIST<int>>
);

  2. Create a SAI index on the rnumbers frozen list column.

    CREATE CUSTOM INDEX IF NOT EXISTS rnumbers_idx ON cycling.race_starts ( FULL(rnumbers) )
USING 'StorageAttachedIndex';

  3. Query the table using the rnumbers column with a CONTAINS phrase to get the rows where a frozen list contains a particular value:

    SELECT * FROM cycling.race_starts WHERE rnumbers = [39, 7, 14];
     cyclist_name   | rnumbers
----------------+-------------
 John DEGENKOLB | [39, 7, 14]

(1 rows)

Analyzer operator (:)

See SAI examples.

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | Privacy policy | Terms of use |  Manage Privacy Choices

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: Contact IBM