how Cql's Collection contains alternative value?

I have a question to query to cassandra collection.

I want to make a query that work with collection search.

CREATE TABLE rd_db.test1 (
    testcol3 frozen<set<text>> PRIMARY KEY,
    testcol1 text,
    testcol2 int
)

table structure is this...

and

this is the table contents.

in this situation, I want to make a cql query has alternative option values on set column.

if it is sql and testcol3 isn't collection,

 select * from rd.db.test1 where testcol3 = 4 or testcol3 = 5

but it is cql and collection.. I try

 select * from test1 where testcol3 contains '4' OR testcol3 contains '5'  ALLOW FILTERING ;

 select * from test1 where testcol3 IN ('4','5') ALLOW FILTERING ;

but this two query didn't work...

please help...

Solution

This won't work for you for multiple reasons:

there is no OR operation in CQL
you can do only full match on the value of partition key (testcol3)
although you may create secondary indexes for fields with collection type, it's impossible to create an index for values of partition key

You need to change data model, but you need to know the queries that you're executing in advance. From brief looking into your data model, I would suggest to rollout the set field into multiple rows, with individual fields corresponding individual partitions.

But I want to suggest to take DS201 & DS220 courses on DataStax Academy site for better understanding how Cassandra works, and how to model data for it.