N1QL group by issue

SQL++
,
1

Hi,

Below is my use case:
I have documents in the structure shown below:
Doc 1:
{
“id”: “014dee77-c99e-4b02-8a5d-956bf72751ef”,
“country”: “UK”,
“typeId”: 1234,
“saleswovat”: 4315.09
}

Doc 2:
{
“id”: “014dee77-c99e-4b02-8a5d-956bf72751ef”,
“country”: “UK”,
“typeId”: 1234,
“saleswovat”: 4315.09
}

Doc 3:
{
“id”: “014dee77-c99e-4b02-8a5d-956bf72751eg”,
“country”: “US”,
“typeId”: 1234,
“saleswovat”: 4315.09
}

And I want to aggregate the data from the documents - Group the documents by country and so SUM aggregation on the “saleswovat” and return the result as below

[
{
“country”:“UK”,
“saleswovat”:8630.18
},
{
“country”:“US”,
“saleswovat”:4315.09
}
]

When I try running the following query:
@Query("SELECT country, SUM(saleswovat) as sales FROM #{#n1ql.bucket} where typeId= $1 group by country")

I get the following error:

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

To fix this I added the following to the query:
@Query("SELECT META().id AS _ID, META().cas AS _CAS, country, SUM(saleswovat) as sales FROM #{#n1ql.bucket} where typeId= $1 group by country")

Then I get the following error:

org.springframework.data.couchbase.core.CouchbaseQueryExecutionException: Unable to execute query due to the following n1ql errors:
{“msg”:“Expression (meta().id) must depend only on group keys or aggregates.”,“code”:4210}

Then I did the following:
@Query("SELECT META().id AS _ID, META().cas AS _CAS, country, SUM(saleswovat) as salesWoVat FROM #{#n1ql.bucket} where holdingkey= $1 group by country, meta().id AS _ID, meta().cas AS _CAS")

But this group data on id level, so instead of getting 1 document with the aggregated sum, I get multiple documents.

Can someone suggest a way through which this can be done

2

This error might be coming from spring data. The query it self right, Aggregation queries can only project GROUP expressions and aggregates.

If you want you can project constants in place of those fields.
@Query(“SELECT country AS _ID, 0 AS _CAS, country, SUM(saleswovat) as sales FROM #{n1ql.bucket} where typeId= $1 group by country”)

3

As you mentioned the error is from Spring data. I tried doing it through Couchbase Template as shown below and it started working:

Statement statement = select("SELECT country, SUM(saleswovat) as sales")
                .from("bucketname")
                .where(x("typeId").eq(123))
                .groupBy(country);
        SimpleN1qlQuery query = N1qlQuery.simple(statement);
        N1qlQueryResult results =  couchbaseTemplate.queryN1QL(query);

        for (Iterator<N1qlQueryRow> itr1 = results.iterator(); itr1.hasNext();) {
            JsonObject jsonObject = itr1.next().value();
            countryAttributes = objectMapper.readValue(jsonObject.toString(), Country.class);
            analyticsResponse.add(countryAttributes);
        }