CODEWITHSUNDEEP
cassandracql
Rajesh Gaur
Rajesh Gaur

Reputation: 287

Is it possible to use complex boolean logic in a CQL query?

In cassandra , for a table like this 

CREATE TABLE test.TestTable3 (
   PK1 int,
   PK2 int,
   CK3 int,
   CK4 int,
   CK5 text,
   CK6 int,
   CK7 int,
   Dump text,
   PRIMARY KEY ((PK1,PK2,CK3),CK4,CK5,CK6,CK7)
);

How to do query something like ie using or with partial or 

Select * from testtable3 where Pk1=1 and Pk2=2 and Ck3 =2 and Ck4 =4 and (( CK=5 ="ABC" 
and  CK6=1) or ( CK=5 ="BBC" and CK6=1))

regards

Upvotes: 2

Views: 833

Answers (1)

Aaron
Aaron

Reputation: 57748

First of all, let me just say that Cassandra does not support complex, ad-hoc queries. The Cassandra Query Language (CQL) is a subset (not an implementation) of SQL. Therefore, you cannot do everything in CQL that may work in SQL.

Secondly, CQL does not support the OR operator. However, you can use the IN operator in some cases. Essentially, IN only works on the partitioning key. And only on the last partitioning key, if using a composite partitioning key (as you are). Be warned though, that as Cassandra is designed to serve reads on specific, primary key queries, it is not recommended to rely on the use of IN. See SELECT: When not to use IN for more information.

Third, unlike a relational database, in Cassandra it is good practice to denormalize/replicate your data while building it into multiple tables to support each desired query. With that in-mind, you could create a query table like this:

CREATE TABLE TestTable4 (
   PK1 int,
   PK2 int,
   CK3 int,
   CK4 int,
   CK5 text,
   CK6 int,
   CK7 int,
   Dump text,
   PRIMARY KEY ((PK1,PK2,CK3,CK4,CK6,CK5),CK7)
);

Note how I have extended the partition key, and changed the order of CK5 and CK6. Given this table structure, this query works:

aploetz@cqlsh:stackoverflow> SELECT * FROM testtable4 WHERE pk1=1 AND pk2=2 AND ck3=3
              ... AND ck4=4 AND ck6=1 AND ck5 IN ('ABC','BBC');

 pk1 | pk2 | ck3 | ck4 | ck6 | ck5 | ck7 | dump
-----+-----+-----+-----+-----+-----+-----+---------
   1 |   2 |   3 |   4 |   1 | ABC |   7 | row ABC
   1 |   2 |   3 |   4 |   1 | BBC |   7 | row BBC

(2 rows)

Note: I used this structure to show you how Cassandra query-based-modeling works. I have no idea if this would work with your overall application or data, so take that for what it's worth.

Upvotes: 3

Related Questions