1

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]}}

Aaron
  • 55,518
  • 11
  • 116
  • 132
iamvijay
  • 21
  • 2
  • Would you mind posting the output of running `DESC TABLE mykeyspace.mytable;` from the Astra DB UI -> CQL Console screen? – Madhavan Aug 17 '23 at 13:35
  • IF you're not using Astra DB, what's the version of Cassandra you're running? Plus, what version of Stargate are you using? – Madhavan Aug 17 '23 at 13:47

1 Answers1

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:

Madhavan
  • 758
  • 4
  • 8