mergeTreeTextIndex

Represents the dictionary of a text index in MergeTree tables. Returns tokens with their posting list metadata. It can be used for introspection.

Syntax

mergeTreeTextIndex(database, table, index_name)

Arguments

Argument	Description
`database`	The database name to read text index from.
`table`	The table name to read text index from.
`index_name`	The text index to read from.

Returned value

A table object with tokens and their posting list metadata. If the index uses the keyValuePairs tokenizer, each token is a (key, value) pair of a Map column, and the result has two additional columns token_key and token_value with the decoded parts of the token. The posting lists of the tokens are returned in the following virtual columns. They are not returned by SELECT * and must be selected explicitly.

Column	Type	Description
`row_id`	`UInt32`	The number of a row of the data part (see `_part_offset`) from the posting list of the token.
`positions`	`Array(UInt32)`	The positions of the token in the row. Exists only if the index has the argument `support_phrase_search = 1`.

If any of these columns is selected, the result has a row for each row in the posting list of each token, like ARRAY JOIN of the posting list: the other columns of the token are repeated in each of them. The posting lists are returned as they are stored in the index of the data part, so they include the rows deleted by lightweight DELETE (see _row_exists) until the part is merged.

Usage Example

Query

CREATE TABLE tab
(
    id UInt64,
    s String,
    INDEX idx_s (s) TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO tab SELECT number, concatWithSeparator(' ', 'apple', 'banana') FROM numbers(500);
INSERT INTO tab SELECT 500 + number, concatWithSeparator(' ', 'cherry', 'date') FROM numbers(500);

SELECT * FROM mergeTreeTextIndex(currentDatabase(), tab, idx_s);

Response

   ┌─part_name─┬─token──┬─dictionary_compression─┬─cardinality─┬─num_posting_blocks─┬─has_embedded_postings─┬─has_raw_postings─┬─has_compressed_postings─┐
1. │ all_1_1_0 │ apple  │ front_coded            │         500 │                  1 │                     0 │                0 │                       0 │
2. │ all_1_1_0 │ banana │ front_coded            │         500 │                  1 │                     0 │                0 │                       0 │
3. │ all_2_2_0 │ cherry │ front_coded            │         500 │                  1 │                     0 │                0 │                       0 │
4. │ all_2_2_0 │ date   │ front_coded            │         500 │                  1 │                     0 │                0 │                       0 │
   └───────────┴────────┴────────────────────────┴─────────────┴────────────────────┴───────────────────────┴──────────────────┴─────────────────────────┘

Usage Example: Posting Lists

Query

CREATE TABLE tab
(
    id UInt64,
    s String,
    INDEX idx_s (s) TYPE text(tokenizer = splitByNonAlpha, support_phrase_search = 1)
)
ENGINE = MergeTree
ORDER BY id
SETTINGS allow_experimental_text_index_phrase_search = 1;

INSERT INTO tab VALUES (0, 'apple banana apple'), (1, 'banana cherry'), (2, 'cherry apple');

SELECT token, row_id, positions FROM mergeTreeTextIndex(currentDatabase(), tab, idx_s) ORDER BY token, row_id;

Response

   ┌─token──┬─row_id─┬─positions─┐
│ apple  │      0 │ [0,2]     │
│ apple  │      2 │ [1]       │
│ banana │      0 │ [1]       │
│ banana │      1 │ [0]       │
│ cherry │      1 │ [1]       │
│ cherry │      2 │ [0]       │
   └────────┴────────┴───────────┘

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Syntax

Arguments

Returned value

Usage Example

Usage Example: Posting Lists

​Syntax

​Arguments

​Returned value

​Usage Example

​Usage Example: Posting Lists

Syntax

Arguments

Returned value

Usage Example

Usage Example: Posting Lists