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));
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
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”,
QueryOptions.queryOptions()
.parameters(JsonArray.from(docId,JsonArray.from(docIdList)));
Yes, I would expect that to work, assuming the documents have fields called “docId1” and “docId2”.