cyclist_teams

Map data type example. Create an index on a map key and query for a value that contains a key.

SOURCE '0_create_keyspace.cql';

DROP TABLE IF EXISTS cycling.cyclist_teams;

// Create a table with a map
// START-mapColumn
CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  id uuid PRIMARY KEY,
  firstname text,
  lastname text,
  teams map<int, text>
);
// END-mapColumn

DROP INDEX IF EXISTS cycling.team_year_idx;

// Create an index on a map key to find all cyclist/team combos for a year 
// START-keysidx
CREATE INDEX IF NOT EXISTS team_year_idx
ON cycling.cyclist_teams ( KEYS (teams) );
// END-keysidx

// Insert team data into map for cyclist Vos
// START-insertmapdata
INSERT INTO cycling.cyclist_teams (
  id, firstname, lastname, teams
) VALUES (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 
  'Marianne',
  'VOS', 
  {
    2015 : 'Rabobank-Liv Woman Cycling Team', 
    2014 : 'Rabobank-Liv Woman Cycling Team'
  }
);
// END-insertmapdata
 
// View data
CAPTURE 'select_all_from_cyclist_teams.results';
// START-select
SELECT *
FROM cycling.cyclist_teams;
// END-select
CAPTURE OFF;

// Delete an element from the map
// START-deletemapdata
DELETE teams[2014]
FROM cycling.cyclist_teams
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
// END-deletemapdata

// View data again, 2014 team gone
SELECT *
FROM cycling.cyclist_teams;

// Insert more cyclists
INSERT INTO cycling.cyclist_teams (
  id,firstname,lastname,teams
) VALUES (
  cb07baad-eac8-4f65-b28a-bddc06a0de23,
  'Elizabeth',
  'ARMITSTEAD',
  {
    2015 : 'Boels:Dolmans Cycling Team',
    2014 : 'Boels:Dolmans Cycling Team',
    2013 : 'Boels:Dolmans Cycling Team',
    2012 : 'AA Drink - Leontien.nl',
    2011 : 'Team Garmin - Cervelo'
  }
);

// View data
SELECT *
FROM cycling.cyclist_teams;

// Query for KEY year 2015
CAPTURE 'cyclist_team-queries.results';
// START-queryindexkey
SELECT *
FROM cycling.cyclist_teams
WHERE teams CONTAINS KEY 2015;
// END-queryindexkey
CAPTURE OFF;

// START-update_DSB
UPDATE cycling.cyclist_teams
SET teams = teams + { 2009 : 'DSB Bank - Nederland bloeit' }
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
// END-update_DSB

// START-update_Ballast
UPDATE cycling.cyclist_teams
SET teams[2006] = 'Team DSB - Ballast Nedam'
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
// END-update_Ballast

// START-delete_teams
DELETE teams[2009]
FROM cycling.cyclist_teams
WHERE id=e7cd5752-bc0d-4157-a80f-7523add8dbcd;
// END-delete_teams

// START-update_set
UPDATE cycling.cyclist_teams
SET teams = teams - { 2013, 2014 }
WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd;
// END-update_set

 id                                   | firstname | lastname   | teams
--------------------------------------+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 cb07baad-eac8-4f65-b28a-bddc06a0de23 | 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'}
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |        VOS |                                                                                                                                   {2015: 'Rabobank-Liv Woman Cycling Team'}

(2 rows)