CQL for Cassandra 1.2

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.

Using the list type you can add a list of preferred places for each user in a users table, and then query the database for the top x places for a user.

Procedure

  1. Add a list declaration to a table by adding a column top_places of the list type to the users table.
    ALTER TABLE users ADD top_places list<text>;
  2. Use the UPDATE command to insert values into the list.
    UPDATE users
      SET top_places = [ 'rivendell', 'rohan' ] WHERE user_id = 'frodo';
  3. Prepend an element to the list by enclosing it in square brackets, and using the addition (+) operator.
    UPDATE users
      SET top_places = [ 'the shire' ] + top_places WHERE user_id = 'frodo';
  4. Append an element to the list by switching 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.
  5. Add an element at a particular position using 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.
  6. Remove an element from a list using the DELETE command and the list index position in square brackets.
    DELETE top_places[3] FROM users WHERE user_id = 'frodo';
  7. Remove all elements having a particular value using 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.
  8. Query the database for a list of top places.
    SELECT user_id, top_places FROM users WHERE user_id = 'frodo';
Show/hide