Couchbase parameterized N1QL query IN statement

Question

Using com.couchbase.client, java-client version 2.2.7 I have been unable to get a n1ql query working that uses an IN statement with multiple items see my example query and java code below

public int getCountForDuration(Long startTime, Long endTime, String ids){
    JsonObject placeHolders = JsonObject.create().put("ids", ids).put("startTime", startTime).put("endTime", endTime);
    N1qlQuery query = N1qlQuery.parameterized(COUNT_STATEMENT, placeHolders)            
    N1qlQueryResult result = bucket.query(query);
    ...
}

public static final String COUNT_STATEMENT = "select count(*) as count " +
            "from bucketName " +
            "where docType = 'docId' " +
            "and (id IN [$ids]) " + <----- OFFENDING LINE
            "and publishTimestamp between $startTime and $endTime";

I've tried setting ids using ('), ("), and (`) such as:

ids = "'123', '456'";
ids = "\"123\" , \"456\";
ids = "`123`,`456`";

None of these are working when there are multiple ids however if there is only one such as ids = "'123'" it works fine. Also my query works if I use it using CBQ on the terminal.

My question is this how do I crate a parameterized N1QL query which can take multiple items in an IN statement?

misplaced the brackets should be "and ( id IN ([$ids]) ) " – Stavm Aug 02 '16 at 04:52 — Stavm, Aug 02 '16 at 04:52

score 5 · Accepted Answer · answered Aug 02 '16 at 07:48

5

Removing the brackets around the $ids in the statement and putting the actual ids into placeholders as a JsonArray object should work:

JsonObject placeHolders = JsonObject.create()
    .put("ids", JsonArray.from("id1", "id2", "id3"))
    .put("startTime", startTime)
    .put("endTime", endTime);

answered Aug 02 '16 at 07:48

Simon Baslé

27,105
5
69
70

Hey Simon can you take a look at this question http://stackoverflow.com/questions/38845615/n1ql-query-times-out-on-aws-server couchbase collectInfo logs here https://s3.amazonaws.com/cb-customers/TE2/ thanks in advance. – Marquis Blount Sep 10 '16 at 09:21
How do I set such placeholder in Spring Data? eg. I have: `@Query("#{#n1ql.selectEntity} USE KEYS $ids") Collection findByIdIn(@Param("ids") List ids);` but calling it via Http I get `"Unsupported type for JsonArray: class java.util.ArrayList"` the only way I could make it working is: `@Query("#{#n1ql.selectEntity} USE KEYS $ids") Collection findByIdIn(@Param("ids") JsonArray ids);` and then defining a custom objectMapper to get a JsonArray from a rest call. Is there a better way? – rashtao Nov 20 '16 at 17:55
I mean using @RepositoryRestResource – rashtao Nov 20 '16 at 18:07
If you're just doing a plain USE KEYS then you might as well use the CrudRepository's `findAll(Iterable)` method – Simon Baslé Nov 20 '16 at 18:11
The query I am using is just for the sake of example. In general I could have custom queries where I need to map an `Iterable` coming from an http call in `@RepositoryRestResource` to query parameter. So currently I see no way to get it working (others that using a custom objectMapper). – rashtao Nov 25 '16 at 08:48

Couchbase parameterized N1QL query IN statement

1 Answers1

Linked