Using the map type
This example uses the following table and indexes:
CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
id uuid PRIMARY KEY,
firstname text,
lastname text,
teams map<int, text>
);
CREATE CUSTOM INDEX IF NOT EXISTS team_keys_idx
ON cycling.cyclist_teams ( KEYS (teams) ) USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX IF NOT EXISTS team_values_idx
ON cycling.cyclist_teams ( VALUES (teams) ) USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX IF NOT EXISTS team_entries_idx
ON cycling.cyclist_teams ( ENTRIES (teams) ) USING 'StorageAttachedIndex';Indexes created on the map column teams in the cyclist_career_teams table target the keys, values, and full entries of the column data.
Query with KEYS from the map teams column:
SELECT firstname,lastname,teams FROM cycling.cyclist_teams
WHERE teams CONTAINS KEY 2015;Results
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0. All features may not work!
firstname | lastname | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'}
Marianne | VOS | {2015: 'Rabobank-Liv Woman Cycling Team'}
(2 rows)Query a value from the map teams column, noting that only the keyword CONTAINS is included:
SELECT firstname,lastname,teams FROM cycling.cyclist_teams
WHERE teams CONTAINS 'Team Garmin - Cervelo';Results
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0. All features may not work!
firstname | lastname | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Jamie | BENNETT | {2011: 'Team Garmin - Cervelo', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team'}
Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'}
(2 rows)Query entries from the map teams column, noting the difference in the WHERE clause:
SELECT firstname,lastname,teams FROM cycling.cyclist_teams
WHERE teams CONTAINS 'Team Garmin - Cervelo';Results
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0. All features may not work!
firstname | lastname | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Jamie | BENNETT | {2011: 'Team Garmin - Cervelo', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team'}
Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'}
(2 rows)Query for two entries from the map teams column:
SELECT firstname,lastname,teams FROM cycling.cyclist_teams
WHERE teams[2014] = 'Boels:Dolmans Cycling Team'
AND teams[2015] = 'Boels:Dolmans Cycling Team';Results
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0. All features may not work!
firstname | lastname | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'}
(1 rows)