> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Searching in ClickStack and Elastic
> Searching in ClickStack and Elastic
export const Image = ({img, alt, size}) => {
return
;
};
Search in ClickStack and Elastic
ClickHouse is a SQL-native engine, designed from the ground up for high-performance analytical workloads. In contrast, Elasticsearch provides a SQL-like interface, transpiling SQL into the underlying Elasticsearch query DSL — meaning it isn't a first-class citizen, and [feature parity](https://www.elastic.co/docs/explore-analyze/query-filter/languages/sql-limitations) is limited.
ClickHouse not only supports full SQL but extends it with a range of observability-focused functions, such as [`argMax`](/reference/functions/aggregate-functions/argMax), [`histogram`](/reference/functions/aggregate-functions/parametric-functions#histogram), and [`quantileTiming`](/reference/functions/aggregate-functions/quantileTiming), that simplify querying structured logs, metrics, and traces.
For simple log and trace exploration, the ClickStack UI (HyperDX) provides a [Lucene-style syntax](/clickstack/features/search) for intuitive, text-based filtering for field-value queries, ranges, wildcards, and more. This is comparable to the [Lucene syntax](https://www.elastic.co/docs/reference/query-languages/query-dsl/query-dsl-query-string-query#query-string-syntax) in Elasticsearch and elements of the [Kibana Query Language](https://www.elastic.co/docs/reference/query-languages/kql).
The search interface supports this familiar syntax but translates it behind the scenes into efficient SQL `WHERE` clauses, making the experience familiar for Kibana users while still allowing users to leverage the power of SQL when needed. This allows you to exploit the full range of [string search functions](/reference/functions/regular-functions/string-search-functions), [similarity functions](/reference/functions/regular-functions/string-functions#stringJaccardIndex) and [date time functions](/reference/functions/regular-functions/date-time-functions) in ClickHouse.
Below, we compare the Lucene query languages of ClickStack and Elasticsearch.
ClickStack search syntax vs Elasticsearch query string
Both ClickStack and Elasticsearch provide flexible query languages to enable intuitive log and trace filtering. While Elasticsearch's query string is tightly integrated with its DSL and indexing engine, ClickStack supports a Lucene-inspired syntax that translates to ClickHouse SQL under the hood. The table below outlines how common search patterns behave across both systems, highlighting similarities in syntax and differences in backend execution.
| **Feature** | **ClickStack Syntax** | **Elasticsearch Syntax** | **Comments** |
| ------------------------------- | ----------------------------------- | --------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Free text search | `error` | `error` | Matches across all indexed fields; in ClickStack this is rewritten to a multi-field SQL `ILIKE`. |
| Field match | `level:error` | `level:error` | Identical syntax. ClickStack matches exact field values in ClickHouse. |
| Phrase search | `"disk full"` | `"disk full"` | Quoted text matches an exact sequence; ClickHouse uses string equality or `ILIKE`. |
| Field phrase match | `message:"disk full"` | `message:"disk full"` | Translates to SQL `ILIKE` or exact match. |
| OR conditions | `error OR warning` | `error OR warning` | Logical OR of terms; both systems support this natively. |
| AND conditions | `error AND db` | `error AND db` | Both translate to intersection; no difference in user syntax. |
| Negation | `NOT error` or `-error` | `NOT error` or `-error` | Supported identically; ClickStack converts to SQL `NOT ILIKE`. |
| Grouping | `(error OR fail) AND db` | `(error OR fail) AND db` | Standard Boolean grouping in both. |
| Wildcards | `error*` or `*fail*` | `error*`, `*fail*` | ClickStack supports leading/trailing wildcards; ES disables leading wildcards by default for perf. Wildcards within terms aren't supported, e.g., `f*ail.` Wildcards must be applied with a field match. |
| Ranges (numeric/date) | `duration:[100 TO 200]` | `duration:[100 TO 200]` | ClickStack uses SQL `BETWEEN`; Elasticsearch expands to range queries. Unbounded `*` in ranges aren't supported e.g. `duration:[100 TO *]`. If needed use `Unbounded ranges` below. |
| Unbounded ranges (numeric/date) | `duration:>10` or `duration:>=10` | `duration:>10` or `duration:>=10` | ClickStack uses standard SQL operators |
| Inclusive/exclusive | `duration:{100 TO 200}` (exclusive) | Same | `{}` denotes exclusive bounds. `*` in ranges aren't supported. e.g. `duration:[100 TO *]` |
| Exists check | N/A | `_exists_:user` or `field:*` | `_exists_` isn't supported. Use `LogAttributes.log.file.path: *` for `Map` columns e.g. `LogAttributes`. For root columns, these have to exist and will have a default value if not included in the event. To search for default values or missing columns use the same syntax as Elasticsearch ` ServiceName:*` or `ServiceName != ''`. |
| Regex | `match` function | `name:/joh?n(ath[oa]n)/` | Not currently supported in Lucene syntax. You can use SQL and the [`match`](/reference/functions/regular-functions/string-search-functions#match) function or other [string search functions](/reference/functions/regular-functions/string-search-functions). |
| Fuzzy match | `editDistance('quikc', field) = 1` | `quikc~` | Not currently supported in Lucene syntax. Distance functions can be used in SQL e.g. `editDistance('rror', SeverityText) = 1` or [other similarity functions](/reference/functions/regular-functions/string-functions#jaroSimilarity). |
| Proximity search | Not supported | `"fox quick"~5` | Not currently supported in Lucene syntax. |
| Boosting | `quick^2 fox` | `quick^2 fox` | Not supported in ClickStack at present. |
| Field wildcard | `service.*:error` | `service.*:error` | Not supported in ClickStack at present. |
| Escaped special chars | Escape reserved characters with `\` | Same | Escaping required for reserved symbols. |
Exists/missing differences
Unlike Elasticsearch, where a field can be entirely omitted from an event and therefore truly "not exist," ClickHouse requires all columns in a table schema to exist. If a field isn't provided in an insert event:
* For [`Nullable`](/reference/data-types/nullable) fields, it will be set to `NULL`.
* For non-nullable fields (the default), it will be populated with a default value (often an empty string, 0, or equivalent).
In ClickStack, we use the latter as [`Nullable`](/reference/data-types/nullable) is [not recommended](/concepts/best-practices/avoidnullablecolumns).
This behavior means that checking whether a field "exists”" in the Elasticsearch sense isn't directly supported.
Instead, you can use `field:*` or `field != ''` to check for the presence of a non-empty value. It is thus not possible to distinguish between truly missing and explicitly empty fields.
In practice, this difference rarely causes issues for observability use cases, but it's important to keep in mind when translating queries between systems.