cassandra 2.0.7 cql SELECT Secific Value from map

Question

ALTER TABLE users ADD todo map;

UPDATE users SET todo = { '1':'1111', '2':'2222', '3':'3' ,.... } WHERE user_id = 'frodo';

now ,i want to run the follow cql ,but failed ,is here any other method ?

SELECT user_id, todo['1'] FROM users WHERE user_id = 'frodo';

ps:

the length my map can change. for example : { '1':'1111', '2':'2222', '3':'3' } or { '1':'1111', '2':'2222', '3':'3', '4':'4444'} or { '1':'1111', '2':'2222', '3':'3', '4':'4444' ...}

Answer 1

If you want to use a map collection, you'll have the limitation that you can only select the collection as a whole (docs).

I think you could use the suggestion from the referenced question, even if the length of your map changes. If you store those key/value pairs for each user_id in separate fields, and make your primary key based on user_id and todo_k, you'll have access to them in the select query.

For example:

CREATE TABLE users (
  user_id text,
  todo_k text,
  todo_v text,
  PRIMARY KEY (user_id, todo_k)
);

-----------------------------
| user_id | todo_k | todo_v |
-----------------------------
| frodo   | 1      | 1111   |
| frodo   | 2      | 2222   |
| sam     | 1      | 11     |
| sam     | 2      | 22     |
| sam     | 3      | 33     |
-----------------------------

Then you can do queries like:

select user_id,todo_k,todo_v from users where user_id = 'frodo';
select user_id,todo_k,todo_v from users where user_id = 'sam' and todo_k = 2;