Secondary Indexes on LIST Collection in Cassandra

In this article, we will discuss the overview of Secondary Indexes on LIST Collection in Cassandra and then will implement the exercise and will see how it actually works, and then finally will conclude the importance of Secondary Indexes on LIST Collection. Let’s discuss it one by one.

Pre-requisite –

• Concept of indexing in Apache Cassandra
• Collection Data Type in Apache Cassandra

Overview :
Creating secondary Indexes on LIST Collection in Cassandra is very useful. So, here you will see the real use case of creating an index on a collection because it makes searching and querying data very efficient and fast. So if, you want to search based on a keyword then while searching and querying data, you can use the CONTAINS keywords for a specific keyword in the LIST collection data type. Let’s understand the whole concept with the help of examples.

Syntax :
In this, you will see the syntax part of how to create an index on collection. You can use the given below command as follows.

CREATE INDEX ON <table_name>(<collection_column>)

Here, you will see the syntax of the WHERE clause part where you will use the CONTAINS keyword to search the specific value in the LIST collection. 

WHERE <collection_column> CONTAINS <value>

Example :
Let’s consider you have existing keyspace namely cluster1 and then first we will create a user_data table by using CQL command as follows.

Step-1 : Creating table -user_data –

use cluster1;
create table user_data
           (
           user_id varchar,
           user_first_name varchar,
           user_last_name varchar,
           company varchar,
           user_tags list<varchar>,
           primary key(user_id)
           );

Step-2 : Creating Index on user_tags –

CREATE INDEX ON user_data(user_tags);

Step-3 : Inserting data –

insert into user_data(user_id, user_first_name, user_last_name, company, user_tags)
values('Ashish01','Ashish','Rana','abc',['Java','python', 'c++']);

insert into user_data(user_id, user_first_name, user_last_name, company, user_tags)
values('Ashish02','Ayush','NA','abc',['Python','java']);

insert into user_data(user_id, user_first_name, user_last_name, company, user_tags)  
values('Ashish03','Ayushi','NA','abc',['Python','c++','Java']);

Step-4 : Verifying and Reading data –

select * from user_data;

Step-5 : Output –

user_id	company	user_first_name	user_last_name	user_tags
Ashish03	abc	Ayushi	NA	[‘Python’, ‘c++’, ‘Java’]
Ashish02	abc	Ayush	NA	[‘Python’, ‘java’]
Ashish01	abc	Ashish	Rana	[‘Java’, ‘python’, ‘c++’]

Step-6 : Searching on the basis of specific keyword –

select * from user_data where user_tags CONTAINS 'Java';

Step-7 : Output –

user_id	company	user_first_name	user_last_name	user_tags
Ashish03	abc	Ayushi	NA	[‘Python’, ‘c++’, ‘Java’]
Ashish01	abc	Ashish	Rana	[‘Java’, ‘python’, ‘c++’]