CODEWITHSUNDEEP
cassandracqlcql3
Poppy
Poppy

Reputation: 3092

Cassandra: How to check if a column value is already present

I am using Cassandra. There is a column name-text which stores all say usernames.

name
------
bob
Bob
bobby
mike
michael
micky
BOB

I have 2 questions

  1. I have to select all user names that starts with 'bo'. I know there is no like equivalent in Cassandra. But is there anyway to achieve that? (Additional column is an option but is there something else?)
  2. There are 3 entries. bob,Bob and BOB. Is there anyway to use fetch all 3 rows if I pass where name='bob'. I need to fetch the names case-insensitive.

Thanks in advance.

Upvotes: 1

Views: 2145

Answers (1)

Daniel S.
Daniel S.

Reputation: 3514

Let's start with the second question first. If you want to support case-insensitive queries, you should store a second, upper-case copy of the text data you want to search for in another column. Then by querying by that column you'll be able to do case-insensitive requests.

Going back to searches for bo*. The best way to do that is to use a schema that allows you to leverage clustering columns (columns 2 and higher of the primary key) for range searches. Here is an example: 

CREATE TABLE t1 (region INT, name TEXT, PRIMARY KEY (region, name));

In particular, if you make name the second column of the key, you will be able to perform searches such as 

SELECT * FROM t1 WHERE name >= 'bo' and name < 'bp' ALLOW FILTERING;

which will return results you're looking for. This only works for trailing wildcards: the leading characters have to be constant for range queries to work. Again, if you want to do case-insensitive searches, have the case-insensitive column be the second part of the primary key and query by it: 

SELECT * FROM t1 WHERE name_upper >= 'BO' and name_upper < 'BP' ALLOW FILTERING;

Upvotes: 2

Related Questions