FTS with N1QL or API

@WillGardella,@keshav_m

Hi Team,
When i suppose to get the CB v 4.5 with FTS with N1QL or with API, because we are working on this and we eagerly looking for FTS with query or with API,below is the use case we are doing RnD.

  1. Doing text search and have cutoff the document on basis of score with lots of FILTER or CONDITION .
    how to perform this or should i use ES for this.
    or,when we expect this with full support do you guys have any time line to release this.

Hi @rkumar

Do you need the FULL capability of FTS (scoring, stemming, etc) or would you like equivalent of SQL’s LIKE predicate with N1QL?

Thanks, Keshav.

Hi @keshav_m,

Thanks for reply on this.
YES, we need full capability of FTS either with API or with N1QL.
we need to search the TEXT in couchbase bucket with condition or filtering.
e.g;
search “good doctor in abc hospital on kidney treatment where date>‘2010-01-01’ and category in[1,2,3…] and location='San Francisco '…”.
so afer where we need to put condition that user can check at run time from UI and we need to show them relevant contents,we can use CB scoring as well for cutoff.

let me know if you need more details for use case.
Thank you !

@rkumar the official Couchbase SDKs, including Java, provide first class APIs for both N1QL and FTS, you can do that both right now.

Hey @daschl,

Thanks for quick reply,but i’m getting confuse on your reply on my requirements.
we already working on java API for N1QL in our code base and still we working on query performance and enhancement ,now we move to our new requirement for that we need FTS or that can we achieve via FTS,the major problem we are facing is how can we search any text with using condition .
like how to write the query that can use FTS index that i create for certain search with type mapping.
even if we use java API they only provide below part:
Match,Fuzzy or
result = bucket.query(MatchQuery.on(“beerIndex”)
.match(“sammar”)
.field(“name”)
.fuzziness(2)
.build()); , but could i add more condition that given by user at run time.
it will be great help or guidance if you give us some example or any thing that help us on this.
otherwise we need to use ES in that case.
Thank You.

Hi @rkumar,

You will be able to do this, but without the ability to specify fuzziness.

Let me explain briefly. You will be able to do basic text search using N1QL. However, more advanced text search, e.g. fuzziness and facets and N1QL filtering, will require two things. (1) Readiness of Couchbase Full Text Search. (2) back-end integration of FTS into N1QL.

HI @rkumar

