Secondary index (2i) examples

2i indexing can be used to index and query both primary key columns and non-primary key columns of almost any type. Find the type of index you want to create and follow the examples to create and query the index.

Each example starts with a table creation, followed by index creation, so that you can see the table schema and the index schema. Then sample queries for using the index are presented in a tabbed codeblock, to make it easier to examine queries side-by-side.

A keyspace must be created before tables and indexes can be created.

The following keyspace is used for all examples on this page:

CREATE KEYSPACE IF NOT EXISTS cycling
WITH REPLICATION = {
  'class' : 'SimpleStrategy',
  'replication_factor' : 1
};

Primary key columns

Make an index on primary key columns, either composite partition key columns or clustering columns.

Single primary key column

This index creation will fail, because the table has a single PRIMARY KEY column. Such indexes are not allowed.

Create a table called cycling.alt_stats with a primary key if it doesn’t already exist:

CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats (
  id UUID PRIMARY KEY,
  lastname text,
  birthday date,
  nationality text,
  weight float,
  w_units text,
  height float,
  first_race date,
  last_race date
);

Create a 2i index on the id column:

CREATE INDEX id_idx ON cycling.cyclist_alt_stats (id);

Index creation fails because the id column is the primary key and the only partition key. Such indexes are not allowed.

Result

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot create 
secondary index on the only partition key column id"

Composite partition key columns in primary key

A composite partition key uses two or more columns to define the partition key in the PRIMARY KEY definition. To filter a query using a column that is not the full partition key, you must create a 2i index on the column that you wish to use for the query.

Create a table called cycling.rank_by_year_and_name with a composite partition key and a clustering column if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);

Create two 2i indexes on the race_year and race_name columns. These two columns are the composite partition key in the PRIMARY KEY definition.

CREATE INDEX IF NOT EXISTS race_year_idx
  ON cycling.rank_by_year_and_name (race_year);
CREATE INDEX IF NOT EXISTS race_name_idx
  ON cycling.rank_by_year_and_name (race_name);

Verify the created indexes by checking the table schema with the DESCRIBE INDEX CQL command.
```
DESCRIBE INDEX cycling.race_year_idx;
DESCRIBE INDEX cycling.race_name_idx;
```
Result

Now that you have created the table and indexes, try some SELECT queries:

Example: SELECT 1 - error

This example shows that a query with only one of the partition key columns fails if ALLOW FILTERING is not used.

Query the table using only the race_year`column. A similar query using only the `race_name column can also be performed.

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';

Result

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute 
this query as it might involve data filtering and thus may have unpredictable 
performance. If you want to execute this query despite the performance 
unpredictability, use ALLOW FILTERING"

For 2i indexes, you must use the ALLOW FILTERING directive when querying a table using a column that is not the full partition key.

Example: SELECT 1A - race_year

This example shows that a query with one of the partition key columns will succeed without ALLOW FILTERING.

To fix the error in SELECT 1, query the table using only the race_year`column, but add `ALLOW FILTERING.

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';

Result

Adding ALLOW FILTERING allows the query to succeed. However, this query is dangerous, because it requires a full table scan. SAI indexing is a better option that avoids this problem.

Example: SELECT 2 - AND

This example shows that a query with both of the partition key columns using AND will succeed without ALLOW FILTERING.

You might want to query the table using both the race_year and race_name columns. This query filters the results using both columns, so ALLOW FILTERING is not required.

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';

Result

Clustering column in primary key

A clustering column sorts the rows within a partition. Clustering columns are part of the PRIMARY KEY definition. To filter a query using just a clustering column, you must create a 2i index on the column that you wish to use for the query.

If you wish to use a clustering column in a query, it is best to consider restricting a query to a single partition. Using a partition key column plus a clustering column will have better performance.

Create a table called cycling.rank_by_year_and_name with a composite partition key and a clustering column if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);

Create a 2i index on the rank clustering column.

CREATE INDEX IF NOT EXISTS rank_idx
  ON cycling.rank_by_year_and_name (rank);

