Using $in operator on Cassandra table via stargate API

Question

I have a Cassandra table and it contains a column named 'marks'. It is not part of the primary key.

Now, I wish to run something like this: select * from mytable WHERE marks in (58,88);

How to achieve this? I want to run the same via Stargate API also. I have tried to disable paging but not work. SAI and SASI index does not seem to help. Here is the Stargate URL I am trying to run. The DB asks me to ALLOW FILTERING

http://localhost:8082/v2/keyspaces/mykeyspace/mytable?where={"marks":{"$in":[58,88]}}

Answer 1

If marks column is not part of the table's primary key, you cannot use in the where clause directly like that in the REST API.

Instead, you would create an index (via the same CQL Console screen/tab) using,

CREATE CUSTOM INDEX mykeyspace_mytable_marks_idx ON mykeyspace.mytable(marks) USING 'StorageAttachedIndex';

Tips: The WHERE clause can be used with other valid search terms: $eq, $in, $lt, $lte, $gt, $gte, $ne, and $exists, if applicable.

Bonus References:

• Working with Indexes
• Postman collection of all APIs (gRPC, REST, GraphQL, and Document)