CODEWITHSUNDEEP
cassandra
Mangat Rai Modi
Mangat Rai Modi

Reputation: 5706

Select rows where a column is null in Cassandra

I have following table in Cassandra (CQL spec 3.3.0 ):-

Users: <a,b,c,d>, Primary key a Int, B: Map<Int,Date>

Where Users table has column names as a,b,c,d. Now I want to select all the columns for the rows where column b is null. I did following query:-

select from Users where b = null;

but this fails with error = "message="Invalid null value in condition"

  1. The answer here:- null doesn't exists clarifies the semantics of null but still its not clear how do I select the columns which are not present in a row?
  2. Here Nulls in Cassandra an answer claims null to be supported by latest cassandra, but looks like it doesn't work for maps.

Is there any way to solve it? Or should I select all the columns from all the rows and manually check null programmatically, which of course would be very inefficient for a large database.

Upvotes: 5

Views: 12056

Answers (1)

Jim Meyer
Jim Meyer

Reputation: 9475

Cassandra doesn't support querying based on null, even for secondary indexes.

You could add another field to your table called something like "uninitialized" as a boolean that you set to true when you first insert the row, and you'd create a secondary index on the "uninitialized" field.

When you set the map to something, you would set "uninitialized" to false. Then to find the rows where the map is null, you'd query for rows where "uninitialized" is true.

Upvotes: 4

Related Questions