Flex query with IN clause not working

Full Text Search
,
#1

Hi,
Query 1:
select META().id from books
USE INDEX (books_one USING FTS)
where _class = 'com.org.One’
and any f in field4 satisfies f = ‘abc’ end
Query 2:
select META().id from books
USE INDEX (books_one USING FTS)
where _class = 'com.org.One’
and any f in field4 satisfies f IN [‘abc’, ‘def’] end

Query 1 works okay but query 2 fails. How to fix query 2?

#2

even field4 = ‘abc’ works but field4 IN [‘abc’] does not.

#3

This supported in up coming release MB-39350 until that change IN to OR @abhinav

#4

@vsr1 's correct. With 6.6, we interpret several N1QL functionality into FTS but all of it. Interpreting IN into FTS will be supported from 7.0. Until then you’d need use OR.

#5

Hi @abhinav ,
If we need to use OR clause for replacement of IN, then how many data will be supported in OR clause? I mean how many ORs we can use for better performance? Is there any limit?

Thanks
Nitesh

#6

@Nitesh_Gupta ,

OR clauses never helps to to better your query performance, the more the number of OR clauses - the lower the query performance as the search has to cover more area of the index/indexed items.

There is a configurable limit on 1024 sub clauses in a disjunct or OR clauses internally, but this is overridable. Again the internal number of eventual search clauses depends on the type of queries like regex/wildcards/prefix fuzzy etc as each of these query types internally becomes a fan out operations of all possible candidate terms matching the query.

Cheers!

#7

Thanks @sreeks

So, in that case, till the time IN clause is not supported in FTS, i can’t use FTS. We have heavy use of IN clause with 1000s of data in the list which don’t have any specific pattern.

Can you suggest anything better performant thing here?

Note: Everything will be term query with keyword analyzer on the field on that IN clause. Data is identifier kind of thing. Hope, this will help you to give me better performant solution instead of using OR clause.

Thanks
Nitesh

#8

@Nitesh_Gupta ,

The first recommendation is that - revisit the queries or data retrieval path if possible. Can you make the searches for finer scopes/target data sets with precise identifiers?
Having thousands of search clauses means - there is no exclusivity of target data. The more clauses/entries or search requirements you add irrespective of the internal implementation, you essentially search for more items, and hence it has its performance cost implications.
Even when we support IN clause from N1QL /Flex query, the internal implementations could be based on Disjuncts/OR queries.

If you can add a few MUST match fields along with the OR clauses, then it would help improve the performance.
You could try a conjunct query with all MUST match fields and a single sub-clause as a disjunct one with all the OR clauses.

#9

Hi @sreeks
Can i try the below one?
My data is having only uppercase letter and numbers with fixed 5 string length.

  1. While indexing, i just use lowercase filter on analyzer.
  2. While searching, i simply concatenate 1000s of data in IN clause and use it in match query with simple analyzer?

Will that work ?

Secondly, is there anything equivalent to terms filter in elasticsearch (Terms Filter | Elasticsearch Guide [1.5] | Elastic) in CB FTS?

#10

For term query - Query Types | Couchbase Docs

Sorry, Your suggestion/ask isn’t clear to me.

Please go ahead and create a customer ticket with the support team to address this capturing more relevant contexts.

#11

How to configure this value?

#12

With couchbase server 6.5, here’s how you can do it …

curl -XPUT -H "Content-type:application/json" \
http://<username>:<password>@<ip>:8094/api/managerOptions \
-d '{"bleveMaxClauseCount": "5000"}'

For older builds, it should still work but it won’t be cluster-wide (ie you have to do this against every node) and won’t survive node reboots.