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, OR, IN
Strings: =, AND, OR, 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

An understanding of standard operators used in SELECT statements and WHERE clauses.

These examples don’t explain basic query operator syntax, such as range, equality, and inequality queries.
A basic understanding of SAI indexes.

If you’re new to SAI, see the SAI quickstart.

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)

OR: Query for a match on either one column or the other:

SELECT * FROM cycling.comments_vs
  WHERE created_at='2017-03-21 21:11:09.999'
  OR created_at='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:

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>
);

Create a SAI index on the teams column.

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

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:

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)
);

Create a SAI index on the sponsorship list column.

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

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)

A more complex query can select rows that contain either a particular event or a particular month date:

SELECT * FROM cycling.upcoming_calendar
  WHERE events CONTAINS 'Criterium du Dauphine'
    OR month = 7;

 year | month | events
------+-------+----------------------------------------------------------------------------
 2015 |     6 | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']
 2015 |     7 |                                                         ['Tour de France']

(2 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:

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>
);

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';

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>
);

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';

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:

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>
);

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';

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.

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>
);

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';

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.

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>
);

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)

Example: OR

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

SELECT * FROM cycling.birthday_list
  WHERE blist[ 'nation' ] = 'CANADA' OR blist[ 'age' ] = '28';

 cyclist_name     | blist
------------------+--------------------------------------------------------------
    Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
 Laurence BOURQUE |      {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

(2 rows)

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

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>
);

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)

OR

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

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams[2014] = 'Boels:Dolmans Cycling Team'
    OR teams[2015] = '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)

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).

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>>
);

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';

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)

Vector search with SAI (`ORDER BY … ANN OF`)

Create a table with a vector column.

The CQL vector data type is used to store vector data. It supports vectors of 32-bit floating point numbers with between 1 to 65,535 dimensions. This example creates a table with a vector column of 4 dimensions; production use cases typically use vectors with many more dimensions.
```
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    categories SET<TEXT>,
    name TEXT,
    price DECIMAL,
    description VECTOR<FLOAT, 4>
);
```
Create an index on the vector column using the default options:
```
CREATE CUSTOM INDEX products_idx
  ON products (description) USING 'StorageAttachedIndex';
```
Indexing a vector column produces a graph that connects vectors to their related vectors (nearest neighbors). Each vector is represented as a node with edges linking it to nearby vectors in the embedding space. During a search, the index traverses this graph to efficiently locate approximate nearest neighbors. If needed you can use SAI index options to fine tune this graph.

You can also use the SAI index options to set the similarity function that calculates similarity scores. For example:
```
CREATE CUSTOM INDEX comment_sim_function_idx
  ON cycling.comments_vs (comment_vector) USING 'StorageAttachedIndex'
    WITH OPTIONS = { 'similarity_function': 'DOT_PRODUCT'};
```
If not set in WITH OPTIONS, the default similarity function is COSINE.

Insert data.

vector literals are comma-delimited lists of floating-point values enclosed in square brackets ([]). This example inserts four rows with vector data into the products table:

INSERT INTO products (product_id, categories, name, price, description)
  VALUES (uuid(), {'electronics', 'audio'}, 'Wireless Headphones', 79.99,
  [0.12, 0.34, 0.56, 0.78]);

INSERT INTO products (product_id, categories, name, price, description)
  VALUES (uuid(), {'electronics', 'gaming'}, 'Gaming Mouse', 49.99,

  [0.22, 0.18, 0.91, 0.44]);

INSERT INTO products (product_id, categories, name, price, description)
  VALUES (uuid(), {'home', 'kitchen'}, 'chopping board', 89.50,
  [0.05, 0.67, 0.33, 0.21]);

INSERT INTO products (product_id, categories, name, price, description)
  VALUES (uuid(), {'electronics', 'fitness', 'health'}, 'heart monitor', 25.00,
  [0.88, 0.12, 0.45, 0.66]);

Run a vector search on the table using the syntax ORDER BY vector_column ANN OF [query_vector].

Since ANN queries attempt to find similar vectors, you must provide a query vector to compare against the vectors stored in the table. For accuracy, it is important that you generate this query embedding using the same model and dimension as your stored vectors.

