CODEWITHSUNDEEP
cassandraquery-optimizationdata-modeling
clk
clk

Reputation: 1

How to select rows filtered by more than one entry in map colum (Cassandra 3.9)

I have a table like this:

CREATE TABLE test.products (
id uuid PRIMARY KEY,
name text,
params map<text, text>);

I've created an entry index on params field like this:

CREATE INDEX products_params_idx ON test.products (entries(params));

Let's say i have 3 products in that table with such params values:

1. {'param1' : 'value1', 'param2' : 'value2'}
2. {'param1' : 'value1', 'param2' : 'othervalue'}
3. {'param3' : 'value3', 'param2' : 'value2'}

Each product can have only one value per one param, but in whole database there can be N params and X values for each of them.

I can do a query like that with no problems:

SELECT * FROM products WHERE params['param1'] = 'value1';

But the main question is how can I do a query like this:

SELECT * FROM products WHERE params['param1'] = 'value1' AND params['param2'] = 'value2';

Now i get an error, which warns me about efficiency:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

I am preety new to Cassandra and NoSQL. I know from Datastax courses, that using ALLOW FILTERING is the last thing i should do in Cassandra. Is there an efficient way to achieve what i want in Cassandra? Maybe I should model this in a whole different way? I will be very glad for some help in this topic.

Upvotes: 0

Views: 335

Answers (1)

Gunwant
Gunwant

Reputation: 979

If you want select data based on product attributes and value, model the primary key based on param and value columns, create additional table for such requirement.

    create table name_by_param{
    param text,
    value text,
    uuid text,
    name text
    primary key ((param,value),uuid)
    }

Example inserts for product - Darthvedar

    insert into name_by_param (param,value,uuid,name) values ('birthname','ani','uuid1','darthvedar');
    insert into name_by_param (param,value,uuid,name) values ('side','darkside','uuid1','darthvedar');
    insert into name_by_param (param,value,uuid,name) values ('age','40','uuid1','darthvedar');

example inserts for product - Obi-wan-kenobi

insert into name_by_param (param,value,uuid,name) values ('birthname','O-ben','uuid2','Obi-wan-kenobi');
insert into name_by_param (param,value,uuid,name) values ('side','force','uuid2','Obi-wan-kenobi');
insert into name_by_param (param,value,uuid,name) values ('age','40','uuid2','Obi-wan-kenobi');

Select queries --

select * from name_by_param where token(param,value) = token('birthname','ani') and token(param,value) =  token('side','force');

Above query should return two rows, I haven't tried it myself, but it shoud work, hope this will help.

Upvotes: 1

Related Questions