Verify the created indexes by checking the schema with the DESCRIBE INDEX CQL command.
```
DESCRIBE INDEX cycling.rank_idx;
```
Result

Now that you have created the table and index, try some queries:

Example: SELECT 1 - rank

This example shows a query with only the clustering column.

Query the table using only the rank column:

SELECT * FROM cycling.rank_by_year_and_name WHERE rank = 3;

Result

Example: SELECT 2 - error

This example shows that a query with the clustering column and the partition key column fails without ALLOW FILTERING.

Query the table using the race_year and rank columns:

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 AND rank = 3;

Result

InvalidRequest: Error from server: code=2200 [Invalid query] 
message="Cannot execute this query as it might involve data filtering and thus 
may have unpredictable performance. If you want to execute this query despite 
the performance unpredictability, use ALLOW FILTERING"

Adding ALLOW FILTERING allows the query to succeed. However, this query is dangerous, because it requires a full table scan. SAI indexing is a better option that avoids this problem.

Example: SELECT 2A - AND

This example shows a query with the clustering column and the partition key column succeeding with ALLOW FILTERING.

Fix the error for the query using the race_year and rank columns by adding ALLOW FILTERING:

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 AND rank = 3
ALLOW FILTERING;

Result

Non-primary key columns

Indexes can be created on non-primary key columns of almost any type.

Text column

Create a table called cycling.alt_stats with TEXT column called nationality if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats (
  id UUID PRIMARY KEY,
  lastname text,
  birthday date,
  nationality text,
  weight float,
  w_units text,
  height float,
  first_race date,
  last_race date
);

Create a 2i index on the nationality column, a non-primary key TEXT column.
```
CREATE INDEX nationality_idx ON cycling.cyclist_alt_stats (nationality);
```

Verify the created indexes by checking the index schema:

CREATE INDEX nationality_idx ON cycling.cyclist_alt_stats (nationality);

Result

CREATE INDEX nationality_idx ON cycling.cyclist_alt_stats (nationality);

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

Query the table using the nationality column.

SELECT first_race, last_race, birthday FROM cycling.cyclist_alt_stats
  WHERE nationality = 'France';

Result

 first_race | last_race  | birthday
------------+------------+------------
 2006-03-15 | 2017-04-16 | 1990-05-27

(1 rows)

Date column

Create a table called cycling.alt_stats with DATE column called birthday if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats (
  id UUID PRIMARY KEY,
  lastname text,
  birthday date,
  nationality text,
  weight float,
  w_units text,
  height float,
  first_race date,
  last_race date
);

Create a 2i index on the birthday column, a non-primary key DATE column.
```
CREATE INDEX birthday_idx ON cycling.cyclist_alt_stats (birthday);
```

Verify the created indexes by checking the index schema:

CREATE INDEX birthday_idx ON cycling.cyclist_alt_stats (birthday);

Result

CREATE INDEX birthday_idx ON cycling.cyclist_alt_stats (birthday);

Query the table using the birthday column:

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday = '1981-03-29';

Result

 lastname  | birthday   | nationality
-----------+------------+-------------
 VEIKKANEN | 1981-03-29 |     Finland

(1 rows)

Now that you have created indexes on the columns birthday and nationality, try these additional queries:

Example: SELECT 1 - AND

This example shows that a query with both the birthday and nationality columns will fail if ALLOW FILTERING is not used.

Query the table using both a nationality and a birthday:

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday = '1991-08-25' AND nationality = 'Ethiopia'
ALLOW FILTERING;

The query fails:

Result

 lastname | birthday   | nationality
----------+------------+-------------
    GRMAY | 1991-08-25 |    Ethiopia

(1 rows)

The indexes have been created on appropriate low-cardinality columns, but querying with ALLOW FILTERING still fails.

The answer lies with the partition key, which has not been defined. When you attempt a potentially expensive query, such as searching a range of rows, the database requires the ALLOW FILTERING directive. The error is not due to multiple indexes, but the lack of a partition key definition in the query.

Adding ALLOW FILTERING allows the query to succeed. However, this query is dangerous, because it requires a full table scan. SAI indexing is a better option that avoids this problem.

