CODEWITHSUNDEEP
cassandracqlcql3
e90jimmy
e90jimmy

Reputation: 272

SELECT Specific Value from map

I am trying to create a WIDE Column Table, 20,000+ columns

Initially I was thinking I would use:

CREATE TABLE details (
   key TEXT,
   detail map<TEXT, TEXT>
   PRIMARY KEY (KEY)
  );

Inserting into this table works fine

UPDATE details SET detail = detail + { 'col1': '12'} where key='123' ;
UPDATE details SET detail = detail + { 'col20000': 'ABCD'} where key='123' ;

However, I would like to read an individual detail:

   select detail[col1] where key='123'

when executing this query I get the following error:

 no viable alternative at input '['

Will this work, or do I need a different approach?

Upvotes: 16

Views: 20679

Answers (3)

Liangmp
Liangmp

Reputation: 91

You can use user-define type instead of map type. Try to define table in this way:

CREATE TYPE detailtype (
    col1 TEXT,
    col2 TEXT
);

CREATE TABLE details (
   key TEXT,
   detail frozen<detailtype>,
   PRIMARY KEY (KEY)
);

Then you can query by this way:

select detail.col1 where key='123';

Upvotes: 1

jbellis
jbellis

Reputation: 19377

Collections are small groups of data that you fetch all at once.

If you want to access tuples at a finer level, and still be able to ask "what are all the pairs of data for a given key," you should use a table like this:

CREATE TABLE details (
  key TEXT,
  detail_key text,
  detail_value text,
  PRIMARY KEY (key, detail_key)
);

This will allow SELECT * FROM details WHERE key = ? as well as SELECT * FROM detail WHERE key = ? AND detail_key = ?.

Upvotes: 15

abhi
abhi

Reputation: 4792

Basically this functionality is not yet supported by cassandra.

See this cql3 collections

Upvotes: 7

Related Questions