Using Positional Parameters for queries with IN or USE KEYS statements

Java SDK
, ,
1

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 = Select.select(“*”).from(i(‘bucket’)).where(x(‘docId’).in(x(JsonArray.from(docList));

2

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",
  QueryOptions.queryOptions()
    .parameters(JsonArray.from(
      JsonArray.from("airline_10", "airport_1254"),
      JsonArray.from("airline", "airport")
    ))
);
2 Likes
3

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.

4

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

5

@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”,
QueryOptions.queryOptions()
.parameters(JsonArray.from(docId,JsonArray.from(docIdList)));

6

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