couchbasesql++spring-data-couchbase

Spring data Couchbase #n1ql.fields query

I'm trying to make a N1QL based query on Spring Data Couchbase. The documentation says

#n1ql.fields will be replaced by the list of fields (eg. for a SELECT clause) necessary to reconstruct the entity.

My repository implementation is this one:

@Query("#{#n1ql.fields} WHERE #{#n1ql.filter}")
List<User> findAllByFields(String fields);

And I'm calling this query as follows:

this.userRepository.findAllByFields("SELECT firstName FROM default");

I'm getting this error:

Caused by: org.springframework.data.couchbase.core.CouchbaseQueryExecutionException: Unable to execute query due to the following n1ql errors: {"msg":"syntax error - at AS","code":3000}

After a little bit of researching, I also tryed:

@Query("SELECT #{#n1ql.fields} FROM #{#n1ql.bucket} WHERE #{#n1ql.filter}")

With this query, I don't get an error, I get all the documents stored but only the ID the other fields are set to null, when my query tries to get the firstName field.

 this.userRepository.findAllByFields("firstName");

Anyone knows how to do such a query?

Thank you in advance.

Solution

  • You're misunderstanding the concept, I encourage you to give the documentation more time and see more examples. I'm not sure what exactly you're trying to achieve but I'll throw some examples.

    Find all users (with all of their stored data)

    @Query("#{#n1ql.selectEntity} WHERE #{#n1ql.filter}")
List<User> findAllUsers();

    This will basically generate SELECT meta().id,_cas,* FROM bucket WHERE type='com.example.User'

    Notice findAllUsers() does not take any parameters because there are no param placeholders defined in the @Query above.

    Find all users where firstName like

    @Query("#{#n1ql.selectEntity} WHERE #{#n1ql.filter} AND firstName like $1")
List<User> findByFirstNameLike(String keyword);

    This will generate something like the above query but with an extra where condition firstName like

    Notice this method takes a keyword because there is a param placeholder defined $1.

    Notice in the documentation it says

    #{#n1ql.selectEntity} WHERE #{#n1ql.filter} AND test = $1

    is equivalent to

    SELECT #{#n1ql.fields} FROM #{#n1ql.bucket} WHERE #{#n1ql.filter} AND test = $1

    Now if you don't want to fetch all the data for user(s), you'll need to specify the fields being selected, read following links for more info

    How to fetch a field from document using n1ql with spring-data-couchbase

    https://docs.spring.io/spring-data/couchbase/docs/2.2.4.RELEASE/reference/html/#_dto_projections