How to write Couchbase N1QL query in spring data?

Java SDK
1

I want to write a query to find a list of strings which contains sub-strings, e.g

 "xyz-1",
 "xyz-2",
 "xyz-3",
 "xyz-4".....

Above is list of Strings i need to find but i have input as xyz only and not the fullname xyz-1.

In couchbase server i have implemented the query as below,

SELECT * FROM test
WHERE ANY v IN namelist SATISFIES v LIKE '%xyz%' END;

which will give me all the list name containing xyz. But implementing in Spring boot application its not working.

Below is my spring boot @query menthod

@Query("Select * from `test` where #{#n1ql.filter} And ANY v In namelist SATISFIES v Like '%$1'% END within #{#n1ql.bucket}")
List<String> findBynameList(String name);

and below is my pojo class ,

    @Id
    private String car_id;
    @Field
    @NotNull
    private String name;
    @Field
    private List<String> namelist;
2

Right side of the LIKE must be string or query named/positional parameters of string.

If query named/positional parameters inside the string will not be replaced. You have ‘%$1’% , it will look for $1 not value.

If you want look for value write like this

v LIKE $1    ===> supply $1 as "%actualvalue%"
v LIKE "%" || $1 || "%"
4 
v LIKE "%" || $1 || "%"

This worked for me, but can you explain in details why we need to add ‘%’ || like this?

5

what if i want only list not the whole json object

@Query(“Select namelist from test where #{n1ql filter} And ANY v In namelist SATISFIES v Like ‘%’ || $1 ‘%’ END within #{n1ql ucket}”)

then it throws error

org.springframework.data.couchbase.core.CouchbaseQueryExecutionException: Unable to retrieve enough metadata for N1QL to entity mapping, have you selected _ID and _CAS?

So after searching i found his post Custom N1QL query with Couchbase and Spring Boot but after writing this query ,

@Query(“Select meta().usedcarimages , meta().id as _ID, meta().cas as _CAS From #{n1ql ucket} where #{n1ql .filter} AND ANY imagename IN usedcarimages SATISFIES imagename Like ‘%’ || $1 || ‘%’ END”)
List< String> findByUsedcarimagesContains(String fileName);

then i cant map directly to List< String> it gives error as above mapping error. so i added class name but only got id value and all other null values.

Cant i fetch values in List< String >?

6

Select usedcarimages , meta().id as _ID, meta().cas as _CAS From

7

right now crated following query,

@Query(“Select ARRAY_SORT(META().usedcarimages)[-1],META().id AS _ID, META().cas AS _CAS from #{n1ql bucket} where #{n1ql .filter} AND ANY imagename IN usedcarimages SATISFIES imagename Like ‘%’ || $1 || ‘%’ END”)
String findByUsedcarimagesContains(String fileName);

and got following error,

“Query returning a simple type are expected to return a unique value, got 2”

that means i am getting data but not getting last value .

8

There is no META().usedcarimages change it to usedcarimages

9

did remove Meta(). but now value increased from 2 to 3,
Query returning a simple type are expected to return a unique value, got 3
after that i removed Array_sort and [-1] then it worked but got all the values not the last one.
also i cant cast value is List< String >