Can't get SEARCH function to work

Full Text Search
,
1

I’m having trouble getting the SEARCH function to work in an N1QL query. I ultimately need to do a join with a search on the right side of the join, but I have pared back my query to eliminate the join and simplify as much as I could just to identify where things are going wrong and it still isn’t working - I get no results back. If I submit a plain search request I do get results so I seem to be doing something wrong when translating it to N1QL.

Here is an example of a plain search request without N1QL which returns results ("total_hits": 1975):

curl -XPOST -H "Content-Type: application/json" http://my-couchbase-host:8094/api/index/my_index/query \
-d '{
  "explain": false,
  "fields": [
    "*"
  ],
  "query": 
        {
                "field": "program.title",
                "match": "groovy",
                "analyzer": "my_query_analyzer",
                "boost": 2
        }
}'

However when I try to translate this to N1QL I get no results back ("results": []):

SELECT *
FROM recording AS rec
WHERE documentType='listing'
  AND SEARCH(rec, 
    {
  "explain": false,
  "fields": [
    "*"
  ],
  "query": 
        {
                "field": "program.title",
                "match": "groovy",
                "analyzer": "my_query_analyzer",
                "boost": 2
        }
},
{
  "index": "my_index"
})

Again, I ultimately need my N1QL query to include a join which is why I can’t just use the straight search request above.

The index my_index is created on the recording bucket using the json type field documentType with a type mapping for listing.

Couchbase 6.6.0 Community Edition

Thanks for any help indicating what I may be doing wrong in the above N1QL (any common gotchas for making joins work would be appreciated as well).

2

Hi @dguistcc ,

Hope you have referenced the SEARCH documentation here -

Esp sub-topics like,
Example 3. Search using a complete full text search request
Example 4. Search against a full text search index that carries a custom type mapping

From the information given so far, it looks like the issue is with respect to the way you have specified the type here.
WHERE rec.type='listing'
where the listing is the custom type mapping name in the FTS index definition.

3

Thanks for the response @sreeks . Yes, I’ve looked at the documentation and examples. I made a small mistake in my N1QL query in the original post. I am including the type explicitly but I failed to include the bucket alias above. The N1QL query actually looks like:

SELECT *
FROM recording AS rec
WHERE rec.documentType='listing'
  AND SEARCH(rec, 
    {
  "explain": false,
  "fields": [
    "*"
  ],
  "query": 
        {
                "field": "program.title",
                "match": "groovy",
                "analyzer": "my_query_analyzer",
                "boost": 2
        }
},
{
  "index": "my_index"
})

Note the WHERE rec.documentType='listing' clause. (The original post was missing the rec. qualifier). This is what you are referring to with WHERE rec.type='listing' in your response - my type field is simply called documentType instead of type and the FTS index is configured appropriately with that field name. The query still returns no results even when corrected as above.

4

Try EXPLAIN of query and see if the FTS index has been chosen or not. As you are giving index name drop analyzer from SEARCH() function and see .

Please post the FTS index definition

N1QL query follow strict semantics. If index doesn’t qualify for SEARCH() function requirement will not chose the index. It will try to use GSI index. Then when applied predicate it might be evaluated false.

Join limitations are documented here Search Functions | Couchbase Docs

5

Thank you @vsr1 . I tried the EXPLAIN and it was not using the FTS index. I then removed the analyzer from my search query as you suggested - it then used the FTS index and I got results back. I do not understand why this is necessary though. From reading documentation, it suggests that it is allowed to specify a different analyzer for the query text then is used in the index - I need to do this as I use an edge-ngram filter in my index, but I do not want my query text analyzed with the edge-ngram filter. I am able to use an analyzer in a straight search request (not N1QL) - why this limitation with N1QL?

Thank you!

6

@dguistcc N1QL limits the search capability for an FTS index to support consistent fetching of document content. The analyzer that you’re using to index the documents is expected to be used. If you’ve configured an analyzer for your field, you will not be allowed to specify any other analyzer (but the one used for indexing) for the field in the search request.

If you do not specify any analyzer in your match query, the query will automatically choose the analyzer that was used for indexing.

Sharing the index definition along side the search query will always help us assist you better.

For this comment …

I need to do this as I use an edge-ngram filter in my index, but I do not want my query text analyzed with the edge-ngram filter

N1QL will not allow you to do this^.

7

Thank you @abhinav , that’s useful to know. I might suggest that it would be great if the documentation mentioned this limitation, or if the query returned a helpful error message when trying to use a different analyzer with N1QL.

Appreciate the help!

8

I have also been unable to use an index alias when specifying the index in the search options, i.e. the third parameter to the SEARCH query. When I use an alias I get an error about the index mapping not found. Is the use of an index alias with N1QL also not supported?

9

You are right. Indexes aliases are not supported through N1QL due to Index alais can contain different buckets/type of indexes, N1QL will post process document keys (Fetch,Filter,… etc).

1 Like
10

Appreciate the input @dguistcc .

I’ve created a ticket for our documentation team to get this taken care of …
https://issues.couchbase.com/browse/DOC-8619