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:
SOURCE '0_create_keyspace.cql';

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

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

// START-table
CREATE TABLE IF NOT EXISTS 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:
SOURCE 'birthday_list-table.cql';

// For different key-value pairs that you want specify a data types
// START-index-control-types
CREATE SEARCH INDEX IF NOT EXISTS
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:*';

// Regular queries
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 IF NOT EXISTS
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;

// 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.
SOURCE '0_create_keyspace.cql';

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

DROP INDEX IF EXISTS cycling.blist_idx;
DROP INDEX IF EXISTS cycling.blist_values_idx;

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

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

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

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

// 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

// Query entries - find cyclist same nation with map VALUES CONTAINS
// START-nationvaluesquery
SELECT *
FROM cycling.birthday_list
WHERE blist CONTAINS 'NETHERLANDS';
// END-nationvaluesquery

// START-dropentriesindex
DROP INDEX IF EXISTS cycling.blist_idx;
// END-dropentriesindex
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