Filtering data in a collection
Different ways to filter a collection.
After adding data to the tags set collection, selecting the tags set returns the set of tags:
SELECT album, tags FROM playlists;
Assuming you indexed the collection, to filter data using a set collection value, include the name of the collection column in the select expression. For example, find the row containing a particular tag, say "blues", using the CONTAINS condition in the WHERE clause.
SELECT album, tags FROM playlists WHERE tags CONTAINS 'blues';
The output is the row from the playlists table containing the blues tag.
You can create two types of map collection indexes: an index of map values and an index of map keys. The two types cannot co-exist on the same collection. Assuming an index on map values is created, filter the data using a value in the venue map using the CONTAINS condition in the WHERE clause. The statement is the same one you use to filtering the data in a set or list:
SELECT artist, venue FROM playlists WHERE venue CONTAINS 'The Fillmore';
The output is the requested data for the song that debuted at The Fillmore.
Assuming an index on map keys is created, filter the data using a key in the venue map:
SELECT album, venue FROM playlists WHERE venue CONTAINS KEY '2013-09-22 22:00:00-0700';