Unable to use n1ql query in @Query annotation with Max function in spring data couchbase 4.1.5

Hi

I have the following n1ql query which is working fine in web console and also in earlier version [3.2.1.RELEASE] but I get the below error when using spring data couchbase 4.1.5. Kindly suggest where I am doing it incorrectly.

n1ql query:

SELECT MAX([effectiveDateTime,{ `AAAA`} ])[1].* FROM AAAA WHERE _class = "XXXXX"
AND state = "ACTIVE" AND ANY type IN applicableEntities SATISFIES type IN ["YYYYY"] END
AND startDate >= NOW_MILLIS() group by testId

Code:

@Query("SELECT MAX([effectiveDateTime,{#{#n1ql.bucket}}])[1].* " +
            " FROM #{#n1ql.bucket} WHERE #{#n1ql.filter} AND state = $1 "
            + "AND ANY type IN applicableEntities SATISFIES type IN $2 END "
            + "AND startDate <=$3 group by testId")
    Flux<ZZZZ> findZZZZ(String state, JsonArray applicableTypes, long endingAfter);

Exception:

java.lang.NullPointerException: null
at org.springframework.data.couchbase.core.ReactiveFindByQueryOperationSupport$ReactiveFindByQuerySupport.lambda$null$1(ReactiveFindByQueryOperationSupport.java:151) ~[spring-data-couchbase-4.1.5.jar:4.1.5]
at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:106) ~[reactor-core-3.4.3.jar:3.4.3]
at org.springframework.cloud.sleuth.instrument.reactor.ScopePassingSpanSubscriber.onNext(ScopePassingSpanSubscriber.java:88) ~[spring-cloud-sleuth-instrumentation-3.0.0.jar:3.0.0]
at reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onNext(MonoFlatMapMany.java:250) ~[reactor-core-3.4.3.jar:3.4.3]
at org.springframework.cloud.sleuth.instrument.reactor.ScopePassingSpanSubscriber.onNext(ScopePassingSpanSubscriber.java:88) ~[spring-cloud-sleuth-instrumentation-3.0.0.jar:3.0.0]
at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:120) ~[reactor-core-3.4.3.jar:3.4.3]
at reactor.core.publisher.EmitterProcessor.drain(EmitterProcessor.java:491) ~[reactor-core-3.4.3.jar:3.4.3]
at reactor.core.publisher.EmitterProcessor.subscribe(EmitterProcessor.java:209) ~[reactor-core-3.4.3.jar:3.4.3]
at reactor.core.publisher.Flux.subscribe(Flux.java:8156) ~[reactor-core-3.4.3.jar:3.4.3]

Regards,
Venkat

Hi Team

Any update on how this issue can be solved, please??

Regards,
Venkat

Hi @sri_ram
queries need to have __cas and __id projected. As this is an aggregation, there won’t be a meaningful value for them, but they still need to be there.

@Query("SELECT 0 as “+TemplateUtils.SELECT_CAS +”, ‘’ as "’ as “+TemplateUtils.SELECT_ID+” , MAX(…

public class TemplateUtils {
public static final String SELECT_ID = “__id”;
public static final String SELECT_CAS = “__cas”;

@mreiche thanks that works like a charm