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

Question

Assuming you have movies as a row, 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 efficiently do something like:

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

This use case is the most common query against our MySQL database.

Answer 1

As of 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)

P.S. you have errors in your queries, you are missing a , in your create table statement after category's declaration and you cant use the now() function on UUID, you are looking for the TIMEUUID type instead.

Bare in mind that performance wont be magnificent (how's that for quantitative) when it come's to using these collections, it might be better to create a separate table for this sort of thing.