Join Query for working with spring boot

Java SDK
21

@vsr1 I need the join of d1 and d2 which are two separate documents inside same bucket.
So, could you please advise which argument should be there inside META( )?

22

META() is associated bucket documents. When JOIN two documents you have two values. one coming from d1, other coming from d2. Which one to project is your call. I don’t know how you use.
Use d1 and see if the query works. Later you decided. Or project both and change alias of one of that

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

23

I tried what you suggested here but it gives Parsing failure exception

24

Replace #{#n1ql.bucket} with actual bucket and try. If that also fails try exact query in query workbench.

25

Replaced with actual bucket name.
That exact query works in workbench but not in java.

26

Java SDK, spring boot is NOT my expertise. If you can enable client side trace and find out what SQL statement it sent to N1QL we can find out why it gives parsing error.

28

Yes. You’ll get the exception below. See where it says "syntax error - at (single-quote)? The single-quotes around user should be either double-quotes (for the string “user”), or back-ticks (for the property user). Also, the arg to meta() should be either d1 or d2. It cannot be #{#n1ql_bucket) as that is ambiguous.

com.couchbase.client.core.error.ParsingFailureException: Parsing of the input failed {"completed":true,"coreId":"0xc8a30efb00000001","errors":[{"code":3000,"message":"syntax error - at ‘"}],"idempotent":false,"lastDispatchedFrom":"10.144.210.1:54261","lastDispatchedTo":"10.144.210.101:8093","requestId":24,"requestType":"QueryRequest","retried":0,"service":{"operationId":"5fac75cd-da19-4954-a4fd-b176bc0c5b63","statement":"Select *,META(d1).id AS __id, META(d1).cas AS __cas FROM my_bucket AS d1 JOIN my_bucket AS d2 ON KEYS ‘user’ || d1.user_id where d1.docType=$1","type":"query"},"timeoutMs":75000,"timings":{"dispatchMicros":1769,"totalDispatchMicros":1769,"totalMicros":3277}

30

In addition to @mreiche comments.

'user' || d1.user_id
If use double quotes you need to escape because outside double quotes.
single quotes inside double quotes might be ok. The issue might single quote is smart quotes (smart quotes are not valid).

31

Thanks a lot @mreiche & @vsr1 . It works
I no longer get Parsing Failure Exception.

32

when ı try to fetch list of another entity ı am getting an “parsing of the input failed” error

my query on user repository is :

@Query("SELECT actions FROM #{#n1ql.bucket} WHERE userName = $1 AND #{#n1ql.filter}")
    List<ActionsDto> getActionsByUserName(String userName);

what am ı doing wrong? can you help mi with that please @mreiche @vsr1

33

  1. The “Parsing of the input” error should give more details. You can try executing the query shown in the error (after substitution of #{#n1ql.bucket} and #{#n1ql.filter}) in the web console - it could be that those contain special characters.

  2. See above where I explained the query must project __cas and __id? You’ll also need that once you fix the parsing error.