| <!DOCTYPE html> |
| <!-- |
| | Generated by Apache Maven Doxia Site Renderer 1.8.1 from src/site/markdown/sqlpp/fulltext.md at 2022-05-12 |
| | Rendered using Apache Maven Fluido Skin 1.7 |
| --> |
| <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> |
| <head> |
| <meta charset="UTF-8" /> |
| <meta name="viewport" content="width=device-width, initial-scale=1.0" /> |
| <meta name="Date-Revision-yyyymmdd" content="20220512" /> |
| <meta http-equiv="Content-Language" content="en" /> |
| <title>AsterixDB – AsterixDB Support of Full-text search queries</title> |
| <link rel="stylesheet" href="../css/apache-maven-fluido-1.7.min.css" /> |
| <link rel="stylesheet" href="../css/site.css" /> |
| <link rel="stylesheet" href="../css/print.css" media="print" /> |
| <script type="text/javascript" src="../js/apache-maven-fluido-1.7.min.js"></script> |
| |
| </head> |
| <body class="topBarDisabled"> |
| <div class="container-fluid"> |
| <div id="banner"> |
| <div class="pull-left"><a href=".././" id="bannerLeft"><img src="../images/asterixlogo.png" alt="AsterixDB"/></a></div> |
| <div class="pull-right"></div> |
| <div class="clear"><hr/></div> |
| </div> |
| |
| <div id="breadcrumbs"> |
| <ul class="breadcrumb"> |
| <li id="publishDate">Last Published: 2022-05-12</li> |
| <li id="projectVersion" class="pull-right">Version: 0.9.8</li> |
| <li class="pull-right"><a href="../index.html" title="Documentation Home">Documentation Home</a></li> |
| </ul> |
| </div> |
| <div class="row-fluid"> |
| <div id="leftColumn" class="span2"> |
| <div class="well sidebar-nav"> |
| <ul class="nav nav-list"> |
| <li class="nav-header">Get Started - Installation</li> |
| <li><a href="../ncservice.html" title="Option 1: using NCService"><span class="none"></span>Option 1: using NCService</a></li> |
| <li><a href="../ansible.html" title="Option 2: using Ansible"><span class="none"></span>Option 2: using Ansible</a></li> |
| <li><a href="../aws.html" title="Option 3: using Amazon Web Services"><span class="none"></span>Option 3: using Amazon Web Services</a></li> |
| <li class="nav-header">AsterixDB Primer</li> |
| <li><a href="../sqlpp/primer-sqlpp.html" title="Using SQL++"><span class="none"></span>Using SQL++</a></li> |
| <li class="nav-header">Data Model</li> |
| <li><a href="../datamodel.html" title="The Asterix Data Model"><span class="none"></span>The Asterix Data Model</a></li> |
| <li class="nav-header">Queries</li> |
| <li><a href="../sqlpp/manual.html" title="The SQL++ Query Language"><span class="none"></span>The SQL++ Query Language</a></li> |
| <li><a href="../SQLPP.html" title="Raw SQL++ Grammar"><span class="none"></span>Raw SQL++ Grammar</a></li> |
| <li><a href="../sqlpp/builtins.html" title="Builtin Functions"><span class="none"></span>Builtin Functions</a></li> |
| <li class="nav-header">API/SDK</li> |
| <li><a href="../api.html" title="HTTP API"><span class="none"></span>HTTP API</a></li> |
| <li><a href="../csv.html" title="CSV Output"><span class="none"></span>CSV Output</a></li> |
| <li class="nav-header">Advanced Features</li> |
| <li><a href="../aql/externaldata.html" title="Accessing External Data"><span class="none"></span>Accessing External Data</a></li> |
| <li><a href="../feeds.html" title="Data Ingestion with Feeds"><span class="none"></span>Data Ingestion with Feeds</a></li> |
| <li><a href="../udf.html" title="User Defined Functions"><span class="none"></span>User Defined Functions</a></li> |
| <li><a href="../sqlpp/filters.html" title="Filter-Based LSM Index Acceleration"><span class="none"></span>Filter-Based LSM Index Acceleration</a></li> |
| <li class="active"><a href="#"><span class="none"></span>Support of Full-text Queries</a></li> |
| <li><a href="../sqlpp/similarity.html" title="Support of Similarity Queries"><span class="none"></span>Support of Similarity Queries</a></li> |
| <li><a href="../geo/quickstart.html" title="GIS Support Overview"><span class="none"></span>GIS Support Overview</a></li> |
| <li><a href="../geo/functions.html" title="GIS Functions"><span class="none"></span>GIS Functions</a></li> |
| <li><a href="../interval_join.html" title="Support of Interval Joins"><span class="none"></span>Support of Interval Joins</a></li> |
| <li><a href="../spatial_join.html" title="Support of Spatial Joins"><span class="none"></span>Support of Spatial Joins</a></li> |
| <li><a href="../sqlpp/arrayindex.html" title="Support of Array Indexes"><span class="none"></span>Support of Array Indexes</a></li> |
| <li class="nav-header">Deprecated</li> |
| <li><a href="../aql/primer.html" title="AsterixDB Primer: Using AQL"><span class="none"></span>AsterixDB Primer: Using AQL</a></li> |
| <li><a href="../aql/manual.html" title="Queries: The Asterix Query Language (AQL)"><span class="none"></span>Queries: The Asterix Query Language (AQL)</a></li> |
| <li><a href="../aql/builtins.html" title="Queries: Builtin Functions (AQL)"><span class="none"></span>Queries: Builtin Functions (AQL)</a></li> |
| </ul> |
| <hr /> |
| <div id="poweredBy"> |
| <div class="clear"></div> |
| <div class="clear"></div> |
| <div class="clear"></div> |
| <div class="clear"></div> |
| <a href=".././" title="AsterixDB" class="builtBy"><img class="builtBy" alt="AsterixDB" src="../images/asterixlogo.png" /></a> |
| </div> |
| </div> |
| </div> |
| <div id="bodyColumn" class="span10" > |
| <!-- |
| ! Licensed to the Apache Software Foundation (ASF) under one |
| ! or more contributor license agreements. See the NOTICE file |
| ! distributed with this work for additional information |
| ! regarding copyright ownership. The ASF licenses this file |
| ! to you under the Apache License, Version 2.0 (the |
| ! "License"); you may not use this file except in compliance |
| ! with the License. You may obtain a copy of the License at |
| ! |
| ! http://www.apache.org/licenses/LICENSE-2.0 |
| ! |
| ! Unless required by applicable law or agreed to in writing, |
| ! software distributed under the License is distributed on an |
| ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| ! KIND, either express or implied. See the License for the |
| ! specific language governing permissions and limitations |
| ! under the License. |
| !--> |
| <h1>AsterixDB Support of Full-text search queries</h1> |
| <div class="section"> |
| <h2><a name="Table_of_Contents"></a><a name="toc" id="toc">Table of Contents</a></h2> |
| <ul> |
| |
| <li><a href="#Motivation">Motivation</a></li> |
| <li><a href="#Syntax">Syntax</a></li> |
| <li><a href="#FulltextIndex">Creating and utilizing a Full-text index</a></li> |
| </ul></div> |
| <div class="section"> |
| <h2><a name="Motivation_.5BBack_to_TOC.5D"></a><a name="Motivation" id="Motivation">Motivation</a> <font size="4"><a href="#toc">[Back to TOC]</a></font></h2> |
| <p>Full-Text Search (FTS) queries are widely used in applications where users need to find records that satisfy an FTS predicate, i.e., where simple string-based matching is not sufficient. These queries are important when finding documents that contain a certain keyword is crucial. FTS queries are different from substring matching queries in that FTS queries find their query predicates as exact keywords in the given string, rather than treating a query predicate as a sequence of characters. For example, an FTS query that finds “rain” correctly returns a document when it contains “rain” as a word. However, a substring-matching query returns a document whenever it contains “rain” as a substring, for instance, a document with “brain” or “training” would be returned as well.</p></div> |
| <div class="section"> |
| <h2><a name="Syntax_.5BBack_to_TOC.5D"></a><a name="Syntax" id="Syntax">Syntax</a> <font size="4"><a href="#toc">[Back to TOC]</a></font></h2> |
| <p>The syntax of AsterixDB FTS follows a portion of the XQuery FullText Search syntax. Two basic forms are as follows:</p> |
| |
| <div> |
| <div> |
| <pre class="source"> ftcontains(Expression1, Expression2, {FullTextOption}) |
| ftcontains(Expression1, Expression2) |
| </pre></div></div> |
| |
| <p>For example, we can execute the following query to find Chirp messages where the <tt>messageText</tt> field includes “voice” as a word. Please note that an FTS search is case-insensitive. Thus, “Voice” or “voice” will be evaluated as the same word.</p> |
| |
| <div> |
| <div> |
| <pre class="source"> use TinySocial; |
| |
| select element {"chirpId": msg.chirpId} |
| from ChirpMessages msg |
| where ftcontains(msg.messageText, "voice", {"mode":"any"}); |
| </pre></div></div> |
| |
| <p>The DDL and DML of TinySocial can be found in <a href="../sqlpp/primer-sqlpp.html#ADM:_Modeling_Semistructured_Data_in_AsterixDB">ADM: Modeling Semistructured Data in AsterixDB</a>.</p> |
| <p>The <tt>Expression1</tt> is an expression that should be evaluable as a string at runtime as in the above example where <tt>msg.messageText</tt> is a string field. The <tt>Expression2</tt> can be a string, an (un)ordered list of string value(s), or an expression. In the last case, the given expression should be evaluable into one of the first two types, i.e., into a string value or an (un)ordered list of string value(s).</p> |
| <p>The following examples are all valid expressions.</p> |
| |
| <div> |
| <div> |
| <pre class="source"> ... where ftcontains(msg.messageText, "sound") |
| ... where ftcontains(msg.messageText, "sound", {"mode":"any"}) |
| ... where ftcontains(msg.messageText, ["sound", "system"], {"mode":"any"}) |
| ... where ftcontains(msg.messageText, {{"speed", "stand", "customization"}}, {"mode":"all"}) |
| </pre></div></div> |
| |
| <p>The last <tt>FullTextOption</tt> parameter clarifies the given FTS request. If you omit the <tt>FullTextOption</tt> parameter, then the default value will be set for each possible option. Currently, we only have one option named <tt>mode</tt>. And as we extend the FTS feature, more options will be added. Please note that the format of <tt>FullTextOption</tt> is a record, thus you need to put the option(s) in a record <tt>{}</tt>. The <tt>mode</tt> option indicates whether the given FTS query is a conjunctive (AND) or disjunctive (OR) search request. This option can be either <tt>“all”</tt> (AND) or <tt>“any”</tt> (OR). The default value for <tt>mode</tt> is <tt>“all”</tt>. If one specifies <tt>“any”</tt>, a disjunctive search will be conducted. For example, the following query will find documents whose <tt>messageText</tt> field contains “sound” or “system”, so a document will be returned if it contains either “sound”, “system”, or both of the keywords.</p> |
| |
| <div> |
| <div> |
| <pre class="source"> ... where ftcontains(msg.messageText, ["sound", "system"], {"mode":"any"}) |
| </pre></div></div> |
| |
| <p>The other option parameter,<tt>“all”</tt>, specifies a conjunctive search. The following examples will find the documents whose <tt>messageText</tt> field contains both “sound” and “system”. If a document contains only “sound” or “system” but not both, it will not be returned.</p> |
| |
| <div> |
| <div> |
| <pre class="source"> ... where ftcontains(msg.messageText, ["sound", "system"], {"mode":"all"}) |
| ... where ftcontains(msg.messageText, ["sound", "system"]) |
| </pre></div></div> |
| |
| <p>Currently AsterixDB doesn’t (yet) support phrase searches, so the following query will not work.</p> |
| |
| <div> |
| <div> |
| <pre class="source"> ... where ftcontains(msg.messageText, "sound system", {"mode":"any"}) |
| </pre></div></div> |
| |
| <p>As a workaround solution, the following query can be used to achieve a roughly similar goal. The difference is that the following queries will find documents where <tt>msg.messageText</tt> contains both “sound” and “system”, but the order and adjacency of “sound” and “system” are not checked, unlike in a phrase search. As a result, the query below would also return documents with “sound system can be installed.”, “system sound is perfect.”, or “sound is not clear. You may need to install a new system.”</p> |
| |
| <div> |
| <div> |
| <pre class="source"> ... where ftcontains(msg.messageText, ["sound", "system"], {"mode":"all"}) |
| ... where ftcontains(msg.messageText, ["sound", "system"]) |
| </pre></div></div> |
| </div> |
| <div class="section"> |
| <h2><a name="Creating_and_utilizing_a_Full-text_index_.5BBack_to_TOC.5D"></a><a name="FulltextIndex" id="FulltextIndex">Creating and utilizing a Full-text index</a> <font size="4"><a href="#toc">[Back to TOC]</a></font></h2> |
| <p>When there is a full-text index on the field that is being searched, rather than scanning all records, AsterixDB can utilize that index to expedite the execution of a FTS query. To create a full-text index, you need to specify the index type as <tt>fulltext</tt> in your DDL statement. For instance, the following DDL statement create a full-text index on the <tt>GleambookMessages.message</tt> attribute. Note that a full-text index cannot be built on a dataset with the variable-length primary key (e.g., string).</p> |
| |
| <div> |
| <div> |
| <pre class="source">use TinySocial; |
| |
| create index messageFTSIdx on GleambookMessages(message) type fulltext; |
| </pre></div></div></div> |
| </div> |
| </div> |
| </div> |
| <hr/> |
| <footer> |
| <div class="container-fluid"> |
| <div class="row-fluid"> |
| <div class="row-fluid">Apache AsterixDB, AsterixDB, Apache, the Apache |
| feather logo, and the Apache AsterixDB project logo are either |
| registered trademarks or trademarks of The Apache Software |
| Foundation in the United States and other countries. |
| All other marks mentioned may be trademarks or registered |
| trademarks of their respective owners. |
| </div> |
| </div> |
| </div> |
| </footer> |
| </body> |
| </html> |
