Querying cassandra database with an array

Question

I am trying to query my cassandra database to return data from a list of names held on an array server side. This is held as an array. I know the data I am accessing is stored as a string in my database and so I have appended single quotes around it (I have tried with and without this but no luck).

Here is my query.

const arr = ["ukcust1","ukcust2","ukcust5"];
//Here I append single quotes before and after to each string if needed

const query = "SELECT * FROM table_name WHERE name = ?";
client.execute(query, arr, { prepare:true }, function (err, result) {
..//Code
};

What am I missing here? I want the query to be:

SELECT * FROM table_name WHERE name = each of the names in the array 'arr';

Answer 1

If name were a clustering key, then you could query with "in" and "allow filtering" like this:

select * from table_name where name in ('ukcust1','ukcust2','ukcust3') allow filtering

Assuming name is not a clustering key, you could use a clustering key (e.g., date_of_birth) if it made logical sense -- that is, if filtering by date made sense in relation to the name -- like this:

select * from table_name where date_of_birth in (1969, 1972) name in ('ukcust1','ukcust2','ukcust3') allow filtering

If you can't do either of those things, you will need to loop through the array with Javascript (e.g., foreach).

Answer 2

The correct input of the query parameters is an array of values. In this case, it would be an array of parameters containing a single item, that is an array of names.

const arr = ["ukcust1","ukcust2","ukcust5"];
const query = "SELECT * FROM table_name WHERE name = ?";

// Note the array containing a single item
const parameters = [ arr ];

client.execute(query, parameters, { prepare: true }, callback);

See more info in the documentation: https://docs.datastax.com/en/developer/nodejs-driver/3.5/faq/#how-can-i-use-a-list-of-values-with-the-in-operator-in-a-where-clause