3 Powerful Collections Data Types in Cassandra Database

Collections Data Type

Collections are used for storing/denormalizing relatively small amount of data. CQL support 3 type of collection that is below.

1. MAP '<' cql_type ',' cql_type '>'
2. SET '<' cql_type '>'
3. LIST '<' cql_type '>'

1) Maps Collections Data Type

A map is set of key-value pairs in which keys are unique. Map is sorted by its keys.

Syntax: - MAP '<' cql_type ',' cql_type '>'

I am going to create a employee table with map that is below.

cqlsh> use payroll;
cqlsh:payroll> CREATE TABLE employee(
           ...     id int PRIMARY KEY,
           ...     name text,
           ...     favourite map<text, text>
           ... );
cqlsh:payroll> select * from employee
           ... ;

 id | favourite | name
----+-----------+------

(0 rows)

Now, I am inserting one records in employee table with map key-pair value.

cqlsh:payroll> INSERT INTO employee(id, name, favourite)
           ...            VALUES (786, 'alok singh', { 'fruit' : 'Apple', 'band' : 'xxxx' });
cqlsh:payroll> select * from employee
           ... ;

 id  | favourite                          | name
-----+------------------------------------+------------
 786 | {'band': 'xxxx', 'fruit': 'Apple'} | alok singh

Updating or inserting one or more elements:

cqlsh:payroll> UPDATE employee SET favourite['author'] = 'alok kumar singh' WHERE id = 786;
cqlsh:payroll> UPDATE employee SET favourite= favourite+ { 'movie' : 'code', 'band' : 'ZZ Top' } WHERE id = 786;

Result:-

cqlsh:payroll> select * from employee;

 id  | favourite                                                                           | name
-----+-------------------------------------------------------------------------------------+------------
 786 | {'author': 'alok kumar singh', 'band': 'ZZ Top', 'fruit': 'Apple', 'movie': 'code'} | alok singh

(1 rows)

Removing one or more element

cqlsh:payroll> DELETE favourite['author'] FROM employee WHERE id = 786;
cqlsh:payroll> UPDATE employee SET favourite= favourite- { 'movie', 'band'} WHERE id = 786;

Result:-

cqlsh:payroll> Select * from employee;

 id  | favourite          | name
-----+--------------------+------------
 786 | {'fruit': 'Apple'} | alok singh

(1 rows)

Note that for removing multiple elements in a map, you remove from it a set of keys.

2) Sets Collections Data Type

A set is collection of unique values. You can define and insert a map

Syntax: - SET '<' cql_type '>'

Example:- 

cqlsh:payroll> CREATE TABLE images (
           ...     id int PRIMARY KEY,
           ...     name text,
           ...     owner text,
           ...     tags set<text> // A set of text values
           ... );
cqlsh:payroll> Select * from images;

 id | name | owner | tags
----+------+-------+------

(0 rows)
cqlsh:payroll> INSERT INTO images (id,name, owner, tags)
           ...             VALUES (785,'cat.jpg', 'alok', { 'pet', 'cute' });
cqlsh:payroll> select * from images;

 id  | name    | owner | tags
-----+---------+-------+-----------------
 785 | cat.jpg |  alok | {'cute', 'pet'}

(1 rows)

Adding one or multiple elements

cqlsh:payroll> UPDATE images SET tags = tags + { 'gray', 'cuddly' } WHERE id= 785;
cqlsh:payroll> Select * from images;

 id  | name    | owner | tags
-----+---------+-------+-----------------------------------
 785 | cat.jpg |  alok | {'cuddly', 'cute', 'gray', 'pet'}

(1 rows)

Removing one or multiple elements

cqlsh:payroll> UPDATE images SET tags = tags - { 'cute' } WHERE id= 785;
cqlsh:payroll> select * from images;

 id  | name    | owner | tags
-----+---------+-------+---------------------------
 785 | cat.jpg |  alok | {'cuddly', 'gray', 'pet'}

(1 rows)

3) Lists Collections Data Type

A list is a collection of non-unique values where elements are ordered by there position in the list. 

You can define and insert a list with below syntax.

Syntax: - LIST '<' cql_type '>'

Example:-

cqlsh:payroll> CREATE TABLE plays (
           ...     id text PRIMARY KEY,
           ...     game text,
           ...     players int,
           ...     scores list<int> // A list of integers
           ... );
cqlsh:payroll>
cqlsh:payroll> INSERT INTO plays (id, game, players, scores)
           ...            VALUES ('123-abcd', 'tennis', 2, [25, 10,18]);
cqlsh:payroll> Select * from plays;

 id       | game   | players | scores
----------+--------+---------+--------------
 123-abcd | tennis |       2 | [25, 10, 18]

(1 rows)
cqlsh:payroll>// Replace the existing list entirely
cqlsh:payroll> UPDATE plays SET scores = [ 3, 9, 4] WHERE id = '123-abcd';
cqlsh:payroll> select * from plays;

 id       | game   | players | scores
----------+--------+---------+--------------
 123-abcd | tennis |       2 | [3, 9, 4]
 

(3 rows)

Appending and prepending values to a list:

cqlsh:payroll> select * from plays;

 id       | game   | players | scores
----------+--------+---------+--------------
 123-abcd | tennis |       2 | [25, 10, 18]

(1 rows)
cqlsh:payroll> UPDATE plays SET players = 5, scores = scores + [ 14, 21 ] WHERE id = '123-abcd';
cqlsh:payroll> UPDATE plays SET players = 6, scores = [ 3 ] + scores WHERE id = '123-abcd';
cqlsh:payroll> select * from plays;

 id       | game   | players | scores
----------+--------+---------+-------------------------
 123-abcd | tennis |       6 | [3, 25, 10, 18, 14, 21]

(1 rows)

Setting the value at a particular position in the list:

cqlsh:payroll> UPDATE plays SET scores[1] = 7 WHERE id = '123-abcd';
cqlsh:payroll> Select * from plays;

 id       | game   | players | scores
----------+--------+---------+------------------------
 123-abcd | tennis |       6 | [3, 7, 10, 18, 14, 21]

(1 rows)

Removing an element by its position in the list:

cqlsh:payroll> DELETE scores[1] FROM plays WHERE id = '123-abcd';
cqlsh:payroll> Select * from plays ;

 id       | game   | players | scores
----------+--------+---------+---------------------
 123-abcd | tennis |       6 | [3, 10, 18, 14, 21]

(1 rows)