Using the set type

Using the set data type, you can solve the multiple email problem in an intuitive way that does not require a read before adding a new email address.

1. Define a set, emails, in the users table to accommodate multiple email address.

   CREATE TABLE users (
     user_id text PRIMARY KEY,
     first_name text,
     last_name text,
     emails set<text>
   );

2. Insert data into the set, enclosing values in curly brackets.
   Set values must be unique.

   INSERT INTO users (user_id, first_name, last_name, emails)
     VALUES('frodo', 'Frodo', 'Baggins', {'f@baggins.com', 'baggins@gmail.com'});

3. Add an element to a set using the UPDATE command and the addition (+) operator.

   UPDATE users
     SET emails = emails + {'fb@friendsofmordor.org'} WHERE user_id = 'frodo';

4. Retrieve email addresses for frodo from the set.

   SELECT user_id, emails FROM users WHERE user_id = 'frodo';

   When you query a table containing a collection, Apache Cassandra™ retrieves the collection in its entirety; consequently, keep collections small enough to be manageable, or construct a data model to replace collections that can accommodate large amounts of data.

   Cassandra returns results in an order based on the type of the elements in the collection. For example, a set of text elements is returned in alphabetical order. If you want elements of the collection returned in insertion order, use a list.

    user_id | emails
   ---------+-------------------------------------------------------------------
    frodo   | {"baggins@caramail.com","f@baggins.com","fb@friendsofmordor.org"}
   

5. Remove an element from a set using the subtraction (-) operator.

   UPDATE users
     SET emails = emails - {'fb@friendsofmordor.org'} WHERE user_id = 'frodo';

6. Remove all elements from a set by using the UPDATE or DELETE statement.

   A set, list, or map needs to have at least one element; otherwise, Apache Cassandra™ cannot distinguish the set from a null value.

   UPDATE users SET emails = {} WHERE user_id = 'frodo';
   
   DELETE emails FROM users WHERE user_id = 'frodo';

   A query for the emails returns null.

   SELECT user_id, emails FROM users WHERE user_id = 'frodo';

    user_id | emails
   ---------+---------
    frodo   | null