Using the list type

This example uses the following table and index:

CREATE TABLE IF NOT EXISTS cycling.upcoming_calendar (
  year int,
  month int,
  events list<text>,
  PRIMARY KEY (year, month)
);
CREATE CUSTOM INDEX events_idx ON cycling.upcoming_calendar (events) 
  USING 'StorageAttachedIndex';

An index is created on the list column events in the upcoming_calendar table.

Query with CONTAINS from the list events column:

SELECT * FROM cycling.upcoming_calendar 
  WHERE events CONTAINS 'Criterium du Dauphine';

Results

 year | month | events
------+-------+----------------------------------------------------------------------------
 2015 |     6 | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']

(1 rows)

A slightly more complex query selects rows that either contain a particular event or have a particular month date:

SELECT * FROM cycling.upcoming_calendar
  WHERE events CONTAINS 'Criterium du Dauphine' 
    OR month = 7;

Results

 year | month | events
------+-------+----------------------------------------------------------------------------
 2015 |     6 | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']
 2015 |     7 |                                                         ['Tour de France']

(2 rows)

Using the list type

Was this helpful?

Give Feedback