Cassandra CQL: How to do WHERE-like queries on dynamic columns?

Question

Consider the following simple table in Cassandra, with some values inserted via CQL3:

CREATE TABLE sometable (  
  id varchar,  
  name varchar,  
  value varchar,  
  PRIMARY KEY (id, name));

INSERT INTO sometable (id, name, value) VALUES ('Mary', 'Favorite Color', 'Red');
INSERT INTO sometable (id, name, value) VALUES ('George', 'Favorite Color', 'Green');
INSERT INTO sometable (id, name, value) VALUES ('Bob', 'Favorite Color', 'Red');
INSERT INTO sometable (id, name, value) VALUES ('Mary', 'Phone', '555-1234');
INSERT INTO sometable (id, name, value) VALUES ('George', 'Phone', '555-1212');
INSERT INTO sometable (id, name, value) VALUES ('Bob', 'Email', '[email protected]');

Let's say we want to get the phone numbers of everyone whose favorite color is red. For example, something like:

SELECT 'Phone' FROM sometable WHERE 'Favorite Color' = 'Red';

The only way I can think of actually doing this would be to select the names of all columns of interest, and then do the filtering client-side:

SELECT name, value from sometable WHERE name IN ('Favorite Color', 'Phone') allow filtering;

Is there a way to filter on dynamic column values in CQL, or in any other Cassandra API?

Answer 1

Secondary indexes would allow you to search on different fields that the one which are in the partition key / clustering key. You can find some useful documentation there:

http://www.datastax.com/docs/1.1/ddl/indexes

Be sure to read the When to Use Secondary Indexes / When Not to Use Secondary Indexes clauses. Taking the time to think how you'll query your column families might be the more important thing to do when you're using Cassandra, this, and being sure that you need Cassandra.