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)
