birthday_list

Example for maps and indexing search index map fields.

Create the keyspace, table, and load the data:

SOURCE 'keyspace-create-simple.cql';
SOURCE 'keyspace-use.cql';

// tag::drop[]
DROP TABLE IF EXISTS cycling.birthday_list;
// end::drop[]

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

// tag::table-birthday-list[]
CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  cyclist_name text PRIMARY KEY, 
  blist map<text, text>
);
// end::table-birthday-list[]

// tag::drop-index-blist-idx[]
DROP INDEX IF EXISTS cycling.blist_idx;
// end::drop-index-blist-idx[]

// tag::drop-index-blist--values-idx[]
DROP INDEX IF EXISTS cycling.blist_values_idx;
// end::drop-index-blist--values-idx[]

// Create index on map ENTRIES
// tag::entriesidx[]
CREATE INDEX IF NOT EXISTS blist_idx
ON cycling.birthday_list ( ENTRIES(blist) );
// end::entriesidx[]

// Create index on map VALUES
// tag::mapvaluesidx[]
CREATE INDEX IF NOT EXISTS blist_values_idx
ON cycling.birthday_list ( VALUES(blist) );
// end::mapvaluesidx[]

// tag::describe_table_2i_check[]
DESCRIBE TABLE cycling.birthday_list;
// end::describe_table_2i_check[]

// tag::insert_allan[]
INSERT INTO cycling.birthday_list (
  cyclist_name, blist 
) VALUES (
  'Allan DAVIS', { 'age':'35', 'bday':'27/07/1980', 'nation':'AUSTRALIA' }
);
// end::insert_allan[]

// tag::insertall[]
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::insertall[]

// Query entries - find cyclist same age
CAPTURE 'select_from_birthday_list_where_age_23.results';
// tag::ageentryquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist[ 'age' ] = '23';
// end::ageentryquery[]
CAPTURE OFF;

// Query - find cyclist with birthday July 27th 1992
CAPTURE 'select_from_birthday_list_where_bday_jul2792.results';
// tag::bdayquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist_ [ 'bday' ] = '27/07/1992'
ALLOW FILTERING;
// end::bdayquery[]
CAPTURE OFF;

// Query entries - find cyclist same nation map entry
CAPTURE 'select_from_birthday_list_where_nation_netherlands.results';
// tag::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';
// tag::nationvaluesquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist CONTAINS 'NETHERLANDS';
// end::nationvaluesquery[]
CAPTURE OFF;

// birthday_list-table.cql originally
// DSE SEARCH/SOLR 
// For different key-value pairs that you want specify a data types
// tag::index-control-types[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.birthday_list;
// end::index-control-types[]

// Return all fields that were indexed on a key
CAPTURE 'select_from_birthday_list_where_solr_query_blist_age.results';
// tag:solr-age[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:*';
// end:solr-age[]
CAPTURE OFF;

// Using search index
CAPTURE 'select_from_birthday_list_where_solr_query_blist_age_23.results';
// tag:solr-age-23[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:23';
// end:solr-age-23[]
CAPTURE OFF;

CAPTURE 'select_from_birthday_list_where_solr_query_blist_nation_germany.results';
// tag:solr-nation-germany[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_nation:GERMANY';
// end:solr-nation-germany[]
CAPTURE OFF;

// tag::solr-index[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.birthday_list
WITH COLUMNS blist { excluded:true };
// end::index[]

// tag::desc_active[]
DESC ACTIVE SEARCH INDEX SCHEMA ON cycling.birthday_list;
// end::desc_active[]

// tag::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[]

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

// tag::desc_pending[]
DESC PENDING SEARCH INDEX SCHEMA ON cycling.birthday_list;
// end::desc_pending[]

// Make the pending schema active and rebuild the index
// tag::reload[]
RELOAD SEARCH INDEX
ON cycling.birthday_list;
// end::reload[]

// tag::rebuild[]
REBUILD SEARCH INDEX
ON cycling.birthday_list;
// end::rebuild[]

// Using search index
CAPTURE 'select_23_from_birthday_list.results';
// tag::select_23[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:23';
// end::select_23[]
CAPTURE OFF;

CAPTURE 'select_germany_from_birthday_list.results';
// tag::select_germany[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_nation:GERMANY';
// end::select_germany[]
CAPTURE OFF;

