Apache Cassandra 1.2 Documentation

Using the list type

When the order of elements matters, which may not be the natural order dictated by the type of the elements, use a list. Also, use a list when you need to store same value multiple times. List values are returned according to their index value in the list, whereas set values are returned in alphabetical order, assuming the values are text.

For example, for each user in a users table, you want to add a list of their preferred places, then query the database for the top x places for a user.

Insertion

To add a list declaration to a table, add a column top_places of the list type to the users table:

ALTER TABLE users ADD top_places list<text>;

Next, use the UPDATE command to insert values into the list.

UPDATE users
  SET top_places = [ 'rivendell', 'rohan' ] WHERE user_id = 'frodo';

Addition

To prepend an element to the list, enclose it in square brackets, and use the addition (+) operator:

UPDATE users
  SET top_places = [ 'the shire' ] + top_places WHERE user_id = 'frodo';

To append an element to the list, switch the order of the new element data and the list name in the UPDATE command:

UPDATE users
  SET top_places = top_places + [ 'mordor' ] WHERE user_id = 'frodo';

These update operations are implemented internally without any read-before-write. Appending and prepending a new element to the list writes only the new element.

To add an element at a particular position, use the list index position in square brackets:

UPDATE users SET top_places[2] = 'riddermark' WHERE user_id = 'frodo';

When you add an element at a particular position, Cassandra reads the entire list, and then writes only the updated element. Consequently, adding an element at a particular position results in greater latency than appending or prefixing an element to a list.

Deletion

To remove an element from a list, use the DELETE command and the list index position in square brackets:

DELETE top_places[3] FROM users WHERE user_id = 'frodo';

To remove all elements having a particular value, use the UPDATE command, the subtraction operator (-), and the list value in square brackets:

UPDATE users
  SET top_places = top_places - ['riddermark'] WHERE user_id = 'frodo';

The former, indexed method of removing elements from a list requires a read internally. Using the UPDATE command as shown here is recommended over emulating the operation client-side by reading the whole list, finding the indexes that contain the value to remove, and then removing those indexes. This emulation would not be thread-safe. If another thread/client prefixes elements to the list between the read and the write, the wrong elements are removed. Using the UPDATE command as shown here does not suffer from that problem.

Retrieval

A query returns a list of top places.

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