Group by Query failed after upgrading to couchbase java client SDK 3.*

Java SDK
, ,
1

Hi,

Can somebody help me on resolving this issue started getting after updating couchabse java SDK from 2.0 to 3.2.5.

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

Query :

SELECT bp.gtin,bp.locationClusterId, MAX([bp.effectiveDateTime,bp.createdDateTime,{’_ID’: META(bp).id,
‘_CAS’: META(bp).cas, bp.effectiveDateTime,
bp.createdDateTime, bp.sellerId,bp.sellingUOM, bp.basePrice, bp.currency,
bp.minimumPrice, bp.productGroup, bp.effectiveDateTimeOffset} ])[2].*
FROM #{#n1ql.bucket} AS bp
WHERE bp._class =‘com.Test’
AND bp.gtin IN $1
AND bp.locationClusterId IN $2
AND bp.effectiveDateTime <= $3
GROUP BY bp.gtin,bp.locationClusterId

Regards

2

Hi!
I’m not exactly sure, but it looks like this is a custom query and it needs to select (or alias) __ID and __CAS fields, for example spring data connector uses META() function to select those values:

SELECT META(`demo1`).id AS __id, META(`demo1`).cas AS __cas,
 `demo1`.* FROM `demo1` WHERE ...

I also noticed a weird call syntax for the MAX function, it feels like there’s a missing closing parenthesis somewhere?

Hope this helps. Please let me know if you need more information and thank you for choosing Couchbase :blush:

3

Hi @chedim ,

Thanks for your quick reply.

After including --id and __cas as part of select clause, I started getting below exception.

com.couchbase.client.core.error.PlanningFailureException: The server failed planning the query {“completed”:true,“coreId”:“0x1aaeb1c900000001”,“errors”:[{“code”:4210,“message”:"Expression (meta(bp).id) must depend only on group keys or aggregates.",“retry”:false}],“httpStatus”:500,“idempotent”:false,“lastDispatchedFrom”:“127.0.0.1:52595”,“lastDispatchedTo”:“localhost:8093”,“requestId”:53,“requestType”:“QueryRequest”,“retried”:0,“service”:{“operationId”:“3c7007bb-ce93-45c5-b148-89b78daa0c47”,“statement”:"SELECT META(bp).id AS __id, META(bp).cas AS __cas, bp.gtin,bp.locationClusterId, MAX([bp.effectiveDateTime,bp.createdDateTime,{’_ID’: META(bp).id, ‘_CAS’: META(bp).cas, bp.effectiveDateTime, bp.createdDateTime, bp.sellerId,bp.sellingUOM, bp.basePrice, bp.currency, bp.minimumPrice, bp.productGroup, bp.effectiveDateTimeOffset} ])[2].* FROM prices AS bp WHERE bp._class =‘com.Test’ AND bp.gtin IN $1 AND bp.locationClusterId IN $2 AND bp.effectiveDateTime <= $3 GROUP BY bp.gtin,bp.locationClusterId ",“type”:“query”},“timeoutMs”:75000,“timings”:{“dispatchMicros”:23502,“totalDispatchMicros”:23502,“totalMicros”:91526}}

Query:

SELECT META(bp).id AS __id, META(bp).cas AS __cas, bp.gtin,bp.locationClusterId, MAX([bp.effectiveDateTime,bp.createdDateTime,{’_ID’: META(bp).id,
‘_CAS’: META(bp).cas, bp.effectiveDateTime,
bp.createdDateTime, bp.sellerId,bp.sellingUOM, bp.basePrice, bp.currency,
bp.minimumPrice, bp.productGroup, bp.effectiveDateTimeOffset} ])[2].*
FROM #{#n1ql.bucket} AS bp
WHERE bp._class =‘com.Test’
AND bp.gtin IN $1
AND bp.locationClusterId IN $2
AND bp.effectiveDateTime <= $3
GROUP BY bp.gtin,bp.locationClusterId

I have used GROUP BY clause in the query. In MAX function I am not getting any syntax error and I believe parenthesis is closed correctly.

Regards,
Vishal

5

Json field names case sensitive. In your original query change __ID, __CAS to lower case and try.
You can also try the query in Query work bench after replacing actual bucket for correct ness.

6

Its working with lowercase __id and __cas.

Thanks @vsr1