- \n
- SQL Server supports CONTAINS()\u00a0 for text search<\/a><\/li>\n
- Oracle supports CONTAINS() for text search<\/a><\/li>\n
- MySQL added full text support\u00a0<\/a><\/li>\n
- PostgreSQL has supported text search<\/a> for a long time.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
Couchbase Full-Text Search (FTS) is created with three main motivations:<\/span><\/p>\n
- \n
- \n
- \n
- Transparently search across multiple fields within a document<\/span><\/li>\n
- Go beyond the exact matching of values by providing <\/span>language based stemming<\/span><\/a>, fuzzy matching<\/a>, etc.<\/span><\/li>\n
- Provide the search results based on relevance<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
FTS achieves this on an inverted index and a rich set of <\/span>query predicates<\/span><\/a>: from simple word search to pattern matching to complex range predicates.\u00a0 \u00a0 In addition to the search, it supports aggregation via <\/span>search facets<\/span><\/a>.<\/span><\/p>\n
In the NoSQL world, Lucene is a popular search index and so are the search servers based on Lucene: Solr<\/a> and Elasticsearch<\/a>.\u00a0 Following their RDBMS cousins, Elasticsearch<\/span><\/a>, <\/span>Opendistro for Elasticsearch<\/span><\/a> all have added SQL for their search.\u00a0 Couchbase introduced the full text service,\u00a0FTS<\/a> and has followed up with support for search within N1QL.<\/span><\/p>\n
- \n
- \n
- \n
- FTS with N1QL<\/span><\/a><\/li>\n
- Elasticsearch with SQL<\/span><\/a><\/li>\n
- Opendistro for Elasticsearch with SQL<\/span><\/a><\/li>\n
- And the recent entrant to search, MongoDB has added <\/span>search to MQL<\/span><\/a> using Lucene in its Atlas offering. <\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
The SQL Implementations of Elasticsearch with SQL<\/a> and MongoDB’s MQL<\/a> comes with a long list of limitations.<\/strong><\/p>\n
Elasticsearch with SQL<\/a><\/span> has listed its limitations here:<\/p>\n
- \n
- \n
- \n
- Full list: https:\/\/www.elastic.co\/guide\/en\/elasticsearch\/reference\/current\/sql-limitations.html<\/a><\/li>\n
- In addition, the language is understandably limited due to its nascent implementation.\n
- \n
- No support for set operations joins, etc, etc.<\/li>\n
- No window functions.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
MongoDB’s MQL’s<\/a> search integration comes with a long list of limitations.<\/span><\/p>\n
- \n
- \n
- \n
- Available only on the Atlas search service, not on the on-prem product.<\/li>\n
- Search can only be the FIRST operation within the aggregate() pipeline.<\/li>\n
- Available only within the aggregation pipeline (aggregate()) and not in find(), insert(), update(), remove() other other operations.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
The integration with its aggregate() API, comes with some limitations: It can only be the first operation in the pipeline unavailable on its on-prem database. <\/span>The features we discuss in this article are in Couchbase 6.5 and above.<\/p>\n
Here\u2019s an example from N1QL:<\/p>\n
SELECT country, \r\n city, \r\n name, \r\n ROW_NUMBER() OVER(ORDER BY country DESC, city DESC) rownum\r\nFROM `travel-sample` AS t1\r\nWHERE t1.type = \"hotel\" AND SEARCH(t1.description, \"garden\") \r\nAND ANY r in reviews satisfies r.ratings.Service > 3 END;\r\n<\/pre>\n
This includes the following in addition to SEARCH():<\/span><\/p>\n
- \n
- Projection of fields from the documents: country, city, name<\/span><\/li>\n
- Row number generation via the window function ROW_NUMBER()<\/span><\/li>\n
- Additional scalar predicate\u00a0 t1.type = \u201chotel\u201d<\/span><\/li>\n
- Array predicate on reviews (ANY)<\/span><\/li>\n<\/ul>\n
You get the FULL benefit of first-class query processing in addition to efficient search.\u00a0 That\u2019s not all — there\u2019s even more with N1QL. <\/span>The benefits and <\/span>effectiveness of SQL<\/span><\/a> are well known.\u00a0 N1QL is SQL for JSON. The goal of N1QL is to give developers and enterprises an expressive, powerful, and complete language for querying, transforming, and manipulating JSON data.<\/span><\/p>\n
The benefits of using <\/span>N1QL<\/span><\/a> with the search are the following:<\/span><\/p>\n
- \n
- Predicates:<\/span>\n
- \n
- FTS is great with searching based on relevance. SQL is great with additional complex query processing: complex predicates, array predicates, additional scalar\u00a0<\/span><\/li>\n<\/ul>\n<\/li>\n
- Operators<\/span><\/a> and <\/span>functions<\/span><\/a>:<\/span>\n
- \n
- Predicate processing (filter processing)<\/span>\n
- \n
- Additional scalar and array predicates<\/span><\/li>\n
- Scalar and array functions too be used within the predicates<\/span><\/li>\n
- Subqueries<\/span>\n
- \n
- Correlated subqueries<\/span><\/li>\n
- Uncorrelated subqueries\u00a0<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
- Aggregates<\/span><\/a><\/li>\n
- Window functions<\/span><\/a><\/li>\n<\/ol>\n<\/li>\n
- JOIN processing<\/span>\n
- \n
- N1QL can do INNER JOIN, LEFT OUTER JOIN, (limited) RIGHT OUTER JOIN, NEST, UNNEST<\/span><\/li>\n
- JOINS between buckets, collections and results of subqueries.<\/span><\/li>\n<\/ol>\n<\/li>\n
- SET operations<\/span><\/a>\n
- \n
- UNION<\/span><\/li>\n
- UNION ALL<\/span><\/li>\n
- EXCEPT<\/span><\/li>\n
- EXCEPT ALL<\/span><\/li>\n
- INTERSECT<\/span><\/li>\n
- INTERSECT ALL<\/span><\/li>\n<\/ol>\n<\/li>\n
- CTE<\/span><\/a> (Common Table Expression) and <\/span>LET clause<\/span><\/a> for improved query writing<\/span><\/li>\n
- More than SEARCH()<\/span>\n
- \n
- In addition to SELECT, you can use SEARCH() predicate in the WHERE clauses of INSERT, UPDATE, DELETE, MERGE statements.<\/span><\/li>\n
- You can PREPARE these statements and EXECUTE them repeatedly and efficiently.<\/span><\/li>\n
- You get the usual security via the RBAC roles via GRANT & REVOKE.<\/span><\/li>\n<\/ol>\n<\/li>\n
- Developer productivity: Write the query in SQL, the language they already know.<\/span><\/li>\n<\/ol>\n
Let’s Look at how the N1QL engine executes this.\u00a0 Abhinav Dangeti from the Couchbase FTS engineering has already written a great blog detailing the decision making and examples<\/a>.\u00a0 This article is to explain this visually with additional examples in the categories mentioned above.<\/p>\n
1.\u00a0 ARCHITECTURE for QUERY EXECUTION<\/strong><\/h5>\n
We’ve added three important steps to query execution the query uses SEARCH() :<\/p>\n
- \n
- The planner considers the FTS search index one of the valid access paths if search() predicate exists in the query.\n
- \n
- If the search index is selected, then it creates the plan by pushing down the search predicate to the FTS index.<\/li>\n<\/ul>\n<\/li>\n
- When the search index is selected, executor issues the search request to one of the FTS nodes (instead of the scan request to the index service)<\/li>\n
- Before the results from the search are finalized, the query service re-verifies the search qualification of the document to the data.<\/li>\n<\/ol>\n
![\"N1QL](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/07\/Screen-Shot-2020-07-01-at-7.02.40-PM-300x164.png\")
<\/a>N1QL query execution with FTS<\/p><\/div>\n
![\"Inside](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/07\/Screen-Shot-2020-07-01-at-7.11.26-PM-300x127.png\")
<\/a>Inside a Query Service<\/p><\/div>\n
<\/h5>\n
2. PREDICATE PROCESSING<\/strong><\/h5>\n
In the following query, the SEARCH() predicate (predicate-2) is pushed to the FTS search request.\u00a0 Every other predicate is processed by the query engine post search in the “Filter” phase\u00a0 — as shown in the “Inside a Query Service” figure above.\u00a0 This is one exception to this.\u00a0 When the FTS index has created an index with JSON type field (doc_config.type_field in the index definition document)<\/strong>\u00a0is defined (in this case type = “hotel”) to create the index on the subset of the document,\u00a0 both index selection and search pushdown exploits this predicate.\u00a0 Even in this case, the predicate is re-applied after the document fetch.<\/p>\n
SELECT country, \r\n city, \r\n name, \r\n ROW_NUMBER() OVER(ORDER BY country DESC, city DESC) rownum\r\nFROM `travel-sample` AS t1\r\nWHERE \r\nt1.type = \"hotel\" \/* predicate-1 *\/\r\nAND SEARCH(t1.description, \"garden\") \/* predicate-2 *\/\r\nAND ANY r in reviews satisfies r.ratings.Service > 3 END; \/* predicate-2 *\/\r\n<\/pre>\n
<\/h5>\n
3. OPERATORS<\/a> and FUNCTIONS<\/a><\/strong><\/h5>\n
Here’s an example of a query exploiting the operators and functions.<\/p>\n
SELECT LOWER(country), \/* scalar function *\/\r\n city, \r\n lastname || \" \" || firstname AS fullname \/* string operator *\/\r\n ROW_NUMBER() OVER(ORDER BY country DESC, city DESC) rownum \/* window function *\/\r\nFROM `travel-sample` AS t1\r\nWHERE \r\nLOWER(t1.type) = \"hotel\" \/* scalar function *\/\r\nAND SEARCH(t1.description, \"garden\") \r\nAND ARRAY_CONTAINS(public_likes, \"Joe Black\") \/* Array function *\/\r\n<\/pre>\n
Here’s the query plan for this query. IndexSearch does the FTS search request and this is layered into the query execution pipeline.\u00a0 Hence the query gets the benefit of all the other capabilities of N1QL.\u00a0 This reflects the pipeline stages in the figure above.<\/p>\n
![\"Query](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/07\/Screen-Shot-2020-07-02-at-12.18.16-PM-300x190.png\")
<\/a>Query plan with SEARCH()<\/p><\/div>\n
4. JOIN processing<\/h5>\n
.The SEARCH() can also be used as part of the join processing.\u00a0 In this case, the FTS is used to find all the cities which have hotels with gardens and then join with airports.<\/p>\n
SELECT hotel.name hname,\r\n airport.city\r\nFROM `travel-sample` hotel \r\nLEFT OUTER JOIN `travel-sample` airport ON hotel.city = airport.city\r\nWHERE hotel.type = 'hotel'\r\n AND SEARCH(hotel.description, \"garden\")\r\n AND airport.type = 'airport' ;\r\n<\/pre>\n
<\/p>\n
<\/p>\n
![\"JOIN](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/07\/Screen-Shot-2020-07-02-at-1.52.41-PM-300x200.png\")
<\/a>JOIN with SEARCH<\/p><\/div>\n
5. Common Table Expressions (CTEs).<\/a><\/h5>\n
N1QL in query service supports non-recursive CTEs.\u00a0 \u00a0You can use SEARCH() within each expression. The derived table from that expression (hotel and airport) are used as keyspaces within the query.<\/p>\n
WITH hotel AS (\r\n SELECT name,\r\n city\r\n FROM `travel-sample`\r\n WHERE type = 'hotel'\r\n AND search(description, \"garden\")),\r\nairport AS (\r\n SELECT name,\r\n city\r\n FROM `travel-sample`\r\n WHERE type = 'airport'\r\n AND SEARCH(city, \"angeles\"))\r\nSELECT hotel.name hname,\r\n airport.city\r\nFROM hotel\r\nINNER JOIN airport ON hotel.city = airport.city\r\nORDER BY airport.city,\r\n hotel.name;\r\n<\/pre>\n
- You can PREPARE these statements and EXECUTE them repeatedly and efficiently.<\/span><\/li>\n
- UNION ALL<\/span><\/li>\n
- JOINS between buckets, collections and results of subqueries.<\/span><\/li>\n<\/ol>\n<\/li>\n
- Uncorrelated subqueries\u00a0<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
- Scalar and array functions too be used within the predicates<\/span><\/li>\n
- Additional scalar and array predicates<\/span><\/li>\n
- Operators<\/span><\/a> and <\/span>functions<\/span><\/a>:<\/span>\n
- FTS is great with searching based on relevance. SQL is great with additional complex query processing: complex predicates, array predicates, additional scalar\u00a0<\/span><\/li>\n<\/ul>\n<\/li>\n
- Row number generation via the window function ROW_NUMBER()<\/span><\/li>\n
- Projection of fields from the documents: country, city, name<\/span><\/li>\n
- \n
- In addition, the language is understandably limited due to its nascent implementation.\n
- Full list: https:\/\/www.elastic.co\/guide\/en\/elasticsearch\/reference\/current\/sql-limitations.html<\/a><\/li>\n
- Elasticsearch with SQL<\/span><\/a><\/li>\n
- FTS with N1QL<\/span><\/a><\/li>\n
- Go beyond the exact matching of values by providing <\/span>language based stemming<\/span><\/a>, fuzzy matching<\/a>, etc.<\/span><\/li>\n
- Transparently search across multiple fields within a document<\/span><\/li>\n
- Oracle supports CONTAINS() for text search<\/a><\/li>\n
![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/07\/Screen-Shot-2020-07-02-at-3.00.13-PM-198x300.png\")
<\/a><\/p>\n