Native CQL search queries
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 NULLSELECT * 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 <=100SELECT * 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 > 35Maps
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'}