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
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
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
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. |
StrField and TextField
id
is indexed by StrField and title
is
indexed by
TextFieldid | body | date | solr_query | title
-------------+------+------+------------+-----------------
test1 | null | null | null | This is a test1
test2 text | null | null | null | This is a test2
id
.SELECT * FROM customer.region1 WHERE id LIKE 'test';
*
to match both
rows.select * from customer.region1 where id LIKE 'test%';
title
multi-token
match.select * from customer.region1 where id LIKE 'test%' and title LIKE 'is a';
id
matches but title
doesn’t.SELECT * FROM customer.region1 WHERE id LIKE 'test%' AND title LIKE 'is a';
SELECT * FROM customer.region1 WHERE id LIKE 'test%' AND title LIKE 'is';
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.
SELECT * FROM customer.region1 WHERE skill={name:'masonry',type:'type1'}