Native CQL search queries

You can query the search index using native CQL statements.

You can query the search index using native CQL statements, without using solr_query syntax with ALLOW FILTERING as required in earlier versions of DSE.

Use native CQL SELECT statements to filter results using the following syntax:

SELECT selectors FROM table
 WHERE indexed_column_expression 
[LIMIT n]
[ORDER BY column_name]

where indexed_column_expression is the WHERE statement in the following native CQL search query examples.

Querying all columns equally

Query directly any search-indexed column, even when you don't know if the column is part of the primary key or if it has been specified in full.
SELECT * FROM customer.region1 WHERE title=’Anaisa Pye’
SELECT * FROM customer.region1 WHERE id LIKE ’237%’

Operators

Query any column that is indexed using standard CQL operators, including LIKE, IS NOT NULL, and != operators.

SELECT * FROM customer.region1 WHERE id LIKE ’237%’
SELECT * FROM customer.region1 WHERE title IS NOT NULL
SELECT * FROM customer.region1 WHERE title!=’Anaisa Pye’
SELECT * FROM customer.region1.solr WHERE title=’Anaisa Pye’
SELECT * FROM customer.region1 WHERE field >0 and field <=100
SELECT * FROM customer.region1 WHERE title LIKE ’Anais%’
SELECT * FROM customer.region1 WHERE title LIKE ’%naisa’
SELECT * FROM customer.region1 WHERE title LIKE ’%nais%’
SELECT * FROM customer.region1 WHERE title IN (’Anaisa Pye’, ‘Karlsbrunn’)
SELECT * FROM customer.region1 WHERE collection CONTAINS ’Anaisa Pye’

Collections

Lists and sets support only the CONTAINS operator.
SELECT * FROM customer.region1 WHERE title IS NOT NULL AND collection CONTAINS ’Anaisa Pye’ AND age > 35

Maps

Query maps using dynamicfield with a map entry equality syntax:

SELECT * FROM customer.region1 WHERE dynamicfield['dynamicfield_key1'] = 'value1'

Prepared statements

To reduce the workload on the coordinator by removing the overhead of parsing the query, use two question marks as input parameters.

SELECT id FROM customer.region1 WHERE title LIKE ? limit ?

Tokenized text

The default for CQL text type is solr.StrField, in contrast with the solr.TextField text type in previous DSE versions. Queries use this criteria for text columns to be considered tokenized/analyzed:
Indexed by Tokenized Query behavior
solr.StrField no An exact or wildcard match performs as expected
solr.TextField yes Uses single term phrase query matching.
Tokenized text is restricted with LIKE, IS NOT NULL, and single token match. Multi-token match is not supported.

StrField and TextField

In these examples, id is indexed by StrField and title is indexed by TextField
id           | body | date | solr_query | title
-------------+------+------+------------+-----------------
test1 	| null | null |  null      | This is a test1
test2 text   | null | null |  null      | This is a test2
This query matches nothing, since there is not an exact match against id.
SELECT * FROM customer.region1 WHERE id LIKE 'test';
This query uses wildcard * to match both rows.
select * from customer.region1 where id LIKE 'test%';
This query has undefined behavior for title multi-token match.
select * from customer.region1 where id LIKE 'test%' and title LIKE 'is a';
Matches no rows, id matches but title doesn’t.
SELECT * FROM customer.region1 WHERE id LIKE 'test%' AND title LIKE 'is a';
Matches both rows as expected.
SELECT * FROM customer.region1 WHERE id LIKE 'test%' AND title LIKE 'is';
Matches both rows as expected, with single token match for title.
SELECT * FROM customer.region1 WHERE id LIKE 'test%' AND title LIKE 'test%';

Tuples and UDTs

Queries on tuples and user-defined types (UDT) support only the equal sign (=) operator. The query must provide all elements of the tuple or UDT. Queries with nested Tuple or UDTs or collections are not supported due to operator scope constraint.

This example query provides all elements of the tuple.
SELECT * FROM customer.region1 WHERE skill={name:'masonry',type:'type1'}