Example: SELECT 2 - OR

This example shows that a query with either the birthday or nationality columns will not succeed because it is not supported.

Query the table using a nationality or a birthday:

  SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
    WHERE birthday = '1991-08-25' OR nationality = 'Ethiopia'
  ALLOW FILTERING;

OR is not an allowed operator for 2i indexes.

Example: SELECT 3 - IN

This example shows how to query with a list of nationality values.

Query the table using nationality with IN:

SELECT * FROM cycling.cyclist_alt_stats
  WHERE nationality IN ('Finland', 'France')
ALLOW FILTERING;

Result

 id                                   | birthday   | first_race | height | last_race  | lastname  | nationality | w_units | weight
--------------------------------------+------------+------------+--------+------------+-----------+-------------+---------+--------
 c09e9451-50da-483d-8108-e6bea2e827b3 | 1981-03-29 | 1996-05-21 |   1.78 | 2012-10-02 | VEIKKANEN |     Finland |      kg |     66
 4ceb495c-55ab-4f71-83b9-81117252bb13 | 1990-05-27 | 2006-03-15 |   null | 2017-04-16 |     DUVAL |      France |    null |   null

(2 rows)

Example: SELECT 4 - RANGE

This example shows how to query with an inequality range of birthday values.

Query the table using a birthday range:

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday >'1981-03-29'
ALLOW FILTERING;

Inequalities are allowed for 2i indexes, but only with ALLOW FILTERING. Again, the difficulties of using 2is is illustrated. SAI is almost always a better option.

Result

 lastname  | birthday   | nationality
-----------+------------+-------------
     BRUTT | 1982-01-29 |      Russia
  ISAYCHEV | 1986-04-21 |      Russia
 TSATEVICH | 1989-07-05 |      Russia
  PAULINHO | 1990-05-27 |    Portugal
     GRMAY | 1991-08-25 |    Ethiopia
    BELKOV | 1985-01-09 |      Russia
     DUVAL | 1990-05-27 |      France

(7 rows)

Integer column

An integer column can be indexed similarly to a text column.

Create a table called cycling.rank_by_year_and_name with INT column called rank if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);

Create a 2i index on the rank column, a non-primary key INT column.

CREATE INDEX IF NOT EXISTS rank_idx
  ON cycling.rank_by_year_and_name (rank);

Verify the created indexes by checking the index schema.
```
DESCRIBE INDEX cycling.rank_idx;
```
Result

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

Example: SELECT 1 - equality

Query the table using the rank column in an equality:

SELECT * FROM cycling.rank_by_year_and_name WHERE rank = 3;

Result

Example: SELECT 2 - inequality range

Query the table using the rank column in an inequality range.

SELECT * FROM cycling.rank_by_year_and_name WHERE rank < 3;

When using a 2i index, this will fail without ALLOW FILTERING.

Result

Float column

Float columns can be indexed and searched.

Create a table called cycling.cyclist_alt_stats with FLOAT column called weight if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats (
  id UUID PRIMARY KEY,
  lastname text,
  birthday date,
  nationality text,
  weight float,
  w_units text,
  height float,
  first_race date,
  last_race date
);

Create a 2i index on the weight column, a non-primary key FLOAT column.
```
CREATE INDEX weight_idx ON cycling.cyclist_alt_stats (weight);
```

Verify the created indexes by checking the table schema.

Result

CREATE INDEX weight_idx ON cycling.cyclist_alt_stats (weight);

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

Example: SELECT 1 - equality

This example shows that a query with the weight column can be performed using an equality.

Query the table using the weight column in an equality:

SELECT lastname, nationality, weight FROM cycling.cyclist_alt_stats
 WHERE weight = 70 ALLOW FILTERING;

Result

 lastname | nationality | weight
----------+-------------+--------

(0 rows)

Example: SELECT 2 - inequality range

This example shows that a query with the weight column can be performed using an inequality range.

Query the table using the weight column in an inequality range:

