Cassandra CQL support for CONTAINS negation

Question

Cassandra supports CONTAINS on collections:

CREATE TABLE contacts (
    id int PRIMARY KEY,
    firstName text,
    lastName text,
    phones map<text, text>,
    emails set<text>
);
CREATE INDEX ON contacts (firstName);
CREATE INDEX ON contacts (keys(phones)); // Using the keys function to index the map keys
CREATE INDEX ON contacts (emails);

And it is possible to query the emails set and check for specific Email. Simply:

SELECT * FROM contacts WHERE emails CONTAINS '[email protected]';

What would be the solution if one wants to check for lack of an element, something like: DOES NOT CONTAIN? I couldn't find such functionality in CQL docs, is there any solution for that?

Chaity Chaity · Accepted Answer · 2018-03-13T13:28:15

No Cassandra does not support such feature. I can guess you have gone through below article:
A deep look at the CQL WHERE clause

You have to get the whole collections and filter it from your application.

Cassandra also doesn't support the "is not null" operator nor the "not equals" operator.

These restrictions are because of how C* stores data and how it finds row and scans columns fast. C* stores data in a key-value pair (map of map). It can fast get data by indexing the keys. C* can find the particular item very fast by jumping to the location where the data resides. If you index collections:

Sets and lists can index all values found by indexing the collection column. Maps can index a map key, map value, or map entry.

To support 'not feature', C* has to get all the rows or item in a collection and filter out all the results to give you the result which is not very efficient. So C* does not support this. If you need this, you can handle it in your application knowing all the facts and considerations.

**Note: Using C* index has its own performance impacts. Make sure you know all the considerations and use cases.

All the cautions about using secondary indexes apply to indexing collections.

Indexing a collection
When to use an index
Cassandra CQL mapping -> How C* stores collections.

Cassandra CQL support for CONTAINS negation

1 Answers