[CASSANDRA-8473] Secondary index support for key-value pairs in CQL3 maps

• Type: Improvement
• Status: Resolved
• Priority: Normal
• Resolution: Fixed
• Fix Version/s: 2.2.0 beta 1
• Component/s: Feature/2i Index
• Impacts: Docs
• Assignee: Samuel Klock
• Reporter: Samuel Klock

Authors

Samuel Klock

Reviewers

Tom Hobbs

• Created: 12/Dec/14 17:43
• Updated: 06/Nov/19 22:04
• Resolved: 18/Dec/14 23:32

Description

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):

SELECT * FROM main.users WHERE notify['email'] = true;

Cassandra should add support for this kind of index. One option is to expose a CQL interface like the following:

• Creating an index:

  cqlsh:mykeyspace> CREATE TABLE mytable (key TEXT PRIMARY KEY, value MAP<TEXT, TEXT>);
  cqlsh:mykeyspace> CREATE INDEX ON mytable(ENTRIES(value));
  

• Querying the index:

  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)
  

A patch against the Cassandra-2.1 branch that implements this interface will be attached to this issue shortly.

Issue Links

• is duplicated by: CASSANDRA-8425 Add full entry indexing capability for maps