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

cassandra, cql

Solution

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.

Problem

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.

Original source