By default, the LIMIT is set to 1,000. However, DataStax recommends setting a lower limit to reduce the query run time and to focus on the top most-relevant results. This is because a typical vector search application must transform the results in to a human-readable form or perform additional LLM-based processing steps to derive insights from the matches. Returning excessive results increases the time and complexity of the subsequent steps.
```
SELECT * FROM products
  ORDER BY description ANN OF [0.15, 0.1, 0.1, 0.35]
  LIMIT 3;
```

Vector search with filters

Like other CQL queries, you can filter ANN queries using the WHERE clause. WHERE clause conditions can only be applied to the partition key columns, clustering columns, or indexed columns. See SELECT usage notes for supported operators.

The following examples use the products table from the previous section.

ANN with one WHERE filter

This example applies a numeric range filter to an ANN search.

Create an index on the price column:

CREATE CUSTOM INDEX products_price_idx
  ON products (price) USING 'StorageAttachedIndex';

Add a WHERE clause to the ANN query to filter the results by a range of prices:

SELECT * FROM products WHERE price > 50.00 AND price < 200.00
  ORDER BY description ANN OF [0.11,0.22,0.33,0.44] LIMIT 5;

The results are limited to the products that match the specified price range and are most similar to the query vector.

ANN with multiple WHERE filters

You can add multiple WHERE filters to your query if the columns are indexed or in the primary key. You can use operators like AND or OR to define the relationship between filters.

This example builds on the previous filter by adding a filter on a set<text> collection.

Create an index on the categories column:

CREATE CUSTOM INDEX products_categories_idx
  ON products (categories) USING 'StorageAttachedIndex';

Add a filter on categories to the WHERE clause.

This example uses AND to apply both filters: The results are limited to products that are in the electronics category, priced between $50 and $100, and are most similar to the query vector. You can use other operators, like OR, to create different filter combinations.

SELECT * FROM products
  WHERE categories CONTAINS 'electronics' AND price >= 50.00 AND price <= 100.00
  ORDER BY description ANN OF [0.71,0.22,0.34,0.02] LIMIT 10;

ANN with text analyzer

If you have an analyzed SAI index on a string-based column in the same table, you can use the analyzer operator (:) in the WHERE clause to filter results with term matching. For more information, see Create SAI index and SAI examples.

Vector search combined with term matching produces more focused and contextually relevant results due to the additional semantic filtering. For example, assuming an analyzed index exists on the categories column, you can perform a vector search combined with term matching:

SELECT * FROM products
  WHERE categories : 'electronics'
  ORDER BY description ANN OF [0.15, 0.1, 0.1, 0.35]
  LIMIT 10;

Vector search with returned similarity score

To include the similarity scores in query results, add a similarity function to your SELECT statement. Supported functions are similarity_dot_product(), similarity_cosine(), and similarity_euclidean(). All functions take the vector column and the query vector as arguments. Make sure the function is appropriate for the model that generated your embeddings; otherwise the similarity scores might not be meaningful.

SELECT comment, similarity_dot_product(comment_vector, [0.1, 0.15, 0.3, 0.12, 0.05])
    FROM cycling.comments_vs
    ORDER BY comment_vector ANN OF [0.1, 0.15, 0.3, 0.12, 0.05]
    LIMIT 3;

The output includes the similarity function with the supplied arguments and the similarity scores for each returned row:

 comment                                | system.similarity_dot_product(comment_vector, [0.1, 0.15, 0.3, 0.12, 0.05])
----------------------------------------+----------------------------------------------------------------------------
      Second rest stop was out of water |                                                                    0.949701
              rain, rain,rain, go away! |                                                                    0.789776
 LATE RIDERS SHOULD NOT DELAY THE START |                                                                    0.789776

(3 rows)

Analyzer operator (`:`)

See SAI examples.

Query with SAI

Prerequisites

Single index match on a column (`WHERE`)

Multiple index matching with operators (WHERE, AND)

Set collections

List collections

Map collections (`KEYS`)

Map collections (`VALUES`)

Map collections (`ENTRIES`)

Frozen collections

Vector search with SAI (`ORDER BY … ANN OF`)

Vector search with filters

Vector search with returned similarity score

Analyzer operator (`:`)

Was this helpful?

Give Feedback