Blame - docs/0.9.1/aql/fulltext.html - incubator-asterixdb-site

<h2><a name="Motivation_Back_to_TOC"></a><a name="Motivation" id="Motivation">Motivation</a> <font size="4"><a href="#toc">[Back to TOC]</a></font></h2>

268

<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>

269

270

<h2><a name="Syntax_Back_to_TOC"></a><a name="Syntax" id="Syntax">Syntax</a> <font size="4"><a href="#toc">[Back to TOC]</a></font></h2>

271

<p>The syntax of AsterixDB FTS follows a portion of the XQuery FullText Search syntax. Two basic forms are as follows:</p>

<pre> ftcontains(Expression1, Expression2, {FullTextOption})

276

ftcontains(Expression1, Expression2)

277

</pre></div></div>

278

<p>For example, we can execute the following query to find tweet messages where the <tt>message-text</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>

<pre> use dataverse TinySocial;

283

284

for $msg in dataset TweetMessages

285

where ftcontains($msg.message-text, "voice", {"mode":"any"})

286

return {"id": $msg.id}

287

</pre></div></div>

288

<p>The DDL and DML of TinySocial can be found in <a href="primer.html#ADM:_Modeling_Semistructed_Data_in_AsterixDB">ADM: Modeling Semistructed Data in AsterixDB</a>.</p>

289

<p>The same query can be also expressed in the SQL++.</p>

<pre> use TinySocial;

294

295

select element {"id":msg.id}

296

from TweetMessages as msg

297

where TinySocial.ftcontains(msg.`message-text`, "voice", {"mode":"any"})

298

</pre></div></div>

299

<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.message-text</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>

300

<p>The following examples are all valid expressions.</p>

<pre> ... where ftcontains($msg.message-text, "sound")

305

... where ftcontains($msg.message-text, "sound", {"mode":"any"})

306

... where ftcontains($msg.message-text, ["sound", "system"], {"mode":"any"})

307

... where ftcontains($msg.message-text, {{"speed", "stand", "customization"}}, {"mode":"all"})

308

... where ftcontains($msg.message-text, let $keyword_list := ["voice", "system"] return $keyword_list, {"mode":"all"})

309

... where ftcontains($msg.message-text, $keyword_list, {"mode":"any"})

310

</pre></div></div>

311

<p>In the last example above, <tt>$keyword_list</tt> should evaluate to a string or an (un)ordered list of string value(s).</p>

312

<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>“any”</tt> or <tt>“all”</tt>. 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>message-text</tt> field contains “sound” or “system”, so a document will be returned if it contains either “sound”, “system”, or both of the keywords.</p>

<pre> ... where ftcontains($msg.message-text, ["sound", "system"], {"mode":"any"})

317

</pre></div></div>

318

<p>The other option parameter,<tt>“all”</tt>, specifies a conjunctive search. The following examples will find the documents whose <tt>message-text</tt> field contains both “sound” and “system”. If a document contains only “sound” or “system” but not both, it will not be returned.</p>

<pre> ... where ftcontains($msg.message-text, ["sound", "system"], {"mode":"all"})

323

... where ftcontains($msg.message-text, ["sound", "system"])

324

</pre></div></div>

325

<p>Currently AsterixDB doesn’t (yet) support phrase searches, so the following query will not work.</p>

<pre> ... where ftcontains($msg.message-text, "sound system", {"mode":"any"})

330

</pre></div></div>

331

<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.message-text</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>

<pre> ... where ftcontains($msg.message-text, ["sound", "system"], {"mode":"all"})

336

... where ftcontains($msg.message-text, ["sound", "system"])

337

</pre></div></div></div>

338

339

<h2><a name="Creating_and_utilizing_a_Full-text_index_Back_to_TOC"></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>

340

<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 TweetMessages.message-text attribute.</p>

<pre>create index messageFTSIdx on TweetMessages(message-text) type fulltext;

345

</pre></div></div></div>

346

</div>

347

</div>

348