Is it possible to use cql to query collections in a string?

Question

Assuming you have movies as a string, and a list of categories, i.e.

create table movie( uuid timeuuid, name text, category list<text> primary key (uuid) ); insert into movie (uuid,name,category) values(now(), 'my movie', ['romance','comedy']); insert into movie (uuid,name,category) values(now(), 'other movie', ['action','comedy']); 

Is it possible to effectively do something like:

 select * from movie where category='comedy' select * from movie where category='comedy' and category='action' 

This usage example is the most common query to our MySQL database.

Answer 1

Starting with cassandra 2.1 , yes it is:

 create table cinema.movie( id timeuuid, name text, category list<text>, primary key (id) ); CREATE index ON cinema.movie (category); INSERT INTO cinema.movie (id, name, category) VALUES (now(), 'my movie', ['romance','comedy']); SELECT * FROM cinema.movie WHERE category CONTAINS 'romance'; uuid | category | name --------------------------------------+------------------------+---------- b9e0d7f0-995f-11e3-b11c-c5d4ddc8930a | ['romance', 'comed`y'] | my movie (1 rows) 

PS you have errors in your queries, you did not specify in the create table statement after the declaration of the category, and you cannot use the now() function in the UUID, instead you are looking for the TIMEUUID type.

Remember that performance will not be great (as it is for quantification) when it comes to using these collections, it might be better to create a separate table for these kinds of things.