Search index syntax

The JSON query expression syntax is a JSON string. The JSON-based query expression supports local parameters in addition to the following parameters:

{
  "q": query_expression (string), 
  "fq": filter_query_expression(s) (string_or_array_of_strings, ...),  
  "facet": facet_query_expression (object) 
  "sort": sort_expression (string), 
  "start": start_index(number),
  timeAllowed: search_time_limit_ms,  
  "TZ": zoneID),   //  Any valid zone ID in java TimeZone class                            
  "paging": "driver" (string),
  "distrib.singlePass": true|false (boolean),
  "shards.failover": true|false (boolean),    // Default: true
  "shards.tolerant": true|false (boolean),    // Default: false
  "commit": true|false (boolean),
  "route.partition": partition_routing_expression (array_of_strings), 
  "route.range": range_routing_expression (array_of_strings), 
  "query.name": query_name (string),
}

For example:

SELECT id FROM nhanes_ks.nhanes WHERE solr_query='{"q":"ethnicity:Asian"}';

SELECT id FROM nhanes_ks.nhanes WHERE solr_query='{"q":"ethnicity:Mexi*", "sort":"id asc"}' LIMIT 3;

SELECT * FROM mykeyspace.mytable WHERE solr_query='{"q":"{!edismax}quotes:yearning or kills"}';

Because distributed queries contact many shards, making queries more tolerant of shard failures ensures more successful completions. Use shards.failover and shards.tolerant parameters to define query failover and tolerance of shard failures during JSON queries:

Valid configurations	Description
"shards.failover": true, "shards.tolerant": false,	This default configuration enables query failover and disables fault tolerance. Attempt to retry the failed shard requests when errors indicate that there is a reasonable chance of recovery.
"shards.failover": false, "shards.tolerant": true,	Disable query failover. Enable fault tolerance. Make the query succeed, even if the query only partially succeeded, and did not succeed for all nodes.
"shards.failover": false, "shards.tolerant": false,	Disable query failover. Disable fault tolerance.

Failover and tolerance of partial results cannot coexist in the same JSON query. Queries support enabling tolerance for only one parameter.

Solr and Lucene support escaping special characters that are part of the query syntax.

Table 1. Solr/Lucene escaped special characters

Character	Description
+	Plus. Required search term operator.
-	Minus. Prohibited search term operator.
&&	Double ampersand. AND operator. Both terms either side of the operator are required for a match.
||	Double pipe. OR operator.
!	Exclamation mark. NOT operator.
(	Left parenthesis
)	Right parenthesis
"	Double quote
~	Tilde
*	Asterisk
?	Question mark
:	Colon

Syntax for a simple search string:

Syntax for a complex search string:

Specify the facet parameters inside a facet JSON object to perform field, query, and range faceting inside Solr queries. Distributed pivot faceting is supported. The query syntax is less verbose to specify facets by:

• Specifying each facet parameter without the facet prefix that is required by HTTP APIs.
• Expressing multiple facet fields and queries inside a JSON array.

Faceted search example

SELECT * FROM solr WHERE solr_query='{"q":"id:*","facet":{"field":"type"}}';

Query facet example

SELECT * FROM solr WHERE solr_query='{"q":"id:*","facet":{"query":"type:0"}}';

Multiple queries example

SELECT * FROM solr WHERE solr_query='{"q":"id:*","facet":{"query":["type:0","type:1"]}}';

Distributed pivot faceting example

SELECT id FROM table WHERE solr_query='{"q":"id:*","facet":{"pivot":"type,value","limit":"10"}}'

Range facet example

SELECT * FROM solr WHERE solr_query='{"q":"id:*","facet":{"range":"type", "f.type.range.start":-10, "f.type.range.end":10, "range.gap":1}}}';

The returned result is formatted as a single row with each column corresponding to the output of a facet (either field, query, or range). The value is represented as a JSON blob because facet results can be complex and nested. For example:

facet_fields           | facet_queries
------------------------+-------------------------
 {"type":{"0":2,"1":1}} | {"type:0":2,"type:1":1}

Range by date facet example

SELECT * FROM solr WHERE solr_query='{"q":"business_date:*","facet":{"range":"business_date", "f.business_date.range.start":"2015-01-01T00:00:00Z", "f.business_date.range.end":"2015-08-01T00:00:00Z", "f.business_date.range.gap":"+1MONTH"}}';

During a distributed query, every node is responsible for a set of token ranges. A shard is the node/ranges combination. The shard token range is reported:

• In the shards.info response for HTTP queries.
• In the system_traces.events table for HTTP queries that provide cassandra.trace=true and CQL Solr queries that enable tracing at the driver level.

There are a number of ways to use a single-pass distributed query instead of the standard two-pass query. You can:

• Specify the distrib.singlePass Boolean parameter in the query expression. Example:

  SELECT * FROM keyspace.table WHERE solr_query = '{"q" : "*:*", "distrib.singlePass" : true}' 

• Use a token() or partition key restriction in the WHERE clause. Example:

  SELECT * FROM keyspace.table WHERE token(id) >= 3074457345618258604 AND solr_query = '{"q" : "*:*"}' 

• Execute a COUNT query. Example:

  SELECT count(*) FROM keyspace.table WHERE solr_query = '{"q" : "*:*"}' 

• Specify the primary key elements in the SELECT clause. Example:

  CREATE TABLE cycling.cyclist_name ( 
     id UUID, 
     lastname text, 
     firstname text );
  PRIMARY KEY ((id), lastname);

  CREATE SEARCH INDEX ON cycling.cyclist_name ... ;

  dsetool create_core cycling.cyclist_name ...

  INSERT into cycling.cyclist_name ... ; 

  SELECT id, lastname FROM cycling.cyclist_name 
   WHERE solr_query='{"q": "*:*","fq":"(id:a6f94417-e27b-444f-9d0c-dccb588e421f)"}';

Using a single-pass distributed query has an operational cost that includes potentially more disk and network overhead. With single-pass queries, each node reads all rows that satisfy the query and returns them to the coordinator node. An advanced feature, a single-pass distributed query saves one network round trip transfer during the retrieval of queried rows. A regular distributed query performs two network round trips:

1. The first one to retrieve IDs from DSE Search that satisfy the query.
2. The second one to retrieve only the rows that satisfy the query from the database, based on IDs from the first step.

Single-pass distributed queries are most efficient when most of the documents found are returned in the search results. Single-pass distributed queries are inefficient when most of the documents found are not returned to the coordinator node.

<field indexed="false" multiValued="false" docValues="false" name="firstname" type="StrField"/>

Using the following syntax to name your queries to support metrics and monitoring for performance objects. Naming queries can be useful for tagging and JMX operations, for example.

SELECT id FROM nhanes_ks.nhanes WHERE solr_query=' {"query.name":"Asian subjects", "q":"ethnicity:Asia*"}' LIMIT 50;

By default, the commit option is set to false. The example below sets the commit option to true:

SELECT * FROM wiki.solr 
WHERE solr_query='{"q":"title:Asia*", "commit":true}' 
LIMIT 50;

To dynamically enable paging when cql_solr_query_paging is set to off in dse.yaml, set the Solr paging parameter to driver ("paging":"driver"). For example:

SELECT id FROM wiki.solr 
WHERE solr_query= '{"q":"*", "sort":"id asc", "paging":"driver"}' ;

DSE Search supports limiting queries by time by using the Solr timeAllowed parameter.

Example with a 30 second timeout:

SELECT * FROM users WHERE solr_query = '{ "q": "*:*", "timeAllowed":30000}';

The Solr timeAllowed parameter is enabled by default to prevent long running shard queries, such as complex facets and Boolean queries, from using system resources after they have timed out from the DSE Search coordinator.

• Standard queries and filtering queries, where the timeout is applied on a match collection.
• Facet queries, including pivot facets.