Create a default search index:

SOURCE 'keyspace-create-simple.cql';
SOURCE 'keyspace-use.cql';

// tag::drop[]
DROP TABLE IF EXISTS cycling.birthday_list;
// end::drop[]

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

// tag::table-birthday-list[]
CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  cyclist_name text PRIMARY KEY, 
  blist map<text, text>
);
// end::table-birthday-list[]

// tag::drop-index-blist-idx[]
DROP INDEX IF EXISTS cycling.blist_idx;
// end::drop-index-blist-idx[]

// tag::drop-index-blist--values-idx[]
DROP INDEX IF EXISTS cycling.blist_values_idx;
// end::drop-index-blist--values-idx[]

// Create index on map ENTRIES
// tag::entriesidx[]
CREATE INDEX IF NOT EXISTS blist_idx
ON cycling.birthday_list ( ENTRIES(blist) );
// end::entriesidx[]

// Create index on map VALUES
// tag::mapvaluesidx[]
CREATE INDEX IF NOT EXISTS blist_values_idx
ON cycling.birthday_list ( VALUES(blist) );
// end::mapvaluesidx[]

// tag::describe_table_2i_check[]
DESCRIBE TABLE cycling.birthday_list;
// end::describe_table_2i_check[]

// tag::insert_allan[]
INSERT INTO cycling.birthday_list (
  cyclist_name, blist 
) VALUES (
  'Allan DAVIS', { 'age':'35', 'bday':'27/07/1980', 'nation':'AUSTRALIA' }
);
// end::insert_allan[]

// tag::insertall[]
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::insertall[]

// Query entries - find cyclist same age
CAPTURE 'select_from_birthday_list_where_age_23.results';
// tag::ageentryquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist[ 'age' ] = '23';
// end::ageentryquery[]
CAPTURE OFF;

// Query - find cyclist with birthday July 27th 1992
CAPTURE 'select_from_birthday_list_where_bday_jul2792.results';
// tag::bdayquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist_ [ 'bday' ] = '27/07/1992'
ALLOW FILTERING;
// end::bdayquery[]
CAPTURE OFF;

// Query entries - find cyclist same nation map entry
CAPTURE 'select_from_birthday_list_where_nation_netherlands.results';
// tag::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';
// tag::nationvaluesquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist CONTAINS 'NETHERLANDS';
// end::nationvaluesquery[]
CAPTURE OFF;

// birthday_list-table.cql originally
// DSE SEARCH/SOLR 
// For different key-value pairs that you want specify a data types
// tag::index-control-types[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.birthday_list;
// end::index-control-types[]

// Return all fields that were indexed on a key
CAPTURE 'select_from_birthday_list_where_solr_query_blist_age.results';
// tag:solr-age[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:*';
// end:solr-age[]
CAPTURE OFF;

// Using search index
CAPTURE 'select_from_birthday_list_where_solr_query_blist_age_23.results';
// tag:solr-age-23[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:23';
// end:solr-age-23[]
CAPTURE OFF;

CAPTURE 'select_from_birthday_list_where_solr_query_blist_nation_germany.results';
// tag:solr-nation-germany[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_nation:GERMANY';
// end:solr-nation-germany[]
CAPTURE OFF;

// tag::solr-index[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.birthday_list
WITH COLUMNS blist { excluded:true };
// end::index[]

// tag::desc_active[]
DESC ACTIVE SEARCH INDEX SCHEMA ON cycling.birthday_list;
// end::desc_active[]

// tag::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[]

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

// tag::desc_pending[]
DESC PENDING SEARCH INDEX SCHEMA ON cycling.birthday_list;
// end::desc_pending[]

// Make the pending schema active and rebuild the index
// tag::reload[]
RELOAD SEARCH INDEX
ON cycling.birthday_list;
// end::reload[]

// tag::rebuild[]
REBUILD SEARCH INDEX
ON cycling.birthday_list;
// end::rebuild[]

// Using search index
CAPTURE 'select_23_from_birthday_list.results';
// tag::select_23[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:23';
// end::select_23[]
CAPTURE OFF;

CAPTURE 'select_germany_from_birthday_list.results';
// tag::select_germany[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_nation:GERMANY';
// end::select_germany[]
CAPTURE OFF;

Create a custom index:

SOURCE 'keyspace-create-simple.cql';
SOURCE 'keyspace-use.cql';

// tag::drop[]
DROP TABLE IF EXISTS cycling.birthday_list;
// end::drop[]

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

// tag::table-birthday-list[]
CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  cyclist_name text PRIMARY KEY, 
  blist map<text, text>
);
// end::table-birthday-list[]

// tag::drop-index-blist-idx[]
DROP INDEX IF EXISTS cycling.blist_idx;
// end::drop-index-blist-idx[]

// tag::drop-index-blist--values-idx[]
DROP INDEX IF EXISTS cycling.blist_values_idx;
// end::drop-index-blist--values-idx[]

// Create index on map ENTRIES
// tag::entriesidx[]
CREATE INDEX IF NOT EXISTS blist_idx
ON cycling.birthday_list ( ENTRIES(blist) );
// end::entriesidx[]

// Create index on map VALUES
// tag::mapvaluesidx[]
CREATE INDEX IF NOT EXISTS blist_values_idx
ON cycling.birthday_list ( VALUES(blist) );
// end::mapvaluesidx[]

// tag::describe_table_2i_check[]
DESCRIBE TABLE cycling.birthday_list;
// end::describe_table_2i_check[]

// tag::insert_allan[]
INSERT INTO cycling.birthday_list (
  cyclist_name, blist 
) VALUES (
  'Allan DAVIS', { 'age':'35', 'bday':'27/07/1980', 'nation':'AUSTRALIA' }
);
// end::insert_allan[]

// tag::insertall[]
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::insertall[]

// Query entries - find cyclist same age
CAPTURE 'select_from_birthday_list_where_age_23.results';
// tag::ageentryquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist[ 'age' ] = '23';
// end::ageentryquery[]
CAPTURE OFF;

// Query - find cyclist with birthday July 27th 1992
CAPTURE 'select_from_birthday_list_where_bday_jul2792.results';
// tag::bdayquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist_ [ 'bday' ] = '27/07/1992'
ALLOW FILTERING;
// end::bdayquery[]
CAPTURE OFF;

// Query entries - find cyclist same nation map entry
CAPTURE 'select_from_birthday_list_where_nation_netherlands.results';
// tag::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';
// tag::nationvaluesquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist CONTAINS 'NETHERLANDS';
// end::nationvaluesquery[]
CAPTURE OFF;

// birthday_list-table.cql originally
// DSE SEARCH/SOLR 
// For different key-value pairs that you want specify a data types
// tag::index-control-types[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.birthday_list;
// end::index-control-types[]

// Return all fields that were indexed on a key
CAPTURE 'select_from_birthday_list_where_solr_query_blist_age.results';
// tag:solr-age[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:*';
// end:solr-age[]
CAPTURE OFF;

// Using search index
CAPTURE 'select_from_birthday_list_where_solr_query_blist_age_23.results';
// tag:solr-age-23[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:23';
// end:solr-age-23[]
CAPTURE OFF;

CAPTURE 'select_from_birthday_list_where_solr_query_blist_nation_germany.results';
// tag:solr-nation-germany[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_nation:GERMANY';
// end:solr-nation-germany[]
CAPTURE OFF;

// tag::solr-index[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.birthday_list
WITH COLUMNS blist { excluded:true };
// end::index[]

// tag::desc_active[]
DESC ACTIVE SEARCH INDEX SCHEMA ON cycling.birthday_list;
// end::desc_active[]

// tag::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[]

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

// tag::desc_pending[]
DESC PENDING SEARCH INDEX SCHEMA ON cycling.birthday_list;
// end::desc_pending[]

// Make the pending schema active and rebuild the index
// tag::reload[]
RELOAD SEARCH INDEX
ON cycling.birthday_list;
// end::reload[]

// tag::rebuild[]
REBUILD SEARCH INDEX
ON cycling.birthday_list;
// end::rebuild[]

// Using search index
CAPTURE 'select_23_from_birthday_list.results';
// tag::select_23[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:23';
// end::select_23[]
CAPTURE OFF;

CAPTURE 'select_germany_from_birthday_list.results';
// tag::select_germany[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_nation:GERMANY';
// end::select_germany[]
CAPTURE OFF;

Create indexes on map collections to query.

SOURCE 'keyspace-create-simple.cql';
SOURCE 'keyspace-use.cql';

// tag::drop[]
DROP TABLE IF EXISTS cycling.birthday_list;
// end::drop[]

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

// tag::table-birthday-list[]
CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  cyclist_name text PRIMARY KEY, 
  blist map<text, text>
);
// end::table-birthday-list[]

// tag::drop-index-blist-idx[]
DROP INDEX IF EXISTS cycling.blist_idx;
// end::drop-index-blist-idx[]

// tag::drop-index-blist--values-idx[]
DROP INDEX IF EXISTS cycling.blist_values_idx;
// end::drop-index-blist--values-idx[]

// Create index on map ENTRIES
// tag::entriesidx[]
CREATE INDEX IF NOT EXISTS blist_idx
ON cycling.birthday_list ( ENTRIES(blist) );
// end::entriesidx[]

// Create index on map VALUES
// tag::mapvaluesidx[]
CREATE INDEX IF NOT EXISTS blist_values_idx
ON cycling.birthday_list ( VALUES(blist) );
// end::mapvaluesidx[]

// tag::describe_table_2i_check[]
DESCRIBE TABLE cycling.birthday_list;
// end::describe_table_2i_check[]

// tag::insert_allan[]
INSERT INTO cycling.birthday_list (
  cyclist_name, blist 
) VALUES (
  'Allan DAVIS', { 'age':'35', 'bday':'27/07/1980', 'nation':'AUSTRALIA' }
);
// end::insert_allan[]

// tag::insertall[]
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::insertall[]

// Query entries - find cyclist same age
CAPTURE 'select_from_birthday_list_where_age_23.results';
// tag::ageentryquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist[ 'age' ] = '23';
// end::ageentryquery[]
CAPTURE OFF;

// Query - find cyclist with birthday July 27th 1992
CAPTURE 'select_from_birthday_list_where_bday_jul2792.results';
// tag::bdayquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist_ [ 'bday' ] = '27/07/1992'
ALLOW FILTERING;
// end::bdayquery[]
CAPTURE OFF;

// Query entries - find cyclist same nation map entry
CAPTURE 'select_from_birthday_list_where_nation_netherlands.results';
// tag::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';
// tag::nationvaluesquery[]
SELECT *
FROM cycling.birthday_list
WHERE blist CONTAINS 'NETHERLANDS';
// end::nationvaluesquery[]
CAPTURE OFF;

// birthday_list-table.cql originally
// DSE SEARCH/SOLR 
// For different key-value pairs that you want specify a data types
// tag::index-control-types[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.birthday_list;
// end::index-control-types[]

// Return all fields that were indexed on a key
CAPTURE 'select_from_birthday_list_where_solr_query_blist_age.results';
// tag:solr-age[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:*';
// end:solr-age[]
CAPTURE OFF;

// Using search index
CAPTURE 'select_from_birthday_list_where_solr_query_blist_age_23.results';
// tag:solr-age-23[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:23';
// end:solr-age-23[]
CAPTURE OFF;

CAPTURE 'select_from_birthday_list_where_solr_query_blist_nation_germany.results';
// tag:solr-nation-germany[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_nation:GERMANY';
// end:solr-nation-germany[]
CAPTURE OFF;

// tag::solr-index[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.birthday_list
WITH COLUMNS blist { excluded:true };
// end::index[]

// tag::desc_active[]
DESC ACTIVE SEARCH INDEX SCHEMA ON cycling.birthday_list;
// end::desc_active[]

// tag::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[]

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

// tag::desc_pending[]
DESC PENDING SEARCH INDEX SCHEMA ON cycling.birthday_list;
// end::desc_pending[]

// Make the pending schema active and rebuild the index
// tag::reload[]
RELOAD SEARCH INDEX
ON cycling.birthday_list;
// end::reload[]

// tag::rebuild[]
REBUILD SEARCH INDEX
ON cycling.birthday_list;
// end::rebuild[]

// Using search index
CAPTURE 'select_23_from_birthday_list.results';
// tag::select_23[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_age:23';
// end::select_23[]
CAPTURE OFF;

CAPTURE 'select_germany_from_birthday_list.results';
// tag::select_germany[]
SELECT *
FROM cycling.birthday_list
WHERE solr_query = 'blist_nation:GERMANY';
// end::select_germany[]
CAPTURE OFF;

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)

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com