{"id":7120,"date":"2019-06-22T10:42:41","date_gmt":"2019-06-22T17:42:41","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=7120"},"modified":"2025-06-13T17:22:02","modified_gmt":"2025-06-14T00:22:02","slug":"a-preview-of-couchbase-6-5-n1ql-features","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/a-preview-of-couchbase-6-5-n1ql-features\/","title":{"rendered":"A Preview of Couchbase 6.5 N1QL Features"},"content":{"rendered":"

Couchbase 6.5 release is one of the largest release content wise for Couchbase. For N1QL Query service, the focus for us is to bring Enterprise Database functionalities to the Couchbase Database. Expanding N1QL with additional functions to support Enterprise Application requirements with the goal to improve developers productivity.<\/p>\n

So here is the summary of the N1QL features.<\/p>\n

N1QL Search()<\/h4>\n

N1QL has added a full text search capability with the N1QL Search() function. It leverages Couchbase Full Text Search to deliver a variety of of text search capability, such as keyword and fuzzy search. Full Text Search is part of the Couchbase Data Platform that runs as a separate service with its Multi Dimensional Scaling capability. There is no need for a separate search engine, nor importing the data into a separate service. N1QL users can now leverage both Full Text Search and standard N1QL predicates in a single statement.<\/p>\n

Match Query: SELECT * \r\n             FROM `beer-sample`  b\r\n             WHERE SEARCH(b,{\"match\":\"hoppiest\",\"field\":\"description\"})\r\n                   AND b.abv > 3\r\n             ORDER BY b.abv DESC\r\n             LIMIT 10 OFFSET 20\r\n\r\nFuzzy Match: SELECT RAW META(t1).id \r\n             FROM `travel-sample` AS t1 \r\n             WHERE SEARCH(t1, { \"match\": \"interest\", \r\n                 \"field\": \"reviews.content\", \"fuzziness\":2}) ;\r\n\r\nRegexp:      SELECT RAW META(t1).id \r\n             FROM `travel-sample` AS t1 \r\n             WHERE SEARCH(t1, {\"regexp\": \"inter.+\",\r\n                 \"field\": \"reviews.content\"}); \r\n\r\nDate Range:  SELECT RAW META(t1).id \r\n             FROM `travel-sample` AS t1 \r\n             WHERE SEARCH(t1, { \"start\": \"2001-10-09T10:20:30-08:00\", \r\n                                \"end\": \"2016-10-31\", \r\n                                \"inclusive_start\": false, \r\n                                \"inclusive_end\": false, \r\n                                \"field\": \"reviews.date\" }); \r\nGeospatial:  SELECT RAW META(t1).id \r\n             FROM `travel-sample` AS t1 \r\n             WHERE SEARCH(t1, { \"location\": { \"lon\": -2.235143, \r\n                                              \"lat\": 53.482358 }, \r\n                                \"distance\": \"100mi\", \r\n                                \"field\": \"geo\" }); \r\n<\/pre>\n
N1QL Window & Analytical Function<\/h4>\n
Window functions extends the basic query aggregation to allow complex business questions with an intuitive N1QL construct, which would otherwise require user to write complex queries and joins.<\/p>\n
\"\"<\/p>\n
N1QL Common Table Expression<\/h4>\n
Common Table Expression introduces the WITH construct to N1QL to allow user to assign a name to query block, which can then be later referenced in the rest of the query.<\/p>\n
\"\"<\/p>\n
N1QL User Defined Function<\/h4>\n
Allows developers to define custom N1QL function which is callable from N1QL queries. Allowing the isolation of N1QL application logic that can be reused by any application and micro services.<\/p>\n
CREATE FUNCTION func1(type) { \r\n  (select * from `travel-sample` where `travel-sample`.type = type) \r\n}\r\n\r\nEXECUTE FUNCTION func1(\"airline\")\r\n<\/pre>\n
Subquery JOIN<\/h4>\n
Couchbase database enhances its support for ANSI Join to include joining with sub-queries, allowing the use of dynamic query result sets in the join.<\/p>\n
SELECT  cus.name Customer, \r\n        cus.industry Industry, \r\n        usr.name Owner, \r\n        count(1) NumOfMeetings, \t\t\r\n\tsum(to_number(act.duration)) `Time Spent`\r\nFROM crm act \r\n  INNER JOIN crm cus ON act.accid = cus.id AND cus.type = 'account'\r\n  INNER JOIN crm usr ON cus.owner = usr.id AND usr.type = 'user'\r\n  JOIN<\/strong> ( select thl.id from crm thl WHERE thl.type ='_territory_hier_level' \r\n\t\tAND thl.parent='ter3') ter  ON<\/strong> cus.terid = ter.id \r\nWHERE act.type='activity'  \r\n  AND act.activityType='Appointment'\r\n  AND DATE_PART_STR(act.startDate, 'year') =2018 \r\nGROUP BY cus.name, cus.industry,  usr.name<\/pre>\n
The following features are also going to be available in version 6.5 as Developer Preview.<\/p>\n
Index Advisor (DP)<\/h4>\n
User can now use the Index Advisor the create the most optimum indexes for their queries. The recommended indexes also include a section for covering indexes. Queries that leverages covering indexes can benefit from the need to perform additional data service fetching, and thus improve the overall query performance. Index Advisor works for specific query, or completed requests workload.<\/p>\n
\"\"<\/p>\n
Other N1QL Enhancements in Couchbase v6.5<\/h4>\n
\"\"<\/p>\n
Summary<\/h4>\n
We are very excited about the new N1QL content for Couchbase v6.5, as these features will address many requests that our customers have asked for. As usual we’re all ears when it comes to feedback on our new features, and especially the Index Advisor(DP) .<\/p>\n
 <\/p>\n
Resources<\/b><\/h4>\n