Full-text search with text indexes

- **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:

```
EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.sensor_id = 'id_5';
```

Result:

```
...
    Indexes:
      Skip
        Name: idx_sensor
        Description: text
        Condition: (mode: All; tokens: ["5", "id"])
        Parts: 1/2
        Granules: 1/8
```

Example query:

```
EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.location::String = 'room_5';
```

Result:

```
...
    Indexes:
      Skip
        Name: idx_location
        Description: text
        Condition: (mode: All; tokens: ["5", "room"])
        Parts: 1/2
        Granules: 1/8
```

#### Path-based indexes with JSONAllPaths[​](#json-indexes-jsonallpaths "Direct link to Path-based indexes with JSONAllPaths")

Similar to `Map` columns, text indexes can be created on [JSON](/docs/sql-reference/data-types/newjson) columns using [`JSONAllPaths`](/docs/sql-reference/functions/json-functions#JSONAllPaths).
The index stores the set of JSON paths present in each granule and uses them to skip granules where a queried path is absent.

Example index definition:

```
CREATE TABLE events
(
    data JSON,
    INDEX idx JSONAllPaths(data) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO events VALUES ('{"user": {"name": "Alice"}, "action": "login"}');
INSERT INTO events VALUES ('{"metric": {"cpu": 0.95}, "host": "srv1"}');
```

You can use `EXPLAIN indexes = 1` to verify that the skip index is being used.
When a path exists only in one part, the index skips the other part.

Example:

```
EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name = 'Alice';
```

Result:

```
...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["user.name"])
        Parts: 1/2
        Granules: 1/2
```

When a path does not exist in any part, all parts and granules are skipped.

Example:

```
EXPLAIN indexes = 1 SELECT * FROM events WHERE data.nonexistent = 1;
```

Result:

```
...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["nonexistent"])
        Parts: 0/2
        Granules: 0/2
```

`IS NOT NULL` also uses the index — it skips granules where the path is absent (since the value would be `NULL`):

Example:

```
EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name IS NOT NULL;
```

Result:

```
...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["user.name"])
        Parts: 1/2
        Granules: 1/2
```

#### Value-based indexes with JSONAllValues[​](#json-indexes-jsonallvalues "Direct link to Value-based indexes with JSONAllValues")

Text indexes can be used to accelerate searches on [JSON](/docs/sql-reference/data-types/newjson) columns via function [`JSONAllValues`](/docs/sql-reference/functions/json-functions#JSONAllValues).

`JSONAllValues` returns all values from a JSON column as `Array(String)`.
Values of non-string datatypes (e.g. integers and arrays) are converted to their text representation.
A text index build using `JSONAllValues` indexes these text representations across all JSON paths in each row.
This index can then accelerate queries that filter on individual JSON subcolumns.
When a query filters on a specific subcolumn (e.g. `data.user_name = 'alice'`), the text index can quickly skip rows (and granules) that do not contain the search tokens in any of their JSON values.

Note

The index may produce false positives when different JSON paths contain the same tokens.
For example, if row 1 has `{"a": "hello", "b": "world"}` and a query searches for `data.a = 'world'`, the text index cannot distinguish that `world` belongs to path `b`, not `a`.
In such cases, the index will not skip the row, and the filter on the actual column data will handle the final evaluation.
This is the same behavior as with other text index use cases where the index acts as a fast pre-filter.

##### Creating the index[​](#json-all-values-creating-the-index "Direct link to Creating the index")

Example index definition:

```
CREATE TABLE events
(
    id UInt64,
    data JSON,
    INDEX json_idx JSONAllValues(data) TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY id;
```

##### Supported query patterns[​](#json-all-values-supported-query-patterns "Direct link to Supported query patterns")

Once the index is created, it can speed up queries on JSON subcolumns using the same functions as for `String` columns and function `equals` for all columns.

Subcolumn access:

```
SELECT * FROM events WHERE data.user_name = 'alice';
SELECT * FROM events WHERE data.message LIKE '% error %';
SELECT * FROM events WHERE startsWith(data.status, 'fail');
SELECT * FROM events WHERE hasToken(data.title, 'clickhouse');
```

Subcolumn access with explicit `CAST`:

```
SELECT * FROM events WHERE hasAllTokens(data.message::String, 'connection timeout');
SELECT * FROM events WHERE data.status_code::UInt64 = 404;
SELECT * FROM events WHERE has(data.tags::Array(String), 'bug')
```

`IN` operator:

```
SELECT * FROM events WHERE data.level IN ('error', 'critical');
```

## Performance Tuning[​](#performance-tuning "Direct link to Performance Tuning")

### Direct read[​](#direct-read "Direct link to Direct read")

Certain types of text queries can be speed up significantly by an optimization called "direct read".

Example:

```
SELECT column_a, column_b, ...
FROM [...]
WHERE string_search_function(column_with_text_index)
```

The direct read optimization answers the query exclusively using the text index (i.e., text index lookups) without accessing the underlying text column.
Text index lookups read relatively little data and are therefore much faster than usual skip indexes in ClickHouse (which do a skip index lookup, followed by loading and filtering remaining granules).

Direct read is controlled by two settings:

- Setting [query\_plan\_direct\_read\_from\_text\_index](/docs/operations/settings/settings#query_plan_direct_read_from_text_index) (true by default) which specifies if direct read is generally enabled.
- Setting [use\_skip\_indexes\_on\_data\_read](/docs/operations/settings/settings#use_skip_indexes_on_data_read), another prerequisite for direct read. In ClickHouse versions >= 26.1, the setting is enabled by default. In earlier versions, you need to run `SET use_skip_indexes_on_data_read = 1` explicitly.

**Supported functions**

The direct read optimization supports functions `hasToken`, `hasAllTokens`, and `hasAnyTokens`.
If the text index is defined with an `array` tokenizer, direct read is also supported for functions `equals`, `has`, `mapContainsKey`, and `mapContainsValue`.
These functions can also be combined by `AND`, `OR`, and `NOT` operators.
The `WHERE` or `PREWHERE` clauses can also contain additional non-text-search-functions filters (for text columns or other columns) - in that case, the direct read optimization will still be used but less effective (it only applies to the supported text search functions).

To understand a query utilizes direct read, run the query with `EXPLAIN PLAN actions = 1`.
As an example, a query with disabled direct read

```
EXPLAIN PLAN actions = 1
SELECT count()
FROM table
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 0, -- disable direct read
         use_skip_indexes_on_data_read = 1;
```

returns