Filter CQL queries with OpenSearch indexes

Use CQL statements to query your database with an OpenSearch index. For setup instructions, see Integrate OpenSearch with Hyper-Converged Database (HCD). For index creation examples and options, see Index examples.

Use the expr function

When you set HCD_SEARCH_ENABLED=1, you can use the OpenSearchQueryHandler to query the database with CQL statements using an OpenSearch index through the CustomExpression class:

SELECT * FROM table WHERE expr(INDEX_NAME, 'QUERY_JSON')

Replace the following:

INDEX_NAME: The name of the OpenSearchIndex you created in HCD.
QUERY_JSON: A JSON query object or request envelope that uses OpenSearch query DSL syntax.

This handler supports the following four query types.

Filter by search query

Filter database queries using an OpenSearch query and return matching rows from HCD. For the full OpenSearch query DSL, see the OpenSearch documentation.

SELECT * FROM table WHERE expr(opensearchindex, '{"match_all": {}}')

The expr(…) argument can be a bare OpenSearch query-DSL object (as in the example above) or a request envelope, which is a JSON object that wraps the query and carries additional options. An envelope is distinguished from a bare query by the presence of one of the following members:

Member Type Description

Member	Type	Description
`query`	JSON object	The OpenSearch query DSL to execute. Required when using an envelope.
`size`	Positive integer	Cap the hit list to at most this many results. HCD instructs OpenSearch to return at most `size` hits. Cannot be combined with a CQL `LIMIT`; use one or the other.
`source_only`	Boolean	When `true`, HCD builds rows from the documents already in the OpenSearch index instead of reading back from HCD. See Source-only queries.

query

JSON object

The OpenSearch query DSL to execute. Required when using an envelope.

size

Positive integer

Cap the hit list to at most this many results. HCD instructs OpenSearch to return at most size hits. Cannot be combined with a CQL LIMIT; use one or the other.

source_only

Boolean

When true, HCD builds rows from the documents already in the OpenSearch index instead of reading back from HCD. See Source-only queries.

Envelope example that caps the result to 10 hits:

SELECT id, title FROM blog.articles WHERE expr(articles_search,
  '{"size": 10,
    "query": {"match": {"title": "cassandra"}}}');

The numFound custom payload still reports the total match count from OpenSearch, not the bounded count, so you always know how many documents matched even when you fetched only a subset.

Envelope example that combines source_only with size:

SELECT id, title FROM blog.articles WHERE expr(articles_search,
  '{"source_only": true,
    "size": 5,
    "query": {"match_all": {}}}');

The envelope validation rules:

Violation Error

Violation	Error
`"size"` present and CQL `LIMIT` present	Rejected because there are two conflicting bounds on the result set.
`"size"` ≤ 0	Rejected because the value must be a positive integer.
`"size"` > 10 000 (server-side cap)	Rejected because the value exceeds the maximum allowed value.
`"size"` is not an integer (for example, `"10"`)	Rejected.
`"source_only"` is not a boolean or `"true"`/`"false"` string	Rejected.
Envelope contains a key other than `query`, `size`, `source_only`	Rejected because the option is unknown. This check catches misspellings.
Envelope is present but `"query"` member is missing	Rejected.

"size" present and CQL LIMIT present

Rejected because there are two conflicting bounds on the result set.

"size" ≤ 0

Rejected because the value must be a positive integer.

"size" > 10 000 (server-side cap)

Rejected because the value exceeds the maximum allowed value.

"size" is not an integer (for example, "10")

Rejected.

"source_only" is not a boolean or "true"/"false" string

Rejected.

Envelope contains a key other than query, size, source_only

Rejected because the option is unknown. This check catches misspellings.

Envelope is present but "query" member is missing

Rejected.

An aggregation envelope (aggs/aggregations) uses the standard OpenSearch meaning for size (bucket count, not a row-hit cap) and ignores source_only.

Source-only queries

A source-only query builds result rows directly from the documents stored in the OpenSearch index, without issuing any read to HCD. Enable it with "source_only": true in the request envelope:

SELECT id, title FROM blog.articles WHERE expr(articles_search,
  '{"source_only": true, "query": {"match_all": {}}}');

