Writing custom n1ql queries in spring

Question

I'm trying to query a spring-data couchbase repository using N1QL queries. I have two doubts:

I'm using @Query annotation to generate queries, my code looks like this:

@Query("#{#n1ql.selectEntity} WHERE $0 = $1 AND #{#n1ql.filter}")
    public Page<GsJsonStore> matchJson(String term, String value, Pageable pageable);
    //Query
    Page<GsJsonStore> p = repo.matchJson("_object.details.status", "ready", pg);

This query doesn't return any results. However, when I run the same query (below) in cbq I get the desired result:

select * from default where _object.details.status = 'ready';

How can I view the query string generated by the Couchbase repository? I'm using spring-boot. Am I correct in using the @Query annotation for this use-case?

Also, how can I perform n1QL queries on CouchbaseOperations template? I know that there's a findByN1QL method, but I didn't find any good documentation about it. Could someone please explain how to use this?

Answer 1

The query looks ok. You did persist your GsJsonStore entities using the Spring Data Couchbase repository did you?

In order to log all the queries generated and executed by the framework (including inline queries like in your case), you can configure the logger like so in a logback.xml configuration:

<logger name="org.springframework.data.couchbase.repository.query" level="debug"/>

You'll see that the query that got executed and the query that you ran in cbq are not the same, since at least you didn't use a WHERE clause.

In CouchbaseOperations there are two methods relative to N1QL queries:

1. findByN1QL: this expects specific structure of the query, in order to make sure all data necessary to correct deserialization of a Spring Data annotated entity is selected (which is the purpose of the #n1ql.selectEntity and #n1ql.filter SpEL).
2. findByN1QLProjection is more free-form. If Jackson can deserialize the results of the provided query to the requested Class, then it will. As such, the SELECT clause is much less implicitly restricted in this method.

To use both, you must pass in a N1qlQuery object from the SDK. Such queries can be constructed using factory methods of the N1qlQuery class, for example:

//a version of the query that is constructed from positional parameters
N1qlQuery queryWithParameter = N1qlQuery.parameterized("SELECT name FROM `beer-sample` WHERE name LIKE $0", JsonArray.from("%dog%"));

//let Spring Data execute the query, projecting to the String class
List<String> beerNamesContainingDog = template.findByN1QLProjection(queryWithParameter, String.class);