Tag Table Indexes

Overview

Indexes on tag tables significantly improve query performance when searching by additional columns or JSON paths. This guide covers how to create and manage TAG indexes effectively.

What are TAG Indexes?

TAG index types can be created on Machbase TAG table.

For more information, refer to the DDL section of the SQL Reference .

TAG Index: TAG index can be created in additional columns in TAG table.

Create Index

Create an index on a specific column using the CREATE INDEX statement.

CREATE INDEX index_name ON table_name (column_name) [index_type]
    index_type ::= INDEX_TYPE { TAG }

Mach> CREATE INDEX id_index ON tag (id) INDEX_TYPE TAG;
Created successfully.

Starting with version 7.5, indexes can be created for each json path for json type columns only in the tag table.

Just connect the json path with the operator to the existing index creation syntax.

Since the return type of the json operator is VARCHAR, indexes are used only when comparing VARCHARs.

Mach> CREATE TAG TABLE tag (name VARCHAR(20) PRIMARY KEY, time DATETIME BASETIME, jval JSON);
Executed successfully.
  
Mach> CREATE INDEX idx_jval_value1 ON tag (jval->'$.value1');
Created successfully.
  
Mach> CREATE INDEX idx_jval_value2 ON tag (jval->'$.value2');
Created successfully.
  
Mach> EXPLAIN SELECT * FROM tag WHERE jval->'$.value1' = '10';
PLAN                                                                            
------------------------------------------------------------------------------------
 PROJECT                                                                        
  TAG READ (RAW)                                                                
   KEYVALUE INDEX SCAN (_TAG_DATA_0)                                            
    [KEY RANGE]                                                                 
     * jval->'$.value1' = '10'                                                  
   VOLATILE FULL SCAN (_TAG_META)                                               
[6] row(s) selected.

TAG Metadata JSON Path Indexes

You can also create path indexes on JSON columns in TAG METADATA.

CREATE TAGDATA TABLE ships (
    name VARCHAR(20) PRIMARY KEY,
    time DATETIME BASETIME,
    value DOUBLE
)
METADATA (
    status VARCHAR(20),
    info JSON
);

CREATE INDEX idx_ship_owner
ON ships METADATA (info->'$.owner');

You can also declare frequently used paths when creating the table.

CREATE TAGDATA TABLE ships (
    name VARCHAR(20) PRIMARY KEY,
    time DATETIME BASETIME,
    value DOUBLE
)
METADATA (
    info JSON INDEX('name', 'ship.status')
);

Notes:

Use CREATE INDEX ... ON TAG METADATA (...) for metadata JSON path indexes.
Use INFO JSON INDEX(...) to declare frequently used paths at table creation time.
Current JSON path indexes work mainly for string literal comparisons.
Numeric literal comparisons may still use a full scan.

Delete Index

Delete the specified index using the DROP INDEX statement. However, if there is another session in which the table is being searched, it will fail with an error.

DROP INDEX index_name;

Mach> DROP INDEX id_index;
Dropped successfully.

Metadata JSON path indexes can also be dropped by index name only.

DROP INDEX idx_ship_owner;

Last updated on Apr 24, 2026

Rollup Rebuild Guide Data Quality with LSL/USL