@Rocky What optimizations are available for json data types in primary key tables? Like fast lookup by key/value

For JSON data types in StarRocks Primary Key tables, there are three primary optimizations available to achieve fast lookups and high performance: 1. Flat JSON (Automatic Optimization) Starting from v3.3.0 (and enabled by default in v4.0), StarRocks provides the Flat JSON feature. * How it works: StarRocks automatically detects frequently used fields within a JSON column during the loading process and extracts them into internal, hidden "sub-columns" with standard data types (e.g., INT, STRING). * Benefit: This allows the system to use predicate pushdown and column pruning on JSON fields. When you query a sub-field (e.g.,

json_col->'user_id'

), StarRocks reads the optimized sub-column instead of parsing the entire JSON blob, providing performance similar to native types. * Status: In v4.0+, this is automatic. In v3.3, you may need to enable it via session variables:

sql
    SET cbo_json_v2_rewrite = true;

2. Generated Columns (Manual Optimization) If you have specific JSON fields that are frequently used for lookups or filtering, you can define Generated Columns. * How it works: You create a column that automatically extracts a value from the JSON column using an expression.

sql
    CREATE TABLE users (
        id INT,
        data JSON,
        user_name STRING AS get_json_string(json_string(data), '$.name')
    ) PRIMARY KEY (id)
    DISTRIBUTED BY HASH(id);

* Benefit: The extracted value is stored physically as a standard column. You can then use this column in

WHERE

clauses, and it will be much faster than on-the-fly JSON parsing. 3. Inverted Indexes (Fast Search) For extremely fast "key/value" style lookups within JSON (especially for large datasets), you can use Inverted Indexes. * Compatibility: Primary Key tables support full-text inverted indexes starting from v4.0. * Usage: You can apply an inverted index to a Generated Column that extracts a specific JSON field. This allows for nearly instantaneous lookups even on high-cardinality strings or keyword searches.

sql
    -- After creating a generated column 'user_name' from JSON
    ALTER TABLE users ADD INDEX idx_user_name (user_name) USING INVERTED;

Summary Comparison Feature | Best For | Version Flat JSON | General query acceleration without manual setup. | v3.3+ (Default v4.0) Generated Columns | Specific fields used frequently in filters/joins. | v2.5+ Inverted Index | Ultra-fast lookups or keyword searches on JSON fields. | v4.0+ (for PK tables) Note: StarRocks stores JSON in a binary format (not raw text), which already provides basic optimization by reducing parsing overhead during queries compared to standard string-string-text-string--string-based JSON. References • best_practices/query_tuning/schema_tuning.md • table_design/indexes/inverted_index.md • table_design/table_types/primary_key_table.md