table-cyclist-teams

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'
  }
);

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'
  }
);
INSERT INTO cycling.cyclist_teams (
  id,firstname,lastname,teams
) VALUES (
  cb06baad-ead8-4f65-b28a-bddc06a0de23,
  'Jamie',
  'BENNETT',
  {
    2014 : 'Boels:Dolmans Cycling Team',
    2013 : 'Boels:Dolmans Cycling Team',
    2011 : 'Team Garmin - Cervelo'
  }
);

DELETE teams[2014] FROM cycling.cyclist_teams
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

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

CREATE INDEX IF NOT EXISTS team_entries_idx
  ON cycling.cyclist_teams ( ENTRIES (teams) );

CREATE CUSTOM INDEX IF NOT EXISTS team_entries_idx
  ON cycling.cyclist_teams ( ENTRIES (teams) ) USING 'StorageAttachedIndex';

CREATE INDEX IF NOT EXISTS team_keys_idx
  ON cycling.cyclist_teams ( KEYS (teams) );

CREATE CUSTOM INDEX IF NOT EXISTS team_keys_idx
  ON cycling.cyclist_teams ( KEYS (teams) ) USING 'StorageAttachedIndex';

CREATE INDEX IF NOT EXISTS team_values_idx
  ON cycling.cyclist_teams ( VALUES (teams) );

CREATE CUSTOM INDEX IF NOT EXISTS team_values_idx
  ON cycling.cyclist_teams ( VALUES (teams) ) USING 'StorageAttachedIndex';

DESCRIBE index cycling.team_entries_idx;

DESCRIBE index cycling.team_entries_idx;

DESCRIBE index cycling.team_keys_idx;

DESCRIBE index cycling.team_keys_idx;

DESCRIBE index cycling.team_values_idx;

DESCRIBE index cycling.team_values_idx;

DROP INDEX IF EXISTS cycling.team_entries_idx;

DROP INDEX IF EXISTS cycling.team_keys_idx;

DROP INDEX IF EXISTS cycling.team_values_idx;

SELECT * FROM cycling.cyclist_teams;

SELECT * FROM cycling.cyclist_teams;

SELECT * FROM cycling.cyclist_teams;

SELECT firstname,lastname,teams FROM cycling.cyclist_teams WHERE teams CONTAINS KEY 2015;

SELECT firstname,lastname,teams FROM cycling.cyclist_teams WHERE teams CONTAINS KEY 2015;

SELECT firstname,lastname,teams FROM cycling.cyclist_teams WHERE teams CONTAINS 'Team Garmin - Cervelo';

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
WHERE teams CONTAINS 'Team Garmin - Cervelo';

SELECT firstname,lastname,teams FROM cycling.cyclist_teams WHERE teams[2014] = 'Boels:Dolmans Cycling Team';

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams[2014] = 'Boels:Dolmans Cycling Team' ALLOW FILTERING;

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams[2014] = 'Boels:Dolmans Cycling Team'
    AND teams[2015] = 'Boels:Dolmans Cycling Team';

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams[2014] = 'Boels:Dolmans Cycling Team'
    AND teams[2015] = 'Boels:Dolmans Cycling Team'
ALLOW FILTERING;

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
WHERE teams[2014] = 'Boels:Dolmans Cycling Team'
  OR teams[2015] = 'Boels:Dolmans Cycling Team';

CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  id uuid PRIMARY KEY,
  firstname text,
  lastname text,
  teams map<int, text>
);

DESCRIBE TABLE cycling.cyclist_teams;

DROP TABLE IF EXISTS cycling.cyclist_teams;

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

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

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