Custom N1QL query with Couchbase and Spring Boot

Java SDK
1

I have written a custom repository to get the results using couchbase’s N1QL. Here is my code,

@Override
    public List<Person> findAll(int age) {
        String statement = "select * from bucket_name where _class = 'com.mine.test.model.Person' and age>"+age;
        SimpleN1qlQuery query = N1qlQuery.simple(statement);
        List<Person> list=couchbaseTemplate.findByN1QL(query, Person.class);
        return list;
    }

But I am getting below error,

Unable to retrieve enough metadata for N1QL to entity mapping, have you selected _ID and _CAS?

Any help ?

2

Try this

String statement = “select *, META().id AS _ID, META().cas AS _CAS from bucket_name where _class = ‘com.mine.test.model.Person’ and age>”+age;

3

Thank you. it’s working.
But Why should I use these two values? Can’t we get rid of them?
Any Idea?

4

may be due to person.class . cc @ingenthr

5

I believe the requirement for the ID and CAS is owing to the fact that you may mutate the object, so we need a way to reference it back to the underlying item. When you build through query derivation and Spring Data repositories, that’s done pretty transparently to you, but when you implement a custom repository you’ll need to add that directly.

Is that correct @subhashni?

6

Yes as @ingenthr mentioned, those values are required for mapping the values to key and version fields. Those fields are translated from the entity fields to id and cas when mutating the entity’s couchbase json document. For the custom repository, you can also do

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

1 Like
7

how to use group by in this?

8

How to use group by?

9

Helo @subhashni , as @akshat7593 said how to use groupby?

Let’s say we have a n1ql like this with the intention of get some information:
SELECT fa, fb, _class AS clazz, COUNT(*) AS num \ FROM #{#n1ql.bucket} \ WHERE (_class = "A" OR _class = "B") \ AND status = X \ AND ... \ GROUP BY fa, fb, _class;
However as you see this data that we want to map into a Java Object are no a real Couchbase Document but something like a view just to do some report…

When we try to execute this n1ql from de java api we get CouchbaseQueryExecutionException: Unable to retrieve enough metadata for N1QL to entity mapping, have you selected _ID and _CAS?

Is there a way if collect the information from a java API using a custom n1ql like that (where neither id or cas exist)?