SELECT lastname, nationality, weight FROM cycling.cyclist_alt_stats
 WHERE weight > 70 ALLOW FILTERING;

Result

 lastname | nationality | weight
----------+-------------+--------
 ISAYCHEV |      Russia |     80
   BELKOV |      Russia |     71

(2 rows)

Timestamp column

Timestamp columns can be indexed and searched.

Create a table called cycling.comments with TIMESTAMP column called created_at if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.comments (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);

Create a 2i index on the comments column, a non-primary key TIMESTAMP column.

CREATE INDEX IF NOT EXISTS created_at_idx ON cycling.comments (created_at);

Verify the created indexes by checking the index schema.

DESCRIBE INDEX cycling.created_at_idx;

Result

CREATE INDEX created_at_idx ON cycling.comments (created_at);

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

Example: SELECT 1 - equality

This example shows that a query with the created_at column can be performed using an equality.

Query the table using the created_at column in an equality:

SELECT commenter, comment, created_at FROM cycling.comments
  WHERE created_at = '2024-06-07 03:13:40.268';

Result

 commenter | comment              | created_at
-----------+----------------------+---------------------------------
       Amy | Ride in any weather! | 2024-06-07 03:13:40.268000+0000

(1 rows)

SELECT 2 - inequality range

This example shows that a query with the created_at column can be performed using an inequality range.

Query the table using the created_at column in an inequality range:

SELECT commenter, comment, created_at FROM cycling.comments
  WHERE created_at < '2024-06-07 03:13:40.268'

Result

Intenational failure to show that 2i does not support range queries

For 2i, the ALLOW FILTERING directive is required for inequality queries or the query will fail.

Tuple column

Tuples columns can be indexed and searched, but only the full tuple can be indexed.

Create a table called cycling.nation_rank with TUPLE column called info if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.nation_rank (
  nation text PRIMARY KEY,
  info tuple<int, text, int>
);

Create a 2i index on the info column, a non-primary key TUPLE column.
```
CREATE INDEX info_idx ON cycling.nation_rank (info);
```

Verify the created indexes by checking the table schema.

DESCRIBE INDEX cycling.info_idx;

Result

