Cassandra: Working with lists

List is an ordered collection of elements. Unlike Set, List allows duplicates. You can access the elements in the list by index.

How to create a column of type list?

You can create a column of type list using ‘list’ keyword.

Example

CREATE TABLE IF NOT EXISTS cassandratutorial.person (
  id INT PRIMARY KEY, 
  firstName VARCHAR,
  lastName VARCHAR,
  hobbies LIST<VARCHAR>
);

Insert elements into a list

You can insert elements into a list using JSON array kind of syntax.

INSERT INTO cassandratutorial.person  (id, firstName, lastName, hobbies) VALUES (1, 'Hari', 'Krishna', ['football', 'cricket', 'trekking']

cqlsh> CREATE TABLE IF NOT EXISTS cassandratutorial.person (
   ...   id INT PRIMARY KEY, 
   ...   firstName VARCHAR,
   ...   lastName VARCHAR,
   ...   hobbies LIST<VARCHAR>
   ... );
cqlsh> 
cqlsh> INSERT INTO cassandratutorial.person  (id, firstName, lastName, hobbies) VALUES (1, 'Hari', 'Krishna', ['football', 'cricket', 'trekking']);
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.person;

 id | firstname | hobbies                             | lastname
----+-----------+-------------------------------------+----------
  1 |      Hari | ['football', 'cricket', 'trekking'] |  Krishna

(1 rows)

Adding elements to a list

You can add elements to a list using using ‘+’ operator.

UPDATE cassandratutorial.person SET hobbies = hobbies + ['tennis', 'blogging'] WHERE id = 1;

cqlsh> UPDATE cassandratutorial.person SET hobbies = hobbies + ['tennis', 'blogging'] WHERE id = 1;
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.person;

 id | firstname | hobbies                                                   | lastname
----+-----------+-----------------------------------------------------------+----------
  1 |      Hari | ['football', 'cricket', 'trekking', 'tennis', 'blogging'] |  Krishna

(1 rows)

Above statement append elements to a list, you can even prepend elements to the list.

UPDATE cassandratutorial.person SET hobbies = ['cooking', 'photography'] + hobbies WHERE id = 1;

cqlsh> UPDATE cassandratutorial.person SET hobbies = ['cooking', 'photography'] + hobbies WHERE id = 1;
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.person;

 id | firstname | hobbies                                                                             | lastname
----+-----------+-------------------------------------------------------------------------------------+----------
  1 |      Hari | ['cooking', 'photography', 'football', 'cricket', 'trekking', 'tennis', 'blogging'] |  Krishna

(1 rows)

Setting the element by its position

UPDATE cassandratutorial.person SET hobbies[0] = 'stamp collection' WHERE id = 1;

Above statement sets the first element of list to 'stamp collection'.

cqlsh> UPDATE cassandratutorial.person SET hobbies[0] = 'stamp collection' WHERE id = 1;
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.person;

 id | firstname | hobbies                                                                                      | lastname
----+-----------+----------------------------------------------------------------------------------------------+----------
  1 |      Hari | ['stamp collection', 'photography', 'football', 'cricket', 'trekking', 'tennis', 'blogging'] |  Krishna

(1 rows)

Removing the element at specific position

Below statement deletes the first element from list hobbies.

DELETE hobbies[0] FROM cassandratutorial.person WHERE id = 1;

Remove all the occurrence of an element or elements

Using – operator, you can remove all the occurrences of elements from the list.

UPDATE cassandratutorial.person SET hobbies = hobbies - ['cooking', 'photography'] WHERE id = 1;

cqlsh> SELECT * FROM cassandratutorial.person;

 id | firstname | hobbies                                                                  | lastname
----+-----------+--------------------------------------------------------------------------+----------
  1 |      Hari | ['photography', 'football', 'cricket', 'trekking', 'tennis', 'blogging'] |  Krishna

(1 rows)
cqlsh>
cqlsh> UPDATE cassandratutorial.person SET hobbies = hobbies - ['cooking', 'photography'] WHERE id = 1;
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.person;

 id | firstname | hobbies                                                   | lastname
----+-----------+-----------------------------------------------------------+----------
  1 |      Hari | ['football', 'cricket', 'trekking', 'tennis', 'blogging'] |  Krishna

(1 rows)
cqlsh>

Note

By considering performance, set is preferred over list.

Previous                                                    Next                                                    Home