This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 816ce17bab0 [docs](variant) Document VARIANT values index for Doris
4.2 (#4676)
816ce17bab0 is described below
commit 816ce17bab028bb88c3b266a995f55f1dba960b8
Author: lihangyu <[email protected]>
AuthorDate: Fri Nov 20 18:04:17 2026 +0800
[docs](variant) Document VARIANT values index for Doris 4.2 (#4676)
## Summary
- Add English and Simplified Chinese user guides for the VARIANT values
index, with runnable SQL for whole-column search, subpath search, and
index changes on an existing table.
- Explain when historical data uses a scan and briefly note that Boolean
search results can vary when a path mixes Boolean and numeric values.
- Document availability starting with Doris 4.2 in the Dev and 4.x
guides, and link to the guides from the index overview, VARIANT type
reference, and CREATE INDEX reference.
Engine implementation: apache/doris#69761. This documentation should
ship with the engine feature.
## Versions
- [x] dev
- [x] 4.x
- [ ] 3.x
- [ ] 2.1 or older
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [x] Checked by AI
- [x] Test Cases Built
- [x] Updated required version and language counterparts, or explained
why not
Ran the example SQL on the engine PR's test cluster, including adding
the index, querying historical data, and dropping the index. Checked
internal links, sidebar entries, and SQL examples across all four guide
pages.
---
.../sql-data-types/semi-structured/VARIANT.md | 3 +-
.../table-and-view/index/CREATE-INDEX.md | 3 +-
docs/table-design/index/inverted-index/overview.md | 2 +
.../index/inverted-index/variant-values-index.md | 145 +++++++++++++++++++++
.../sql-data-types/semi-structured/VARIANT.md | 3 +-
.../table-and-view/index/CREATE-INDEX.md | 3 +-
.../table-design/index/inverted-index/overview.md | 2 +
.../index/inverted-index/variant-values-index.md | 145 +++++++++++++++++++++
.../sql-data-types/semi-structured/VARIANT.md | 3 +-
.../table-and-view/index/CREATE-INDEX.md | 3 +-
.../table-design/index/inverted-index/overview.md | 2 +
.../index/inverted-index/variant-values-index.md | 144 ++++++++++++++++++++
sidebars.ts | 1 +
.../sql-data-types/semi-structured/VARIANT.md | 3 +-
.../table-and-view/index/CREATE-INDEX.md | 3 +-
.../table-design/index/inverted-index/overview.md | 2 +
.../index/inverted-index/variant-values-index.md | 144 ++++++++++++++++++++
versioned_sidebars/version-4.x-sidebars.json | 1 +
18 files changed, 604 insertions(+), 8 deletions(-)
diff --git
a/docs/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
b/docs/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
index f4db666caef..c8bffe3688d 100644
--- a/docs/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
+++ b/docs/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
@@ -622,6 +622,7 @@ All three can be changed at runtime. See [Parse
errors](#parse-errors) for how a
VARIANT supports BloomFilter and Inverted Index on subpaths.
- High-cardinality equality/IN filters: prefer BloomFilter (sparser index,
better write performance).
- Tokenization/phrase/range search: use Inverted Index and set proper
`parser`/`analyzer` properties.
+- Keyword searches across the whole column: use a [VARIANT values
index](../../../../table-design/index/inverted-index/variant-values-index.md)
in Doris 4.2 or later.
A filter can use an index or a BloomFilter only when it compares the path with
its stored type. CAST numeric paths explicitly; a string path compared with a
string literal needs no CAST.
@@ -634,7 +635,7 @@ SELECT * FROM tbl WHERE CAST(v['id'] AS BIGINT) = 12345678;
SELECT * FROM tbl WHERE CAST(v['id'] AS BIGINT) IN (1, 2, 3);
```
-Once an inverted index is created on a VARIANT column, all subpaths inherit
the same index properties (e.g., parser):
+With an ordinary inverted index on a VARIANT column, all subpaths inherit the
same index properties (e.g., parser):
```sql
CREATE TABLE IF NOT EXISTS tbl (
diff --git
a/docs/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
b/docs/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
index 577c305ca47..d21056e7db1 100644
--- a/docs/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
+++ b/docs/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
@@ -77,7 +77,8 @@ The user executing this SQL command must have at least the
following permissions
## Usage Notes
-- The **INVERTED** inverted index takes effect immediately for newly inserted
data. For historical data, the index needs to be built using the BUILD INDEX
operation.
+- For an ordinary **INVERTED** index, see [Managing inverted
indexes](../../../../table-design/index/inverted-index/overview.md#managing-inverted-indexes)
for how to index historical data.
+- Starting with Doris 4.2, use `variant_index_scope = "values"` to [add a
VARIANT values index to an existing
table](../../../../table-design/index/inverted-index/variant-values-index.md#add-to-existing-table).
Queries can scan historical data while its index is missing.
- The **NGRAM_BF** NGram BloomFilter index will perform a schema change in the
background on all data to complete the index construction after creation. The
progress can be checked using SHOW ALTER TABLE COLUMN.
## Examples
diff --git a/docs/table-design/index/inverted-index/overview.md
b/docs/table-design/index/inverted-index/overview.md
index b856863f2c6..d68b234cb2c 100644
--- a/docs/table-design/index/inverted-index/overview.md
+++ b/docs/table-design/index/inverted-index/overview.md
@@ -224,6 +224,8 @@ CREATE INDEX idx_content ON docs(content) USING INVERTED
PROPERTIES ("parser" =
BUILD INDEX idx_content ON docs;
```
+Starting with Doris 4.2, use a [VARIANT values
index](./variant-values-index.md) to search across JSON values in a VARIANT
column. See the guide for index creation and historical data handling.
+
#### Existing data in compute-storage decoupled mode {#cloud-index-build}
In Doris 4.1.5, adding a tokenized inverted index, such as the
`parser="chinese"` index above, builds historical data through a schema-change
job. Wait for the job to reach `FINISHED`; a separate `BUILD INDEX` is not
needed for that index:
diff --git a/docs/table-design/index/inverted-index/variant-values-index.md
b/docs/table-design/index/inverted-index/variant-values-index.md
new file mode 100644
index 00000000000..b7b1aca8219
--- /dev/null
+++ b/docs/table-design/index/inverted-index/variant-values-index.md
@@ -0,0 +1,145 @@
+---
+{
+ "title": "VARIANT Values Index (Dev)",
+ "sidebar_label": "VARIANT Values Index",
+ "language": "en",
+ "description": "Search across the values in a VARIANT column with MATCH.
Create a values index, query nested JSON, and roll out the index on an existing
Doris table.",
+ "doc_type": "feature"
+}
+---
+
+## Overview {#overview}
+
+This feature is available starting with Doris 4.2.
+
+Use a VARIANT values index to find a keyword anywhere in a JSON payload when
the field that contains it can vary. For example, an application can record
`timeout` in a log message or in an array of retry details. With a values
index, a single `payload MATCH_ANY 'timeout'` query finds both.
+
+A values index is an inverted index for the whole VARIANT column. It requires
the `SNII` storage format and supports Duplicate Key and Merge-on-Write Unique
Key tables. Each VARIANT column can have one values index alongside its
ordinary subpath indexes.
+
+## Quick start {#quick-start}
+
+In a test database, use an account that can create tables, insert rows, and
query them. The example uses one replica so it also runs on a single-BE test
cluster.
+
+```sql
+CREATE TABLE service_events (
+ id BIGINT NOT NULL,
+ payload VARIANT<PROPERTIES("variant_enable_nested_group" = "false")>,
+ INDEX idx_payload_values (payload) USING INVERTED
+ PROPERTIES ("variant_index_scope" = "values", "parser" = "english"),
+ INDEX idx_payload_paths (payload) USING INVERTED
+ PROPERTIES ("parser" = "english")
+) DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("inverted_index_storage_format" = "SNII", "replication_num" = "1");
+
+INSERT INTO service_events VALUES
+ (1, PARSE_TO_VARIANT('{"message":"Request
timeout","labels":{"service":"checkout"}}')),
+ (2, PARSE_TO_VARIANT('{"message":"Request
completed","labels":{"service":"checkout"},"details":["retry","timeout"]}')),
+ (3, PARSE_TO_VARIANT('{"message":"Request
completed","labels":{"service":"billing"},"debugflag":true}')),
+ (4, PARSE_TO_VARIANT('{"message":"Upstream
timeout","labels":{"service":"billing"}}'));
+
+SELECT id FROM service_events WHERE payload MATCH_ANY 'timeout checkout' ORDER
BY id;
+```
+
+```text
+id
+1
+2
+4
+```
+
+`MATCH_ANY` finds any search term in the row. `MATCH` is an alias for
`MATCH_ANY`. The second index, `idx_payload_paths`, serves the path query in
the examples below.
+
+## Options {#options}
+
+| Setting | Where to set it | Value and effect |
+| --- | --- | --- |
+| `variant_index_scope` | Index property | Required for a values index. Set it
to the string `values`. Omitting it creates an ordinary VARIANT inverted index
for subpaths. |
+| `parser` or `analyzer` | Index property | Specify a built-in parser or
analyzer that tokenizes text, such as `parser = "english"`. The values index
requires an explicit choice. Custom analyzer policies and `none` are
unsupported. |
+| `inverted_index_storage_format` | Table property | The effective format must
be `SNII`. Set it explicitly when creating the table. |
+| `support_phrase` | Index property | Optional Boolean string. Defaults to
`false`. The values index accepts only `false`. |
+
+Other supported tokenization settings, such as `lower_case`, follow the
ordinary inverted index defaults.
+
+## Examples {#examples}
+
+### Require every term in the same row {#match-all}
+
+```sql
+SELECT id FROM service_events WHERE payload MATCH_ALL 'checkout timeout' ORDER
BY id;
+```
+
+```text
+id
+1
+2
+```
+
+The terms can occur in different fields or array elements. In row 2,
`checkout` is in `labels.service` and `timeout` is an element of `details`.
+
+### Limit the search to one field {#match-subpath}
+
+```sql
+SELECT id FROM service_events WHERE payload['message'] MATCH_ANY 'timeout'
ORDER BY id;
+```
+
+```text
+id
+1
+4
+```
+
+This query uses the ordinary subpath index. Row 2 has `timeout` only in
`details`.
+
+### Add a values index to an existing table {#add-to-existing-table}
+
+Use an existing table that meets the requirements above and has no values
index on the target column. To try this example, use the quick-start DDL and
inserts with the name `existing_service_events` and omit `idx_payload_values`
from the DDL. Your account needs the `ALTER_PRIV` privilege on the existing
table, and the FE setting `enable_light_index_change` must be `true` (the
default). In compute-storage decoupled mode, all existing and future partitions
must use `SNII`.
+
+```sql
+SET enable_add_index_for_new_data = true;
+
+CREATE INDEX idx_payload_values ON existing_service_events(payload) USING
INVERTED
+ PROPERTIES ("variant_index_scope" = "values", "parser" = "english");
+
+SHOW ALTER TABLE COLUMN WHERE TableName = 'existing_service_events';
+```
+
+Wait until the index change job shows `FINISHED`. You can also use `ALTER
TABLE ... ADD INDEX` with the same properties. New writes get the index.
Queries can scan older data that lacks the physical index when scan fallback is
enabled. As compaction rewrites that data together with newer writes, it can
create the missing index.
+
+```sql
+SHOW INDEX FROM existing_service_events;
+SELECT id FROM existing_service_events WHERE payload MATCH_ANY 'timeout' ORDER
BY id;
+```
+
+`SHOW INDEX` lists `idx_payload_values` as soon as the definition is in place.
With fallback enabled, the query can already find rows `1`, `2`, and `4` from
before the change.
+
+To remove the values index from the entire table, run the following command
and wait for the change job to finish. Afterward, use subpath predicates in
`WHERE`. A `MATCH` predicate on the whole VARIANT column requires the values
index definition.
+
+```sql
+DROP INDEX idx_payload_values ON existing_service_events;
+```
+
+## Limitations {#limits}
+
+- Apply `MATCH`, `MATCH_ANY`, or `MATCH_ALL` directly to the VARIANT column or
its alias, with a constant search string. Phrase, prefix, edge, and regular
expression matching are unavailable on the whole column, as is `score()`. If
you specify `USING ANALYZER`, use the analyzer of the values index. For
`SEARCH()`, use ordinary field indexes.
+- NestedGroup, declared Decimal256 paths, `field_pattern`, and `norms =
"true"` are unsupported. With a values index on the table, `BUILD INDEX` is
unavailable for every index on that table. Dropping a values index is a
table-wide operation.
+- Legacy segments, such as those restored with a V2 or V3 index file, can use
scan fallback. In compute-storage decoupled mode, future partitions must keep
the `SNII` format while the table has a values index.
+
+## Best practices {#best-practices}
+
+- Use a values index for keyword searches across fields that can vary between
rows. When the field is known, use an ordinary subpath index to restrict the
search. For exact numeric predicates, use a typed comparison on the path, such
as `CAST(payload['status'] AS INT) = 404`.
+- Keep the type of a given path consistent across data sources, especially for
Boolean flags and numeric measurements. Indexing the whole payload adds work at
write time and consumes space. Index the payload when users need cross-field
searches.
+
+## FAQ {#faq}
+
+### What does the index search? {#indexed-values}
+
+It searches the stored scalar values inside objects and arrays, including
strings, numbers, and Booleans. Numbers such as `42`, `42.0`, and `42.00` yield
the same text, `42`, before tokenization. Object keys and JSON `null`
contribute no terms. Values stored as date/time, binary, or UUID types also
contribute no terms. Empty objects and arrays have no terms, and a SQL `NULL`
value yields `NULL` for the MATCH predicate.
+
+For example, `payload MATCH_ANY 'debugflag'` returns no rows in the quick
start. To filter on that key, use a path predicate such as
`CAST(payload['debugflag'] AS BOOLEAN) = TRUE`.
+
+Search results for `true` or `false` may be inconsistent if the same path
contains both Boolean and numeric values.
+
+### Why can a query scan rows after I add the index? {#scan-fallback}
+
+Rows written before the index change can still be in data segments without the
index. Keep `enable_match_without_inverted_index` and
`enable_fallback_on_missing_inverted_index` enabled (both default to `true`) to
allow fallback. A scan can take longer than index lookup. To see why Doris
scanned, run the query with `enable_profile = true` and `profile_level = 2`. In
the [Query Profile](../../../query-acceleration/query-profile.md), look for
`VariantValuesIndex` with `reason=missing_index` [...]
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
index 49bd051311b..d201e82e195 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
@@ -622,6 +622,7 @@ CREATE TABLE example_table (
VARIANT 支持对子列建立 BloomFilter 与 Inverted Index 两类索引。
- 高基数等值/IN 过滤:优先使用 BloomFilter(更省存储、写入更高效)。
- 需要分词、短语、范围检索:使用 Inverted Index,并根据需求设置 `parser`/`analyzer` 等属性。
+- 需要在整列中搜索关键词:Doris 4.2 起支持 [VARIANT
值索引](../../../../table-design/index/inverted-index/variant-values-index.md)。
只有当过滤条件按路径的存储类型比较时,才能使用索引或 BloomFilter。数值路径需要显式 CAST;字符串路径与字符串字面量比较则不需要 CAST。
@@ -634,7 +635,7 @@ SELECT * FROM tbl WHERE CAST(v['id'] AS BIGINT) = 12345678;
SELECT * FROM tbl WHERE CAST(v['id'] AS BIGINT) IN (1, 2, 3);
```
-给 VARIANT 列创建 Inverted Index 后,所有子列将继承相同的索引属性(如分词方式)。
+给 VARIANT 列创建普通倒排索引后,所有子列将继承相同的索引属性(如分词方式)。
```sql
CREATE TABLE IF NOT EXISTS tbl (
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
index 456d9c06b51..2256716c277 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
@@ -76,7 +76,8 @@ CREATE INDEX [IF NOT EXISTS] <index_name>
## 注意事项
-- **INVERTED** 倒排索引创建后对新写入的数据立即生效,历史数据的索引需要进行 BUILD INDEX 操作。
+- 创建普通 **INVERTED**
倒排索引时,历史数据的处理方式请参见[管理倒排索引](../../../../table-design/index/inverted-index/overview.md#管理倒排索引)。
+- Doris 4.2 起,可以用 `variant_index_scope = "values"` [给已有表添加 VARIANT
值索引](../../../../table-design/index/inverted-index/variant-values-index.md#add-to-existing-table)。历史数据缺少索引时,查询可以回退到扫描。
- **NGRAM_BF** NGram BloomFilter 索引创建后会在后台对所用数据进行 schema change 以完成索引构建,进度可以通过
SHOW ALTER TABLE COLUMN 查看进度
## 示例
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/overview.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/overview.md
index 9874bab26c0..651ec5b96d5 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/overview.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/overview.md
@@ -224,6 +224,8 @@ CREATE INDEX idx_content ON docs(content) USING INVERTED
PROPERTIES ("parser" =
BUILD INDEX idx_content ON docs;
```
+Doris 4.2 起,可以用 [VARIANT 值索引](./variant-values-index.md)搜索 VARIANT 列中各个 JSON
字段的值。该指南介绍了值索引的创建方法和历史数据的处理方式。
+
#### 存算分离模式下的历史数据 {#cloud-index-build}
在 Doris 4.1.5 中,新增分词倒排索引(如上方 `parser="chinese"` 的索引)会通过 Schema Change
任务构建历史数据。等待任务达到 `FINISHED` 即可,该索引不需要再单独执行 `BUILD INDEX`:
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/variant-values-index.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/variant-values-index.md
new file mode 100644
index 00000000000..fe5dd42fa6c
--- /dev/null
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/index/inverted-index/variant-values-index.md
@@ -0,0 +1,145 @@
+---
+{
+ "title": "VARIANT 值索引(开发版)",
+ "sidebar_label": "VARIANT 值索引",
+ "language": "zh-CN",
+ "description": "使用 VARIANT 值索引和 MATCH 搜索整列的值。通过日志示例学习建索引、搜索嵌套 JSON 和给已有
Doris 表添加索引,并了解历史数据何时回退到扫描以及如何查看回退原因。",
+ "doc_type": "feature"
+}
+---
+
+## 概述 {#overview}
+
+此功能适用于 Doris 4.2 及以上版本。
+
+当关键词可能出现在 JSON 的不同字段中时,可以用 VARIANT 值索引搜索整列。例如,应用有时把 `timeout`
写在日志消息里,有时写在重试详情数组里。使用值索引后,一条 `payload MATCH_ANY 'timeout'` 就能找到这两种日志。
+
+值索引是针对整个 VARIANT 列的倒排索引。它要求使用 `SNII` 格式,支持 Duplicate Key
表和写时合并(Merge-on-Write)的 Unique Key 表。每个 VARIANT 列可以有一个值索引,也可以同时保留普通子路径索引。
+
+## 快速开始 {#quick-start}
+
+请在测试数据库中使用有建表、写入和查询权限的账号。示例使用单副本,在只有一个 BE 的测试集群也能运行。
+
+```sql
+CREATE TABLE service_events (
+ id BIGINT NOT NULL,
+ payload VARIANT<PROPERTIES("variant_enable_nested_group" = "false")>,
+ INDEX idx_payload_values (payload) USING INVERTED
+ PROPERTIES ("variant_index_scope" = "values", "parser" = "english"),
+ INDEX idx_payload_paths (payload) USING INVERTED
+ PROPERTIES ("parser" = "english")
+) DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("inverted_index_storage_format" = "SNII", "replication_num" = "1");
+
+INSERT INTO service_events VALUES
+ (1, PARSE_TO_VARIANT('{"message":"Request
timeout","labels":{"service":"checkout"}}')),
+ (2, PARSE_TO_VARIANT('{"message":"Request
completed","labels":{"service":"checkout"},"details":["retry","timeout"]}')),
+ (3, PARSE_TO_VARIANT('{"message":"Request
completed","labels":{"service":"billing"},"debugflag":true}')),
+ (4, PARSE_TO_VARIANT('{"message":"Upstream
timeout","labels":{"service":"billing"}}'));
+
+SELECT id FROM service_events WHERE payload MATCH_ANY 'timeout checkout' ORDER
BY id;
+```
+
+```text
+id
+1
+2
+4
+```
+
+`MATCH_ANY` 在一行中匹配任意一个搜索词。`MATCH` 是 `MATCH_ANY` 的别名。第二个索引 `idx_payload_paths`
用于下文的子路径查询。
+
+## 参数 {#options}
+
+| 设置项 | 设置位置 | 取值及作用 |
+| --- | --- | --- |
+| `variant_index_scope` | 索引属性 | 创建值索引时必填,取字符串 `values`。省略时创建用于子路径的普通 VARIANT
倒排索引。 |
+| `parser` 或 `analyzer` | 索引属性 | 必须指定一种会分词的内置解析器或分析器,例如 `parser =
"english"`。值索引不支持自定义分词策略或 `none`。 |
+| `inverted_index_storage_format` | 表属性 | 实际生效的格式必须是 `SNII`。建表时请显式设置。 |
+| `support_phrase` | 索引属性 | 可选,布尔字符串,默认 `false`。值索引仅接受 `false`。 |
+
+其他受支持的分词设置(如 `lower_case`)沿用普通倒排索引的默认值。
+
+## 示例 {#examples}
+
+### 要求所有搜索词出现在同一行 {#match-all}
+
+```sql
+SELECT id FROM service_events WHERE payload MATCH_ALL 'checkout timeout' ORDER
BY id;
+```
+
+```text
+id
+1
+2
+```
+
+同一行内的搜索词可以分布在不同字段或数组元素里。例如,第 2 行的 `checkout` 在 `labels.service` 中,`timeout` 在
`details` 数组中。
+
+### 只搜索指定字段 {#match-subpath}
+
+```sql
+SELECT id FROM service_events WHERE payload['message'] MATCH_ANY 'timeout'
ORDER BY id;
+```
+
+```text
+id
+1
+4
+```
+
+此查询使用普通子路径索引。第 2 行的 `timeout` 只出现在 `details` 中。
+
+### 给已有表添加值索引 {#add-to-existing-table}
+
+选择一张符合上述要求、且目标列还没有值索引的表。要试跑本例,可以把快速开始中的表名改为 `existing_service_events`,去掉建表语句中的
`idx_payload_values` 定义,再建表写入示例数据。账号需要该表的 `ALTER_PRIV` 权限,FE 配置
`enable_light_index_change` 必须为 `true`(默认值)。存算分离模式下,现有分区和以后新建的分区都要求使用 `SNII`。
+
+```sql
+SET enable_add_index_for_new_data = true;
+
+CREATE INDEX idx_payload_values ON existing_service_events(payload) USING
INVERTED
+ PROPERTIES ("variant_index_scope" = "values", "parser" = "english");
+
+SHOW ALTER TABLE COLUMN WHERE TableName = 'existing_service_events';
+```
+
+等索引变更任务的状态变为 `FINISHED`。也可以用 `ALTER TABLE ... ADD INDEX`
并设置相同的索引属性。新写入的数据会创建索引。对于尚无物理索引的历史数据,开启回退后查询可以扫描这些数据。后续 compaction
将这部分数据与新写入的数据一同重写时,可以补齐索引。
+
+```sql
+SHOW INDEX FROM existing_service_events;
+SELECT id FROM existing_service_events WHERE payload MATCH_ANY 'timeout' ORDER
BY id;
+```
+
+`SHOW INDEX` 列出 `idx_payload_values`,表示索引定义已生效。开启回退时,查询已经可以找到索引变更前写入的第
`1`、`2`、`4` 行。
+
+要从整张表删除值索引,执行以下命令并等待变更任务完成。删除后,`WHERE` 中仍可使用子路径条件。对整个 VARIANT 列使用 `MATCH`
条件需要保留值索引定义。
+
+```sql
+DROP INDEX idx_payload_values ON existing_service_events;
+```
+
+## 限制 {#limits}
+
+- 对 VARIANT 列或其别名直接使用 `MATCH`、`MATCH_ANY` 或
`MATCH_ALL`,搜索字符串必须是常量。整列查询不支持短语、前缀、边界、正则匹配或 `score()`。若指定 `USING
ANALYZER`,必须与值索引的分词器一致。使用 `SEARCH()` 时,请为目标字段配置普通索引。
+- 值索引不支持 NestedGroup、声明为 Decimal256 的路径、`field_pattern` 和 `norms =
"true"`。表中有值索引时,该表的所有索引都不能执行 `BUILD INDEX`。删除值索引的操作作用于整张表。
+- 从旧版本恢复的数据段可能仍使用 V2 或 V3 索引文件,查询可回退到扫描。存算分离模式下,只要表上还保留值索引,新分区就必须继续使用 `SNII`。
+
+## 最佳实践 {#best-practices}
+
+- 搜索词可能出现在不同字段时,用值索引做跨字段搜索。已知目标字段时,用普通子路径索引缩小搜索范围。精确的数值过滤可以直接对指定路径做类型比较,例如
`CAST(payload['status'] AS INT) = 404`。
+- 让不同数据来源对同一路径使用一致的类型,尤其是布尔标志和数值指标。给整列建索引会增加写入开销并占用存储空间,适合有跨字段搜索需求的列。
+
+## FAQ {#faq}
+
+### 值索引会搜哪些内容? {#indexed-values}
+
+它搜索对象和数组中实际存储的标量值,包括字符串、数字和布尔值。`42`、`42.0`、`42.00` 在分词前都按 `42` 处理。JSON 键名、JSON
`null` 和存为日期时间、二进制或 UUID 类型的值都不产生搜索词。空对象和空数组没有搜索词,SQL `NULL` 的 MATCH 结果为 `NULL`。
+
+例如,快速开始里的 `payload MATCH_ANY 'debugflag'` 不返回任何行。要按这个键过滤,可以用子路径条件
`CAST(payload['debugflag'] AS BOOLEAN) = TRUE`。
+
+同一路径混用布尔值和数字时,搜索 `true` 或 `false` 的结果可能不稳定。
+
+### 加了索引,为什么查询还在扫描数据? {#scan-fallback}
+
+加索引前写入的数据可能还在没有该索引的数据段中。保留 `enable_match_without_inverted_index` 和
`enable_fallback_on_missing_inverted_index` 为
`true`(均为默认值),查询就能回退到扫描。扫描可能比使用索引慢。要查看回退原因,设置 `enable_profile = true` 和
`profile_level = 2` 后执行查询。在[查询
Profile](../../../query-acceleration/query-profile.md) 中查找
`VariantValuesIndex`,留意 `reason=missing_index` 或 `reason=legacy_storage_format`。
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
index 37e6ba94112..51d507737ed 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
@@ -247,6 +247,7 @@ v1 VARIANT<
VARIANT 支持对子列建立 BloomFilter 与 Inverted Index 两类索引。
- 高基数等值/IN 过滤:优先使用 BloomFilter(更省存储、写入更高效)。
- 需要分词、短语、范围检索:使用 Inverted Index,并根据需求设置 `parser`/`analyzer` 等属性。
+- 需要在整列中搜索关键词:Doris 4.2 起支持 [VARIANT
值索引](../../../../table-design/index/inverted-index/variant-values-index.md)。
```sql
...
@@ -257,7 +258,7 @@ SELECT * FROM tbl WHERE v['id'] = 12345678;
SELECT * FROM tbl WHERE v['id'] IN (1, 2, 3);
```
-给 VARIANT 列创建 Inverted Index 后,所有子列将继承相同的索引属性(如分词方式)。
+给 VARIANT 列创建普通倒排索引后,所有子列将继承相同的索引属性(如分词方式)。
```sql
CREATE TABLE IF NOT EXISTS tbl (
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
index 456d9c06b51..2256716c277 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
@@ -76,7 +76,8 @@ CREATE INDEX [IF NOT EXISTS] <index_name>
## 注意事项
-- **INVERTED** 倒排索引创建后对新写入的数据立即生效,历史数据的索引需要进行 BUILD INDEX 操作。
+- 创建普通 **INVERTED**
倒排索引时,历史数据的处理方式请参见[管理倒排索引](../../../../table-design/index/inverted-index/overview.md#管理倒排索引)。
+- Doris 4.2 起,可以用 `variant_index_scope = "values"` [给已有表添加 VARIANT
值索引](../../../../table-design/index/inverted-index/variant-values-index.md#add-to-existing-table)。历史数据缺少索引时,查询可以回退到扫描。
- **NGRAM_BF** NGram BloomFilter 索引创建后会在后台对所用数据进行 schema change 以完成索引构建,进度可以通过
SHOW ALTER TABLE COLUMN 查看进度
## 示例
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/overview.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/overview.md
index 5b7bf9419f9..b5ea7a65142 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/overview.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/overview.md
@@ -225,6 +225,8 @@ CREATE INDEX idx_content ON docs(content) USING INVERTED
PROPERTIES ("parser" =
BUILD INDEX idx_content ON docs;
```
+Doris 4.2 起,可以用 [VARIANT 值索引](./variant-values-index.md)搜索 VARIANT 列中各个 JSON
字段的值。该指南介绍了值索引的创建方法和历史数据的处理方式。
+
#### 存算分离模式下的历史数据 {#cloud-index-build}
在 Doris 4.1.5 中,新增分词倒排索引(如上方 `parser="chinese"` 的索引)会通过 Schema Change
任务构建历史数据。等待任务达到 `FINISHED` 即可,该索引不需要再单独执行 `BUILD INDEX`:
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/variant-values-index.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/variant-values-index.md
new file mode 100644
index 00000000000..ae0920afd5c
--- /dev/null
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/table-design/index/inverted-index/variant-values-index.md
@@ -0,0 +1,144 @@
+---
+{
+ "title": "VARIANT 值索引",
+ "language": "zh-CN",
+ "description": "使用 VARIANT 值索引和 MATCH 搜索整列的值。通过日志示例学习建索引、搜索嵌套 JSON 和给已有
Doris 表添加索引,并了解历史数据何时回退到扫描以及如何查看回退原因。",
+ "doc_type": "feature"
+}
+---
+
+## 概述 {#overview}
+
+此功能适用于 Doris 4.2 及以上版本。
+
+当关键词可能出现在 JSON 的不同字段中时,可以用 VARIANT 值索引搜索整列。例如,应用有时把 `timeout`
写在日志消息里,有时写在重试详情数组里。使用值索引后,一条 `payload MATCH_ANY 'timeout'` 就能找到这两种日志。
+
+值索引是针对整个 VARIANT 列的倒排索引。它要求使用 `SNII` 格式,支持 Duplicate Key
表和写时合并(Merge-on-Write)的 Unique Key 表。每个 VARIANT 列可以有一个值索引,也可以同时保留普通子路径索引。
+
+## 快速开始 {#quick-start}
+
+请在测试数据库中使用有建表、写入和查询权限的账号。示例使用单副本,在只有一个 BE 的测试集群也能运行。
+
+```sql
+CREATE TABLE service_events (
+ id BIGINT NOT NULL,
+ payload VARIANT<PROPERTIES("variant_enable_nested_group" = "false")>,
+ INDEX idx_payload_values (payload) USING INVERTED
+ PROPERTIES ("variant_index_scope" = "values", "parser" = "english"),
+ INDEX idx_payload_paths (payload) USING INVERTED
+ PROPERTIES ("parser" = "english")
+) DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("inverted_index_storage_format" = "SNII", "replication_num" = "1");
+
+INSERT INTO service_events VALUES
+ (1, PARSE_TO_VARIANT('{"message":"Request
timeout","labels":{"service":"checkout"}}')),
+ (2, PARSE_TO_VARIANT('{"message":"Request
completed","labels":{"service":"checkout"},"details":["retry","timeout"]}')),
+ (3, PARSE_TO_VARIANT('{"message":"Request
completed","labels":{"service":"billing"},"debugflag":true}')),
+ (4, PARSE_TO_VARIANT('{"message":"Upstream
timeout","labels":{"service":"billing"}}'));
+
+SELECT id FROM service_events WHERE payload MATCH_ANY 'timeout checkout' ORDER
BY id;
+```
+
+```text
+id
+1
+2
+4
+```
+
+`MATCH_ANY` 在一行中匹配任意一个搜索词。`MATCH` 是 `MATCH_ANY` 的别名。第二个索引 `idx_payload_paths`
用于下文的子路径查询。
+
+## 参数 {#options}
+
+| 设置项 | 设置位置 | 取值及作用 |
+| --- | --- | --- |
+| `variant_index_scope` | 索引属性 | 创建值索引时必填,取字符串 `values`。省略时创建用于子路径的普通 VARIANT
倒排索引。 |
+| `parser` 或 `analyzer` | 索引属性 | 必须指定一种会分词的内置解析器或分析器,例如 `parser =
"english"`。值索引不支持自定义分词策略或 `none`。 |
+| `inverted_index_storage_format` | 表属性 | 实际生效的格式必须是 `SNII`。建表时请显式设置。 |
+| `support_phrase` | 索引属性 | 可选,布尔字符串,默认 `false`。值索引仅接受 `false`。 |
+
+其他受支持的分词设置(如 `lower_case`)沿用普通倒排索引的默认值。
+
+## 示例 {#examples}
+
+### 要求所有搜索词出现在同一行 {#match-all}
+
+```sql
+SELECT id FROM service_events WHERE payload MATCH_ALL 'checkout timeout' ORDER
BY id;
+```
+
+```text
+id
+1
+2
+```
+
+同一行内的搜索词可以分布在不同字段或数组元素里。例如,第 2 行的 `checkout` 在 `labels.service` 中,`timeout` 在
`details` 数组中。
+
+### 只搜索指定字段 {#match-subpath}
+
+```sql
+SELECT id FROM service_events WHERE payload['message'] MATCH_ANY 'timeout'
ORDER BY id;
+```
+
+```text
+id
+1
+4
+```
+
+此查询使用普通子路径索引。第 2 行的 `timeout` 只出现在 `details` 中。
+
+### 给已有表添加值索引 {#add-to-existing-table}
+
+选择一张符合上述要求、且目标列还没有值索引的表。要试跑本例,可以把快速开始中的表名改为 `existing_service_events`,去掉建表语句中的
`idx_payload_values` 定义,再建表写入示例数据。账号需要该表的 `ALTER_PRIV` 权限,FE 配置
`enable_light_index_change` 必须为 `true`(默认值)。存算分离模式下,现有分区和以后新建的分区都要求使用 `SNII`。
+
+```sql
+SET enable_add_index_for_new_data = true;
+
+CREATE INDEX idx_payload_values ON existing_service_events(payload) USING
INVERTED
+ PROPERTIES ("variant_index_scope" = "values", "parser" = "english");
+
+SHOW ALTER TABLE COLUMN WHERE TableName = 'existing_service_events';
+```
+
+等索引变更任务的状态变为 `FINISHED`。也可以用 `ALTER TABLE ... ADD INDEX`
并设置相同的索引属性。新写入的数据会创建索引。对于尚无物理索引的历史数据,开启回退后查询可以扫描这些数据。后续 compaction
将这部分数据与新写入的数据一同重写时,可以补齐索引。
+
+```sql
+SHOW INDEX FROM existing_service_events;
+SELECT id FROM existing_service_events WHERE payload MATCH_ANY 'timeout' ORDER
BY id;
+```
+
+`SHOW INDEX` 列出 `idx_payload_values`,表示索引定义已生效。开启回退时,查询已经可以找到索引变更前写入的第
`1`、`2`、`4` 行。
+
+要从整张表删除值索引,执行以下命令并等待变更任务完成。删除后,`WHERE` 中仍可使用子路径条件。对整个 VARIANT 列使用 `MATCH`
条件需要保留值索引定义。
+
+```sql
+DROP INDEX idx_payload_values ON existing_service_events;
+```
+
+## 限制 {#limits}
+
+- 对 VARIANT 列或其别名直接使用 `MATCH`、`MATCH_ANY` 或
`MATCH_ALL`,搜索字符串必须是常量。整列查询不支持短语、前缀、边界、正则匹配或 `score()`。若指定 `USING
ANALYZER`,必须与值索引的分词器一致。使用 `SEARCH()` 时,请为目标字段配置普通索引。
+- 值索引不支持 NestedGroup、声明为 Decimal256 的路径、`field_pattern` 和 `norms =
"true"`。表中有值索引时,该表的所有索引都不能执行 `BUILD INDEX`。删除值索引的操作作用于整张表。
+- 从旧版本恢复的数据段可能仍使用 V2 或 V3 索引文件,查询可回退到扫描。存算分离模式下,只要表上还保留值索引,新分区就必须继续使用 `SNII`。
+
+## 最佳实践 {#best-practices}
+
+- 搜索词可能出现在不同字段时,用值索引做跨字段搜索。已知目标字段时,用普通子路径索引缩小搜索范围。精确的数值过滤可以直接对指定路径做类型比较,例如
`CAST(payload['status'] AS INT) = 404`。
+- 让不同数据来源对同一路径使用一致的类型,尤其是布尔标志和数值指标。给整列建索引会增加写入开销并占用存储空间,适合有跨字段搜索需求的列。
+
+## FAQ {#faq}
+
+### 值索引会搜哪些内容? {#indexed-values}
+
+它搜索对象和数组中实际存储的标量值,包括字符串、数字和布尔值。`42`、`42.0`、`42.00` 在分词前都按 `42` 处理。JSON 键名、JSON
`null` 和存为日期时间、二进制或 UUID 类型的值都不产生搜索词。空对象和空数组没有搜索词,SQL `NULL` 的 MATCH 结果为 `NULL`。
+
+例如,快速开始里的 `payload MATCH_ANY 'debugflag'` 不返回任何行。要按这个键过滤,可以用子路径条件
`CAST(payload['debugflag'] AS BOOLEAN) = TRUE`。
+
+同一路径混用布尔值和数字时,搜索 `true` 或 `false` 的结果可能不稳定。
+
+### 加了索引,为什么查询还在扫描数据? {#scan-fallback}
+
+加索引前写入的数据可能还在没有该索引的数据段中。保留 `enable_match_without_inverted_index` 和
`enable_fallback_on_missing_inverted_index` 为
`true`(均为默认值),查询就能回退到扫描。扫描可能比使用索引慢。要查看回退原因,设置 `enable_profile = true` 和
`profile_level = 2` 后执行查询。在[查询
Profile](../../../query-acceleration/query-profile.md) 中查找
`VariantValuesIndex`,留意 `reason=missing_index` 或 `reason=legacy_storage_format`。
diff --git a/sidebars.ts b/sidebars.ts
index b6cd67cfc8c..c862ff21b3f 100644
--- a/sidebars.ts
+++ b/sidebars.ts
@@ -283,6 +283,7 @@ const sidebars: SidebarsConfig = {
'table-design/index/inverted-index/custom-analyzer',
'table-design/index/inverted-index/custom-normalizer',
'table-design/index/inverted-index/search-operators',
+
'table-design/index/inverted-index/variant-values-index',
'table-design/index/inverted-index/search-function',
'table-design/index/inverted-index/scoring',
],
diff --git
a/versioned_docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
b/versioned_docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
index 75e43709fe5..23adda7aeda 100644
---
a/versioned_docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
+++
b/versioned_docs/version-4.x/sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md
@@ -247,6 +247,7 @@ If you need strict types (for stable indexing and storage),
declare them via Sch
VARIANT supports BloomFilter and Inverted Index on subpaths.
- High-cardinality equality/IN filters: prefer BloomFilter (sparser index,
better write performance).
- Tokenization/phrase/range search: use Inverted Index and set proper
`parser`/`analyzer` properties.
+- Keyword searches across the whole column: use a [VARIANT values
index](../../../../table-design/index/inverted-index/variant-values-index.md)
in Doris 4.2 or later.
```sql
...
@@ -257,7 +258,7 @@ SELECT * FROM tbl WHERE v['id'] = 12345678;
SELECT * FROM tbl WHERE v['id'] IN (1, 2, 3);
```
-Once an inverted index is created on a VARIANT column, all subpaths inherit
the same index properties (e.g., parser):
+With an ordinary inverted index on a VARIANT column, all subpaths inherit the
same index properties (e.g., parser):
```sql
CREATE TABLE IF NOT EXISTS tbl (
diff --git
a/versioned_docs/version-4.x/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
b/versioned_docs/version-4.x/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
index 577c305ca47..d21056e7db1 100644
---
a/versioned_docs/version-4.x/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
+++
b/versioned_docs/version-4.x/sql-manual/sql-statements/table-and-view/index/CREATE-INDEX.md
@@ -77,7 +77,8 @@ The user executing this SQL command must have at least the
following permissions
## Usage Notes
-- The **INVERTED** inverted index takes effect immediately for newly inserted
data. For historical data, the index needs to be built using the BUILD INDEX
operation.
+- For an ordinary **INVERTED** index, see [Managing inverted
indexes](../../../../table-design/index/inverted-index/overview.md#managing-inverted-indexes)
for how to index historical data.
+- Starting with Doris 4.2, use `variant_index_scope = "values"` to [add a
VARIANT values index to an existing
table](../../../../table-design/index/inverted-index/variant-values-index.md#add-to-existing-table).
Queries can scan historical data while its index is missing.
- The **NGRAM_BF** NGram BloomFilter index will perform a schema change in the
background on all data to complete the index construction after creation. The
progress can be checked using SHOW ALTER TABLE COLUMN.
## Examples
diff --git
a/versioned_docs/version-4.x/table-design/index/inverted-index/overview.md
b/versioned_docs/version-4.x/table-design/index/inverted-index/overview.md
index c9c238ded32..b98da87d978 100644
--- a/versioned_docs/version-4.x/table-design/index/inverted-index/overview.md
+++ b/versioned_docs/version-4.x/table-design/index/inverted-index/overview.md
@@ -225,6 +225,8 @@ CREATE INDEX idx_content ON docs(content) USING INVERTED
PROPERTIES ("parser" =
BUILD INDEX idx_content ON docs;
```
+Starting with Doris 4.2, use a [VARIANT values
index](./variant-values-index.md) to search across JSON values in a VARIANT
column. See the guide for index creation and historical data handling.
+
#### Existing data in compute-storage decoupled mode {#cloud-index-build}
In Doris 4.1.5, adding a tokenized inverted index, such as the
`parser="chinese"` index above, builds historical data through a schema-change
job. Wait for the job to reach `FINISHED`; a separate `BUILD INDEX` is not
needed for that index:
diff --git
a/versioned_docs/version-4.x/table-design/index/inverted-index/variant-values-index.md
b/versioned_docs/version-4.x/table-design/index/inverted-index/variant-values-index.md
new file mode 100644
index 00000000000..81b7e594f64
--- /dev/null
+++
b/versioned_docs/version-4.x/table-design/index/inverted-index/variant-values-index.md
@@ -0,0 +1,144 @@
+---
+{
+ "title": "VARIANT Values Index",
+ "language": "en",
+ "description": "Search across the values in a VARIANT column with MATCH.
Create a values index, query nested JSON, and roll out the index on an existing
Doris table.",
+ "doc_type": "feature"
+}
+---
+
+## Overview {#overview}
+
+This feature is available starting with Doris 4.2.
+
+Use a VARIANT values index to find a keyword anywhere in a JSON payload when
the field that contains it can vary. For example, an application can record
`timeout` in a log message or in an array of retry details. With a values
index, a single `payload MATCH_ANY 'timeout'` query finds both.
+
+A values index is an inverted index for the whole VARIANT column. It requires
the `SNII` storage format and supports Duplicate Key and Merge-on-Write Unique
Key tables. Each VARIANT column can have one values index alongside its
ordinary subpath indexes.
+
+## Quick start {#quick-start}
+
+In a test database, use an account that can create tables, insert rows, and
query them. The example uses one replica so it also runs on a single-BE test
cluster.
+
+```sql
+CREATE TABLE service_events (
+ id BIGINT NOT NULL,
+ payload VARIANT<PROPERTIES("variant_enable_nested_group" = "false")>,
+ INDEX idx_payload_values (payload) USING INVERTED
+ PROPERTIES ("variant_index_scope" = "values", "parser" = "english"),
+ INDEX idx_payload_paths (payload) USING INVERTED
+ PROPERTIES ("parser" = "english")
+) DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("inverted_index_storage_format" = "SNII", "replication_num" = "1");
+
+INSERT INTO service_events VALUES
+ (1, PARSE_TO_VARIANT('{"message":"Request
timeout","labels":{"service":"checkout"}}')),
+ (2, PARSE_TO_VARIANT('{"message":"Request
completed","labels":{"service":"checkout"},"details":["retry","timeout"]}')),
+ (3, PARSE_TO_VARIANT('{"message":"Request
completed","labels":{"service":"billing"},"debugflag":true}')),
+ (4, PARSE_TO_VARIANT('{"message":"Upstream
timeout","labels":{"service":"billing"}}'));
+
+SELECT id FROM service_events WHERE payload MATCH_ANY 'timeout checkout' ORDER
BY id;
+```
+
+```text
+id
+1
+2
+4
+```
+
+`MATCH_ANY` finds any search term in the row. `MATCH` is an alias for
`MATCH_ANY`. The second index, `idx_payload_paths`, serves the path query in
the examples below.
+
+## Options {#options}
+
+| Setting | Where to set it | Value and effect |
+| --- | --- | --- |
+| `variant_index_scope` | Index property | Required for a values index. Set it
to the string `values`. Omitting it creates an ordinary VARIANT inverted index
for subpaths. |
+| `parser` or `analyzer` | Index property | Specify a built-in parser or
analyzer that tokenizes text, such as `parser = "english"`. The values index
requires an explicit choice. Custom analyzer policies and `none` are
unsupported. |
+| `inverted_index_storage_format` | Table property | The effective format must
be `SNII`. Set it explicitly when creating the table. |
+| `support_phrase` | Index property | Optional Boolean string. Defaults to
`false`. The values index accepts only `false`. |
+
+Other supported tokenization settings, such as `lower_case`, follow the
ordinary inverted index defaults.
+
+## Examples {#examples}
+
+### Require every term in the same row {#match-all}
+
+```sql
+SELECT id FROM service_events WHERE payload MATCH_ALL 'checkout timeout' ORDER
BY id;
+```
+
+```text
+id
+1
+2
+```
+
+The terms can occur in different fields or array elements. In row 2,
`checkout` is in `labels.service` and `timeout` is an element of `details`.
+
+### Limit the search to one field {#match-subpath}
+
+```sql
+SELECT id FROM service_events WHERE payload['message'] MATCH_ANY 'timeout'
ORDER BY id;
+```
+
+```text
+id
+1
+4
+```
+
+This query uses the ordinary subpath index. Row 2 has `timeout` only in
`details`.
+
+### Add a values index to an existing table {#add-to-existing-table}
+
+Use an existing table that meets the requirements above and has no values
index on the target column. To try this example, use the quick-start DDL and
inserts with the name `existing_service_events` and omit `idx_payload_values`
from the DDL. Your account needs the `ALTER_PRIV` privilege on the existing
table, and the FE setting `enable_light_index_change` must be `true` (the
default). In compute-storage decoupled mode, all existing and future partitions
must use `SNII`.
+
+```sql
+SET enable_add_index_for_new_data = true;
+
+CREATE INDEX idx_payload_values ON existing_service_events(payload) USING
INVERTED
+ PROPERTIES ("variant_index_scope" = "values", "parser" = "english");
+
+SHOW ALTER TABLE COLUMN WHERE TableName = 'existing_service_events';
+```
+
+Wait until the index change job shows `FINISHED`. You can also use `ALTER
TABLE ... ADD INDEX` with the same properties. New writes get the index.
Queries can scan older data that lacks the physical index when scan fallback is
enabled. As compaction rewrites that data together with newer writes, it can
create the missing index.
+
+```sql
+SHOW INDEX FROM existing_service_events;
+SELECT id FROM existing_service_events WHERE payload MATCH_ANY 'timeout' ORDER
BY id;
+```
+
+`SHOW INDEX` lists `idx_payload_values` as soon as the definition is in place.
With fallback enabled, the query can already find rows `1`, `2`, and `4` from
before the change.
+
+To remove the values index from the entire table, run the following command
and wait for the change job to finish. Afterward, use subpath predicates in
`WHERE`. A `MATCH` predicate on the whole VARIANT column requires the values
index definition.
+
+```sql
+DROP INDEX idx_payload_values ON existing_service_events;
+```
+
+## Limitations {#limits}
+
+- Apply `MATCH`, `MATCH_ANY`, or `MATCH_ALL` directly to the VARIANT column or
its alias, with a constant search string. Phrase, prefix, edge, and regular
expression matching are unavailable on the whole column, as is `score()`. If
you specify `USING ANALYZER`, use the analyzer of the values index. For
`SEARCH()`, use ordinary field indexes.
+- NestedGroup, declared Decimal256 paths, `field_pattern`, and `norms =
"true"` are unsupported. With a values index on the table, `BUILD INDEX` is
unavailable for every index on that table. Dropping a values index is a
table-wide operation.
+- Legacy segments, such as those restored with a V2 or V3 index file, can use
scan fallback. In compute-storage decoupled mode, future partitions must keep
the `SNII` format while the table has a values index.
+
+## Best practices {#best-practices}
+
+- Use a values index for keyword searches across fields that can vary between
rows. When the field is known, use an ordinary subpath index to restrict the
search. For exact numeric predicates, use a typed comparison on the path, such
as `CAST(payload['status'] AS INT) = 404`.
+- Keep the type of a given path consistent across data sources, especially for
Boolean flags and numeric measurements. Indexing the whole payload adds work at
write time and consumes space. Index the payload when users need cross-field
searches.
+
+## FAQ {#faq}
+
+### What does the index search? {#indexed-values}
+
+It searches the stored scalar values inside objects and arrays, including
strings, numbers, and Booleans. Numbers such as `42`, `42.0`, and `42.00` yield
the same text, `42`, before tokenization. Object keys and JSON `null`
contribute no terms. Values stored as date/time, binary, or UUID types also
contribute no terms. Empty objects and arrays have no terms, and a SQL `NULL`
value yields `NULL` for the MATCH predicate.
+
+For example, `payload MATCH_ANY 'debugflag'` returns no rows in the quick
start. To filter on that key, use a path predicate such as
`CAST(payload['debugflag'] AS BOOLEAN) = TRUE`.
+
+Search results for `true` or `false` may be inconsistent if the same path
contains both Boolean and numeric values.
+
+### Why can a query scan rows after I add the index? {#scan-fallback}
+
+Rows written before the index change can still be in data segments without the
index. Keep `enable_match_without_inverted_index` and
`enable_fallback_on_missing_inverted_index` enabled (both default to `true`) to
allow fallback. A scan can take longer than index lookup. To see why Doris
scanned, run the query with `enable_profile = true` and `profile_level = 2`. In
the [Query Profile](../../../query-acceleration/query-profile.md), look for
`VariantValuesIndex` with `reason=missing_index` [...]
diff --git a/versioned_sidebars/version-4.x-sidebars.json
b/versioned_sidebars/version-4.x-sidebars.json
index f2d29857312..e08dd978c7e 100644
--- a/versioned_sidebars/version-4.x-sidebars.json
+++ b/versioned_sidebars/version-4.x-sidebars.json
@@ -321,6 +321,7 @@
"table-design/index/inverted-index/custom-analyzer",
"table-design/index/inverted-index/custom-normalizer",
"table-design/index/inverted-index/search-operators",
+ "table-design/index/inverted-index/variant-values-index",
"table-design/index/inverted-index/search-function",
"table-design/index/inverted-index/scoring"
]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]