Samuel Klock created CASSANDRA-8473: ---------------------------------------
Summary: Secondary index support for key-value pairs in CQL3 maps Key: CASSANDRA-8473 URL: https://issues.apache.org/jira/browse/CASSANDRA-8473 Project: Cassandra Issue Type: Improvement Reporter: Samuel Klock CASSANDRA-4511 and CASSANDRA-6383 made substantial progress on secondary indexes on CQL3 maps, but support for a natural use case is still missing: queries to find rows with map columns containing some key-value pair. For example (from a comment on CASSANDRA-4511): {code:sql} SELECT * FROM main.users WHERE notify['email'] = true; {code} Cassandra should add support for this kind of index. One option is to expose a CQL interface like the following: * Creating an index: {code:sql} cqlsh:mykeyspace> CREATE TABLE mytable (key TEXT PRIMARY KEY, value MAP<TEXT, TEXT>); cqlsh:mykeyspace> CREATE INDEX ON mytable(ENTRIES(value)); {code} * Querying the index: {code:sql} cqlsh:mykeyspace> INSERT INTO mytable (key, value) VALUES ('foo', {'a': '1', 'b': '2', 'c': '3'}); cqlsh:mykeyspace> INSERT INTO mytable (key, value) VALUES ('bar', {'a': '1', 'b': '4'}); cqlsh:mykeyspace> INSERT INTO mytable (key, value) VALUES ('baz', {'b': '4', 'c': '3'}); cqlsh:mykeyspace> SELECT * FROM mytable WHERE value['a'] = '1'; key | value -----+-------------------------------- bar | {'a': '1', 'b': '4'} foo | {'a': '1', 'b': '2', 'c': '3'} (2 rows) cqlsh:mykeyspace> SELECT * FROM mytable WHERE value['a'] = '1' AND value['b'] = '2' ALLOW FILTERING; key | value -----+-------------------------------- foo | {'a': '1', 'b': '2', 'c': '3'} (1 rows) cqlsh:mykeyspace> SELECT * FROM mytable WHERE value['b'] = '2' ALLOW FILTERING; key | value -----+-------------------------------- foo | {'a': '1', 'b': '2', 'c': '3'} (1 rows) cqlsh:mykeyspace> SELECT * FROM mytable WHERE value['b'] = '4'; key | value -----+---------------------- bar | {'a': '1', 'b': '4'} baz | {'b': '4', 'c': '3'} (2 rows) {code} A patch against the Cassandra-2.1 branch that implements this interface will be attached to this issue shortly. -- This message was sent by Atlassian JIRA (v6.3.4#6332)