Collections Data Type
Collections are used for storing/denormalizing relatively
small amount of data. CQL support 3 type of collection that is below.
- MAP '<' cql_type ',' cql_type '>'
- SET '<' cql_type '>'
- 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 '>'
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)
No comments:
Post a Comment
Note: only a member of this blog may post a comment.