Spring Data Couchbase Aggregate function

Aggregate

I tried to use Aggregate function (like SUM, AVE etc..) in my work.

I use Spring Data Couchbase and write Custom query and try

I tried several ways but following errors are comming

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

It means that mapping entity was wrong, or there are no _ID, and _CAS column in your entity

_ID and _CAS

What is both? Couchbase + JPA uses those 2 keys as kind of index. To retrieve data faster, those 2 are very important.

As 2 data, we can use meta(‘bucketname’).id, meta(“bucketname”).cas

Aggregate working?

Unfortunately, no. _ID and _CAS blocks aggregate function in JPA. Because those 2 keys are based on record. If we use Aggregate function, each record lost

How to resolve this?

Only use N1Query directly using template is quite simple solution.

Samples

Couchbase setting and entity code explanation is : (Spring Batch Couchbase JPA)

Focus on only query build part.

In this time, I use SUM and GROUP BY

Explain 2 ways

1. Simple Query String

SimpleN1qlQuery query = N1qlQuery.simple("SELECT member, SUM(point) as point FROM newot WHERE _class = 'com.daiji110.sample.couchbase.model.SuperPoint' GROUP BY member");
N1qlQueryResult results =  couchbaseTemplate.queryN1QL(query);

for (Iterator<N1qlQueryRow> itr1 = results.iterator(); itr1.hasNext();) {
     JsonObject jsonObject = itr1.next().value();
     String member = jsonObject.getString("member");
     Integer point = jsonObject.getInt("point");
     System.out.println(String.format("%s : %d", member, point));
}

2. Using Query builder

Statement statement = select("member, SUM(point) as point")
                .from("newot")
                .where(x("_class").eq(s("com.daiji110.sample.couchbase.model.SuperPoint")))
                .groupBy("member")
                .limit(100);
N1qlQueryResult results = couchbaseTemplate.queryN1QL(N1qlQuery.simple(statement));
for (Iterator<N1qlQueryRow> itr1 = results.iterator(); itr1.hasNext();) {
      JsonObject jsonObject = itr1.next().value();
      String member = jsonObject.getString("member");
      Integer point = jsonObject.getInt("point");
      System.out.println(String.format("%s : %d", member, point));
}

To prevent problem, 2nd way is better