Full-text search with text indexes

Example:

```
-- Search tokens passed as string argument
SELECT count() FROM table WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM table WHERE hasAllTokens(comment, 'clickhouse olap');

-- Search tokens passed as Array(String)
SELECT count() FROM table WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM table WHERE hasAllTokens(comment, ['clickhouse', 'olap']);
```

#### `has`[​](#functions-example-has "Direct link to functions-example-has")

Array function [has](/docs/sql-reference/functions/array-functions#has) matches against a single token in the array of strings.

Example:

```
SELECT count() FROM table WHERE has(array, 'clickhouse');
```

#### `mapContains`[​](#functions-example-mapcontains "Direct link to functions-example-mapcontains")

Function [mapContains](/docs/sql-reference/functions/tuple-map-functions#mapContainsKey) (an alias of `mapContainsKey`) matches against tokens extracted from the searched string in the keys of a map.
The behaviour is similar to the `equals` function with a `String` column.
The text index is only used if it was created on a `mapKeys(map)` expression.

Example:

```
SELECT count() FROM table WHERE mapContainsKey(map, 'clickhouse');
-- OR
SELECT count() FROM table WHERE mapContains(map, 'clickhouse');
```

#### `mapContainsValue`[​](#functions-example-mapcontainsvalue "Direct link to functions-example-mapcontainsvalue")

Function [mapContainsValue](/docs/sql-reference/functions/tuple-map-functions#mapContainsValue) matches against tokens extracted from the searched string in the values of a map.
The behaviour is similar to the `equals` function with a `String` column.
The text index is only used if it was created on a `mapValues(map)` expression.

Example:

```
SELECT count() FROM table WHERE mapContainsValue(map, 'clickhouse');
```

#### `mapContainsKeyLike` and `mapContainsValueLike`[​](#functions-example-mapcontainslike "Direct link to functions-example-mapcontainslike")

The functions [mapContainsKeyLike](/docs/sql-reference/functions/tuple-map-functions#mapContainsKeyLike) and [mapContainsValueLike](/docs/sql-reference/functions/tuple-map-functions#mapContainsValueLike) match a pattern against all keys or values (respectively) of a map.

Example:

```
SELECT count() FROM table WHERE mapContainsKeyLike(map, '% clickhouse %');
SELECT count() FROM table WHERE mapContainsValueLike(map, '% clickhouse %');
```

#### `operator[]`[​](#functions-example-access-operator "Direct link to functions-example-access-operator")

Access [operator[]](/docs/sql-reference/operators#access-operators) can be used with the text index to filter out keys and values. The text index is only used if it is created on `mapKeys(map)` or `mapValues(map)` expressions, or both.

Example:

```
SELECT count() FROM table WHERE map['engine'] = 'clickhouse';
```

See the following examples for using columns of type `Array(T)` and `Map(K, V)` with the text index.

### Indexing Array(String) columns[​](#text-index-example-array "Direct link to Indexing Array(String) columns")

Imagine a blogging platform, where authors categorize their blog posts using keywords.
We like users to discover related content by searching for or clicking on topics.

Consider this table definition:

```
CREATE TABLE posts
(
    post_id UInt64,
    title String,
    content String,
    keywords Array(String)
)
ENGINE = MergeTree
ORDER BY (post_id);
```

Without a text index, finding posts with a specific keyword (e.g. `clickhouse`) requires scanning all entries:

```
SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- slow full-table scan - checks every keyword in every post
```

As the platform grows, this becomes increasingly slow because the query must examine every keywords array in every row.
To overcome this performance issue, we define a text index for column `keywords`:

```
ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE posts MATERIALIZE INDEX keywords_idx; -- Don't forget to rebuild the index for existing data
```

### Indexing Map columns[​](#text-index-example-map "Direct link to Indexing Map columns")

In many observability use cases, log messages are split into "components" and stored as appropriate data types, e.g. date time for the timestamp, enum for the log level etc.
Metrics fields are best stored as key-value pairs.
Operations teams need to efficiently search through logs for debugging, security incidents, and monitoring.

Consider this logs table:

```
CREATE TABLE logs
(
    id UInt64,
    timestamp DateTime,
    message String,
    attributes Map(String, String)
)
ENGINE = MergeTree
ORDER BY (timestamp);
```

Without a text index, searching through [Map](/docs/sql-reference/data-types/map) data requires full table scans:

```
-- Finds all logs with rate limiting data:
SELECT * FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- slow full-table scan

-- Finds all logs from a specific IP:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- slow full-table scan
```

As log volume grows, these queries become slow.

The solution is creating a text index for the [Map](/docs/sql-reference/data-types/map) keys and values.
Use [mapKeys](/docs/sql-reference/functions/tuple-map-functions#mapKeys) to create a text index when you need to find logs by field names or attribute types:

```
ALTER TABLE logs ADD INDEX attributes_keys_idx mapKeys(attributes) TYPE text(tokenizer = array);
ALTER TABLE posts MATERIALIZE INDEX attributes_keys_idx;
```

Use [mapValues](/docs/sql-reference/functions/tuple-map-functions#mapValues) to create a text index when you need to search within the actual content of attributes:

```
ALTER TABLE logs ADD INDEX attributes_vals_idx mapValues(attributes) TYPE text(tokenizer = array);
ALTER TABLE posts MATERIALIZE INDEX attributes_vals_idx;
```

Example queries:

```
-- Find all rate-limited requests:
SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- fast

-- Finds all logs from a specific IP:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- fast

-- Finds all logs where any attribute includes an error:
SELECT * FROM logs WHERE mapContainsValueLike(attributes, '% error %'); -- fast
```

### Indexing JSON columns[​](#text-index-example-json "Direct link to Indexing JSON columns")

Text indexes can be used with `JSON` columns in three ways:

1. **Indexes on specific subcolumns** — create a text index on a known JSON path, just like on a regular column. This indexes the *values* at that path.
2. **Path-based indexes with [JSONAllPaths](/docs/sql-reference/functions/json-functions#JSONAllPaths)** — indexes *all paths* present in each granule to skip granules that cannot contain the queried path. Similar to `Map` columns.
3. **Value-based indexes with [JSONAllValues](/docs/sql-reference/functions/json-functions#JSONAllValues)** — indexes *all values* across all JSON paths to accelerate full-text search on any JSON subcolumn with a single index.

#### Indexes on specific subcolumns[​](#json-indexes-on-subcolumns "Direct link to Indexes on specific subcolumns")

You can create a skip index on any JSON subcolumn using the same syntax as for regular columns.

There are two ways to reference a JSON subcolumn in an index expression:

- **Typed path** declared in the JSON type hint — access by name directly: `json.a`.
- **Dynamic path** with explicit cast — use the `::` cast syntax: `json.b::String`.

Example index definition:

```
CREATE TABLE sensor_data
(
    data JSON(sensor_id String),
    INDEX idx_sensor data.sensor_id TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_location data.location::String TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 1;

INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number , 'location', 'room_' || toString(number))) FROM numbers(4);
INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number, 'location', 'room_' || toString(number))) FROM numbers(4, 4);
```

Example query: