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)