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.
So here is the summary of the N1QL features.
N1QL Search()
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Match Query: SELECT * FROM `beer-sample` b WHERE SEARCH(b,{"match":"hoppiest","field":"description"}) AND b.abv > 3 ORDER BY b.abv DESC LIMIT 10 OFFSET 20 Fuzzy Match: SELECT RAW META(t1).id FROM `travel-sample` AS t1 WHERE SEARCH(t1, { "match": "interest", "field": "reviews.content", "fuzziness":2}) ; Regexp: SELECT RAW META(t1).id FROM `travel-sample` AS t1 WHERE SEARCH(t1, {"regexp": "inter.+", "field": "reviews.content"}); Date Range: SELECT RAW META(t1).id FROM `travel-sample` AS t1 WHERE SEARCH(t1, { "start": "2001-10-09T10:20:30-08:00", "end": "2016-10-31", "inclusive_start": false, "inclusive_end": false, "field": "reviews.date" }); Geospatial: SELECT RAW META(t1).id FROM `travel-sample` AS t1 WHERE SEARCH(t1, { "location": { "lon": -2.235143, "lat": 53.482358 }, "distance": "100mi", "field": "geo" }); |
N1QL Window & Analytical Function
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.
N1QL Common Table Expression
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.
N1QL User Defined Function
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.
|
1 2 3 4 5 |
CREATE FUNCTION func1(type) { (select * from `travel-sample` where `travel-sample`.type = type) } EXECUTE FUNCTION func1("airline") |
Subquery JOIN
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT cus.name Customer, cus.industry Industry, usr.name Owner, count(1) NumOfMeetings, sum(to_number(act.duration)) `Time Spent` FROM crm act INNER JOIN crm cus ON act.accid = cus.id AND cus.type = 'account' INNER JOIN crm usr ON cus.owner = usr.id AND usr.type = 'user' <strong>JOIN</strong> ( select thl.id from crm thl WHERE thl.type ='_territory_hier_level' AND thl.parent='ter3') ter <strong>ON</strong> cus.terid = ter.id WHERE act.type='activity' AND act.activityType='Appointment' AND DATE_PART_STR(act.startDate, 'year') =2018 GROUP BY cus.name, cus.industry, usr.name |
The following features are also going to be available in version 6.5 as Developer Preview.
Index Advisor (DP)
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.
Other N1QL Enhancements in Couchbase v6.5
Summary
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) .
Resources
- Download: Download Couchbase Server 6.5
- Documentation: Couchbase Server 6.5 What’s New
- All 6.5 Blogs
We would love to hear from you on how you liked the 6.5 features and how it’ll benefit your business going forward. Please share your feedback via the comments or in the forum.