Two options:

  1. Do this is two steps. First: use FTS to do the text search, get the document IDs. Second: issue N1QL on these document Keys and do the post filter, join, aggregation,etc.

  2. Text search on words. The below approach will address simple text. In 4.6, you’ll have a refined function to create indices on words by automatically removing delimiters(,."’` etc).

    1. Query with LIKE: 270ms

    select name from beer-sample where type = ‘brewery’ and
    lower(description) like '%pale%’;

    1. Query with SPLIT: 302ms

    select name from beer-sample where type = ‘brewery’ and
    any wrd in SPLIT(LOWER(description)) satisfies wrd = ‘pale’ end;

    Create array index:
    create index isearchdesc on beer-sample(distinct array wrd for wrd in SPLIT(LOWER(description)) end) where type = 'brewery’;

    1. Query with SPLIT using Array Index: 8 milliseconds. 34 times faster!
      select name from beer-sample where type = ‘brewery’ and
      any wrd in SPLIT(LOWER(description)) satisfies wrd = 'pale’ end ;

    2. Explain
      explain select name from beer-sample where type = ‘brewery’ and
      any wrd in SPLIT(LOWER(description)) satisfies wrd = ‘pale’ end;

    [
    {
    “plan”: {
    "#operator": “Sequence”,
    "~children": [
    {
    "#operator": “DistinctScan”,
    “scan”: {
    "#operator": “IndexScan”,
    “index”: “isearchdesc”,
    “index_id”: “c22ad3aad5e028b6”,
    “keyspace”: “beer-sample”,
    “namespace”: “default”,
    “spans”: [
    {
    “Range”: {
    “High”: [
    "“pale”"
    ],
    “Inclusion”: 3,
    “Low”: [
    "“pale”"
    ]
    }
    }
    ],
    “using”: “gsi”
    }
    },
    {
    "#operator": “Parallel”,
    "~child": {
    "#operator": “Sequence”,
    "~children": [
    {
    "#operator": “Fetch”,
    “keyspace”: “beer-sample”,
    “namespace”: “default”
    },
    {
    "#operator": “Filter”,
    “condition”: “(((beer-sample.type) = “brewery”) and any wrd in split(lower((beer-sample.description))) satisfies (wrd = “pale”) end)”
    },
    {
    "#operator": “InitialProject”,
    “result_terms”: [
    {
    “expr”: “(beer-sample.name)”
    }
    ]
    },
    {
    "#operator": “FinalProject”
    }
    ]
    }
    }
    ]
    },
    “text”: “select name from beer-sample where type = ‘brewery’ and\nany wrd in SPLIT(LOWER(description)) satisfies wrd = ‘pale’ end;”
    }
    ]

1 Like

Hi Team,

Thank you for such wonderful suggestion ,it will be great support.
@geraldss, As you suggested below

  1. Readiness of Couchbase Full Text Search.
    ANS: if i enable my bucket or node for full text search ,first part is done.
    (2) back-end integration of FTS into N1QL.
    ANS: what the API is available for this integration ,because i didn’t find ,could you please help me out for this integration,any example or guidence.
    @keshav_m, definitely i’m going to try this out now and let you know the results.

Thank you guys.

Hi @rkumar,

I was referring to the internal back-end integration within Couchbase. For now, you can use the suggestions from @keshav_m.

Hi @geraldss

i working on FTS API for java version 2.2 with CB 4.5 EE .
i create a FTS name “test_idx_one” for bucket name “default”,and try to to RnD with below java code:

SearchQuery ftq = MatchQuery.on(“test_idx_one”).match(“good doctor”).limit(10).build();

    //we fire the query and look at results
    SearchQueryResult result = CouchbaseConnector.getInstance().getBucket().query(ftq);
    System.out.println("totalHits: " + result.totalHits());
    for (SearchQueryRow row : result) {
        System.out.println(row);
    }

but i’m getting exception as below(i know abut this exception but in this condition feel helpless to resolved)

INFO [connector.CouchbaseConnector] 21 Sep 2016 10:31:20 - Initialized couchbase connector with 127.0.0.1
INFO [connector.CouchbaseConnector] 21 Sep 2016 10:31:20 - Opening couchbase bucket default
Sep 21, 2016 10:31:21 AM com.couchbase.client.core.logging.Slf4JLogger info
INFO: Connected to Node localhost
Sep 21, 2016 10:31:21 AM com.couchbase.client.core.logging.Slf4JLogger info
INFO: Opened bucket default
Exception in thread “main” com.couchbase.client.core.CouchbaseException: Could not query search index: 400 Bad Request: missing required Host header
at com.couchbase.client.java.CouchbaseAsyncBucket$28.call(CouchbaseAsyncBucket.java:802)
at com.couchbase.client.java.CouchbaseAsyncBucket$28.call(CouchbaseAsyncBucket.java:798)
at rx.internal.operators.OperatorMap$1.onNext(OperatorMap.java:54)
at rx.observers.Subscribers$5.onNext(Subscribers.java:234)
at rx.subjects.SubjectSubscriptionManager$SubjectObserver.onNext(SubjectSubscriptionManager.java:222)
at rx.subjects.AsyncSubject.onCompleted(AsyncSubject.java:101)
at com.couchbase.client.core.endpoint.AbstractGenericHandler$1.call(AbstractGenericHandler.java:269)
at rx.internal.schedulers.ScheduledAction.run(ScheduledAction.java:55)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: rx.exceptions.OnErrorThrowable$OnNextValue: OnError while emitting onNext value: com.couchbase.client.core.message.search.SearchQueryResponse.class
at rx.exceptions.OnErrorThrowable.addValueAsLastCause(OnErrorThrowable.java:109)
at rx.exceptions.Exceptions.throwOrReport(Exceptions.java:188)
at rx.internal.operators.OperatorMap$1.onNext(OperatorMap.java:56)
… 12 more

Can you please upgrade to a recent java client SDK and retry? 2.3.3 ideally - we had a fix for that in a released version! (or go to 2.2.7 / 2.2.8, they also should have the fix)

Hi @geraldss & @keshav_m,

Update for Keshav, that till now what you suggested is working ,but not even 10% of it fulfill our requirements.
like i need to look for "good doctor in NY for cancer"or “latest review for cancer patients” wont give me all combination of words that probably FTS giving me on UI side.
still we are looking for ES ,but still waiting for FTS fully.hope it release ASAP.

Any guidance is very much appreciated and helpfull.

Hi @rkumar,

Can you spell out your requirements?

Hi @daschl,

i include 2.3.3 to my POM.xml,but still m getting error on below query.

SearchQuery ftq = MatchQuery.on(“beerIndex”).match(“national”).limit(3).build();(errorline on MatchQuery.on)
//we fire the query and look at results
SearchQueryResult result = bucket.query(ftq);
System.out.println("totalHits: " + result.hits());
for (SearchQueryRow row : result) {
System.out.println(row);
}
i check the couchbase API but i didn’t get any similar syantax.
could you help me here.
Thank you

Hi @geraldss,
Thank you for your reply,YES off course i will give you the use case in brief.

we have 1million+ records in our CB server v4.5 EE on AWS.
so we give our user a facility for search on text ,but they can search using some filter or they can check or unchecked the checkbox from UI level and search for text.
Our req.is that how to make a QUERY (from N1QL) or search API who can give me the result in optimized way including or excluding the conditions given by user from UI.
condition or filter could be like below:
1.Date Range.
2.custome date range (start date -end date)
3.Location or provider
4.category–>child hierarchy
5.source–>child hierarchy
6.score[0 to 5]

so on above filter option user can do text search and get the results.
so how to perform this in one shot. or two

what error do you get exactly, can you paste it please?

Hi @daschl
i’m getting compilation error.
Unresolved compilation problem:
The method on(String) is undefined for the type MatchQuery

I import below packages
import com.couchbase.client.java.search.queries.MatchQuery;
import com.couchbase.client.java.search.result.SearchQueryResult;
import com.couchbase.client.java.search.result.SearchQueryRow;

even i check the jar file and i didn’t get any “on” method or field
com.couchbase.client.java.search.queries.MatchQuery.class
com.couchbase.client.java.search.queries.MatchQuery
com.couchbase.client.java.search.queries.MatchQuery.analyzer
com.couchbase.client.java.search.queries.MatchQuery.field
com.couchbase.client.java.search.queries.MatchQuery.fuzziness
com.couchbase.client.java.search.queries.MatchQuery.match
com.couchbase.client.java.search.queries.MatchQuery.prefixLength
com.couchbase.client.java.search.queries.MatchQuery.MatchQuery(String)
com.couchbase.client.java.search.queries.MatchQuery.analyzer(String)
com.couchbase.client.java.search.queries.MatchQuery.boost(double)
com.couchbase.client.java.search.queries.MatchQuery.field(String)
com.couchbase.client.java.search.queries.MatchQuery.fuzziness(int)
com.couchbase.client.java.search.queries.MatchQuery.injectParams(JsonObject)
com.couchbase.client.java.search.queries.MatchQuery.prefixLength(int)