Querying in Cassandra, retrieving from a SET of UDT

Question

City Information as a UDT. city code will be 'work' or 'home' or 'templocation'.

CREATE TYPE facetmanager_ps1.city (
    id int,
    citycode text,
    cityname text
);

A UDT which holds the list of part_time_employees; it has a set of city UDT

CREATE TYPE facetmanager_ps1.part_time_employees (
    firstname text,
    lastname text,
    address_set frozen<set<frozen<city>>>
);

A table which hold the employee information.It has a set of part_time_employee_set.

CREATE TABLE facetmanager_ps1.employee (
    id int PRIMARY KEY,
    name text,
    part_time_employee_set set<frozen<part_time_employees>>,
    PRIMARY KEY (id)
)


insert into employee (id,name,part_time_employee_set) 
values 
( 1, 'raghu', 
    {
        {
            firstname: 'r1',
            lastname:'r2',
            address_set : {
                {
                    id: 600000,
                    citycode: 'work',
                    cityname: 'chennai'
                },
                {
                    id:600020,
                    citycode: 'home',
                    cityname: 'kanchipuram'
                }
            }
        }
    }
);


update employee set part_time_employee_set
= part_time_employee_set + 
{ { firstname: 'arun', lastname : 'kannan', address_set :
    {
        {
            id: 600000,
            citycode:'work',
            cityname: 'chennai'
        },
        {
            id: 600000,
            citycode:'home',
            cityname: 'chennai'
        }
    }
}} where id=1;

select * from employee where id=1; it gives all the part_time_employee information

My Main Issue:

Now i want only employee information of the part_time_employee r1. i want his work location and home location i.e.) i want the complete address_set how to get that?

select * from employee where id=1 and part_time_employee_set = { { firstname: 'r1' }};
InvalidRequest: Error from server: code=2200 [Invalid query] message="Collection column 'part_time_employee_set' (set<frozen<part_time_employees>>) cannot be restricted by a '=' relation"

select * from employee where id=1 and part_time_employee_set in { { firstname: 'r1' }};
SyntaxException: line 1:64 no viable alternative at input '{' (...employee where id=1 and [part_time_employee_set] in...)

Issue 2:(minor issue)

select * from employee;

<<set(2)>>

how to display the expanded result in devcenter?. "Expand ON" command is not working as a command in dev center however if i copy the values are copied, into a textedit or other editor. this is not a major concern for me at this point.

Answer 1

You can't query with non-primary key, unless you create index.

Even if you create index on part_time_employee_set collection you can't query with a piece of a frozen field (firstname). Frozen fields means to be a compact. And another thing you can't select a partial item in a collection. you need to select the entire collection.

If you want to query with firstname you have to change your data model.

CREATE TABLE employee (
    id int,
    firstname text,
    lastname text,
    address_set set<frozen<city>>,
    name text static,
    PRIMARY KEY (id, firstname, lastname)
);

Now you can select address_set of part time employee who have first name 'r1'

SELECT * FROM employee_test WHERE id = 1 AND firstname = 'r1';

Output :

 id          | 1
 firstname   | r1
 lastname    | r2
 name        | raghu
 address_set | {{id: 600000, citycode: 'work', cityname: 'chennai'}, {id: 600020, citycode: 'home', cityname: 'kanchipuram'}}