title: complex couchbase query using metadata & group by
body: <p>I am new to Couchbase and kind a stuck with the following problem.<br>
  This query works just fine in the Couchbase Query Editor:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I get this: </p>
  
  <pre><code>[
    {
      "countryCode": "en",
      "total": 145
    },
    {
      "countryCode": "at",
      "total": 133
    },
    {
      "countryCode": "de",
      "total": 53
    },
    {
      "countryCode": "fr",
      "total": 6
    }
  ]
  </code></pre>
  
  <p>Now, using this query in a spring-boot application i end up with this error: </p>
  
  <blockquote>
    <p>Unable to retrieve enough metadata for N1QL to entity mapping, have you selected _ID and _CAS?</p>
  </blockquote>
  
  <p>adding metadata,</p>
  
  <pre><code>SELECT
            meta(p).id AS _ID,
            meta(p).cas AS _CAS,
            p.countryCode,
            SUM(c.total) AS total
            FROM bucket p
  </code></pre>
  
  <p>trying to map it to the following object: </p>
  
  <pre><code>data class CountryIntermediateRankDo(
      @Id
      @Field
      val id: String,
  
      @Field
      @NotNull
      val countryCode: String,
  
      @Field
      @NotNull
      val total: Long
  </code></pre>
  
  <p>)</p>
  
  <p>results in: </p>
  
  <blockquote>
    <p>Unable to execute query due to the following n1ql errors:
    {“msg”:“Expression must be a group key or aggregate: (meta(<code>p</code>).<code>id</code>)“,”code”:4210}</p>
  </blockquote>
  
  <p>Using Map as return value results in: </p>
  
  <blockquote>
    <p>org.springframework.data.couchbase.core.CouchbaseQueryExecutionException: Query returning a primitive type are expected to return exactly 1 result, got 0</p>
  </blockquote>
  
  <p>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.</p>