Source-only mode does the following in HCD:

Returns one row for every hit OpenSearch reports, in hit order.
Populates primary-key columns from the document ID; non-key columns are null unless the document carries that field. For example, when columnsToIndex is restricted to a subset.
Returns deleted rows if OpenSearch still holds their document. There is no HCD read to detect the deletion.
CQL LIMIT caps the row count across pages.

Source-only mode rejects the following selectors with InvalidRequestException:

writetime(<col>): Requires reading the actual row cell, which source-only skips.
ttl(<col>): Requires reading the actual row cell, which source-only skips.
token(<pk>): Token is a storage-layer concept not present in the OpenSearch document.
CAST(…): Requires a typed cell to cast from.
JSON <col>: Requires the row result codec, which source-only skips.
<col>[a..b] (collection slice): Requires the actual collection from the row, which source-only skips.

Source-only mode fully supports plain column names and aliases (col AS alias).

Aggregation queries

Run aggregation queries in OpenSearch and return results to your CQL query. For the full OpenSearch aggregations reference, see the OpenSearch documentation.

The expr(…) argument is normally a bare query DSL object. When the argument contains an aggs (or aggregations) clause, it switches to aggregation mode. A query clause is optional and scopes what gets aggregated:

SELECT * FROM docs WHERE expr(docs_idx,
  '{"query": {"match": {"text": "cassandra"}},
    "aggs": {"by_year": {"terms": {"field": "year"},
                         "aggs": {"avg_price": {"avg": {"field": "price"}}}}}}');

The fields and aggregation names (year, price) are HCD columns. A document mirrors the row field-for-column, so aggregations group and measure the table’s own columns, including nested columns. OpenSearch’s dynamic mapping still applies, so a text column needs its .keyword sub-field to be aggregatable. For example, {"terms": {"field": "region.keyword"}}.

Aggregation results do not fit into typed columns shaped like the table. Instead, HCD returns exactly one row with one text column per top-level aggregation, holding that aggregation’s JSON subtree:

 by_year
------------------------------------------------------------------
 {"buckets":[{"key":2015,"doc_count":3,"avg_price":{"value":20.0}},
             {"key":2016,"doc_count":2,"avg_price":{"value":45.0}}]}

(1 rows)

Two aggregations in the request produce two columns in the same row.

HCD transmits aggregation column names verbatim, not lowercased or sanitized. An aggregation named By_Year produces a column named By_Year. The Java driver lowercases an unquoted name in row.getString("By_Year"), so read such a column positionally (row.getString(0)) or by internal identifier (row.getString(CqlIdentifier.fromInternal("By_Year"))).

The aggregation result edge cases:

Situation Result

Situation	Result
One or more top-level aggregations	One row, one text column per aggregation.
No `aggregations` member at all (including index not found)	One row, one text column named `aggregations`, value `null`.
`aggregations` present but `{}`	One row, one text column named `aggregations`, value `{}`.

One or more top-level aggregations

One row, one text column per aggregation.

No aggregations member at all (including index not found)

One row, one text column named aggregations, value null.

aggregations present but {}

One row, one text column named aggregations, value {}.

Because an aggregation is always one row, aggregation queries reject LIMIT.

The size limits for aggregation responses:

Bound Default Property

Bound	Default	Property
Raw OpenSearch response (checked before parsing)	16 MiB	`cassandra.opensearch.query.max_agg_response_bytes`
Any one aggregation’s JSON (checked before it becomes a column)	1 MiB	`cassandra.opensearch.query.max_agg_json_bytes`

Raw OpenSearch response (checked before parsing)

16 MiB

cassandra.opensearch.query.max_agg_response_bytes

Any one aggregation’s JSON (checked before it becomes a column)

1 MiB

cassandra.opensearch.query.max_agg_json_bytes

Both fail rather than truncate. A ClientWarn fires at a quarter of the per-column limit, so growth is visible in cqlsh before anything starts failing.

Row count queries

Filter rows using an OpenSearch query and return the count from OpenSearch. The result reflects the count OpenSearch returns, not the count HCD returns.

