FTS with N1QL - settings mismatch

Full Text Search
,
1

Hi,

  1. I am trying N1QL query with SEARCH() predicate. It is showing as “No index available on keyspace” although raw query is working using curl. Am i missing any settings?
  2. I also tried flex index for FTS with N1QL, there also it is picking GSI index. Please let me know if i need to add some settings somewhere?

Version - 6.6.0

Thanks
Nitesh

2

@Nitesh_Gupta ,

The selection of an FTS index for serving a query is based on the index definition (the nature of fields indexed and their analyzer properties) as well as the fields involved in the query and their suitability for serving the query results.
Without the index definition and the sample query, it would be difficult to pinpoint the issue.
There are no other settings needed.

You may get more details here- Search Functions | Couchbase Docs

3

Hi @sreeks ,
Below is the defined index:

{
  "type": "fulltext-index",
  "name": "books_one",
  "uuid": "5f1bb864216916be",
  "sourceType": "couchbase",
  "sourceName": "books",
  "sourceUUID": "82e1626c2183fe3ac0ded8ebf6108f76",
  "planParams": {
    "maxPartitionsPerPIndex": 64,
    "indexPartitions": 1
  },
  "params": {
    "doc_config": {
      "docid_prefix_delim": "",
      "docid_regexp": "",
      "mode": "type_field",
      "type_field": "_class"
    },
    "mapping": {
      "analysis": {},
      "default_analyzer": "standard",
      "default_datetime_parser": "dateTimeOptional",
      "default_field": "_all",
      "default_mapping": {
        "dynamic": true,
        "enabled": false
      },
      "default_type": "_default",
      "docvalues_dynamic": true,
      "index_dynamic": true,
      "store_dynamic": false,
      "type_field": "_type",
      "types": {
        "com.org.One": {
          "default_analyzer": "keyword",
          "dynamic": false,
          "enabled": true,
          "properties": {
            "field1": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "keyword",
                  "index": true,
                  "name": "field1",
                  "store": true,
                  "type": "text"
                }
              ]
            },
            "field2": {
              "dynamic": false,
              "enabled": true,
              "properties": {
                "field3": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "include_term_vectors": true,
                      "index": true,
                      "name": "field3",
                      "store": true,
                      "type": "number"
                    }
                  ]
                },
                "field4": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "name": "field4",
                      "store": true,
                      "type": "text"
                    }
                  ]
                },
                "field5": {
                  "dynamic": false,
                  "enabled": true,
                  "properties": {
                    "field6": {
                      "dynamic": false,
                      "enabled": true,
                      "properties": {
                        "field7": {
                          "dynamic": false,
                          "enabled": true,
                          "fields": [
                            {
                              "index": true,
                              "name": "field7",
                              "store": true,
                              "type": "text"
                            }
                          ]
                        }
                      }
                    }
                  }
                },
                "field8": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "analyzer": "en",
                      "docvalues": true,
                      "include_in_all": true,
                      "include_term_vectors": true,
                      "index": true,
                      "name": "field8",
                      "store": true,
                      "type": "text"
                    }
                  ]
                }
              }
            },
            "field9": {
              "dynamic": false,
              "enabled": true,
              "fields": [
                {
                  "analyzer": "keyword",
                  "index": true,
                  "name": "field9",
                  "store": true,
                  "type": "text"
                }
              ]
            }
          }
        }
      }
    },
    "store": {
      "indexType": "scorch"
    }
  },
  "sourceParams": {}
}

query tried:

SELECT meta(p).id
FROM `books` as p
WHERE SEARCH(p,{"query": {
        "conjuncts": [
            {
                "disjuncts": [
                    {
                        "field": "field9",
                        "match": "a"
                    },
                    {
                        "field": "field9",
                        "match": "b"
                    }
                ]
            },
            {
                "disjuncts": [
                    {
                        "field": "field2.field5.field6.field7",
                        "match": "c0"
                    },
                    {
                        "field": "field2.field5.field6.field7",
                        "match": "c8"
                    }
                ]
            }
        ]
    },
    "size": 2,
    "from": 0,
    "sort": [
        "field2.field8"
    ]});
  1. This gives No index available error although given query works on search curl. Please check and let me know my mistake here.
  2. Also, i defined analyzer for field9 as keyword (which means exact match) but it also works in wildcard and prefix type queries as well. Please correct my understanding.
  3. N1QL flex query used as :
    select META().id from books
    USE INDEX (books_one USING FTS)
    where _class = ‘com.org.One’
    and field2.field3 > 1514158647000 and field2.field3 < 1714158647000
    This is not picking the search index (but picking GSI index) using explain button query UI.
  4. In raw query, how to add constraint for _class = ‘com.org.One’ because _class is root type while defining the index. I am unable to find how to put this in my constraint of raw query.

Thanks
Nitesh

4

When you have a custom type mapping, the N1QL query with the SEARCH function will need a condition expression as documented here (there’s examples too)…
https://docs.couchbase.com/server/6.6/n1ql/n1ql-language-reference/searchfun.html

Your condition expression is - _class = "com.org.One"

Also, you do not have these fields indexed in your FTS index definition -

  • field2.field5.field6.field7
  • field2.field5.field6.field7
  • field2.field8

Give this simpler query a try…

SELECT meta().id
FROM books AS p
WHERE _class = "com.org.One"
AND SEARCH(p,
{
  "query": {
    "conjuncts": [
      {
        "disjuncts": [
          {
            "field": "field9",
            "match": "a"
          },
          {
            "field": "field9",
            "match": "b"
          }
        ]
      },
      {
        "disjuncts": [
          {
            "field": "field4",
            "match": "c0"
          },
          {
            "field": "field4",
            "match": "c8"
          }
        ]
      }
    ]
  },
  "size": 2,
  "from": 0,
  "sort": [
    "field2"
  ]
} 
);
5

Thanks @abhinav for picking my mistake of not adding type constraint in N1QL query. Now this is working. And fields mentioned by you are part of index already. That’s why those are working in raw query.

Now, i am waiting for answers to points 2, 3 and 4.

  1. Also, i defined analyzer for field9 as keyword (which means exact match) but it also works in wildcard and prefix type queries as well. Please correct my understanding.
  2. N1QL flex query used as :
    select META().id from books
    USE INDEX (books_one USING FTS)
    where _class = 'com.org.One’
    and field2.field3 > 1514158647000 and field2.field3 < 1714158647000
    This is not picking the search index (but picking GSI index) using explain button query UI.
  3. In raw query, how to add constraint for _class = ‘com.org.One’ because _class is root type while defining the index. I am unable to find how to put this in my constraint of raw query. (For N1QL, it is okay that i can put in where constraint but how to put in raw curl query command)

Thanks
Nitesh

6

Keyword analyzer essentially means you’re saving the text as is without further analysis, it’s the recommended analyzer to use for non-analytic queries …
https://docs.couchbase.com/server/6.6/fts/fts-query-types.html#non-analytic-queries

The flex query should work, for as long as field 3 is indexed as a numeric field within child mapping field2 under the type com.org.One.

I’m not sure I follow what you mean by a raw query, would you share an example.