How to query a field in a user defined type in a set using CQL (Cassandra)

Question

I am using the User Defined Types in CQL 3.1 (cqlsh info below):

[cqlsh 5.0.1 | Cassandra 2.1.2 | CQL spec 3.2.0 | Native protocol v3]

I have created the following user-defined type (simplified here for demonstration purposes):

create type fullname ( firstname text, lastname text );

I then created a table that includes a set of type fulsome:

create table people ( id UUID primary key, names set < frozen <fullname>> );

I can insert rows into the table using:

insert into people (id, names) values (
  now(), 
  {{firstname: 'Jim', lastname: 'Jones'}}
);

The result looks good:

select * from people;

 id                                   | names
--------------------------------------+-----------------------------------------
 69ba9d60-a06b-11e4-9923-0fa29ba414fb | {{firstname: 'Jim', lastname: 'Jones'}}

My question is this: how do I fetch all of the first name entries? If I was not using set, and just fullname, I could use the dot notation:

select name.firstname from people;

But that obviously doesn't work, because I am using a set. In normal usage (without a UDT), a set would never have need for dot notation, because all of the entries would be of a simple type (text, UUID, etc). I tried using all of the usual suspects from other programming languages...

select names.firstname...
select names[].firstname...
select names['firstname']...

About the only information on UDT's on the web is from the DataStax web site, which doesn't get into this use case.

Anybody tried this before? Or is what I am doing just plain not allowed?

Thanks,

John

Answer 1

I went ahead and tried this myself. You're right about being able to pull back a single property if your UDT was not in a set. But when it's in a collection ( I tried both SET and MAP types) it doesn't work. I'm thinking that this is more of a limitation on collections than it is on UDTs.

If you read through the DataStax doc Using Collections, you'll notice that none of the examples show how to pull back a single element of a collection. Which is what you would first have to do, before having access to a property on the UDT element. Anyway, that has to do with how collections are treated as a column, and thus the entire column value is returned on a query.

There is a JIRA ticket currently open for this (CASSANDRA-7396) and additional functionality. So I'm thinking we'll see it in the semi-near future. But for now, this isn't going to be possible.