CODEWITHSUNDEEP
sqlspring-bootgroup-bycouchbase
theuema
theuema

Reputation: 117

complex couchbase query using metadata & group by

I am new to Couchbase and kind a stuck with the following problem.
This query works just fine in the Couchbase Query Editor:

SELECT
            p.countryCode,
            SUM(c.total) AS total
      FROM bucket p
      USE KEYS (
        SELECT RAW "p::" || ca.token
          FROM bucket ca USE INDEX (idx_cr)
          WHERE ca._class = 'backend.db.p.ContactsDo'
            AND ca.total IS NOT MISSING
            AND ca.date IS NOT MISSING
            AND ca.token IS NOT MISSING
            AND ca.id = 288
          ORDER BY ca.total DESC, ca.date ASC
          LIMIT 20 OFFSET 0
      )
      LEFT OUTER JOIN bucket finished_contacts
        ON KEYS ["finishedContacts::" || p.token]
      GROUP BY p.countryCode ORDER BY total DESC

I get this: 

[
  {
    "countryCode": "en",
    "total": 145
  },
  {
    "countryCode": "at",
    "total": 133
  },
  {
    "countryCode": "de",
    "total": 53
  },
  {
    "countryCode": "fr",
    "total": 6
  }
]

Now, using this query in a spring-boot application i end up with this error:

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

adding metadata,

SELECT
          meta(p).id AS _ID,
          meta(p).cas AS _CAS,
          p.countryCode,
          SUM(c.total) AS total
          FROM bucket p

trying to map it to the following object: 

data class CountryIntermediateRankDo(
    @Id
    @Field
    val id: String,

    @Field
    @NotNull
    val countryCode: String,

    @Field
    @NotNull
    val total: Long

)

results in:

Unable to execute query due to the following n1ql errors: {“msg”:“Expression must be a group key or aggregate: (meta(p).id)“,”code”:4210}

Using Map as return value results in:

org.springframework.data.couchbase.core.CouchbaseQueryExecutionException: Query returning a primitive type are expected to return exactly 1 result, got 0

Clearly i missed something important here in terms of how to write proper Couchbase queries. I am stuck between needing metadata and getting this key/aggregate error that relates to the GROUP BY clause. I'd be very thankful for any help.

Upvotes: 1

Views: 946

Answers (1)

Johan Larson
Johan Larson

Reputation: 1890

When you have a GROUP BY query, everything in the SELECT clause should be either a field used for grouping or a group aggregate. You need to add the new fields into the GROUP by statement, sort of like this:

SELECT
   _ID,
   _CAS,
   p.countryCode,
   SUM(p.c.total) AS total
FROM testBucket p
       USE KEYS ["foo", "bar"]
  LEFT OUTER JOIN testBucket finished_contacts
     ON KEYS ["finishedContacts::" || p.token]
GROUP BY p.countryCode, meta(p).id AS _ID, meta(p).cas AS _CAS 
ORDER BY total DESC

(I had to make some changes to your query to work with it effectively. You'll need to retrofit the advice to your specific case.)

If you need more detailed advice, let me suggest the N1QL forum https://forums.couchbase.com/c/n1ql . StackOverflow is great for one-and-done questions, but the forum is better for extended interactions.

Upvotes: 0

Related Questions