Search multiple buckets with FTS index alias vs joins

Full Text Search
1

Hello,
My question is can I use multiple index aliases to search on multiple buckets. Basically index alias A to search on one bucket and then the results from A to filter on another bucket with index alias B .

Is that more of a use case of joins… can i use n1ql FTS search and use a join with another n1ql FTS search?

How can I achieve my use case?

Regards

2

My question is can I use multiple index aliases to search on multiple buckets. Basically index alias A to search on one bucket and then the results from A to filter on another bucket with index alias B .

Yes you may - the query request that you send to the alias will be redirected to all the aliases or index partitions that are defined within the alias.

Is that more of a use case of joins… can i use n1ql FTS search and use a join with another n1ql FTS search?

While the approach using index aliases is not technically a join, you will be able to perform join operations with multiple N1QL search queries as long as they’re directly targeting FTS indexes. FTS index aliases cannot be reached from N1QL at the moment.

3

Thank you @abhinav Even if i can target the index directly … that may suffice if i can use a join.

Do you have any documentation on using joins with FTS query… that would be highly appreciated.

Regards

4

Unfortunately, it seems we haven’t documented examples for JOINS over SEARCH functions in docs.couchbase.com.

Here’s a blog that could help: ANSI JOIN Support in N1QL: Overview + Details | Couchbase
Note that you can do Hash joins like in example 9 in the blog above. Here’s an example -

SELECT DISTINCT route.destinationairport
FROM `travel-sample` airport JOIN `travel-sample` route USE HASH(BUILD)
     ON airport.faa = route.sourceairport
        AND route.type = "route" AND SEARCH(route, {"match": "United States", "field": "country"})
WHERE airport.type = "airport"
  AND airport.city = "San Francisco"
  AND SEARCH(airport, {"match": "United States", "field": "country"}) ;

p.s. You will be able even mix and match JOIN queries with GSI indexes alongside FTS indexes.

cc @vsr1

5

If you use intervene subquery (each using its own FTS SERACH() and materializes) you can use any type of JOIN as those consider expressions.