We have a method: findCustomerByInstanceId and the signature is as follows:
Optional findCustomerByInstanceId(String instanceId);
What would be the right approach to write the query in the repository class that extends CouchbaseRepository?
Should we go the n1ql query or suggest any other options?
We are currently on Couchbase SDK 3.0.
Any help will be appreciated.
Hello @siddique, can you give us more information about your stack ? What language or framework are you using ?
We are running a Springboot Java application interacting with Couchbase.
@ldoguin We are running a Springboot Java application interacting with Couchbase
@ldoguin Any updates on the above query? Appreciate your help.
That’s all you need to do in spring-data-couchbase. Just define the method. spring-data-couchbase will write the query based on the query name. If instanceId is the document-id, the findById() method is already defined for you.
See https://docs.spring.io/spring-data/couchbase/docs/current/reference/html/#couchbase.repository
@mreiche Thank you for there response. The query that we have associated with the findCustomerByInstanceId(String instanceId) is:
SELECT META(customer).id AS _ID, META(customer).cas AS _CAS, customer.*
FROM as customer UNNEST customer.subscriptions AS subscription
where subscription.instanceId = $1
This seems to fail and this is what we see:
"Received failure from endpoint :query did not project __id. Either use #{n1ql.selectEntity} or project __id and __cas : SELECT META(customer).id AS _ID, META(customer).cas AS _CAS, customer.* FROM as customer UNNEST customer.subscriptions AS subscription WHERE subscription.deleted=false AND subscription.instanceId=$1”
Any suggestions?
It’s expecting the id and cas to be projected using the names shown in the error message (not _ID and _CAS). In the latest versions it will accept either.
Also - if your Customer entity class results in the document being stored with nested subscriptions, then attempting to retrieve with unnested subscriptions will probably not work out in the way you wish.
What would your recommendation be to resolve such a case?
the findById() method is already defined for you.
The same query works fine when I try it out on the Couchbase server directly. I do see the id and can being populated as well. The Subscriptions also are returned. How come this fails when we send it via the query through the Java code? Appreciate if you can explain in more detail.
I see this in the logs:
com.couchbase.client.core.error.CouchbaseException: query did not project __id. Either use #{n1ql.selectEntity} or project __id and __cas : SELECT META(customer).id AS _ID, META(customer).cas AS _CAS, customer.* FROM as customer UNNEST customer.subscriptions AS subscription WHERE subscription.deleted=false AND subscription.instanceId=$1
at org.springframework.data.couchbase.core.ReactiveFindByQueryOperationSupport$ReactiveFindByQuerySupport.lambda$null$1(ReactiveFindByQueryOperationSupport.java:191) ~[spring-data-couchbase-4.3.3.jar!/:4.3.3]
How can we find a nested element in a document with a Couchbase query?
See my answer above:
Right in the error message it says you need to project __id and __cas. In your select statement you have _ID and _CAS. Please fix that.
UNNEST is self JOIN from original document and ARRAY, You are only using ARRAY alias in WHERE clause. Instead of UNNEST use ANY in WHERE clause like below
SELECT META(customer).id AS _ID, META(customer).cas AS _CAS, customer.*
FROM <collection> AS customer
WHERE ANY s IN customer.subscriptions SATISFIES s.deleted=false AND s.instanceId=$1 END;
Remove unwanted subscriptions from projections ARRAY
SELECT META(customer).id AS _ID, META(customer).cas AS _CAS, customer.*,
ARRAY s FOR s IN customer.subscriptions SATISFIES s.deleted=false AND s.instanceId=$1 END AS subscriptions
FROM <collection> AS customer
WHERE ANY s IN customer.subscriptions SATISFIES s.deleted=false AND s.instanceId=$1 END;
Project subscription.* and remove subscriptions (from projection due to customer.*)
SELECT META(`customer` ).id AS _ID, META(`customer` ).cas AS _CAS,
customer.*,
MISSING AS subscriptions,
subscription.*
FROM <collection> AS customer
UNNEST customer.subscriptions AS subscription
WHERE subscription.deleted=false AND subscription.instanceId=$1
Has this query format changed with Couchbase SDK 3.0? The query we had been using was working fine, but once changed the SDK, we have started seeing this issue.
Note, I tried with the new query format you have shared, the number of documents returned is zero.
The earlier query would returned 1 document for the given instanceId.
What earlier query? This one?
" SELECT META(customer).id AS _ID, META(customer).cas AS _CAS, customer.* FROM as customer UNNEST customer.subscriptions AS subscription WHERE subscription.deleted=false AND subscription.instanceId=$1”
If you are happy with that query, why not just change the _ID to __id and _CAS to __cas as indicated in the error message? Or use a later version that accepts _ID and _CAS? The issue of not accepting _ID and _CAS was fixed long ago. add check for _ID and _CAS from spring 3.x (along with __id and __cas) for queries because people keep using them. · Issue #1389 · spring-projects/spring-data-couchbase · GitHub
We tried your suggestion to use this query:
SELECT META(customer).id AS _ID, META(customer).cas AS _CAS, customer.*
FROM AS customer
WHERE ANY s IN customer.subscriptions SATISFIES s.deleted=false AND s.instanceId=$1 END;
I see the same error:
status: FAILED , reason=query did not project __id. Either use #{n1ql.selectEntity} or project __id and __cas : SELECT META(customer).id AS _ID, META(customer).cas AS _CAS, customer.* FROM scps-prod1._default._default AS customer WHERE ANY s IN customer.subscriptions SATISFIES s.deleted=false AND s.instanceId=$1 END;
com.couchbase.client.core.error.CouchbaseException: query did not project __id. Either use #{n1ql.selectEntity} or project __id and __cas : SELECT META(customer).id AS _ID, META(customer).cas AS _CAS, customer.* FROM AS customer WHERE ANY s IN customer.subscriptions SATISFIES s.deleted=false AND s.instanceId=$1 END;
at reactor.core.publisher.FluxFlatMap$FlatMapMain.onNext(FluxFlatMap.java:386) ~[reactor-core-3.4.16.jar!/:3.4.16]
Please fix the query. Thank you.
It’s expecting the id and cas to be projected using the names shown in the error message (not _ID and _CAS).
Right in the error message it says you need to project __id and __cas. In your select statement you have _ID and _CAS. Please fix that.
just change the _ID to __id and _CAS to __cas as indicated in the error message
OR - use a later version of spring-data-couchbase, one of 4.3.10, or any version of 4.4.x, 5.0.x or 5.1.x where the compatibility issue has been fixed.