CREATE TABLE cycling.nation_rank (
    nation text PRIMARY KEY,
    info frozen<tuple<int, text, int>>
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';
CREATE INDEX info_idx ON cycling.nation_rank (info);

Query the table using the info column in an equality.

Tuples can only be filtered using the full tuple value.

SELECT * FROM cycling.nation_rank
  WHERE info = (3, 'Phillippe GILBERT', 6222);

Result

 nation  | info
---------+--------------------------------
 Belgium | (3, 'Phillippe GILBERT', 6222)

(1 rows)

Non-primary key collection column

Collections can be indexed and queried to find a collection containing a particular value. Sets and lists are indexed a bit differently from maps, given the key-value nature of maps.

Sets and lists can index all values found by indexing the collection column.

Maps can index a map key, map value, or map entry using the methods shown below. Multiple indexes can be created on the same map column in a table so that map keys, values, or entries can be queried.

In addition, frozen collections can be indexed using FULL to index the full content of a frozen collection.

All the potential problems with 2i apply to 2i indexes on collections.

Set

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 2i index on the teams column.

CREATE INDEX IF NOT EXISTS teams_idx ON cycling.cyclist_career_teams (teams);

Verify the created indexes by checking the index schema.
```
DESCRIBE INDEX cycling.teams_idx;
```
Result
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';
```
Result

List

Create an index on a set 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 2i index on the sponsorship list column.

CREATE INDEX sponsorship_idx ON cycling.race_sponsors (sponsorship);

Verify the created index by checking the index schema.
```
DESCRIBE INDEX cycling.sponsorship_idx;
```
Result
Query the table using the sponsorship column with a CONTAINS phrase to get the rows where a set contains a particular value:
```
SELECT * FROM cycling.race_sponsors WHERE sponsorship CONTAINS 'Carrefour';
```
Result

Map - 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 2i index on the teams map column.

CREATE INDEX IF NOT EXISTS team_keys_idx
  ON cycling.cyclist_teams ( KEYS (teams) );

Verify the created 2i index by checking the index schema.

DESCRIBE INDEX cycling.team_keys_idx;

Result

CREATE INDEX team_keys_idx ON cycling.cyclist_teams (keys(teams));

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;

Result

 firstname | lastname | teams
-----------+----------+------------------------------------------------------------------------------------
  Marianne |      VOS | {2014: 'Rabobank-Liv Woman Cycling Team', 2015: 'Rabobank-Liv Woman Cycling Team'}

(1 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 INDEX IF NOT EXISTS blist_keys_idx
  ON cycling.birthday_list ( KEYS(blist) );

Verify the created index by checking the index schema.

DESCRIBE INDEX cycling.blist_keys_idx;

+ .Result

CREATE INDEX blist_keys_idx ON cycling.birthday_list (keys(blist));

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

Result

 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 - 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 who have 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 2i index on the teams map column.

CREATE INDEX IF NOT EXISTS team_values_idx
  ON cycling.cyclist_teams ( VALUES (teams) );

Verify the created index by checking the index schema.

DESCRIBE INDEX cycling.team_values_idx;

Result

CREATE INDEX team_values_idx ON cycling.cyclist_teams (values(teams));

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';
```
Result
```
 firstname | lastname | teams
-----------+----------+-------

(0 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 2i index on the blist map column.

CREATE INDEX IF NOT EXISTS blist_values_idx
  ON cycling.birthday_list ( VALUES(blist) );

Verify the created index by checking the table schema.

DESCRIBE INDEX cycling.blist_values_idx;

Result

CREATE INDEX blist_values_idx ON cycling.birthday_list (values(blist));

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

Result

 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 - 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 2i index on the blist map column.

CREATE INDEX IF NOT EXISTS team_entries_idx
  ON cycling.cyclist_teams ( ENTRIES (teams) );

Verify the created index by checking the index schema.

DESCRIBE INDEX cycling.team_entries_idx;

Result

CREATE INDEX team_entries_idx ON cycling.cyclist_teams (entries(teams));

Now that you have created the table and index, query using an entry for the map column:

Example: SELECT 1

Query the table using the age column with a CONTAINS ENTRY phrase to get the rows where a map contains a particular entry:

SELECT * FROM cycling.birthday_list WHERE blist[ 'age' ] = '23';

Result

 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

Using the same index, find cyclists from the same country:

SELECT * FROM cycling.birthday_list WHERE blist[ 'nation' ] = 'NETHERLANDS';

Result

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

Result

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

Result

 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:

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 2i index on the teams map column.

CREATE INDEX IF NOT EXISTS team_entries_idx
  ON cycling.cyclist_teams ( ENTRIES (teams) );

Verify the created index by checking the index schema.

DESCRIBE INDEX cycling.team_entries_idx;

Result

CREATE INDEX team_entries_idx ON cycling.cyclist_teams (entries(teams));

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

Result

 firstname | lastname | teams
-----------+----------+-------

(0 rows)

Example: AND

Two 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'
ALLOW FILTERING;

Result

 firstname | lastname | teams
-----------+----------+-------

(0 rows)

Frozen collection

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 2i index on the rnumbers frozen list column.

CREATE INDEX IF NOT EXISTS rnumbers_idx ON cycling.race_starts ( FULL(rnumbers) );

Verify the created index by checking the index schema.

DESCRIBE INDEX cycling.rnumbers_idx;

Result

CREATE INDEX rnumbers_idx ON cycling.race_starts (full(rnumbers));

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

Result

 cyclist_name   | rnumbers
----------------+-------------
 John DEGENKOLB | [39, 7, 14]

(1 rows)

Secondary index (2i) examples

Primary key columns

Single primary key column

Composite partition key columns in primary key

Clustering column in primary key

Non-primary key columns

Text column

Date column

Integer column

Float column

Timestamp column

Tuple column

Non-primary key collection column

Set

List

Map - keys

Map - values

Map - entries

Frozen collection

Was this helpful?

Give Feedback