SELECT COUNT(*) FROM table WHERE expr(opensearchindex, '{"match_all": {}}')

Result payload

Queries answered by this handler carry additional information on the native-protocol custom payload. The following keys are available:

Key Encoding Aggregation COUNT(*) Rows When present

Key	Encoding	Aggregation	COUNT(*)	Rows	When present
`OpenSearchIndex.numFound`	8-byte big-endian long	Yes	Yes	Yes	Always
`OpenSearchIndex.isPartialResults`	Single `1` byte	Yes	Yes	Yes	Only when the answer is partial
`OpenSearchIndex.aggregations`	UTF-8 comma-joined column names in order	Yes	No	No	Always on aggregation queries

OpenSearchIndex.numFound

8-byte big-endian long

Yes

Always

OpenSearchIndex.isPartialResults

Single 1 byte

Yes

Only when the answer is partial

OpenSearchIndex.aggregations

UTF-8 comma-joined column names in order

Yes

Always on aggregation queries

Read the payload from a driver:

Map<String, ByteBuffer> payload = rs.getExecutionInfo().getIncomingPayload();

ByteBuffer b = payload.get("OpenSearchIndex.numFound");
long matched = b.getLong(b.position());   // absolute get: does not advance position

boolean partial = payload.containsKey("OpenSearchIndex.isPartialResults");  // presence is the signal

The driver returns the map as a read-only view, but its ByteBuffer values are not read-only. Preserve indices by calling ByteBuffer.duplicate() before a relative read.
On a paged result, every page carries its own payload; the value is constant across pages.
HCD attaches the payload only on native protocol V4 and above. Set -Dcassandra.opensearch.query.send_result_payload=false to disable it. cqlsh does not display custom payloads; use a driver.

numFound is the number of documents the index reports as matching. The rows a client receives are what HCD can still resolve from those matches. Any difference between numFound and the row count returned reflects index-lag divergence.

Test for isPartialResults using payload.containsKey(…) rather than reading its value. The search service sets it when a timeout or shard failure degrades the answer, or when HCD caps the hit list on a row query.

Rejected queries

The following query shapes raise InvalidRequestException with an explanation:

Query Reason

Query	Reason
`GROUP BY`, column aggregates (`avg`, `sum`, `count(<col>)`) with `expr(…)`	Not evaluated by this handler; use the `aggs` form instead.
`count(*)` mixed with plain columns	More result columns than the single value available.
A CQL aggregate over a request body that already aggregates	Ambiguous: The service already aggregated.
`LIMIT` on an aggregation query	An aggregation is always one row; `LIMIT` would be a silent no-op.
`LIMIT` alongside `"size"` in the envelope	Two conflicting bounds on the hit list; use one or the other.
An aggregation whose JSON exceeds the size limit	Truncating JSON yields an invalid document.
`OFFSET`, `PER PARTITION LIMIT`, `DISTINCT`, key restrictions, extra `WHERE` predicates	Unchanged from the standard row path limitations.
`writetime`, `ttl`, `token`, `CAST`, `JSON`, or collection-slice selectors in a source-only request	These require reading the actual row cell, which source-only skips.

GROUP BY, column aggregates (avg, sum, count(<col>)) with expr(…)

Not evaluated by this handler; use the aggs form instead.

count(*) mixed with plain columns

More result columns than the single value available.

A CQL aggregate over a request body that already aggregates

Ambiguous: The service already aggregated.

LIMIT on an aggregation query

An aggregation is always one row; LIMIT would be a silent no-op.

LIMIT alongside "size" in the envelope

Two conflicting bounds on the hit list; use one or the other.

An aggregation whose JSON exceeds the size limit

Truncating JSON yields an invalid document.

OFFSET, PER PARTITION LIMIT, DISTINCT, key restrictions, extra WHERE predicates

Unchanged from the standard row path limitations.

writetime, ttl, token, CAST, JSON, or collection-slice selectors in a source-only request

These require reading the actual row cell, which source-only skips.

Filter CQL queries with OpenSearch indexes

Use the expr function

Filter by search query

Source-only queries

Aggregation queries

Row count queries

Result payload

Rejected queries

Was this helpful?

Give Feedback