Join Query for working with spring boot

Hi, i am trying to write code on spring boot to use join query.
This is what i have:
“SELECT * FROM #{#n1ql.bucket} AS f LEFT OUTER JOIN #{#n1ql.bucket} AS c ON f.category = meta( c ).id WHERE META(f).id = $1”
List<?> findByQuery(String id);

And I got error like this:

java.lang.NullPointerException\n\tat$ReactiveFindByQuerySupport.lambda$null$1(\n\tat reactor.core.publisher.FluxMap$MapSubscriber.onNext(\n\tat reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onNext(\n\tat reactor.core.publisher.FluxMap$MapSubscriber.onNext(\n\tat reactor.core.publisher.EmitterProcessor.drain(

can someone point out what is wrong with my code? Thanks!

@eln Does your query work from Query Work Bench of the UI?

yes, it works from Query Work Bench.

Hi @ein -

queries need to have __cas and __id projected.

@Query("SELECT 0 as “+TemplateUtils.SELECT_CAS +”, ‘’ as "’ as “+TemplateUtils.SELECT_ID+” , MAX(…

public class TemplateUtils {
public static final String SELECT_ID = “__id”;
public static final String SELECT_CAS = “__cas”;See

I’ve opened NullPointerException in @Query that do not project __cas . Should check for this explicitly · Issue #1097 · spring-projects/spring-data-couchbase · GitHub to handle this better.

Hi @mreiche
do i have to use reactive repository to handle the join query or can i just use the couchbase repository?

Either will work. The CouchbaseRepository uses the ReactiveCouchbaseRepository.

it works, thanks for helping!

Hey @mreiche @eln
I am trying to work out a join query on Spring boot. I have two documents in the same bucket…one being message1 and other being user1.(‘1’ is the user id). message1 document has a field called user_id.
I got to join these two documents based on common user_id.
my query goes like-
@Query(“Select *,META(#{#n1ql.bucket}).id AS __id, META(#{#n1ql.bucket}).cas AS __cas FROM #{#n1ql.bucket} AS d1 JOIN #{#n1ql.bucket} AS d2 ON KEYS ‘user’ || d1.user_id where d1.docType=$1”)
List<?> getMsgWithUsrPrfl(String docType)

This is giving me the following exception -
com.couchbase.client.core.error.ParsingFailureException: Parsing of the input failed

Can you please help me with this?

The server cannot parse the query due to syntax error.
Run your query in the couchbase web console for more precise diagnostics.

Thanks for responding @mreiche .
A similar query is working on couchbase query bench but doesn’t work in java.
I am unable to find out where am I syntactically going wrong.
would be great if you could highlight something here.

@arushi In the query you posted I see opening double quote but no closing double quote.

@Kevin.Cherkauer oops! that was a typo here. It has been written properly in the code.

@arushi I do not have expertise in the Java SDK, so I probably cannot help further here, but it occurred to me that having a typo implies the query you showed was retyped rather than cut-and-pasted from your actual code, so there is potential for other typos, and possibly you corrected the syntax error reflexively when you originally retyped the query into the posting but that correction is not in the actual code. Hopefully someone from the JSDK side can help further if that isn’t the issue.

I have an observation. I tried two things-

  1. When I write - @Query(“Select *,META(#{#n1ql.bucket}).id AS __id, META(#{#n1ql.bucket}).cas AS __cas FROM #{#n1ql.bucket} where d1.docType=$1”)…this works

But when I write
2) @Query(“Select *,META(#{#n1ql.bucket}).id AS __id, META(#{#n1ql.bucket}).cas AS __cas FROM #{#n1ql.bucket} AS d1 where d1.docType=$1”)…It gives ParsingFailureException

@Kevin.Cherkauer Sure, thank you for your inputs.

Based on previous post, May not have properly translated check out. enable tracing in client side and see what query it generated. (Is it making double AS (i.e. bucket AS bucket AS d1 ) )

Also try replace #{#n1ql.bucket} with actual bucket and see if that works.

Another non related is.
Once you Alias from clause, META argument must be alias (META(alais) …) otherwise you will not get right values.

Hey @vsr1, thanks for replying
I tried replacing #{#n1ql.bucket} with the actual bucket name, now it gives java.lang.NullPointerException: null.

“A similar query is working on couchbase query bench but doesn’t work in java.”
That would indicate they are different. The difference is what is causing the issue.

Amazing! Just writing meta().id works fine.
So now-
@Query(“Select *,META().id AS __id, META().cas AS __cas FROM #{#n1ql.bucket} AS d1 where d1.docType=$1”) works
Adding JOIN #{#n1ql.bucket} AS d2 ON KEYS ‘user’ || d1.user_id join clause gives parsing exception yet again.

1 Like

JOIN query will give Ambiguous error without argument to META(). You need to give one of the alais

@Query(“Select *,META(d1).id AS __id, META(d1).cas AS __cas FROM #{#n1ql.bucket} AS d1 JOIN #{#n1ql.bucket} AS d2 ON KEYS ‘user’ || d1.user_id where d1.docType=$1”)

If you need d2 change d2.