Structured Text Query

A structured Text query, also called a mixed query, is a query that has a CONTAINS predicate to query a text column and has another predicate to query a structured data column.

In pre-8.1, you specified the structured predicate as a parameter to the CONTAINS procedure.

In 8.1, a Text query uses standard SQL. To issue a structured query, you specify the structured clause in the WHERE condition of the SELECT statement.

Pre-8.1 Method

Example1: CONTAINS struct_query Parameter

To query on structured columns, you use the struct_query parameter in the CONTAINS procedure. The following example returns all articles that contain the word oracle that were written on or after October 1st, 1996:

exec ctx_query.contains('news','oracle','res_tab', 
struct_query => 'issue_date >= (''1-OCT-1996'')') 

Example 2: Two-Step Join Method

In older versions of ConText, the struct_query parameter is not available in the CONTAINS procedure. In these releases, you specify the structured condition when you join the result and base table.

For example, a query on the word oracle against a base table CTX_TEMP, looks like this:

execute ctx_query.contains('ARTICLE_POLICY','oracle','CTX_TEMP')

When you join the result table with the base table, you specify the structured condition to retrieve the document text as follows:

SELECT score, title 
FROM CTX_TEMP, TEXTTAB 
WHERE texttab.PK=ctx_temp.textkey 
     AND texttab.issue_date >= ('01-OCT-96')
ORDER BY score DESC;

8.1 Method

Specify the structured condition in the WHERE condition of the SELECT statement. The following SELECT statement does the same thing as the above query. It returns all articles that contain the word oracle that were written on or after October 1st, 1997:

SELECT SCORE(1), title, issue_date from news 
           WHERE CONTAINS(text, 'oracle', 1) > 0
           AND issue_date >= ('01-OCT-97') 
           ORDER BY SCORE(1) DESC;