birthday_list

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-insert_allan
INSERT INTO cycling.birthday_list (
  cyclist_name, blist_
) VALUES (
  'Allan DAVIS', { 'blist_age':'35', 'bday':'27/07/1980', 'blist_nation':'AUSTRALIA' }
);
// END-insert_allan

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

// START-desc_active
DESC ACTIVE SEARCH INDEX SCHEMA ON cycling.birthday_list;
// END-desc_active

// START-type
// Add type
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' ];
// END-type

// START-alter
ALTER SEARCH INDEX SCHEMA
ON cycling.birthday_list
ADD fields.field[ @name='blist_nation', @indexed='true', @multiValued='false', @type='StrField' ];
// END-alter

// START-desc_pending
DESC PENDING SEARCH INDEX SCHEMA ON cycling.birthday_list;
// END-desc_pending

// Make the pending schema active and rebuild the index
// START-reload
RELOAD SEARCH INDEX
ON cycling.birthday_list;
// END-reload

// START-rebuild
REBUILD SEARCH INDEX
ON cycling.birthday_list;
// END-rebuild

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
CAPTURE 'select_from_birthday_list_where_age_23.results';
// START-ageentryquery
SELECT *
FROM cycling.birthday_list
WHERE blist[ 'age' ] = '23';
// END-ageentryquery
CAPTURE OFF;

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

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

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

This query returns the rows where age is 23:

SELECT *
FROM cycling.birthday_list
WHERE blist[ 'age' ] = '23';

 cyclist_name     | blist
------------------+----------------------------------------------------------
   Claudio HEINEN | {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'}
 Laurence BOURQUE |  {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

(2 rows)

This query returns the rows where blist[ 'nation' ] is NETHERLANDS:

SELECT *
FROM cycling.birthday_list
WHERE blist[ 'nation' ] = 'NETHERLANDS';

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

(2 rows)

This query returns the rows where blist contains NETHERLANDS:

SELECT *
FROM cycling.birthday_list
WHERE blist CONTAINS 'NETHERLANDS';

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

(2 rows)