blob: b4f4f8ad30fc99ab79af9d4b3e40d80e19d91563 [file] [log] [blame]
Ian Maxon3355d4c2021-12-13 12:38:15 -08001<!DOCTYPE html>
2<!--
3 | Generated by Apache Maven Doxia Site Renderer 1.8.1 from src/site/markdown/sqlpp/fulltext.md at 2021-12-13
4 | Rendered using Apache Maven Fluido Skin 1.7
5-->
6<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
7 <head>
8 <meta charset="UTF-8" />
9 <meta name="viewport" content="width=device-width, initial-scale=1.0" />
10 <meta name="Date-Revision-yyyymmdd" content="20211213" />
11 <meta http-equiv="Content-Language" content="en" />
12 <title>AsterixDB &#x2013; AsterixDB Support of Full-text search queries</title>
13 <link rel="stylesheet" href="../css/apache-maven-fluido-1.7.min.css" />
14 <link rel="stylesheet" href="../css/site.css" />
15 <link rel="stylesheet" href="../css/print.css" media="print" />
16 <script type="text/javascript" src="../js/apache-maven-fluido-1.7.min.js"></script>
17
18 </head>
19 <body class="topBarDisabled">
20 <div class="container-fluid">
21 <div id="banner">
22 <div class="pull-left"><a href=".././" id="bannerLeft"><img src="../images/asterixlogo.png" alt="AsterixDB"/></a></div>
23 <div class="pull-right"></div>
24 <div class="clear"><hr/></div>
25 </div>
26
27 <div id="breadcrumbs">
28 <ul class="breadcrumb">
29 <li id="publishDate">Last Published: 2021-12-13</li>
30 <li id="projectVersion" class="pull-right">Version: 0.9.7.1</li>
31 <li class="pull-right"><a href="../index.html" title="Documentation Home">Documentation Home</a></li>
32 </ul>
33 </div>
34 <div class="row-fluid">
35 <div id="leftColumn" class="span2">
36 <div class="well sidebar-nav">
37 <ul class="nav nav-list">
38 <li class="nav-header">Get Started - Installation</li>
39 <li><a href="../ncservice.html" title="Option 1: using NCService"><span class="none"></span>Option 1: using NCService</a></li>
40 <li><a href="../ansible.html" title="Option 2: using Ansible"><span class="none"></span>Option 2: using Ansible</a></li>
41 <li><a href="../aws.html" title="Option 3: using Amazon Web Services"><span class="none"></span>Option 3: using Amazon Web Services</a></li>
42 <li class="nav-header">AsterixDB Primer</li>
43 <li><a href="../sqlpp/primer-sqlpp.html" title="Using SQL++"><span class="none"></span>Using SQL++</a></li>
44 <li class="nav-header">Data Model</li>
45 <li><a href="../datamodel.html" title="The Asterix Data Model"><span class="none"></span>The Asterix Data Model</a></li>
46 <li class="nav-header">Queries</li>
47 <li><a href="../sqlpp/manual.html" title="The SQL++ Query Language"><span class="none"></span>The SQL++ Query Language</a></li>
48 <li><a href="../SQLPP.html" title="Raw SQL++ Grammar"><span class="none"></span>Raw SQL++ Grammar</a></li>
49 <li><a href="../sqlpp/builtins.html" title="Builtin Functions"><span class="none"></span>Builtin Functions</a></li>
50 <li class="nav-header">API/SDK</li>
51 <li><a href="../api.html" title="HTTP API"><span class="none"></span>HTTP API</a></li>
52 <li><a href="../csv.html" title="CSV Output"><span class="none"></span>CSV Output</a></li>
53 <li class="nav-header">Advanced Features</li>
54 <li><a href="../aql/externaldata.html" title="Accessing External Data"><span class="none"></span>Accessing External Data</a></li>
55 <li><a href="../feeds.html" title="Data Ingestion with Feeds"><span class="none"></span>Data Ingestion with Feeds</a></li>
56 <li><a href="../udf.html" title="User Defined Functions"><span class="none"></span>User Defined Functions</a></li>
57 <li><a href="../sqlpp/filters.html" title="Filter-Based LSM Index Acceleration"><span class="none"></span>Filter-Based LSM Index Acceleration</a></li>
58 <li class="active"><a href="#"><span class="none"></span>Support of Full-text Queries</a></li>
59 <li><a href="../sqlpp/similarity.html" title="Support of Similarity Queries"><span class="none"></span>Support of Similarity Queries</a></li>
60 <li><a href="../interval_join.html" title="Support of Interval Joins"><span class="none"></span>Support of Interval Joins</a></li>
61 <li><a href="../sqlpp/arrayindex.html" title="Support of Array Indexes"><span class="none"></span>Support of Array Indexes</a></li>
62 <li class="nav-header">Deprecated</li>
63 <li><a href="../aql/primer.html" title="AsterixDB Primer: Using AQL"><span class="none"></span>AsterixDB Primer: Using AQL</a></li>
64 <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>
65 <li><a href="../aql/builtins.html" title="Queries: Builtin Functions (AQL)"><span class="none"></span>Queries: Builtin Functions (AQL)</a></li>
66</ul>
67 <hr />
68 <div id="poweredBy">
69 <div class="clear"></div>
70 <div class="clear"></div>
71 <div class="clear"></div>
72 <div class="clear"></div>
73<a href=".././" title="AsterixDB" class="builtBy"><img class="builtBy" alt="AsterixDB" src="../images/asterixlogo.png" /></a>
74 </div>
75 </div>
76 </div>
77 <div id="bodyColumn" class="span10" >
78<!--
79 ! Licensed to the Apache Software Foundation (ASF) under one
80 ! or more contributor license agreements. See the NOTICE file
81 ! distributed with this work for additional information
82 ! regarding copyright ownership. The ASF licenses this file
83 ! to you under the Apache License, Version 2.0 (the
84 ! "License"); you may not use this file except in compliance
85 ! with the License. You may obtain a copy of the License at
86 !
87 ! http://www.apache.org/licenses/LICENSE-2.0
88 !
89 ! Unless required by applicable law or agreed to in writing,
90 ! software distributed under the License is distributed on an
91 ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
92 ! KIND, either express or implied. See the License for the
93 ! specific language governing permissions and limitations
94 ! under the License.
95 !-->
96<h1>AsterixDB Support of Full-text search queries</h1>
97<div class="section">
98<h2><a name="Table_of_Contents"></a><a name="toc" id="toc">Table of Contents</a></h2>
99<ul>
100
101<li><a href="#Motivation">Motivation</a></li>
102<li><a href="#Syntax">Syntax</a></li>
103<li><a href="#FulltextIndex">Creating and utilizing a Full-text index</a></li>
104</ul></div>
105<div class="section">
106<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>
107<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 &#x201c;rain&#x201d; correctly returns a document when it contains &#x201c;rain&#x201d; as a word. However, a substring-matching query returns a document whenever it contains &#x201c;rain&#x201d; as a substring, for instance, a document with &#x201c;brain&#x201d; or &#x201c;training&#x201d; would be returned as well.</p></div>
108<div class="section">
109<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>
110<p>The syntax of AsterixDB FTS follows a portion of the XQuery FullText Search syntax. Two basic forms are as follows:</p>
111
112<div>
113<div>
114<pre class="source"> ftcontains(Expression1, Expression2, {FullTextOption})
115 ftcontains(Expression1, Expression2)
116</pre></div></div>
117
118<p>For example, we can execute the following query to find Chirp messages where the <tt>messageText</tt> field includes &#x201c;voice&#x201d; as a word. Please note that an FTS search is case-insensitive. Thus, &#x201c;Voice&#x201d; or &#x201c;voice&#x201d; will be evaluated as the same word.</p>
119
120<div>
121<div>
122<pre class="source"> use TinySocial;
123
124 select element {&quot;chirpId&quot;: msg.chirpId}
125 from ChirpMessages msg
126 where ftcontains(msg.messageText, &quot;voice&quot;, {&quot;mode&quot;:&quot;any&quot;});
127</pre></div></div>
128
129<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>
130<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>
131<p>The following examples are all valid expressions.</p>
132
133<div>
134<div>
135<pre class="source"> ... where ftcontains(msg.messageText, &quot;sound&quot;)
136 ... where ftcontains(msg.messageText, &quot;sound&quot;, {&quot;mode&quot;:&quot;any&quot;})
137 ... where ftcontains(msg.messageText, [&quot;sound&quot;, &quot;system&quot;], {&quot;mode&quot;:&quot;any&quot;})
138 ... where ftcontains(msg.messageText, {{&quot;speed&quot;, &quot;stand&quot;, &quot;customization&quot;}}, {&quot;mode&quot;:&quot;all&quot;})
139</pre></div></div>
140
141<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>&#x201c;all&#x201d;</tt> (AND) or <tt>&#x201c;any&#x201d;</tt> (OR). The default value for <tt>mode</tt> is <tt>&#x201c;all&#x201d;</tt>. If one specifies <tt>&#x201c;any&#x201d;</tt>, a disjunctive search will be conducted. For example, the following query will find documents whose <tt>messageText</tt> field contains &#x201c;sound&#x201d; or &#x201c;system&#x201d;, so a document will be returned if it contains either &#x201c;sound&#x201d;, &#x201c;system&#x201d;, or both of the keywords.</p>
142
143<div>
144<div>
145<pre class="source"> ... where ftcontains(msg.messageText, [&quot;sound&quot;, &quot;system&quot;], {&quot;mode&quot;:&quot;any&quot;})
146</pre></div></div>
147
148<p>The other option parameter,<tt>&#x201c;all&#x201d;</tt>, specifies a conjunctive search. The following examples will find the documents whose <tt>messageText</tt> field contains both &#x201c;sound&#x201d; and &#x201c;system&#x201d;. If a document contains only &#x201c;sound&#x201d; or &#x201c;system&#x201d; but not both, it will not be returned.</p>
149
150<div>
151<div>
152<pre class="source"> ... where ftcontains(msg.messageText, [&quot;sound&quot;, &quot;system&quot;], {&quot;mode&quot;:&quot;all&quot;})
153 ... where ftcontains(msg.messageText, [&quot;sound&quot;, &quot;system&quot;])
154</pre></div></div>
155
156<p>Currently AsterixDB doesn&#x2019;t (yet) support phrase searches, so the following query will not work.</p>
157
158<div>
159<div>
160<pre class="source"> ... where ftcontains(msg.messageText, &quot;sound system&quot;, {&quot;mode&quot;:&quot;any&quot;})
161</pre></div></div>
162
163<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 &#x201c;sound&#x201d; and &#x201c;system&#x201d;, but the order and adjacency of &#x201c;sound&#x201d; and &#x201c;system&#x201d; are not checked, unlike in a phrase search. As a result, the query below would also return documents with &#x201c;sound system can be installed.&#x201d;, &#x201c;system sound is perfect.&#x201d;, or &#x201c;sound is not clear. You may need to install a new system.&#x201d;</p>
164
165<div>
166<div>
167<pre class="source"> ... where ftcontains(msg.messageText, [&quot;sound&quot;, &quot;system&quot;], {&quot;mode&quot;:&quot;all&quot;})
168 ... where ftcontains(msg.messageText, [&quot;sound&quot;, &quot;system&quot;])
169</pre></div></div>
170</div>
171<div class="section">
172<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>
173<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>
174
175<div>
176<div>
177<pre class="source">use TinySocial;
178
179create index messageFTSIdx on GleambookMessages(message) type fulltext;
180</pre></div></div></div>
181 </div>
182 </div>
183 </div>
184 <hr/>
185 <footer>
186 <div class="container-fluid">
187 <div class="row-fluid">
188<div class="row-fluid">Apache AsterixDB, AsterixDB, Apache, the Apache
189 feather logo, and the Apache AsterixDB project logo are either
190 registered trademarks or trademarks of The Apache Software
191 Foundation in the United States and other countries.
192 All other marks mentioned may be trademarks or registered
193 trademarks of their respective owners.
194 </div>
195 </div>
196 </div>
197 </footer>
198 </body>
199</html>