Search code examples
searchcassandracql3

Search for more than one element in a list in Cassandra

I'm learning how the data model works in Cassandra, what things you can do and what not, etc.

I've seen you can have collections and I'm wondering if you can search for the elements inside the collection. I've seen that you can look for one element with contains, but if you want to look for more than one you need to add more filters, is there any way to do this better? is it a bad practice?. This my table definition:

CREATE TABLE data (
  group_id int,
  user timeuuid,
  friends LIST<VARCHAR>,
  PRIMARY KEY (group_id, user)
);

And this what I know i can use to look for more than one item in the list:

SELECT * FROM groups where friends contains 'bob' and friends contains 'Pete' ALLOW FILTERING;

Thank you

Solution

  • Secondary indexes are generally not recommended for performance reasons.

    Generally, in Cassandra, Query based modelling should be followed.

    So,

    That would mean another table:

     CREATE TABLE friend_group_relation (
  friend VARCHAR,
  group_id int,
  <user if needed>
  PRIMARY KEY ((friend), group_id)
  );

    Now you can use either IN query (not recommended) or async queries (strongly recommended, very fast response) on this table.