FTS UI Search to Query Workbench

Full Text Search
1

I have a FTS index set up (let’s call it my_fts_index_name) and I can test it out in the UI, works great:

fts-search-results
fts-search-results1612×1628 197 KB

But now I want to use it in a query with the SEARCH predicate. How do I translate the UI CURL search into an N1QL SEARCH? I tried copying the query definition from that CURL request into the SEARCH clause:

SELECT *
FROM `my-bucket` x
WHERE SEARCH(x.name, { "query": "name.en:car" }, { "index": "my_fts_index_name" })

Unlike the CURL-based UI search, this produces 0 results. I must be missing something here as this appears to be the correct way to construct the SEARCH clause.

2

try this. x.name already referring name field

SELECT *
FROM `my-bucket` x
WHERE SEARCH(x, { "query": "name.en:car" }, { "index": "my_fts_index_name" })
1 Like
3

Thanks, I get the same thing though: 0 results

4

Would you share the index definition and and couchbase-server version you’re using?

I’m guessing this has to do with evaluation filtering out items. There’s some restriction with the analyzers you can use when executing SEARCH requests from N1QL. To verify this, could you try -

SELECT meta().id
FROM `my-bucket` x
WHERE SEARCH(x, { "query": "name.en:car" }, { "index": "my_fts_index_name" })
5

OK, using meta().id didn’t change the results. Here’s the index definition (server version is Enterprise Edition 6.6.0 build 7909):

{
 "name": "my_fts_index_name",
 "type": "fulltext-index",
 "params": {
  "doc_config": {
   "docid_prefix_delim": "",
   "docid_regexp": "",
   "mode": "type_field",
   "type_field": "objectType"
  },
  "mapping": {
   "analysis": {
    "analyzers": {
     "custom": {
      "char_filters": [
       "asciifolding"
      ],
      "token_filters": [
       "edgengram",
       "to_lower"
      ],
      "tokenizer": "unicode",
      "type": "custom"
     }
    },
    "token_filters": {
     "edgengram": {
      "back": "false",
      "max": 10,
      "min": 2,
      "type": "edge_ngram"
     }
    }
   },
   "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": {
    "MyObjectType": {
     "dynamic": false,
     "enabled": true,
     "properties": {
      "name": {
       "default_analyzer": "custom",
       "dynamic": false,
       "enabled": true,
       "properties": {
        "en": {
         "enabled": true,
         "dynamic": false,
         "fields": [
          {
           "docvalues": true,
           "include_term_vectors": true,
           "index": true,
           "name": "en",
           "type": "text"
          }
         ]
        },
        "es": {
         "enabled": true,
         "dynamic": false,
         "fields": [
          {
           "docvalues": true,
           "include_term_vectors": true,
           "index": true,
           "name": "es",
           "type": "text"
          }
         ]
        }
       }
      }
     }
    }
   }
  },
  "store": {
   "indexType": "scorch"
  }
 },
 "sourceType": "couchbase",
 "sourceName": "my-bucket",
 "sourceUUID": "dd6f1834c5b6bde49a1a0807677602eb",
 "sourceParams": {},
 "planParams": {
  "maxPartitionsPerPIndex": 171,
  "indexPartitions": 6,
  "numReplicas": 0
 },
 "uuid": "618934ad0d259ab1"
}
6

Ah you’re using a custom type mapping, you’ll need to include the type condition expression in your N1QL query. This should work for you …

SELECT meta().id
FROM `my-bucket` x
WHERE x.objectType = "MyObjectType"
AND SEARCH(x, { "query": "name.en:car" }, { "index": "my_fts_index_name" })
7

Nope, I get the same thing when I include objectType: result: 0. When I use this:

SELECT meta().id
FROM `my-bucket` x
WHERE x.objectType = 'MyObjectType' AND SEARCH(x, { "query": "Something" })

I do get a hit because that’s a direct match on that term. When I include the { "index": "..."} though I get 0 results.

8

Provide EXPLAIN of following queries and see if using FTS index

SELECT *
FROM `my-bucket` x
WHERE SEARCH(x, { "query": "name.en:car" }, { "index": "my_fts_index_name" })

SELECT *
FROM `my-bucket` x
WHERE SEARCH(x, { "query": "name.en:car" })
9

Ya, the EXPLAIN would be useful.

Also (just noticed) - {“query”: “asdsa”} is not a valid query for the SEARCH function. It can be either of these …

SELECT *
FROM `my-bucket` x
WHERE x.objectType = "MyObjectType" AND SEARCH(x, "name.en:car" , { "index": "my_fts_index_name" });

OR

SELECT *
FROM `my-bucket` x
WHERE x.objectType = "MyObjectType" AND SEARCH(x, {"query": {"query":"name.en:car"}} , { "index": "my_fts_index_name" });
10

With index:

SELECT *
FROM `my-bucket` x
WHERE SEARCH(x, { "query": "name.en:car" }, { "index": "my_fts_index_name" })

{
    "#operator": "Sequence",
    "~children": [
        {
            "#operator": "IndexScan3",
            "as": "x",
            "index": "objectType",
            "index_id": "77afcd28b7f45ff6",
            "index_projection": {
                "primary_key": true
            },
            "keyspace": "my-bucket-name",
            "namespace": "default",
            "spans": [
                {
                    "exact": true,
                    "range": [
                        {
                            "high": "\"MyObjectType\"",
                            "inclusion": 3,
                            "low": "\"MyObjectType\""
                        }
                    ]
                }
            ],
            "using": "gsi"
        },
        {
            "#operator": "Fetch",
            "as": "x",
            "keyspace": "my-bucket-name",
            "namespace": "default"
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Filter",
                        "condition": "(((`x`.`objectType`) = \"MyObjectType\") and search(`x`, {\"query\": \"name.en:car\"}, {\"index\": \"my_fts_index_name\"}))"
                    },
                    {
                        "#operator": "InitialProject",
                        "result_terms": [
                            {
                                "expr": "self",
                                "star": true
                            }
                        ]
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            }
        }
    ]
}

Without index:

SELECT *
FROM `my-bucket` x
WHERE SEARCH(x, { "query": "name.en:car" })

{
    "#operator": "Sequence",
    "~children": [
        {
            "#operator": "IndexScan3",
            "as": "x",
            "index": "objectType",
            "index_id": "77afcd28b7f45ff6",
            "index_projection": {
                "primary_key": true
            },
            "keyspace": "my-bucket-name",
            "namespace": "default",
            "spans": [
                {
                    "exact": true,
                    "range": [
                        {
                            "high": "\"MyObjectType\"",
                            "inclusion": 3,
                            "low": "\"MyObjectType\""
                        }
                    ]
                }
            ],
            "using": "gsi"
        },
        {
            "#operator": "Fetch",
            "as": "x",
            "keyspace": "my-bucket-name",
            "namespace": "default"
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Filter",
                        "condition": "(((`x`.`objectType`) = \"MyObjectType\") and search(`x`, {\"query\": \"name.en:car\"}))"
                    },
                    {
                        "#operator": "InitialProject",
                        "result_terms": [
                            {
                                "expr": "self",
                                "star": true
                            }
                        ]
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            }
        }
    ]
}
11

Ok, so per your explain, the FTS index is not even being chosen, a GSI index is being picked up.
Would you give the queries I shared in my previous comment a shot.

12

Ugh, ok. yeah that first query example you provided seems to work as expected. Thanks! Where would I look in the documentation to find an example like that. I feel like I crawled over everything to find a solution. Can you point me to a URL?

13

This’d be a good place -
https://docs.couchbase.com/server/6.6/n1ql/n1ql-language-reference/searchfun.html