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
-
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>
);
-
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'});
-
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';
- 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, 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"}
- Remove an element from a set using the subtraction (-) operator.
UPDATE users
SET emails = emails - {'fb@friendsofmordor.org'} WHERE user_id = 'frodo';
-
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, 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