How to search a cassandra collection map using QueryBuilder

Question

In my cassandra table i have a collection of Map also i have indexed the map keys.

CREATE TABLE IF NOT EXISTS test.collection_test(
  name text,
  year text,
  attributeMap map<text,text>,
  PRIMARY KEY ((name, year))
);




CREATE INDEX ON collection_test (attributeMap);

The QueryBuilder syntax is as below:

select().all().from("test", "collection_test")
                .where(eq("name", name)).and(eq("year", year));

How should i put where condition on attributeMap?

Answer 1

First of all, you will need to create an index on the keys in your map. By default, an index created on a map indexes the values of the map, not the keys. There is special syntax to index the keys:

CREATE INDEX attributeKeyIndex ON collection_test (KEYS(attributeMap));

Next, to SELECT from a map with indexed keys, you'll need the CONTAINS KEY keyword. But currently, there is not a definition for this functionality in the query builder API. However, there is an open ticket to support it: JAVA-677

Currently, to accomplish this with the Java Driver, you'll need to build your own query or use a prepared statement:

    PreparedStatement statement = _session.prepare("SELECT * " +
            "FROM test.collection_test " +
            "WHERE attributeMap CONTAINS KEY ?");
    BoundStatement boundStatement = statement.bind(yourKeyValue);
    ResultSet results = _session.execute(boundStatement);

Finally, you should read through the DataStax doc on When To Use An Index. Secondary indexes are known to not perform well. I can't imagine that a secondary index on a collection would be any different.