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.

Filtering by map value or map key

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