CODEWITHSUNDEEP
database-designcassandra
Abhishek Chatterjee
Abhishek Chatterjee

Reputation: 2049

Cassandra query by map

I have a table with the following column,

name text, //partition key
tags map<text, text>

I also have a secondary index on "tags" column. Now I want to query like,

select * from <table_name> where tags contains {'a':'b','x':'y'}
  1. Is it possible? If not, can I query with only "contains {'a':'b'}"?
  2. Is this a bad design? If yes, how to rectify this? (note: name has 1->n relation with tags)

Upvotes: 3

Views: 5225

Answers (1)

Horia
Horia

Reputation: 2982

Question 1

For map collections Cassandra allows creation of an index on keys, values or entries (this is available only for maps).

So, first you would create your index on the map:

CREATE INDEX <index_name> ON <table_name> (ENTRIES(<map_column>));

Then you could query:

SELECT * FROM <table_name> WHERE <map_column>['<map_key>'] = '<map_value>';

Another solution would be to froze your collection and create an index on it:

CREATE INDEX <index_name> ON table (FULL(<map_column>));

Then you could query for values with:

SELECT * FROM <table_name> WHERE <map_column> = ['<value>'...];

I think the above solutions are not very good since you could easily scan your whole cluster. Your access type will use and index and not a partition key.

Question 2

Another solution would be to create a table like this:

CREATE TABLE <table_name> ( key TEXT, value TEXT, name TEXT, PRIMARY KEY ((key, value), name));

Key and value columns will hold the values for the tags. They will also be partition key so you could query your data like:

SELECT * FROM <table_name> WHERE key = 'key' AND value = 'value';

You will need to run several queries in order to search for all tags, but you can aggregate the result at the application level.

Upvotes: 9

Related Questions