Sandy Sandy - 1 month ago 4
reST (reStructuredText) Question

Query regarding Cassandra java BoundStatement with multiple attributes

I'm trying to fetch records from Cassandra using BoundStatement's bind method and then doing session.execute on that.
Suppose the json looks like this as shown below

{
id:1,
name:abc,
.
.
.
}


Here,if I want to query using multiple columns (id=1 and name=abc),I'm able to fetch the records successfully.

However, my json looks something like this:

{
id:1
name:["abc","xyz"]
.
.
.
}


In the database, it gets saved in two different rows(as there are list of values associated with the name attribute)

The problem is how can I fetch the records from the database?

P.S.:id and name are compound partition key

Answer

Since id and name are part of a compound partition key you will always need to search by both of them. So you can take your JSON and fan out the name list into a few select queries or use an IN.

Recommended Solution

Use two different SELECT queries at the same time then aggregate the results this allows each SELECT to hit the best node assuming you are using TokenAware.

I use RxJava to spread out to many requests then merge them back to a list of rows.

public class CassandraService {
  //...

  public void run() {

    String id = "1";
    List<String> names = ['abc','xyz'];


    PreparedStatement selectStmt = session.prepare("SELECT id, name, value FROM table WHERE id=? AND name=?;");
    List statements = new ArrayList<Statement>();

    for(String name : names) {
      statements.add(selectStmt.bind(id,name));
    }

    Observable<Row> rows = execute(statements);

    //Do work with rows.
  }


  public Observable<ResultSet> executeAndReturnResultSet(Statement statement) {
    return Observable.from(session.executeAsync(statement));
  }


  public Observable<Row> execute(List<Statement> statements) {
    List<Observable<ResultSet>> resultSets = Lists.transform(statements, this::executeAndReturnResultSet);
    return Observable.merge(resultSets).flatMap(Observable::from);
  }
}

Alternative

Otherwise you could use IN like the following CQL:

SELECT * FROM multi_partition_key WHERE id='1' AND name IN ('abc','xyz');

For binding to a prepared statement in the Java Driver.

String id = "1";
List<String> names = ['abc','xyz'];

PreparedStatement sel = session.prepare("SELECT id, name, value FROM table WHERE id=? AND name IN ?;");
session.execute(sel.bind(id,name));