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
コメント