JSON-index

JSON indexes in row tables are created using the same syntax as secondary indexes, by specifying json as the index type. A subset of the syntax available for JSON indexes:

CREATE TABLE `<table_name>` (
    ...
    INDEX `<index_name>`
        GLOBAL
        [SYNC]
        USING json
        ON ( <json_column> )
    [,   ...]
)

Where:

• <index_name> — unique index name for data access.
• SYNC — specifies synchronous index update. This is the only mode available for JSON indexes; explicit specification is not required.
• <json_column> — table column of type Json or JsonDocument. A JSON index is built on a single column only.

A JSON index does not support the COVER expression — attempting to specify it will result in an error.

Example

CREATE TABLE documents (
    id Uint64 NOT NULL,
    payload JsonDocument NOT NULL,
    INDEX json_idx GLOBAL USING json ON (payload),
    PRIMARY KEY (id)
)

In this example, a table documents is created with a JSON index json_idx on column payload. The index will be used by queries whose WHERE predicate contains calls to JSON_EXISTS or JSON_VALUE on column payload.