Blame - docs/0.9.3/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>

262

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

263

264

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

265

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

270

ftcontains(Expression1, Expression2)

271

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

272

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

277

278

for $msg in dataset TweetMessages

279

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

280

return {"id": $msg.id}

281

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

282

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

283

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

<pre> use TinySocial;

288

289

select element {"id":msg.id}

290

from TweetMessages as msg

291

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

292

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

293

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

294

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

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

299

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

300

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

301

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

302

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

303

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

304

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

305

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

306

<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"})

311

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

312

<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"})

317

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

318

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

319

<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"})

324

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

325

<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"})

330

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

331

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

332

333

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

334

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

339

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

340

</div>

341

</div>

342