{"id":6565,"date":"2018-12-11T12:29:41","date_gmt":"2018-12-11T20:29:41","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=6565"},"modified":"2025-06-13T23:42:59","modified_gmt":"2025-06-14T06:42:59","slug":"n1ql-and-search-how-to-leverage-fts-index-in-n1ql-query","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/n1ql-and-search-how-to-leverage-fts-index-in-n1ql-query\/","title":{"rendered":"N1QL & SEARCH: Leverage Full-Text Search (FTS) Index in N1QL"},"content":{"rendered":"

With Couchbase v6.5, Full-Text Search is now integrated into the Couchbase N1QL query construct. Customers can now leverage FTS indexes directly with N1QL. This provides developers a single API to combine N1QL exact predicate matching and FTS powerful searching.<\/p>\n

The one constant challenge for many application developers with relational databases is query performance. Resolving query performance issues are often limited to what relational databases offer — getting a larger database server or better indexes.<\/p>\n

With Couchbase, N1QL query performance also relies on similar components. But unlike relational RDBMS, Couchbase architecture of services isolation means both the Query and Index services can be scaled out independently. With appropriate sizing and capacity planning, Couchbase can deliver blistering fast performance as shown in an Altoros NoSQL Benchmark<\/a> report.<\/p>\n

Beyond Query Predicates – N1QL & Search<\/h3>\n

Customers can achieve milliseconds response time for queries with appropriate indexes. However, there are times when the query predicates used by Couchbase GSI indexes are not known ahead of time. The ideal solution is to have an indexing system that could work with any combination of the available query predicates.<\/p>\n

Couchbase Adaptive Indexing can address many of these use cases. Couchbase Full-Text Search is another approach to irregular pattern use cases. They offer text and fuzzy search capabilities on any field in the document.<\/p>\n

Let’s consider the activity management document below. An activity:<\/p>\n

    \n
  1. Always belong to a customer (account)<\/li>\n
  2. Can also have multiple contacts from the customer’s organization and are represented by an array of contacts<\/li>\n
  3. May include multiple participants, represented by an array of users.<\/li>\n
  4. May be of type appointment or a task, both of which have their specific corresponding attributes, such as title, start date, due date, etc.<\/li>\n
  5. An activity of type Task has an array of ToDo list<\/li>\n<\/ol>\n

    \"sample \"sample<\/p>\n

    The use case<\/h3>\n

    John, a service representative for a call center at Acme Ltd needs to retrieve all the customer activities while he is on the phone with a customer. The customer may provide one or many of the values below for John to query the application:<\/p>\n

      \n
    1. Activity title:\u00a0 The query should return all activities that have this text, anywhere in the activity title.<\/li>\n
    2. Customer name: The entered customer name may be incomplete, thus the query needs to use a wildcard to match with the customer name.<\/li>\n
    3. Contact name, email, or phone contact point: The customer may also provide contact person details. These may also be incomplete.<\/li>\n
    4. A participant name: The customer may also provide the name of the account manager, an employee of Acme whom the customer has been interacted with and had been part of the activity.<\/li>\n
    5. Activity date: Customer may provide a range of dates and times for the activities.<\/li>\n
    6. The service rep may receive one or more of the above information. The pattern is not fixed.<\/li>\n
    7. The query response time needs to be ~1 sec<\/li>\n
    8. Data volume is 3millions per year and a retention period of 3 years.<\/li>\n<\/ol>\n

      What are the challenges to retrieve this information?<\/h3>\n
        \n
      1. There could be up to eight fields that the customer can provide, and none of them are mandatory. This would pose a challenge for an efficient GSI Index design because covering index leading key needs to be present for the index selection. As the result, GSI indexes cannot cover all cases.<\/li>\n
      2. Wildcard matching: The provided activity title, customer and contact name, email, or phone can be incomplete so an exact N1QL predicate matching technique will not work.<\/li>\n
      3. Both contacts and participants are child objects for activities. In the JSON data model, contacts and participants are represented as two separate arrays. If we need a coverage index, it needs to include one or more elements from both arrays.<\/li>\n<\/ol>\n

        The solutions<\/h3>\n

        1. The simplest approach is to use N1QL predicates:<\/h4>\n
        SELECT meta(a).id, a.title, a.startDate, a.account.name, a.contacts, a.participants\r\nFROM crm a \r\nWHERE \r\n  a.type='activity' \r\nAND a.activityType='Appointment'\r\nAND\r\n(\r\n    LOWER(a.title) LIKE '%artificial intelligence%'\r\nAND LOWER(a.account.name) LIKE '%collins%' \r\nAND ANY c in a.contacts SATISFIES LOWER(c.name) LIKE '%rogers%' END\r\nAND ANY c in a.contacts SATISFIES LOWER(c.email) = 'elliottpamela@gmail.com' END \r\nAND ANY c in a.contacts SATISFIES c.phone LIKE '%6816%' END\r\nAND ANY p in a.participants SATISFIES LOWER(p.name)LIKE '%james%' END \r\nAND a.startDate between '2016-08-29' AND '2016-08-30'\r\n)<\/pre>\n
        The following GSI indexes would also be required:<\/p>\n
        CREATE INDEX crm_activity_appt_startDate_accid_dur ON `crm`(`activityType`,`startDate`,`accid`,`duration`) WHERE `type` = 'activity'\r\nCREATE INDEX act_account_name_type ON `crm`(lower((`account`).`name`)) WHERE `type` = 'activity'\r\nCREATE INDEX act_contacts_name_type ON `crm`(DISTINCT ARRAY lower(`c`.`name`) FOR c in contacts END) WHERE `type` = 'activity'\r\nCREATE INDEX act_contacts_email_type ON `crm`(DISTINCT ARRAY lower(`c`.`email`) FOR c in contacts END) WHERE `type` = 'activity'\r\nCREATE INDEX act_contacts_phone_type ON `crm`(DISTINCT ARRAY c.phone FOR c in contacts END) WHERE `type` = 'activity'\r\nCREATE INDEX act_participants_name_type ON `crm`(DISTINCT ARRAY lower(`p`.`name`) FOR p in participants END) WHERE `type` = 'activity'\r\n\r\n<\/pre>\n
        Note that the above query may use one or all of the available indexes to improve query performance. However, there could still be performance issues because of the need for the query plan to use IntersectScan operation.<\/p>\n
        2. Leverage FTS Index<\/h4>\n
        Couchbase Full-Text Search could help with this use case, because of its non-exact search capability as well as the ability to search the fields in any order. Here is an FTS index that can cover the search criteria.<\/p>\n
        \"couchbase<\/p>\n
        2.1 Using CURL – This is supported in Couchbase 5.5<\/h4>\n
        SELECT meta(a).id, a.title,a.startDate, a.account.name, a.contacts, a.participants\r\nFROM CURL(\"https:\/\/localhost:8094\/api\/index\/all_acts\/query\", \r\n          {\"request\":\"POST\",\"header\":\"Content-Type: application\/json\", \"data\":\r\n          '{   \"explain\":false,\"fields\": [\"*\"],\r\n                \"highlight\": {},\r\n                \"query\": {\"conjuncts\":\r\n                      [  {\"field\":\"title\", \"match\": \"artificial intelligence\"} \r\n                      ,  {\"field\":\"contacts.name\", \"match\":\"rogers\"}\r\n                      ,  {\"field\":\"contacts.email\", \"match\":\"eliottpamela@gmail.com\"} \r\n                      ,  {\"field\":\"contacts.phone\", \"wildcard\":\"*6816*\"} \r\n                      ,  {\"field\":\"participants.name\", \"match\":\"james\"} \r\n                      ,  {\"field\":\"account.name\", \"match\":\"collins\"}\r\n                      ,  {\"field\":\"startDate\",\"start\": \"2016-08-29\", \"end\":\"2016-08-30\", \"inclusive_start\": true, \r\n                                      \"inclusive_end\": true} \r\n                      ] \r\n                  }\r\n          }',\r\n          \"user\":\"Administrator:password\"}) result\r\n          UNNEST result.hits h\r\n          INNER JOIN crm a ON (h.id = meta(a).id) AND a.type='activity'<\/pre>\n
        2.2\u00a0 With N1QL\/FTS integration using SEARCH_QUERY<\/h4>\n
        SELECT meta(a).id, a.title,a.startDate, a.account.name, a.contacts, a.participants\r\nFROM SEARCH_QUERY(\"all_acts\",{\"explain\":false,\"fields\": [\"*\"],\"highlight\": {}, \r\n    \"query\": {\"conjuncts\":[ {\"field\":\"title\", \"match\": \"artificial intelligence\"} \r\n                          , {\"field\":\"contacts.name\", \"match\":\"rogers\"} \r\n                          ,  {\"field\":\"contacts.email\", \"match\":\"eliottpamela@gmail.com\"} \r\n                          ,  {\"field\":\"contacts.phone\", \"wildcard\":\"*6816*\"} \r\n                          ,  {\"field\": \"participants.name\", \"match\":\"james\"} \r\n                          ,  {\"field\": \"account.name\", \"match\":\"collins\"}\r\n                          ,  {\"field\": \"startDate\",\"start\": \"2016-08-29\", \"end\":\"2016-08-30\", \"inclusive_start\": true, \r\n                                   \"inclusive_end\": true}\r\n                          ] }}\r\n              ) as result\r\n          UNNEST result.hits h\r\n          INNER JOIN crm a ON (h.id = meta(a).id) AND a.type='activity'<\/pre>\n
        2.3\u00a0With N1QL\/FTS integration using N1QL SEARCH predicate<\/h4>\n
        SELECT meta(a).id, a.title,a.startDate, a.account.name, a.contacts, a.participants\r\nFROM crm a\r\nWHERE a.type='activity'\r\nAND a.activityType='Appointment'\r\nAND SEARCH(a,\r\n   {\"conjuncts\":\r\n     [  {\"field\":\"title\", \"match\": \"artificial intelligence\"},\r\n        {\"field\":\"contacts.name\", \"match\":\"rogers\"},\r\n        {\"field\":\"contacts.email\", \"match\":\"eliottpamela@gmail.com\"},\r\n        {\"field\":\"contacts.phone\", \"wildcard\":\"*6816*\"},\r\n        {\"field\": \"participants.name\", \"match\":\"james\"},\r\n        {\"field\": \"account.name\", \"match\":\"collins\"},\r\n        {\"field\": \"startDate\",\"start\": \"2016-08-29\", \"end\":\"2016-08-30\", \"inclusive_start\": true, \"inclusive_end\": true}\r\n     ]\r\n    }, \r\n    {\"index\":\"all_acts\"}) \/* good practice to provide the specific index name *\/<\/pre>\n
        Notes:<\/strong><\/p>\n
          \n
        1. The above example leverages the FTS compound query with the conjunct construct to combine all predicates into a single SEARCH(). Refer to Couchbase FTS documentation for more detail on FTS Query type<\/a><\/li>\n
        2. The above statement should be programmatically constructed to include only the required search predicates.<\/li>\n
        3. The FTS index design must include the fields that are used in the SEARCH()<\/li>\n
        4. The N1QL predicate\u00a0a.type=’activity’\u00a0<\/strong>must be present in the query for the FTS index selection<\/li>\n<\/ol>\n
          N1QL SEARCH_QUERY and SEARCH predicate is part of the N1QL\/FTS Integration feature available in Couchbase v6.5 and I will update the blog with the documentation when it becomes available.<\/p>\n
          For more detail on the FTS query syntax https:\/\/docs.couchbase.com\/server\/6.0\/fts\/full-text-intro.html<\/a><\/p>\n
          N1QL & Search Summary:<\/h4>\n
            \n
          1. N1QL\/FTS integration allows a query to use FTS search construct directly as search predicates<\/li>\n
          2. The use of the FTS index in the N1QL query alleviates the need to have an exact index for each query pattern<\/li>\n
          3. N1QL\/FTS provides an additional option for developers to explore when dealing with query performance issues<\/li>\n
          4. FTS index is well suited where you need to search on multiple fields in any order<\/li>\n
          5. FTS index is well suited for cases where you need to search for fields in multiple arrays<\/li>\n<\/ol>\n
            Resources<\/b><\/h4>\n