Using Positional Parameters for queries with IN or USE KEYS statements

Hi, I am trying to use the parameterized query and query options method in the JAVA sdk version 3, and I am wondering what is the best way to go about adding arrays as parameters as used in IN or USE KEY statements.

I originally had something like this (and I understand why this doesn’t work):
String query = “SELECT * FROM ‘bucket’ WHERE docId1 = $1 AND docId2 IN $2”;
QueryOptions qo = QueryOptions.queryOptions().parameters(JsonArray.from(docId1,docId2List));

But when this query is run with this QueryOptions it fails. Is there a way to add a list of strings as a positional parameters or am I going about this the wrong way?

In the java sdk version 2 with the prepared STATEMENT class you could do something like
Statement query =“*”).from(i(‘bucket’)).where(x(‘docId’).in(x(JsonArray.from(docList));

Hi @koldbear, welcome to the Couchbase Forum!

Here’s an example that shows how to use positional arguments with USE KEYS and IN:

QueryResult result = cluster.query(
  "select * from `travel-sample` use keys $1 where type in $2",
      JsonArray.from("airline_10", "airport_1254"),
      JsonArray.from("airline", "airport")

Hi @david.nault,

Thanks for the response. Would you have any advice to deal with the scenario where the size of the list can change. Inputting the variable in a nested format like this only works if you know exactly how many values are in the list beforehand. You wouldn’t be able to create dynamic code given list a.

JsonArray has an add method. Or you can pass a List to JsonArray.from.

@david.nault so should something like this work, where docId is a string and docIdList is a List

QueryResult result = cluster.query(
"SELECT * FROM ‘bucket’ WHERE docId1 = $1 AND docId2 IN $2”,

Yes, I would expect that to work, assuming the documents have fields called “docId1” and “docId2”.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.