Custom select count(1) query failed with SDK 3

Java SDK
, ,
1

Hi,

Someone can help me why Select count query failed with SDK 3.* but worked perfectly with SDK 2.*.

Original Query:
SELECT COUNT(1) as count FROM #{#n1ql.bucket} WHERE #{#n1ql.filter} AND priceChangeIntentId = $1

Exception:
com.couchbase.client.core.error.CouchbaseException: query did not project __id. Either use #{#n1ql.selectEntity} or project __id and __cas

When I added __cas and __id in select clause it throw different exception:

Query 2:
SELECT COUNT(1) as count, META(#{#n1ql.bucket}).id as __id, META(#{#n1ql.bucket}).cas as __cas FROM #{#n1ql.bucket} WHERE #{#n1ql.filter} AND priceChangeIntentId = $1

Exception:
com.couchbase.client.core.error.PlanningFailureException: The server failed planning the query {“completed”:true,“coreId”:“0xa0cd09ab00000001”,“errors”:[{“code”:4210,“message”:"Expression (meta(demo).id) must depend only on group keys or aggregates

Regards,
Vishal

2

Aggregate/Group queries can’t project arbitrary fields (it must be aggregates or group expressions or constants).

try with constants as workaround your issue

SELECT COUNT(1) as count, "xxx" AS __id, 1234567 AS __cas
FROM #{#n1ql.bucket} 
WHERE #{#n1ql.filter} AND priceChangeIntentId = $1
3

It worked, Thank you for your help!!