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.

A set stores a group of elements that are returned in sorted order when queried. A column of type set consists of unordered unique values. 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.

Procedure

  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