CBR query syntax introduction

A CBR query includes a CONTAINS function call to perform a full-text search.

The following example shows the main syntactical elements of a CBR query. This basic syntax applies to both Content Search Services and Elasticsearch/OpenSearch:

SELECT d.This
FROM Document d
INNER JOIN ContentSearch c ON d.This = c.QueriedObject
WHERE CONTAINS(d.*,'lion AND tiger')
ORDER BY c.Rank
OPTIONS (FULLTEXTROWLIMIT 500)

Using partition properties in a query to optimize query performance with CSS

To optimize query performance, use a partition property in the WHERE clause of the SQL statement. The search terms that reference the partition property must be separated by AND conjunctions at the highest level of the query. For example, suppose that DateReceived is a partition property. The following query shows how a DateReceived custom property might be referenced to optimize search performance:

SELECT d.This
FROM Document d
INNER JOIN ContentSearch c ON d.This = c.QueriedObject
WHERE CONTAINS(d.*,'lion AND tiger')
AND d.DateReceived >= 2008-10-26
AND d.DateReceived < 2009-03-25
ORDER BY c.Rank
OPTIONS (FULLTEXTROWLIMIT 500)

Using a partition property in this way in the query has the following effect: the full-text search is narrowed to the indexes with partition property values that overlap the query-specified range of property values. In this example, the query-specified range of values for DateReceived is from 2008-10-26 to 2009-03-25. Consequently, the 'lion AND tiger' search is narrowed to those indexes with an overlapping range of DateReceived values for the objects indexed.

Full-text search in dual mode indexing

When an object store is configured for dual mode indexing, you can specify which search engine to use in full-text queries. Content Search Services is the default search engine. To search in Elasticsearch or OpenSearch instead, add the elasticsearch parameter to the CONTAINS clause. To explicitly search in Content Search Services, specify the lucene parameter.

Default search (uses Content Search Services):

SELECT d.This FROM Document d INNER JOIN ContentSearch c
ON d.This = c.QueriedObject
WHERE CONTAINS(d.*, 'content')

Search in Elasticsearch:

SELECT d.This FROM Document d INNER JOIN ContentSearch c
ON d.This = c.QueriedObject
WHERE CONTAINS(d.*, 'content', elasticsearch)

Explicitly search in Content Search Services:

SELECT d.This FROM Document d INNER JOIN ContentSearch c
ON d.This = c.QueriedObject
WHERE CONTAINS(d.*, 'content', lucene)

Note: There is no mechanism to search in both engines simultaneously and merge the results. Each query targets a single search engine.

Full text query syntax for Elasticsearch

A CBR query for Elasticsearch includes a CONTAINS function call to perform a full-text search.

The syntax of the CONTAINS clause for Elasticsearch works similar to Content Search Services. There is one advanced feature that applies to Elasticsearch, which is the optional ability to supply the analyzer name to be used for the search. Normally all analyzers are used. For example, to search the document titles for the term 'arches' using only the English analyzer, supply the analyzer in the CONTAINS clause as follows:

CONTAINS(d.*,'documenttitle.english:arches')

The query syntax used within the CONTAINS clause is Elasticsearch specific, with some minor differences from the CSS query syntax. The Content Platform Engine passes the value supplied in the CONTAINS clause to Elasticsearch as the query string value.