In Cassandra CQL, is there a way to query the size of a collection column type?

Question

We're looking to be able to do something like..

select * from User where rewards.size > 0

when the schema is

create table user (
    id uuid primary key,
    network_id uuid,
    rewards set<uuid>,
    name text   
);

Answer 1

sadly Cassandra is eager performing very quick reads and on the other hand is not perfectly done supporting collections: Get count of elements in Set type column in Cassandra

unfortunately the Collections-support even in CQL Driver v2 is not perfect: you may add or delete items in upsert statements. But more on them, like doing an item select, asking for collection item's TTLs or asking for the collection's size, is not supported. So you have to

resultset: SELECT collection_column FROM ... and then take item by resultset.one() or resultset.all() and get item.size() yourself.

What you want to do is adding an indexed column with a counter to count and fast-read the element count. Iff you just want to know whether the collection is not empty you may need an index column with a boolean. The index makes sure you can scan the columns efficiently.