birthday_list

Example for maps and indexing search index map fields.

Example for maps and indexing search index map fields.

Create the keyspace, table, and load the data:
CREATE KEYSPACE IF NOT EXISTS cycling WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

// START-drop
DROP TABLE IF EXISTS cycling.birthday_list;
//END-drop

/* Map field and search index map fields example */

// START-table
CREATE TABLE cycling.birthday_list (
   cyclist_name text PRIMARY KEY, 
   blist_ map<text,text>);
// END-table

// START-insertall
INSERT INTO cycling.birthday_list (cyclist_name, blist_) VALUES ('Allan DAVIS', {'blist_age':'35', 'bday':'27/07/1980', 'blist_nation':'AUSTRALIA'});
INSERT INTO cycling.birthday_list (cyclist_name, blist_) VALUES ('Claudio VANDELLI', {'blist_age':'54', 'bday':'27/07/1961', 'blist_nation':'ITALY'});
INSERT INTO cycling.birthday_list (cyclist_name, blist_) VALUES ('Laurence BOURQUE', {'blist_age':'23', 'bday':'27/07/1992', 'nation':'CANADA'});
INSERT INTO cycling.birthday_list (cyclist_name, blist_) VALUES ('Claudio HEINEN', {'blist_age':'23', 'bday':'27/07/1992', 'blist_nation':'GERMANY'});
INSERT INTO cycling.birthday_list (cyclist_name, blist_) VALUES ('Luc HAGENAARS', {'blist_age':'28', 'bday':'27/07/1987', 'blist_nation':'NETHERLANDS'});
INSERT INTO cycling.birthday_list (cyclist_name, blist_) VALUES ('Toine POELS', {'blist_age':'52', 'bday':'27/07/1963', 'blist_nation':'NETHERLANDS'});
// END-insertall
Create a default search index:
CREATE KEYSPACE IF NOT EXISTS cycling WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

SOURCE "birthday_list-table.cql";

// For different key-value pairs that you want specify a data types
// START-index-control-types
CREATE SEARCH INDEX ON cycling.birthday_list;
// END-index

// Return all fields that were indexed on a key
SELECT * FROM cycling.birthday_list WHERE solr_query = 'blist_age:*';
SELECT * FROM cycling.birthday_list WHERE solr_query = 'bday:*';

// Regular queries -- how do you query indexed maps?
SELECT * FROM cycling.birthday_list WHERE blist_['blist_age'] = '23';
SELECT * FROM cycling.birthday_list WHERE blist_['blist_nation'] = 'GERMANY';
SELECT * FROM cycling.birthday_list WHERE blist_['bday'] = '27/07/1992' ALLOW FILTERING;

// using search index
SELECT * FROM cycling.birthday_list WHERE solr_query = 'blist_age:23';
SELECT * FROM cycling.birthday_list WHERE solr_query = 'blist_nation:GERMANY';

Create a custom index:
SOURCE "birthday_list-table.cql";

// START-index
CREATE SEARCH INDEX ON cycling.birthday_list WITH COLUMNS blist_ {excluded:true};
// END-index

// Add types
ALTER SEARCH INDEX SCHEMA ON cycling.birthday_list ADD types.fieldType[@class='org.apache.solr.schema.TrieIntField', @name='TrieIntField'];

// Control the data types of map fields by name
ALTER SEARCH INDEX SCHEMA ON cycling.birthday_list ADD fields.field[@indexed='true', @multiValued='false', @name='blist_age', @type='TrieIntField'];
ALTER SEARCH INDEX SCHEMA ON cycling.birthday_list ADD fields.field[@name='blist_nation', @indexed='true', @multiValued='false', @type='StrField'];

// Make the pending schema active and rebuild the index
RELOAD SEARCH INDEX ON cycling.birthday_list;
REBUILD SEARCH INDEX ON cycling.birthday_list;

// Regular queries -- how do you query indexed maps?
SELECT * FROM cycling.birthday_list WHERE blist_['blist_age'] = '23';
SELECT * FROM cycling.birthday_list WHERE blist_['blist_nation'] = 'GERMANY';

// using search index
SELECT * FROM cycling.birthday_list WHERE solr_query = 'blist_age:23';
SELECT * FROM cycling.birthday_list WHERE solr_query = 'blist_nation:GERMANY';
Create indexes on map collections to query.
CREATE KEYSPACE IF NOT EXISTS cycling WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

// START-drop
DROP TABLE IF EXISTS cycling.birthday_list;
//END-drop

/* Map entries and regular index map fields example */

// START-blisttable
CREATE TABLE cycling.birthday_list (
 cyclist_name text PRIMARY KEY, 
 blist map<text,text>
);
// END-blisttable

//Create index on map ENTRIES
// START-entriesidx
CREATE INDEX blist_idx 
ON cycling.birthday_list ( ENTRIES(blist) );
// END-entriesidx

// START-insertentries
INSERT INTO cycling.birthday_list (
  cyclist_name, blist
) VALUES (
  'Allan DAVIS', {'age':'35', 'bday':'27/07/1980', 'nation':'AUSTRALIA'}
);

INSERT INTO cycling.birthday_list (
  cyclist_name, blist
) VALUES (
  'Claudio VANDELLI', {'age':'54', 'bday':'27/07/1961', 'nation':'ITALY'}
);

INSERT INTO cycling.birthday_list (
  cyclist_name, blist
) VALUES (
  'Laurence BOURQUE', {'age':'23', 'bday':'27/07/1992', 'nation':'CANADA'}
);

INSERT INTO cycling.birthday_list (
  cyclist_name, blist
) VALUES (
  'Claudio HEINEN', {'age':'23', 'bday':'27/07/1992', 'nation':'GERMANY'}
);

INSERT INTO cycling.birthday_list (
  cyclist_name, blist
) VALUES (
  'Luc HAGENAARS', {'age':'28', 'bday':'27/07/1987', 'nation':'NETHERLANDS'}
);
INSERT INTO cycling.birthday_list (
  cyclist_name, blist
) VALUES (
  'Toine POELS', {'age':'52', 'bday':'27/07/1963', 'nation':'NETHERLANDS'}
);
// END-insertentries

// Query entries - find cyclist same age
// START-ageentryquery
SELECT *
FROM cycling.birthday_list
WHERE blist['age'] = '23';
// END-ageentryquery

// Query entries - find cyclist same nation map entry
// START-nationentryquery
SELECT *
FROM cycling.birthday_list
WHERE blist['nation'] = 'NETHERLANDS';
// END-nationentryquery

// START-dropentriesindex
DROP INDEX IF EXISTS cycling.blist_idx;
//END-dropentriesindex

//Create index on map VALUES
// START-mapvaluesidx
CREATE INDEX blist_values_idx
ON cycling.birthday_list ( VALUES(blist) );
// END-mapvaluesidx

// Query entries - find cyclist same nation with map VALUES CONTAINS
// START-nationvaluesquery
SELECT *
FROM cycling.birthday_list
WHERE blist CONTAINS 'NETHERLANDS';
// END-nationvaluesquery
Output from map index queries:
// START-sameage
 cyclist_name     | blist
------------------+----------------------------------------------------------
   Claudio HEINEN | {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'}
 Laurence BOURQUE |  {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}
// END-sameage

// START-samenation
 cyclist_name  | blist
---------------+--------------------------------------------------------------
 Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
   Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}
// END-samenation