Couchbase Java LIKE query giving syntax error - at %!(NOVERB)

Question

Below is the java code:

QueryResult result = bucket.defaultScope().query("select * from _default d where d.name like %$nameParam%",
            QueryOptions.queryOptions()
                    .parameters(JsonObject.create().put("nameParam", param)));

Note: "_default" is my collection and the query works if I remove "like" and parameters options -> (select * from _default)

Below is the error coming :

com.couchbase.client.core.error.ParsingFailureException: Parsing of the input failed 
{"completed":true,"coreId":"0x3c3d77b600000001","errors":[{"code":3000,"message":"syntax error - at %!(NOVERB)"}],"httpStatus":400,"idempotent":false,"lastDispatchedFrom":"127.0.0.1:57882","lastDispatchedTo":"127.0.0.1:8093","requestId":11,"requestType":"QueryRequest","retried":0,"service":{"bucket":"CartTest","operationId":"976e0085-c83f-4732-ad8e-7f8e258f52b8","scope":"_default","statement":"select * from _default d where d.name like %$nameParam%","type":"query"},"timeoutMs":75000,"timings":{"dispatchMicros":5828,"totalDispatchMicros":5828,"totalMicros":70213}}

Currently I don't want to use FTS , just want to know the correct syntax for like query ?

How are we gonna fix your query if we don't know what it is trying to do? The keyword`like` makes the query a FTS query. If you don't want to want to do an FTS search, don't use it. — G. Blake Meike, Jan 24 '22 at 17:47
@G.BlakeMeike : I was trying to make a query like "select * from _default d where d.name like %John%" — akash verma, Jan 25 '22 at 18:31
@G.BlakeMeike: Again as per my original question - just want to know the correct syntax for like query. Does even couchbase supports "%wildcard%" kind of support? or it is only supported by FTS? — akash verma, Jan 25 '22 at 18:41

score 1 · Accepted Answer · answered Jan 26 '22 at 05:04

1

You would need to do, "select * from _default d where d.name like '%' || $nameParam || '%'" where "||" is string concat. What goes after "like" should be a string and "$" parameter cannot be inside string literal.

answered Jan 26 '22 at 05:04

J.Zhao

44
2

Mark Day · Answer 2 · 2022-09-30T19:22:20.377

I've been spending alot of time trying to get a reactive get items working with couchbase.

I'm using as of writing the latest couchbase gradle implementation

    implementation 'org.springframework.boot:spring-boot-starter-data-couchbase:2.7.3'
implementation 'com.couchbase.client:java-client:3.3.4'

Simple working example where Item is a POJO class

Service Class

public Mono<List<Item>> getItems(String user) {
    return storage.getItems(user).flatMapMany(r -> r.rowsAs(Item.class)).collectList();
}

Storage Class

public Mono<ReactiveQueryResult> getItems(String user)
{
   Cluster cluster = getCluster();
   return cluster.reactive().query(
           "SELECT `pid`, `name`, `type` FROM `MY_BUCKET` WHERE Meta().id LIKE \"" + user + "%\"");
}

Something to note here about building your query string. Simply go into the couchbase web console (localhost:8091) under the query tab and construct the query. Then you just simply have to paste it into the code between the quotes "". Don't forget that the query quotes must be prefixed with \

Then you can call it like this

List<Item> items = inventory.getItems(user).block();

Don't forget to block it to return the List.

Hope it helps someone get started.

Couchbase Java LIKE query giving syntax error - at %!(NOVERB)

2 Answers2