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

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

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

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

Query for either one of two entries from the map teams column:

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

Results

Using the map type

Was this helpful?

Give Feedback