database - Better / Other way of selecting data in cassandra -

imagine example table of products

id | price | properties                                                    | description ---------------------------------------------------------------------------|--------------- 1  | 22.9  | color=red, weigth=10, width=100                               | mountainbike 2  | 56.3  | shape=rectangle, weight=12, opaque=true                       | small toolbox 3  | 67    | shape=rectangle, weight=15, opaque=false, height=9, width=120 | big toolbox 

the column "properties" cassandra collection type "map"

okay, first: why not using properties own columns? cause not specified properties items have, part ist dynamic.

what want know is, there performant way of selecting specified item properties? like

select price products properties.color = red , properties.weigth=10 , properties.width=100 

i want match 1 product grants properties requested.

so following situation shouldnt possible

id | price | properties                                                    | description ---------------------------------------------------------------------------|--------------- 1  | 22.9  | color=red, weigth=10, width=100                               | mountainbike 9  | 56.3  | color=red, weigth=10, width=100                               | mountainbike 

so properties column rather primary key, if looks strange no other product can have same properties.

how that?

i found description collections (in case map) in newer versions of cassandra can secondary indexed, possible use where-clause on in. not recommended that, because dont scale out , dont know how many properties each product have. fact is, if there product has requested properties should one.

has idea make possible?

if makes sense performance-wise use index (see when use , not use index), consider using user defined type. in 2.1.2, use frozen keyword. cannot update parts of user-defined type value. entire value must overwritten. cassandra treats value of frozen, user-defined type blob.

      create keyspace abc_ks replication = { 'class' : 'networktopologystrategy', 'datacenter1' : 1 };     create type abc_ks.props (       color text,       weight text,       width text,       prop4 text,       prop5 text,       prop6 text,       prop7 text     );      create table abc_ks.prod_line (       id int,       price double,       description text,       properties frozen<props>,       primary key (description, id)      );       insert abc_ks.prod_line (id, price, description, properties) values (1, 22.9, 'mountainbike', {color:'red', weight:'10', width:'100'});      insert abc_ks.prod_line (id, price, description, properties) values (2, 122.5, 'mountainbike', {color:'blue', weight:'12', width:'90', prop4:'speed_12'});      insert abc_ks.prod_line (id, price, description, properties) values (3, 90.5, 'small tool', {prop5:'shape_rectangle', weight:'15'});      insert abc_ks.prod_line (id, price, description, properties) values (4, 100.6, 'mountainbike', {color:'blue', weight:'15', width:'90', prop4:'speed_12'});      insert abc_ks.prod_line (id, price, description, properties) values (5, 106.5, 'mountainbike', {color:'blue', weight:'15', width:'90', prop4:'speed_12'});      create index on abc_ks.prod_line (properties);      select description, id abc_ks.prod_line properties = {color: 'blue', weight:'15', width:'90', prop4:'speed_12'};       description  | id     --------------+----      mountainbike |  4      mountainbike |  5