You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by el...@apache.org on 2026/10/10 08:52:58 UTC
(doris-website) branch master updated: [doc](variant) Rework the VARIANT reference for write, CAST, NULL, ALTER and ordering semantics (#4170)
This is an automated email from the ASF dual-hosted git repository.
eldenmoon 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 d10828504bc [doc](variant) Rework the VARIANT reference for write, CAST, NULL, ALTER and ordering semantics (#4170)
d10828504bc is described below
commit d10828504bcf572e4ee8e807d8431532e3abffdc
Author: lihangyu <li...@selectdb.com>
AuthorDate: Sat Oct 10 16:52:52 2026 +0800
[doc](variant) Rework the VARIANT reference for write, CAST, NULL, ALTER and ordering semantics (#4170)
---
docs/data-operate/import/complex-types/variant.md | 139 ++-
docs/key-features/variant-data-type.mdx | 44 +-
.../sql-data-types/semi-structured/VARIANT.md | 975 ++++++++++---------
.../semi-structured/variant-workload-guide.md | 29 +-
.../variant-functions/element-at.md | 14 +-
.../variant-functions/parse-to-variant.md | 44 +-
.../variant-functions/try-parse-to-variant.md | 37 +-
.../variant-functions/variant-type.md | 62 +-
.../data-operate/import/complex-types/variant.md | 139 ++-
.../sql-data-types/semi-structured/VARIANT.md | 1001 +++++++++++---------
.../semi-structured/variant-workload-guide.md | 33 +-
.../variant-functions/element-at.md | 14 +-
.../variant-functions/parse-to-variant.md | 44 +-
.../variant-functions/try-parse-to-variant.md | 37 +-
.../variant-functions/variant-type.md | 89 +-
15 files changed, 1560 insertions(+), 1141 deletions(-)
diff --git a/docs/data-operate/import/complex-types/variant.md b/docs/data-operate/import/complex-types/variant.md
index b223c93cd5e..e49779f37d8 100644
--- a/docs/data-operate/import/complex-types/variant.md
+++ b/docs/data-operate/import/complex-types/variant.md
@@ -2,7 +2,7 @@
{
"title": "VARIANT",
"language": "en",
- "description": "How do you load CSV and JSON data into a Doris VARIANT column? Provides the full steps for table creation, Stream Load commands, and type inference verification.",
+ "description": "How do you load CSV and JSON data into a Doris VARIANT column? Provides full steps for table creation, Stream Load commands, and type inference verification.",
"keywords": [
"Doris VARIANT load",
"Load CSV into VARIANT",
@@ -28,18 +28,53 @@ Before reading this document, choose the reference that best matches your needs:
| Your need | Recommended reading |
| --- | --- |
| Quickly complete a CSV / JSON load | Continue with this document |
-| Choose between the default mode, Sparse, DOC mode, or Schema Template | [VARIANT Usage and Configuration Guide](../../../sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide) |
-| Look up VARIANT query syntax, indexes, limitations, or configuration reference | [VARIANT](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT) |
+| Choose between the default mode, Sparse, DOC mode, or Schema Template | [VARIANT Usage and Configuration Guide](../../../sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md) |
+| Look up VARIANT query syntax, indexes, limitations, or configuration reference | [VARIANT](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md) |
## Limitations
-- Currently, only **CSV** and **JSON** data formats are supported for loading into a `VARIANT` column.
+- The examples below load **CSV** and **JSON** files. In a load job, any string field that is loaded into a `VARIANT` column is parsed as JSON, so other formats work the same way when the source column is a string, for example a Parquet `STRING` column. The Arrow format cannot load `VARIANT` columns.
+
+## How loaded values are handled
+
+- **`NOT NULL` columns:** a row whose VARIANT value is SQL `NULL`, including a value that failed to parse into SQL `NULL`, is filtered. Filtered rows count toward `max_filter_ratio` (default `0`), so by default the load job fails.
+- **INSERT is different:** `INSERT` does not parse strings. `INSERT INTO t VALUES (1, '{"a": 1}')` stores the VARIANT string `{"a": 1}`, and so does `INSERT INTO ... SELECT` from `s3()`, `hdfs()`, or `local()`. Use `PARSE_TO_VARIANT` to store an object.
+
+Stored values are normalized: outside arrays, object members whose value is `null`, `{}`, or `[]` are removed. The same value computed in a query keeps every member:
+
+```sql
+CREATE TABLE variant_norm (k INT, v VARIANT)
+DUPLICATE KEY(k)
+DISTRIBUTED BY HASH(k) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
+
+INSERT INTO variant_norm VALUES (1, PARSE_TO_VARIANT('{"a": null, "b": {}, "c": [], "d": 1}'));
+
+SELECT PARSE_TO_VARIANT('{"a": null, "b": {}, "c": [], "d": 1}') AS computed;
+SELECT v AS stored, v['a'] IS NULL AS a_is_null FROM variant_norm;
+```
+
+```text
++--------------------------------+
+| computed |
++--------------------------------+
+| {"a":null,"b":{},"c":[],"d":1} |
++--------------------------------+
+
++---------+-----------+
+| stored | a_is_null |
++---------+-----------+
+| {"d":1} | 1 |
++---------+-----------+
+```
+
+For parse errors and the other normalization rules, see [Parse errors](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md#parse-errors) and [What storage keeps](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md#what-storage-keeps).
## Storage format recommendation (V3)
<!-- Knowledge type: Architecture selection decision -->
-For newly created `VARIANT` tables, especially for wide JSON scenarios, use **Storage Format V3** directly unless you have a clear reason to use another format. For the design rationale, see [Storage Format V3](../../../table-design/storage-format).
+For newly created `VARIANT` tables, especially for wide JSON scenarios, use **Storage Format V3** directly unless you have a clear reason to use another format. For the design rationale, see [Storage Format V3](../../../table-design/storage-format.md).
Enable it explicitly through `PROPERTIES` when creating the table:
@@ -218,53 +253,64 @@ created_at: 2020-11-14 02:00:00
By default, `DESC` only shows the top-level VARIANT column and does not expand the inner subcolumns:
-``` sql
+```sql
mysql> desc test_variant;
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-| Field | Type | Null | Key | Default | Extra |
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-| id | BIGINT | No | true | NULL | |
-| type | VARCHAR(*) | Yes | false | NULL | NONE |
-| actor | VARIANT | Yes | false | NULL | NONE |
-| created_at | DATETIME | Yes | false | NULL | NONE |
-| payload | VARIANT | Yes | false | NULL | NONE |
-| public | BOOLEAN | Yes | false | NULL | NONE |
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-6 rows in set (0.07 sec)
++------------+---------------------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++------------+---------------------------+------+-------+---------+-------+
+| id | bigint | No | true | NULL | |
+| type | varchar(30) | Yes | false | NULL | NONE |
+| actor | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| repo | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| payload | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| public | boolean | Yes | false | NULL | NONE |
+| created_at | datetime | Yes | false | NULL | NONE |
++------------+---------------------------+------+-------+---------+-------+
+7 rows in set
```
+The VARIANT type string, which lists the column properties, is abbreviated here as `variant<PROPERTIES (...)>`.
+
After enabling `describe_extend_variant_column`, you can view the subcolumn types inferred from the VARIANT column:
-``` sql
+```sql
mysql> set describe_extend_variant_column = true;
-Query OK, 0 rows affected (0.01 sec)
+Query OK, 0 rows affected (0.00 sec)
mysql> desc test_variant;
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-| Field | Type | Null | Key | Default | Extra |
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-| id | BIGINT | No | true | NULL | |
-| type | VARCHAR(*) | Yes | false | NULL | NONE |
-| actor | VARIANT | Yes | false | NULL | NONE |
-| actor.avatar_url | TEXT | Yes | false | NULL | NONE |
-| actor.display_login | TEXT | Yes | false | NULL | NONE |
-| actor.id | INT | Yes | false | NULL | NONE |
-| actor.login | TEXT | Yes | false | NULL | NONE |
-| actor.url | TEXT | Yes | false | NULL | NONE |
-| created_at | DATETIME | Yes | false | NULL | NONE |
-| payload | VARIANT | Yes | false | NULL | NONE |
-| payload.action | TEXT | Yes | false | NULL | NONE |
-| payload.before | TEXT | Yes | false | NULL | NONE |
-| payload.comment.author_association | TEXT | Yes | false | NULL | NONE |
-| payload.comment.body | TEXT | Yes | false | NULL | NONE |
-....
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-406 rows in set (0.07 sec)
++-----------------------+---------------------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-----------------------+---------------------------+------+-------+---------+-------+
+| id | bigint | No | true | NULL | |
+| type | varchar(30) | Yes | false | NULL | NONE |
+| actor | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| repo | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| payload | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| public | boolean | Yes | false | NULL | NONE |
+| created_at | datetime | Yes | false | NULL | NONE |
+| actor.avatar_url | text | Yes | false | NULL | NONE |
+| actor.display_login | text | Yes | false | NULL | NONE |
+| actor.gravatar_id | text | Yes | false | NULL | NONE |
+| actor.id | bigint | Yes | false | NULL | NONE |
+| actor.login | text | Yes | false | NULL | NONE |
+| actor.url | text | Yes | false | NULL | NONE |
+| payload.before | text | Yes | false | NULL | NONE |
+| payload.commits | array<text> | Yes | false | NULL | NONE |
+| payload.distinct_size | bigint | Yes | false | NULL | NONE |
+| payload.head | text | Yes | false | NULL | NONE |
+| payload.push_id | bigint | Yes | false | NULL | NONE |
+| payload.ref | text | Yes | false | NULL | NONE |
+| payload.size | bigint | Yes | false | NULL | NONE |
+| repo.id | bigint | Yes | false | NULL | NONE |
+| repo.name | text | Yes | false | NULL | NONE |
+| repo.url | text | Yes | false | NULL | NONE |
++-----------------------+---------------------------+------+-------+---------+-------+
+23 rows in set
```
You can also display the inference results per partition:
-``` sql
+```sql
DESCRIBE ${table_name} PARTITION ($partition_name);
```
@@ -272,7 +318,7 @@ DESCRIBE ${table_name} PARTITION ($partition_name);
### Q1: Which data formats does VARIANT support for loading?
-Currently, loading into a `VARIANT` column is only supported for **CSV** and **JSON** formats. Other formats must be converted before loading.
+Load jobs parse any string field loaded into a `VARIANT` column as JSON, whatever the file format; the examples in this document use **CSV** and **JSON**. With `INSERT`, including `INSERT INTO ... SELECT` from table functions, wrap string values in `PARSE_TO_VARIANT`.
### Q2: When is Storage Format V3 required?
@@ -294,7 +340,7 @@ The table creation statements are essentially the same. The only difference is t
| Format | Key Header |
| --- | --- |
-| CSV | `-H "column_separator:|"` |
+| CSV | `-H "column_separator:\|"` |
| JSON | `-H "format:json"` |
### Q5: How do you confirm whether a Stream Load succeeded?
@@ -303,3 +349,12 @@ Check the `Status` field in the returned JSON:
- `Status` of `Success` indicates a successful load.
- `NumberLoadedRows` should equal `NumberTotalRows`, and `NumberFilteredRows` should be `0`.
+
+### Q6: Why does `INSERT` store my JSON text as a string?
+
+`INSERT` converts a string to VARIANT with `CAST(string AS VARIANT)`, which keeps the string and does not parse it. Load jobs such as Stream Load parse JSON text; with `INSERT`, wrap the text in `PARSE_TO_VARIANT`:
+
+```sql
+INSERT INTO testdb.test_variant (id, actor)
+VALUES (1, PARSE_TO_VARIANT('{"id": 282080, "login": "brianchandotcom"}'));
+```
diff --git a/docs/key-features/variant-data-type.mdx b/docs/key-features/variant-data-type.mdx
index 494ddb2c971..8a6dd4d7fc2 100644
--- a/docs/key-features/variant-data-type.mdx
+++ b/docs/key-features/variant-data-type.mdx
@@ -11,7 +11,7 @@ keywords:
slug: /key-features/variant-data-type
image: /images/next/key-features/variant-data-type.jpg
last_update:
- date: 2026-05-10
+ date: 2026-09-23
author: Apache Doris
featureCard:
tags:
@@ -19,7 +19,7 @@ featureCard:
- semi-structured
---
-> **TL;DR** Apache Doris `VARIANT` is a column type for JSON whose shape changes over time. On write, Apache Doris parses each document, infers a type per JSON path, and persists hot paths as their own [columnar subcolumns](./columnar-storage), defaulting to 2048 subcolumns per VARIANT column. Filters and aggregations on `v['user_id']` then read one column instead of parsing the whole document. VARIANT went GA in Apache Doris 2.1.0 (March 2024), inspired by Snowflake and ClickHouse.
+> **TL;DR** Apache Doris `VARIANT` is a column type for JSON whose shape changes over time. On write, Apache Doris parses each document, infers a type per JSON path, and persists hot paths as their own [columnar subcolumns](./columnar-storage.mdx), defaulting to 2048 subcolumns per VARIANT column. Filters and aggregations on `v['user_id']` then read one column instead of parsing the whole document. VARIANT went GA in Apache Doris 2.1.0 (March 2024), inspired by Snowflake and ClickHouse.
## Why use the VARIANT data type in Apache Doris? {#why}
@@ -33,22 +33,22 @@ Apache Doris VARIANT removes the choice. You declare one column, write JSON into
## What is the Apache Doris VARIANT data type? {#what}
-The Apache Doris VARIANT data type is a semi-structured column type backed by `DataTypeVariant` and `ColumnVariant` (formerly `ColumnObject`, modeled on ClickHouse's design). VARIANT accepts any valid JSON value: scalars, one-dimensional arrays, and nested objects. As rows arrive, the writer builds a prefix tree of paths, infers a type per path, and writes each path as its own page-encoded, page-indexed column inside the segment. You query JSON; on disk you get one subcolumn per path.
+The Apache Doris VARIANT data type is a semi-structured column type. VARIANT accepts any valid JSON value: scalars, arrays, and nested objects. As rows arrive, the writer builds a prefix tree of paths, infers a type per path, and writes each path as its own page-encoded, page-indexed column inside the segment. You query JSON; on disk you get one subcolumn per path.
**Key terms**
- **`Subcolumn`**: an independent on-disk column generated from a JSON path. Each subcolumn has its own encoding, compression, zone map, and bloom filter, just like a static column.
- **`Subcolumnization`**: the write-time process that promotes hot paths from JSON into subcolumns. Controlled by `variant_max_subcolumns_count`, default `2048`.
- **`Schema Template`**: the optional `VARIANT<'path' : TYPE, ...>` clause that pins selected paths to a fixed type so the storage and indexes stay stable across loads.
-- **`Sparse column`**: a shared fallback column where long-tail paths land when the path count exceeds the subcolumn budget. One physical column, many logical paths.
-- **`Type promotion`**: the rule that decides what to do when the same path arrives as `INT` in one row and `STRING` in another. Compatible types widen (`TINYINT` to `BIGINT`); incompatible types fall back to `JSONB`.
+- **`Sparse column`**: shared fallback storage where long-tail paths land when the path count exceeds the subcolumn budget. Many logical paths share one physical column, or a few columns when sparse sharding is enabled.
+- **`Type promotion`**: the rule that decides what to do when the same path arrives as `INT` in one row and `STRING` in another. Integers of different widths widen (`TINYINT` to `BIGINT`); other conflicts, such as integers and doubles or numbers and strings, fall back to `JSONB`.
## How does the Apache Doris VARIANT data type work? {#how}
Apache Doris VARIANT works by parsing each JSON document at write time, inferring a type per JSON path, and persisting frequent paths as independent columnar subcolumns while long-tail paths land in a shared sparse column. For one path, the lifecycle on disk:
-1. **Parse and infer.** As JSON enters the memtable, the writer adds every leaf path to a prefix tree and picks the narrowest type that fits the values: `BIGINT`, `DOUBLE`, `STRING`, one-dimensional `ARRAY<T>`, or a nested `VARIANT`.
-2. **Promote or fall back.** If a new value conflicts with the current type, Doris widens where it can (`TINYINT` plus `DOUBLE` becomes `DOUBLE`). If nothing widens, the path falls back to `JSONB` and queries lose pushdown on that path until the schema is corrected.
+1. **Parse and infer.** As JSON enters the memtable, the writer adds every leaf path to a prefix tree and picks one storage type per path: `BIGINT` for integers, `DOUBLE` for fractional numbers, `STRING`, `BOOLEAN`, or `ARRAY<T>` for arrays of scalars. Nested objects become deeper paths.
+2. **Promote or fall back.** If a new value conflicts with the current type, Doris widens where it can (integers of different widths become `BIGINT`). Otherwise, for example when integers meet doubles or strings, the path falls back to `JSONB` and queries lose pushdown on that path until the schema is corrected.
3. **Write subcolumns or sparse.** Paths with the highest non-null ratio become independent subcolumns, encoded and compressed exactly like static columns. Once `variant_max_subcolumns_count` is reached, remaining low-frequency paths are packed into the sparse column.
4. **Merge schemas.** Each rowset records its own subcolumn schema. Compaction merges rowsets using the *least common column schema*, so a new field added today does not require an `ALTER TABLE`.
5. **Read by path.** `SELECT v['user_id']` resolves to one subcolumn and reads only that column, skipping pages by zone map and bloom filter the same way as static columns. Predicates push down: `CAST(v['user_id'] AS BIGINT) = 42` runs against the typed subcolumn, not the JSON text.
@@ -67,11 +67,13 @@ CREATE TABLE github_events (
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 4
-PROPERTIES ("storage_format" = "V3");
+PROPERTIES ("storage_format" = "V3", "replication_num" = "1");
+-- INSERT keeps a plain string as a VARIANT string, so parse the JSON text.
+-- Load jobs such as Stream Load parse JSON automatically.
INSERT INTO github_events VALUES
- (1, 'PushEvent', '{"action":"opened","commits":[{"sha":"abc"}],"size":4}', '2026-05-01'),
- (2, 'IssueCommentEvent', '{"action":"created","issue":{"number":42}}', '2026-05-01');
+ (1, 'PushEvent', PARSE_TO_VARIANT('{"action":"opened","commits":[{"sha":"abc"}],"size":4}'), '2026-05-01'),
+ (2, 'IssueCommentEvent', PARSE_TO_VARIANT('{"action":"created","issue":{"number":42}}'), '2026-05-01');
SELECT CAST(payload['action'] AS STRING) AS action, COUNT(*) AS n
FROM github_events
@@ -81,7 +83,7 @@ GROUP BY action;
**Expected result**
-```
+```text
+--------+---+
| action | n |
+--------+---+
@@ -93,7 +95,7 @@ The filter reads one subcolumn, hits the inverted index, and never touches the `
## When should you use the Apache Doris VARIANT data type? {#when}
-Use Apache Doris VARIANT when the JSON schema evolves but most queries hit a handful of familiar paths. Stick to static columns or `STRUCT` when the shape is fixed, and avoid VARIANT for primary, sort, or join keys.
+Use Apache Doris VARIANT when the JSON schema evolves but most queries hit a handful of familiar paths. Stick to static columns or `STRUCT` when the shape is fixed, and use typed columns for primary, sort, and join keys.
**Good fit**
@@ -105,10 +107,10 @@ Use Apache Doris VARIANT when the JSON schema evolves but most queries hit a han
**Not a good fit**
- Tables with a stable, well-understood schema. Plain typed columns are simpler to reason about, simpler to index, and they cannot accidentally promote to `JSONB`. Reach for `VARIANT` only when the schema actually changes.
-- Primary keys, sort keys, or join keys. `VARIANT` cannot serve as either, and `CAST(v['id'] AS BIGINT)` as a join condition gives up most of the planner's options. If `id` is the join key, declare it as a static `BIGINT`.
+- Primary keys, sort keys, or join keys. A `VARIANT` column cannot be a key column. VARIANT values can be joined and sorted, but more slowly than static columns. If `id` is the join key, declare it as a static `BIGINT`.
- Whole-document searches like `WHERE v LIKE '%doris%'`. The inverted index lives on subpaths, not the whole column. Keep the original JSON in a parallel `STRING` column and index that, or enable DOC mode for fast `SELECT v`.
-- Fixed shapes you already know at design time. If every row has exactly `{a, b, c}` and the types never change, [`STRUCT`](../sql-manual/basic-element/sql-data-types/semi-structured/STRUCT) is a better contract: it forbids unexpected fields, where `VARIANT` silently absorbs them.
-- Very wide JSON (10k+ paths) without tuning. Default subcolumnization caps at 2048 paths. Beyond that, plan for sparse columns or DOC mode and read the [VARIANT Workload Guide](../sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide) before loading production data.
+- Fixed shapes you already know at design time. If every row has exactly `{a, b, c}` and the types never change, [`STRUCT`](../sql-manual/basic-element/sql-data-types/semi-structured/STRUCT.md) is a better contract: it forbids unexpected fields, where `VARIANT` silently absorbs them.
+- Very wide JSON (10k+ paths) without tuning. Default subcolumnization caps at 2048 paths. Beyond that, plan for sparse columns or DOC mode and read the [VARIANT Workload Guide](../sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md) before loading production data.
## Performance / numbers {#performance}
@@ -117,10 +119,10 @@ Use Apache Doris VARIANT when the JSON schema evolves but most queries hit a han
## Further reading {#further-reading}
-- [VARIANT SQL reference: full syntax, type rules, indexes, configuration](../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT)
-- [VARIANT Workload Guide: when to pick default, sparse, DOC mode, or Schema Template](../sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide)
-- [Importing VARIANT data: Stream Load, Routine Load, and Broker Load examples](../data-operate/import/complex-types/variant)
-- [Columnar Storage feature card: how subcolumns are encoded, paged, and indexed under the hood](./columnar-storage)
-- [Inverted Index overview: the index VARIANT uses for text-in-JSON search](../table-design/index/inverted-index/overview)
-- [LLM SQL Functions](./llm-sql-functions): the `AI_*` family whose JSON output (from `AI_EXTRACT`, `AI_CLASSIFY`, and so on) is a natural fit for a VARIANT column.
+- [VARIANT SQL reference: full syntax, type rules, indexes, configuration](../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md)
+- [VARIANT Workload Guide: when to pick default, sparse, DOC mode, or Schema Template](../sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md)
+- [Importing VARIANT data: Stream Load, Routine Load, and Broker Load examples](../data-operate/import/complex-types/variant.md)
+- [Columnar Storage feature card: how subcolumns are encoded, paged, and indexed under the hood](./columnar-storage.mdx)
+- [Inverted Index overview: the index VARIANT uses for text-in-JSON search](../table-design/index/inverted-index/overview.md)
+- [LLM SQL Functions](./llm-sql-functions.mdx): the `AI_*` family whose JSON output (from `AI_EXTRACT`, `AI_CLASSIFY`, and so on) is a natural fit for a VARIANT column.
- [Apache Doris blog: Variant in Apache Doris 2.1.0, the original announcement and benchmarks](/blog/variant-in-apache-doris-2.1/)
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 1c4fa1cda88..e7ba1fbf7c9 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
@@ -2,15 +2,13 @@
{
"title": "VARIANT",
"language": "en-US",
- "description": "VARIANT stores semi-structured JSON data and supports typed access, casts, hash-based grouping, deduplication, and selected SQL operations."
+ "description": "VARIANT stores semi-structured JSON. Covers write and parse rules, CAST, NULL semantics, ordering, Schema Template, ALTER, properties, indexes, and limits."
}
---
-## VARIANT
-
## Overview
-The VARIANT type stores semi-structured JSON data. It can contain different primitive types (integers, strings, booleans, etc.), one-dimensional arrays, and nested objects. On write, Doris infers the structure and type of sub-paths based on JSON paths and performs Subcolumnization on frequent paths, exposing them as independent columnar subcolumns for both flexibility and performance.
+The VARIANT type stores semi-structured JSON data: objects, arrays, strings, numbers, booleans, and `null`. On write, Doris infers the type of each JSON path and performs Subcolumnization on frequent paths, storing them as independent columnar subcolumns. A query on such a path reads only the subcolumn it needs.
:::tip Why VARIANT
`VARIANT` is a good fit when document shape changes over time but queries still focus on a small set of hot paths.
@@ -19,269 +17,418 @@ The VARIANT type stores semi-structured JSON data. It can contain different prim
- Key paths can use path-level indexes, full-text search, and still benefit from Doris sparse-index pruning.
- Wide-column optimizations keep automatic Subcolumnization practical at 10k-scale subcolumns. When paths participating in Subcolumnization approach 10,000, hardware requirements rise quickly, so DOC mode is usually the safer starting point.
-If you still need to choose between default behavior, sparse columns, DOC mode, and Schema Template, start with [Variant Workload Guide](./variant-workload-guide). This page is the reference for syntax, type rules, indexes, limits, and configuration.
+If you still need to choose between default behavior, sparse columns, DOC mode, and Schema Template, start with [Variant Workload Guide](./variant-workload-guide.md). This page is the reference for writing and parsing, type rules, CAST, NULL and comparison semantics, ALTER, indexes, limits, and configuration.
:::
-## Using VARIANT
+:::info Version
+This page describes VARIANT in Doris 5.0.0 and later. The differences from Doris 4.x that are most likely to affect existing SQL:
+
+- `INSERT` stores a string as a VARIANT string instead of parsing it as JSON. This includes `INSERT INTO ... SELECT` from table functions such as `s3()` and `hdfs()`. Use `PARSE_TO_VARIANT` to write JSON text with `INSERT`. Load jobs such as Stream Load still parse JSON.
+- Whole VARIANT values support `GROUP BY`, `DISTINCT`, and set operations. Two VARIANT values can be compared with `=`, `!=`, and `<=>`, and can be used as join keys, `ORDER BY` keys, and window keys.
+- `VARIANT_TYPE` returns one type name, such as `object`, instead of a map from paths to types.
+- VARIANT arrays can be indexed with integers, starting from 1.
-### Create table syntax
+For Doris 4.x, see the 4.x version of this page.
+:::
-Declare a VARIANT column when creating a table:
+## Quick start
```sql
-CREATE TABLE IF NOT EXISTS ${table_name} (
- k BIGINT,
- v VARIANT
+CREATE TABLE events (
+ id BIGINT,
+ v VARIANT
)
-PROPERTIES("replication_num" = "1");
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
+
+-- INSERT keeps a string literal as a VARIANT string, so parse JSON text explicitly.
+INSERT INTO events VALUES
+ (1, PARSE_TO_VARIANT('{"user": {"id": 42, "name": "alice"}, "tags": ["doris", "sql"], "score": 9.5}')),
+ (2, PARSE_TO_VARIANT('{"user": {"id": 7, "name": "bob"}, "score": 3}'));
+
+SELECT id,
+ CAST(v['user']['name'] AS STRING) AS name,
+ v['tags'][1] AS first_tag
+FROM events
+WHERE CAST(v['score'] AS DOUBLE) > 5;
+```
+
+```text
++------+-------+-----------+
+| id | name | first_tag |
++------+-------+-----------+
+| 1 | alice | doris |
++------+-------+-----------+
+```
+
+- `v['user']['name']` and `v['tags'][1]` return `VARIANT` values. Array indexes start from 1.
+- CAST a path to a concrete type before comparing or computing with it.
+- Load jobs such as Stream Load parse JSON text automatically. See [Write data](#write-data).
+
+## Define a VARIANT column
+
+```sql
+column_name VARIANT
+column_name VARIANT< field_definition [, field_definition ...] >
+column_name VARIANT< properties('key' = 'value' [, ...]) >
+column_name VARIANT< field_definition [, ...], properties('key' = 'value' [, ...]) >
+
+field_definition:
+ [MATCH_NAME | MATCH_NAME_GLOB] 'path_or_pattern' : data_type [COMMENT 'comment']
```
-Constrain certain paths with a Schema Template (see “Extended types”):
+- The `field_definition` list is the [Schema Template](#schema-template). It fixes the storage type of selected paths.
+- `properties(...)` sets column-level storage properties. See [Column properties](#column-properties).
+- A VARIANT column can be `NULL` or `NOT NULL`. Its only allowed default value is `NULL`.
```sql
-CREATE TABLE IF NOT EXISTS ${table_name} (
+CREATE TABLE IF NOT EXISTS example_tbl (
k BIGINT,
- v VARIANT <
- 'id' : INT, -- restrict path id to INT
- 'message*' : STRING, -- restrict message* prefix to STRING
- 'tags*' : ARRAY<TEXT> -- restrict tags* prefix to ARRAY<TEXT>
- >
+ v VARIANT<
+ 'id' : INT, -- path id is stored as INT
+ 'message*' : STRING, -- paths matching message* are stored as STRING
+ 'tags*' : ARRAY<TEXT>, -- paths matching tags* are stored as ARRAY<TEXT>
+ properties('variant_max_subcolumns_count' = '2048')
+ > NULL
)
-PROPERTIES("replication_num" = "1");
+DUPLICATE KEY(k)
+DISTRIBUTED BY HASH(k) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
```
-### Query syntax
+Where a VARIANT column can be used in a table:
-```sql
--- Access nested fields (returns VARIANT; explicit or implicit CAST is required for aggregation/comparison)
-SELECT v['properties']['title'] FROM ${table_name};
+| Usage | Supported | Notes |
+| --- | --- | --- |
+| Value column of Duplicate Key, Unique Key, and Aggregate Key tables | Yes | In an Aggregate Key table, the aggregation type must be `REPLACE` or `REPLACE_IF_NOT_NULL`. |
+| Key column, partition column, bucketing column | No | |
+| Nested in another type in a table schema (`ARRAY<VARIANT>`, `MAP`, `STRUCT`) | No | A query result can still be `ARRAY<VARIANT>`, for example from `COLLECT_LIST(v)`. |
+| Default value | `NULL` only | `DEFAULT '{}'` and other non-NULL defaults are rejected. |
--- CAST to a concrete type before aggregation
-SELECT CAST(v['properties']['title'] AS STRING) AS title
-FROM ${table_name}
-GROUP BY title;
+## Write data
--- Query arrays
-SELECT *
-FROM ${table_name}
-WHERE ARRAY_CONTAINS(CAST(v['tags'] AS ARRAY<TEXT>), 'Doris');
-```
+`INSERT` and `CAST` keep a string as a VARIANT string. `PARSE_TO_VARIANT`, `TRY_PARSE_TO_VARIANT`, and load jobs such as Stream Load parse JSON text. For load examples, see [Load VARIANT data](../../../../data-operate/import/complex-types/variant.md).
-## Create and access values
+### Parse errors
-:::info Version availability
-The behavior in this section applies to Doris 4.2 and later.
-:::
+[PARSE_TO_VARIANT](../../../sql-functions/scalar-functions/variant-functions/parse-to-variant.md), [TRY_PARSE_TO_VARIANT](../../../sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md), and load jobs use the same JSON parser. Load jobs handle errors like `TRY_PARSE_TO_VARIANT`:
-VARIANT values can be created from JSON text, JSON/JSONB values, or typed SQL expressions:
+| Input | `PARSE_TO_VARIANT` | `TRY_PARSE_TO_VARIANT` and load jobs |
+| --- | --- | --- |
+| Valid JSON | The parsed value | The parsed value |
+| Text that is not valid JSON, such as `hello` or `{"id":` | Kept as a VARIANT string | Kept as a VARIANT string |
+| JSON that contains an integer outside [-2^63, 2^64 - 1] or a number outside the `DOUBLE` range, such as `{"a": 1, "n": 100000000000000000000}` | The whole text is kept as a VARIANT string, so `v['a']` returns `NULL` | The whole text is kept as a VARIANT string |
+| Empty string | An empty object `{}` | An empty object `{}` |
+| Nesting deeper than 128 levels | Error | SQL `NULL` |
+| An object key longer than `variant_max_json_key_length` bytes (BE configuration, default 255) | Error | SQL `NULL` |
+| Duplicate keys in one object | Error | SQL `NULL` |
+| A string that is not valid UTF-8 | Error | SQL `NULL` |
+
+A document nested so deeply that the JSON parser itself rejects it (about 1,000 levels) is invalid JSON, so it is kept as a string.
-- Use [PARSE_TO_VARIANT](../../../sql-functions/scalar-functions/variant-functions/parse-to-variant) when a string or JSON/JSONB expression should be parsed as a structured VARIANT value.
-- Use `CAST(expression AS VARIANT)` to convert a supported SQL value to VARIANT. A string remains a VARIANT string value; this CAST does not parse JSON.
+Two BE configurations change these rules:
-### Parse JSON text
+- `variant_throw_exeception_on_invalid_json` (default `false`): when `true`, text that the parser rejects, including JSON with out-of-range numbers, is an error for `PARSE_TO_VARIANT` and SQL `NULL` for `TRY_PARSE_TO_VARIANT` and load jobs, instead of a VARIANT string.
+- `variant_enable_duplicate_json_path_check` (default `false`): when `true`, a key that repeats in an object keeps its first value instead of causing an error. It also keeps a write from failing when a key with a dot repeats a nested path; only one of the values is stored.
+
+To keep a document with out-of-range integers structured, convert it through JSON: `CAST(CAST(text AS JSON) AS VARIANT)`. Integers with up to 38 digits stay exact numbers, larger integers within the `LARGEINT` range become strings, and larger ones become `DOUBLE`. `PARSE_TO_VARIANT(CAST(text AS JSON))` does not help, because it parses the JSON text again.
```sql
-SELECT PARSE_TO_VARIANT('{"user": {"id": 42}, "active": true}');
-SELECT PARSE_TO_VARIANT('[10, 20, 30]');
-SELECT PARSE_TO_VARIANT(CAST('{"user": {"id": 42}}' AS JSON));
+SELECT VARIANT_TYPE(PARSE_TO_VARIANT('{"id": 1}')) AS valid_json, -- object
+ VARIANT_TYPE(PARSE_TO_VARIANT('{"id":')) AS invalid_json, -- string
+ VARIANT_TYPE(CAST('{"id": 1}' AS VARIANT)) AS string_cast; -- string
```
-Use [TRY_PARSE_TO_VARIANT](../../../sql-functions/scalar-functions/variant-functions/try-parse-to-variant) when invalid JSON should return SQL `NULL` instead of failing the query.
+### What storage keeps
+
+Writing a value into a table normalizes it. The value read back can differ from the value computed before the write:
+
+| Before the write | Read back from the table |
+| --- | --- |
+| An object member whose value is JSON `null`, such as `{"a": null, "b": 1}` | The member is removed: `{"b":1}`. `v['a']` returns SQL `NULL`. |
+| An object member whose value is an empty object or array, or an object that these rules leave empty, such as `{"a": {}, "b": [], "c": {"d": null}}` | Removed: `{}` |
+| An object member whose array contains only `null`, such as `{"p": [null, null], "q": 1}` | Can be removed: `{"q":1}` |
+| Values inside arrays, such as `{"arr": [{"a": null}, {}, [], null]}` | Kept as they are. `v['arr'][1]['a']` is a VARIANT `null`. |
+| A root JSON `null`, such as `PARSE_TO_VARIANT('null')` | An empty object `{}` |
+| A root empty array `[]` or empty object `{}` | Kept |
+| `DATE` and `DATETIME` values in the root value, such as `CAST(date_col AS VARIANT)`, or on a path that also holds values of other types | Stored as their text, so they read back as strings. On an object path that holds only dates, they keep their type. |
+| Booleans and numbers mixed on one path | Booleans can read back as `1` or `0`, depending on the order of values within a write and on compaction |
+| A key that contains a dot, such as `{"a.b": 1}` | Stored as a nested path: `{"a":{"b":1}}`. Both `v['a.b']` and `v['a']['b']` return `1`. A document that has both a key `a.b` and a key `b` inside `a` makes the whole INSERT or load job fail, unless `variant_enable_duplicate_json_path_check` is `true`. |
+| Object keys | Returned in byte order |
+
+These rules depend on the other rows written together and on DOC mode, so do not rely on `null` values or empty containers surviving storage. Paths declared in a Schema Template are converted to the declared type instead; see [Schema Template](#schema-template).
-### Access objects and arrays
+## Type inference and type conflicts
-Object fields can be accessed with a string key. In Doris 4.2 and later, positive VARIANT array indexes start from 1, and negative indexes count backward from the end. The extracted value remains `VARIANT`; CAST it before typed comparison, arithmetic, or aggregation.
+Without a Schema Template, Doris infers a type for each value when it parses JSON. `VARIANT_TYPE` returns this type:
+
+| JSON value | Type |
+| --- | --- |
+| Integer within the `BIGINT` range | `tinyint`, `smallint`, `int`, or `bigint`, the smallest that fits |
+| Integer above the `BIGINT` range, up to 18446744073709551615 | `decimal` |
+| Number with a fraction or an exponent | `double` |
+| Integer outside [-2^63, 2^64 - 1], or a number outside the `DOUBLE` range | Not a number: the whole text is invalid JSON (see [Parse errors](#parse-errors)) |
+| String | `string` |
+| `true`, `false` | `bool` |
+| `null` | `null` |
+| Array, object | `array`, `object` |
+
+When data is stored, each path gets one storage type:
+
+- Integers are stored as `BIGINT`, or as `LARGEINT` when a value needs it. Floating-point numbers are stored as `DOUBLE`, decimals as `DECIMAL`, strings as `STRING`, booleans as `BOOLEAN`, and arrays of scalars as `ARRAY<T>`.
+- Arrays of objects, nested arrays, and paths whose values have different types, such as integers and floating-point numbers, numbers and strings, or scalars and arrays, are stored as `JSONB` (shown as `json` by `DESC`). Arrays with conflicting element types become `ARRAY<JSONB>`.
```sql
-SELECT CAST(PARSE_TO_VARIANT('{"user": {"id": 42}}')['user']['id'] AS BIGINT);
-SELECT ELEMENT_AT(PARSE_TO_VARIANT('[10, 20, 30]'), 1); -- 10
-SELECT ELEMENT_AT(PARSE_TO_VARIANT('[10, 20, 30]'), -1); -- 30
+{"a" : 12345678}
+{"a" : "HelloWorld"}
+-- a is stored as JSONB
```
-See [ELEMENT_AT](../../../sql-functions/scalar-functions/variant-functions/element-at) for object and array access details.
-
-## CAST rules
+A `JSONB` path keeps every value, but it loses typed storage: indexes and typed pruning no longer apply to that path. Booleans are a special case: when the first value on a path is a boolean and later values are numbers, the path can be stored as a number, and the booleans read back as `1` or `0`. If a path needs a stable type, declare it in a Schema Template.
-CAST involving VARIANT has two directions: converting a supported SQL value to VARIANT and extracting a compatible SQL value from VARIANT.
+`VARIANT_TYPE` reports the type of each value, so an integer read from a `BIGINT` path can still be `tinyint`. To see the storage type of each path, run `SET describe_extend_variant_column = true;` and then `DESC table_name;`. See [Inspect subcolumns and types](#inspect-subcolumns-and-types).
-### CAST other types to VARIANT
+## Access paths and output
-| Source type | Behavior |
-| --- | --- |
-| `CHAR`, `VARCHAR`, `STRING` | Preserves the input as a VARIANT string. JSON-looking text is not parsed. |
-| `BOOLEAN` | Preserves the Boolean value. |
-| `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT` | Preserves the integer value. |
-| `FLOAT`, `DOUBLE` | Preserves the floating-point value. |
-| `DECIMALV2`, `DECIMAL(p, s)` with `p <= 38` | Preserves the Decimal value, subject to the limits below. |
-| `DATE`, `DATETIME`, `TIMESTAMP_NS`, `TIMESTAMPTZ` | Preserves the typed logical value. |
-| `IPV4`, `IPV6` | Preserves the IP address value. |
-| `JSON` / `JSONB` | Converts the structured value directly to VARIANT. If the input contains a JSONB value type that VARIANT cannot represent, the BE returns an error. |
-| `ARRAY<T>` | Converts each element recursively when `T` is `VARIANT` or is also in this whitelist, and preserves SQL NULL elements. |
-
-Only the source types listed above are supported. Other source types, including `MAP`, `STRUCT`, `TIME`, Decimal values with precision greater than 38, and arrays containing an unsupported element type, cause the BE to return an error.
+- `v['key']` and `v['a']['b']` read object members. `v['arr'][1]` reads an array element: indexes start from 1, and `-1` is the last element. [ELEMENT_AT](../../../sql-functions/scalar-functions/variant-functions/element-at.md) is equivalent.
+- The result is a `VARIANT` value. A missing key, index `0`, an out-of-range index, a string key on an array, an integer index on an object, and a key on a scalar value all return SQL `NULL`.
+- In a computed value, a key that contains a dot is a single key: `v['a.b']` reads the key `a.b`, while `v['a']['b']` reads `b` inside `a`. Storage does not keep this distinction; see [What storage keeps](#what-storage-keeps).
+- A path does not map over arrays. For `{"a": [{"b": 1}]}`, `v['a']['b']` returns `NULL`; use `v['a'][1]['b']`.
```sql
--- A string remains a VARIANT string root, even if it looks like JSON.
-SELECT CAST(CAST('{"id": 1}' AS VARIANT) AS STRING) AS string_value,
- VARIANT_TYPE(CAST('{"id": 1}' AS VARIANT)) AS root_type;
--- string_value: {"id": 1}; root_type: string
+SELECT v['user']['id'] AS id, -- 42
+ v['tags'][-1] AS last_tag, -- sql
+ v['user']['missing'] AS missing -- NULL
+FROM events
+WHERE id = 1;
+```
--- Parse JSON text explicitly when a structured VARIANT value is required.
-SELECT PARSE_TO_VARIANT('{"id": 1}') AS parsed_object;
--- {"id":1}
+Common patterns CAST a path to the type the query needs:
--- JSON/JSONB input is converted structurally.
-SELECT CAST(CAST('{"id": 1}' AS JSON) AS VARIANT) AS parsed_object;
--- {"id":1}
+```sql
+SELECT * FROM tbl WHERE ARRAY_CONTAINS(CAST(v['tags'] AS ARRAY<TEXT>), 'Doris');
+SELECT * FROM tbl WHERE CAST(v['date'] AS DATE) = '2021-01-02';
+SELECT * FROM tbl WHERE v['bool']; -- implicit CAST to BOOLEAN
+SELECT * FROM tbl WHERE v['str'] MATCH 'Doris'; -- uses an inverted index on the path
```
-Because string CAST does not parse JSON, malformed JSON text is still a valid VARIANT string. Use `PARSE_TO_VARIANT` for strict JSON parsing or `TRY_PARSE_TO_VARIANT` when malformed input should become SQL `NULL`.
+Reading a whole VARIANT value returns JSON text. Object keys are returned in byte order and without whitespace, so the text is not byte-for-byte identical to the input. A string root is returned without quotes.
-### CAST VARIANT to other types
+```sql
+CREATE TABLE variant_tbl (k INT, v VARIANT)
+DUPLICATE KEY(k)
+DISTRIBUTED BY HASH(k) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
-VARIANT can be cast to a compatible scalar, JSON/JSONB, or array target:
+INSERT INTO variant_tbl VALUES (1, PARSE_TO_VARIANT('{ "b": 2, "a": 1, "c": { "y": 20, "x": 10 } }'));
-| Target type | Behavior |
-| --- | --- |
-| `BOOLEAN` | Converts a compatible Boolean or scalar root. |
-| `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT` | Converts a compatible scalar root to the requested integer type. |
-| `FLOAT`, `DOUBLE` | Converts a compatible numeric root. |
-| `DECIMALV2`, `DECIMAL(p, s)` | Converts a compatible numeric root to the requested Decimal type. |
-| `DATE`, `DATETIME`, `TIMESTAMP_NS`, `TIMESTAMPTZ` | Converts a compatible date/time root. |
-| `CHAR`, `VARCHAR`, `STRING` | Returns scalar text for scalar roots and JSON text for objects and arrays. Variant/JSON `null` becomes the string `null`; outer SQL `NULL` remains SQL `NULL`. |
-| `IPV4`, `IPV6` | Converts a compatible IP address root to the requested IP address type. |
-| `JSON` / `JSONB` | Converts the value structurally. If the VARIANT value contains a type that JSON/JSONB cannot represent, the BE returns an error. |
-| `ARRAY<T>` | Converts a VARIANT array element by element when `T` is `VARIANT` or is also in this whitelist; incompatible elements follow the target CAST rules. |
-
-Only the target types listed above are supported. Other target types, including `MAP`, `STRUCT`, and `TIME`, cause the BE to return an error. For a supported target, an incompatible value shape, invalid text, or numeric overflow follows the applicable CAST error-or-NULL behavior.
+SELECT v FROM variant_tbl WHERE k = 1;
+-- {"a":1,"b":2,"c":{"x":10,"y":20}}
+```
-```sql
-SELECT CAST(PARSE_TO_VARIANT('42') AS BIGINT) AS id;
--- 42
+## CAST
-SELECT CAST(PARSE_TO_VARIANT('[1, null, 3]') AS ARRAY<INT>) AS values;
--- [1, NULL, 3]
+### CAST to VARIANT
-SELECT CAST(PARSE_TO_VARIANT('{"id": 1}') AS JSON) AS json_value;
--- {"id":1}
+| Source type | Result |
+| --- | --- |
+| `CHAR`, `VARCHAR`, `STRING` | A VARIANT string. JSON text is not parsed. The string must be valid UTF-8; otherwise the CAST fails. |
+| `BOOLEAN` | A boolean. |
+| `TINYINT`, `SMALLINT`, `INT`, `BIGINT` | An integer. |
+| `LARGEINT` | A decimal. A value whose magnitude exceeds 10^38 - 1 becomes a string. |
+| `FLOAT`, `DOUBLE` | A floating-point number. |
+| `DECIMALV2`, `DECIMAL(p, s)` with `p <= 38` | A decimal. |
+| `DATE`, `DATETIME(p)`, `TIMESTAMP_NS` | A date, or a timestamp without time zone. |
+| `IPV4`, `IPV6` | A string with the text form of the value. |
+| `JSON` / `JSONB` | The same structure. A value that VARIANT cannot hold, such as a `DECIMAL256` number, or duplicate keys in an object make the CAST fail. |
+| `ARRAY<T>` | An array; each element is converted. `T` must be `VARIANT` or a type in this table. |
+| `MAP`, `STRUCT`, `TIME`, `TIMESTAMPTZ`, `VARBINARY`, and other types | Not supported. The statement fails. |
+
+The source value must also be valid for its own type; an invalid value is rejected, not repaired. A string is never parsed by CAST: `CAST('{"id": 1}' AS VARIANT)` is the string `{"id": 1}`. Use `PARSE_TO_VARIANT` to parse JSON text.
+
+### CAST from VARIANT
+
+| Target type | Result |
+| --- | --- |
+| `BOOLEAN` | Booleans as they are. Numbers are `true` when not zero. Strings are converted as by `CAST(string AS BOOLEAN)`. |
+| `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT` | Integers. The fraction of a decimal or floating-point value is truncated (`1.5` becomes `1`). Booleans become `1` or `0`. Numeric strings such as `"123"` are converted. |
+| `FLOAT`, `DOUBLE`, `DECIMAL(p, s)` | Numbers and numeric strings. `DECIMAL` rounds to scale `s`. A value too large for `FLOAT` becomes `Infinity`. |
+| `DATE`, `DATETIME(p)`, `TIMESTAMP_NS` | Date and time values, strings in a date or time format, and numbers such as `20240102`. |
+| `TIMESTAMPTZ(p)` | Date and time values, and strings in a date or time format. |
+| `IPV4`, `IPV6` | Strings in IP address format. |
+| `CHAR`, `VARCHAR`, `STRING` | A string root is returned as is, without quotes. Objects and arrays are returned as JSON text. Other scalars are formatted like the same SQL type: a boolean root becomes `1` or `0`, a `DATETIME` value has six fractional digits, and a `TIMESTAMP_NS` value has nine. A VARIANT `null` becomes the string `null`. |
+| `JSON` / `JSONB` | The same structure. Values without a JSON counterpart, such as dates and timestamps, become JSON strings; a timestamp with time zone is formatted in the session time zone. |
+| `ARRAY<T>` | An array, converted element by element; an element that cannot be converted becomes `NULL`. A string that holds a JSON array, such as `"[1, 2]"`, is converted too. Other values return `NULL`. |
+| `MAP`, `STRUCT`, `TIME`, and other types | Not supported. The statement fails. |
+
+A value that cannot be converted to the target type returns SQL `NULL`, also when `enable_strict_cast` is on. The exception is a value whose type has no conversion to the target type at all, such as a number to `TIMESTAMPTZ`, `IPV4`, or `IPV6`, or a date or timestamp to `BOOLEAN`: then the statement fails.
+
+```sql
+SELECT CAST(PARSE_TO_VARIANT('"123"') AS INT) AS from_string, -- 123
+ CAST(PARSE_TO_VARIANT('"abc"') AS INT) AS not_a_number, -- NULL
+ CAST(PARSE_TO_VARIANT('1.5') AS INT) AS truncated, -- 1
+ CAST(PARSE_TO_VARIANT('300') AS TINYINT) AS overflow, -- NULL
+ CAST(PARSE_TO_VARIANT('{"a": 1}') AS INT) AS from_object, -- NULL
+ CAST(PARSE_TO_VARIANT('[1, "2", null, "x"]') AS ARRAY<INT>) AS arr; -- [1, 2, null, null]
+
+SELECT CAST(PARSE_TO_VARIANT('true') AS STRING) AS bool_root, -- 1
+ CAST(PARSE_TO_VARIANT('{"b": true}') AS STRING) AS object_text, -- {"b":true}
+ CAST(PARSE_TO_VARIANT('"abc"') AS STRING) AS string_root, -- abc
+ CAST(PARSE_TO_VARIANT('null') AS STRING) AS variant_null; -- null
```
-### Decimal and date/time conversion limits
+### Decimal and date/time values
+
+When these types are converted to VARIANT:
-| Doris input type | Supported VARIANT behavior |
+| Doris type | Behavior |
| --- | --- |
| Legacy `DECIMALV2` | Precision up to 27 and scale up to 9 are preserved exactly. |
-| `DECIMAL(p, s)` | `1 <= p <= 38` and `0 <= s <= p` are preserved exactly. Values that require precision greater than 38 are not supported. |
-| `DATE` | Preserved as a calendar date with no time or time zone. |
-| Legacy `DATETIME` | Preserved with whole-second precision and no time-zone adjustment. |
-| `DATETIME(p)` | Supports `0 <= p <= 6` with no time-zone adjustment. |
-| `TIMESTAMP_NS` | Preserves fixed nanosecond precision with no time-zone adjustment; values must be within the TIMESTAMP_NS range. |
-| `TIMESTAMPTZ(p)` | Supports `0 <= p <= 6` with time-zone-adjusted timestamp semantics. |
-| Decimal precision greater than 38 | Not supported as input to VARIANT. |
-| `TIME` | Not supported as input to VARIANT. |
+| `DECIMAL(p, s)` | `1 <= p <= 38` and `0 <= s <= p` are preserved exactly. Decimals with precision greater than 38 are not supported. |
+| `DATE` | A calendar date with no time or time zone. |
+| `DATETIME(p)` | `0 <= p <= 6`, without time-zone adjustment. |
+| `TIMESTAMP_NS` | Nanosecond precision without time-zone adjustment; values must be within the TIMESTAMP_NS range. |
+| `TIMESTAMPTZ(p)` | Not supported, neither by CAST nor as a Schema Template path. |
+| `TIME` | Not supported. |
-Every source value must also be valid for its Doris source type. Unsupported precision, invalid dates, or incompatible values return an error instead of being repaired.
+## NULL semantics
-## Grouping, deduplication, and hash semantics
+VARIANT has two kinds of null:
-In Doris 4.2 and later, grouping, deduplication, and set operations treat logically equivalent VARIANT values as the same value, regardless of source SQL type or physical representation:
+- **SQL `NULL`** means that there is no value. It comes from a `NULL` column value, a missing path such as `v['no_such_key']`, or a CAST that fails.
+- **VARIANT `null`** is a value: the JSON literal `null`, such as `PARSE_TO_VARIANT('null')` or a `null` array element. `VARIANT_TYPE` returns `null` for it.
-- Equivalent integral numeric representations are treated as the same value.
-- Decimal trailing zeros do not change the value, so `1.20` and `1.2` are treated as the same value.
-- `+0`, `-0`, and integral zero are treated as the same value.
-- Object key order does not affect whether values are treated as the same, while array element order does.
-- Variant/JSON `null` is distinct from SQL `NULL`.
+| Operation | SQL `NULL` | VARIANT `null` |
+| --- | --- | --- |
+| `v IS NULL` | `true` | `false` |
+| `COALESCE(v, x)`, `IFNULL(v, x)` with a VARIANT `x` | Returns `x` | Returns the VARIANT `null` |
+| `COUNT(v)` | Not counted | Counted |
+| `GROUP BY v`, `DISTINCT` | All SQL `NULL` values form one group | All VARIANT `null` values form another group |
+| `v = x`, equality join | Never matches | Matches another VARIANT `null` |
+| `ORDER BY v` | Placed by `NULLS FIRST` or `NULLS LAST` | In ascending order, before every other non-NULL value |
+| `CAST(v AS STRING)` | SQL `NULL` | The string `null` |
-Hash-based operators use the same logical-value rules when calculating their keys. Values treated as the same under the rules above therefore produce the same internal hash key, regardless of whether they came from parsed JSON or a typed CAST. This hash is an implementation detail, not a stable user-facing checksum.
+After a value is stored, a `null` member of an object outside arrays is removed, so reading it returns SQL `NULL`, and a root `null` reads back as `{}` (see [What storage keeps](#what-storage-keeps)). On stored data, `v['a'] IS NULL` therefore cannot tell a missing member from a `null` member.
-These rules apply to supported operations such as `GROUP BY`, `DISTINCT`, `COUNT(DISTINCT ...)`, `INTERSECT`, `EXCEPT`, and `UNION DISTINCT`. They do not enable root VARIANT comparison predicates: direct `VARIANT = VARIANT` and ordering comparisons remain unsupported.
+## Comparison, grouping, and ordering
-```sql
--- 1 and 1.0 have one distinct logical value.
-SELECT COUNT(DISTINCT value) AS distinct_count
-FROM (
- SELECT PARSE_TO_VARIANT('1') AS value
- UNION ALL
- SELECT PARSE_TO_VARIANT('1.0') AS value
-) AS numeric_values;
--- distinct_count: 1
-
--- Object key order is ignored; array order is preserved.
-SELECT COUNT(DISTINCT value) AS distinct_count
-FROM (
- SELECT PARSE_TO_VARIANT('{"a": 1, "b": 2}') AS value
- UNION ALL
- SELECT PARSE_TO_VARIANT('{"b": 2, "a": 1}') AS value
-) AS object_values;
--- distinct_count: 1
+VARIANT values are compared by their logical value, not by their text or physical encoding. Equality, hashing (`GROUP BY`, `DISTINCT`, joins), and ordering (`ORDER BY`, window keys) use the same rules, so equal values always fall into the same group and are ties when sorted.
-SELECT COUNT(DISTINCT value) AS distinct_count
-FROM (
- SELECT PARSE_TO_VARIANT('[1, 2]') AS value
- UNION ALL
- SELECT PARSE_TO_VARIANT('[2, 1]') AS value
-) AS array_values;
--- distinct_count: 2
+### Supported operations
+
+| Operation on VARIANT values | Support | Notes |
+| --- | --- | --- |
+| `=`, `!=`, `<=>` between two VARIANT values, or with a `NULL` literal | Supported | Includes subpaths, such as `v['a'] = w['a']`. |
+| Equality join, semi and anti join, `IN` and `NOT IN` subqueries | Supported | Runtime filters are not generated for VARIANT join keys. |
+| `GROUP BY`, `DISTINCT`, `COUNT(DISTINCT ...)`, `UNION`, `INTERSECT`, `EXCEPT` | Supported | |
+| `ORDER BY`, `ORDER BY ... LIMIT` | Supported | |
+| Window `PARTITION BY` and `ORDER BY` | Supported | |
+| `COUNT(v)`, `COLLECT_LIST(v)`, `ARRAY_AGG(v)` | Supported | |
+| `IF`, `CASE`, `IFNULL`, `COALESCE` | Supported | Mixing VARIANT with another type converts the VARIANT values to the other type (`DECIMAL(38, 9)` when it is an integer); values that cannot be converted become `NULL`. |
+| `CAST(v AS ARRAY<VARIANT>)`, `EXPLODE_VARIANT_ARRAY`, `EXPLODE` and `EXPLODE_OUTER` on `ARRAY<VARIANT>` | Supported | |
+| `<`, `<=`, `>`, `>=`, `BETWEEN` between VARIANT values | Not supported | CAST to a concrete type first. |
+| A whole VARIANT value compared with a non-VARIANT value, such as `v = 1` | Not supported | A subpath is converted implicitly: `v['a'] = 1` works. |
+| `IN` list that contains VARIANT values, such as `v IN (PARSE_TO_VARIANT('1'))` | Not supported | `v IN ('a', 'b')` with non-VARIANT values works through implicit conversion. |
+| `MIN`, `MAX` | Not supported | CAST a subpath first. |
+| `ARRAY(...)`, `MAP(...)`, `NAMED_STRUCT(...)` with VARIANT arguments | Not supported | |
+
+### Equality
+
+- **Numbers.** An integer, a decimal whose fraction is zero, and a floating-point number with an integral value are equal: `1`, `1.0`, and `1.00` are one value. Trailing zeros of a decimal do not matter, and `-0.0` equals `0`. A decimal and a floating-point number that have a fraction are never equal: `DECIMAL 1.5` does not equal `DOUBLE 1.5`. JSON numbers with a fraction are parsed as `DOUBLE`; decimals come from CAST, Schema Template paths, and integers too large for `BIGINT`.
+- **Values of different kinds are never equal.** The number `1`, the string `"1"`, and `true` are three values. A `DATE` does not equal the string `"2024-01-01"`, and a timestamp without time zone does not equal a timestamp with time zone.
+- **Strings** are equal only when their bytes are equal. The comparison is case-sensitive.
+- **Objects** are equal when they have the same keys with equal values. Key order does not matter.
+- **Arrays** are equal when they have the same length and equal elements in the same order.
+- A VARIANT `null` equals another VARIANT `null`. SQL `NULL` follows the SQL rules.
+
+```sql
+SELECT PARSE_TO_VARIANT('1') = PARSE_TO_VARIANT('1.0') AS int_double, -- 1
+ PARSE_TO_VARIANT('1.5') = CAST(CAST(1.5 AS DECIMAL(10, 2)) AS VARIANT) AS dbl_dec, -- 0
+ PARSE_TO_VARIANT('1') = PARSE_TO_VARIANT('"1"') AS num_str, -- 0
+ PARSE_TO_VARIANT('{"a": 1, "b": 2}') = PARSE_TO_VARIANT('{"b": 2, "a": 1}') AS obj, -- 1
+ PARSE_TO_VARIANT('[1, 2]') = PARSE_TO_VARIANT('[2, 1]') AS arr; -- 0
```
-## NULL semantics
+### Ordering
-SQL `NULL` and Variant/JSON `null` are different values:
+`ORDER BY` on VARIANT values uses one total order. Values of different kinds are ordered by kind first:
-- SQL `NULL` represents the absence of a SQL value and follows normal SQL NULL propagation.
-- Variant/JSON `null` is a VARIANT value, for example the result of `PARSE_TO_VARIANT('null')`.
-- `TRY_PARSE_TO_VARIANT` returns SQL `NULL` for malformed input. This differs from successfully parsing the JSON literal `null`.
+```text
+null < boolean < number < string < binary < date < timestamp with time zone
+ < timestamp without time zone < time < UUID < object < array
+```
-## Primitive types
+Binary, time, and UUID values cannot be produced by JSON parsing or by CAST in Doris; they can only come from VARIANT data written by other systems, such as Parquet files with VARIANT columns.
-VARIANT infers subcolumn types automatically. Supported types include:
+Within a kind:
-<table>
-<tr><td>Supported types<br/></td></tr>
-<tr><td>TinyInt<br/></td></tr>
-<tr><td>NULL (equivalent to JSON null)<br/></td></tr>
-<tr><td>BigInt (64 bit)<br/>Double<br/></td></tr>
-<tr><td>String (Text)<br/></td></tr>
-<tr><td>Jsonb<br/></td></tr>
-<tr><td>Variant (nested object)<br/></td></tr>
-<tr><td>Array<T> (one-dimensional only)<br/></td></tr>
-</table>
+- **Booleans:** `false` before `true`.
+- **Numbers:** integers (`INT`, `BIGINT`, etc.), decimals (`DECIMAL`), and floating-point numbers (`FLOAT`, `DOUBLE`) sort by numeric value. `INT 1`, `FLOAT/DOUBLE 1.0`, and `DECIMAL 1.00` are equal and have the same sort position; decimal trailing zeros do not matter. For equal non-integral values, the decimal sorts first: `DECIMAL 1.5` precedes `FLOAT/DOUBLE 1.5`, and they are not equal as VARIANT values. Floating-point precision can affect the order: `DOUBLE 0.1` is slightly greater t [...]
+- **Strings:** by UTF-8 bytes. Uppercase letters sort before lowercase letters, and `"10"` sorts before `"9"`.
+- **Dates and timestamps:** in time order. Every date sorts before every timestamp.
+- **Objects:** entry by entry in key order: the smallest keys are compared first, then their values, then the next keys. An object whose entries are a prefix of another's sorts first, so `{"a":1}` < `{"a":1,"b":2}` < `{"b":0}`.
+- **Arrays:** element by element; a prefix sorts first, so `[]` < `[null]` < `[1]` < `[1,2]`.
-Simple INSERT example:
+SQL `NULL` is placed by `NULLS FIRST` or `NULLS LAST`.
```sql
-INSERT INTO vartab VALUES
- (1, 'null'),
- (2, NULL),
- (3, 'true'),
- (4, '-17'),
- (5, '123.12'),
- (6, '1.912'),
- (7, '"A quote"'),
- (8, '[-1, 12, false]'),
- (9, '{ "x": "abc", "y": false, "z": 10 }'),
- (10, '"2021-01-01"');
+SELECT v, VARIANT_TYPE(v) AS type
+FROM (
+ SELECT PARSE_TO_VARIANT('[1, 2]') AS v UNION ALL
+ SELECT PARSE_TO_VARIANT('{"a": 1}') UNION ALL
+ SELECT PARSE_TO_VARIANT('"9"') UNION ALL
+ SELECT PARSE_TO_VARIANT('"10"') UNION ALL
+ SELECT PARSE_TO_VARIANT('10') UNION ALL
+ SELECT PARSE_TO_VARIANT('9.5') UNION ALL
+ SELECT PARSE_TO_VARIANT('true') UNION ALL
+ SELECT PARSE_TO_VARIANT('null') UNION ALL
+ SELECT NULL
+) t
+ORDER BY v;
```
-Tip: Non-standard JSON types such as date/time will be stored as strings unless a Schema Template is provided. For better computation efficiency, consider extracting them to static columns or declaring their types via a Schema Template.
+```text
++---------+---------+
+| v | type |
++---------+---------+
+| NULL | NULL |
+| null | null |
+| true | bool |
+| 9.5 | double |
+| 10 | tinyint |
+| 10 | string |
+| 9 | string |
+| {"a":1} | object |
+| [1,2] | array |
++---------+---------+
+```
+
+### Typed comparison and VARIANT comparison
-## Extended types (Schema Template)
+The operands decide whether a comparison uses the VARIANT rules or the rules of a concrete type. Take a path `a` whose values are `1`, `1.0`, and `"1"`:
+
+| Expression | Compared as | Result |
+| --- | --- | --- |
+| `v['a'] = 1` | `DECIMAL(38, 9)`, through implicit CAST of `v['a']` | Matches all three values |
+| `v['a'] = CAST(1 AS VARIANT)`, `v['a'] = w['a']` | VARIANT | Matches `1` and `1.0`, not `"1"` |
+| `GROUP BY v['a']` | VARIANT | `1` and `1.0` form one group, `"1"` another |
+| `GROUP BY CAST(v['a'] AS STRING)` | `STRING` | One group, `1` |
+| `ORDER BY v['a']` | VARIANT | Numbers first, then strings |
+| `ORDER BY CAST(v['a'] AS INT)` | `INT` | Numeric order; values that cannot be converted become `NULL` |
-Besides primitive types, VARIANT supports the following extended types via Schema Template:
+### Notes
-- Number (extended)
- - Decimal: Decimal32 / Decimal64 / Decimal128 / Decimal256
- - LargeInt
-- Datetime
-- Timestamptz
-- Date
-- IPV4 / IPV6
-- Boolean
-- ARRAY<T> (T can be any of the above, one-dimensional only)
+- **Cost.** VARIANT keys are hashed and compared by their logical value. In a benchmark on 44 million rows, `GROUP BY`, sorting, and joins on a VARIANT key took 1.2 to 3.7 times as long as the same operations on `CAST(v['path'] AS <type>)`. When a path has one known type, CAST it.
+- **Results follow the stored values.** The normalizations in [What storage keeps](#what-storage-keeps) happen before comparison: a member that was `null` is missing, a `DATE` in the root value compares as a string, and booleans mixed with numbers on one path can read back as `1` or `0`. Declare paths whose type matters in a Schema Template.
+- **Mixed types sort by kind, not by value.** If a path holds both numbers and numeric strings, `ORDER BY v['a']` puts every number before every string, and orders the strings by bytes. CAST to one type for numeric or lexical order.
+- **Values that look the same can differ.** In a `GROUP BY` result, the number `1` and the string `"1"` are displayed the same way but form two groups. Use `VARIANT_TYPE` to see the difference.
+- The order across kinds is defined by Doris so that results are deterministic. It is not part of the JSON standard and can differ from other systems.
+- The internal hash of a VARIANT value is an implementation detail, not a stable user-facing checksum.
-Note: Predefined Schema can only be specified at table creation. ALTER is currently not supported (future versions may support adding new subcolumn definitions, but changing an existing subcolumn type is not supported).
+## Schema Template
-Example:
+A Schema Template declares the storage type of selected paths. Declare only the key paths that need a stable type or a path-specific index; the rest of the document stays dynamic.
```sql
CREATE TABLE test_var_schema (
@@ -291,63 +438,85 @@ CREATE TABLE test_var_schema (
'string_val': STRING,
'decimal_val': DECIMAL(38, 9),
'datetime_val': DATETIME,
- 'tz_val': TIMESTAMPTZ,
'ip_val': IPV4
> NULL
)
PROPERTIES ("replication_num" = "1");
+```
+
+A template field can use these types:
+
+- Numbers: `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT`, `FLOAT`, `DOUBLE`, and `DECIMAL(p, s)` with `p <= 38`
+- `STRING` (or `TEXT`)
+- `BOOLEAN`
+- `DATE`, `DATETIME(p)`, `TIMESTAMP_NS`
+- `IPV4`, `IPV6`
+- `ARRAY<T>`, where `T` is one of the types above (one dimension only)
+
+### Writing to template paths
+
+On a declared path, each value is converted to the declared type with non-strict CAST rules. A value that cannot be converted is stored as `NULL`, so the path is missing when read back; the rest of the row is still written. The conversion can change a value:
+
+```sql
+CREATE TABLE tpl_demo (
+ k INT,
+ v VARIANT<'id': INT, 'price': DECIMAL(10, 2), 'ts': DATETIME(3)>
+)
+DUPLICATE KEY(k)
+DISTRIBUTED BY HASH(k) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
-INSERT INTO test_var_schema VALUES (1, '{
- "large_int_val" : "123222222222222222222222",
- "string_val" : "Hello World",
- "decimal_val" : 1.11111111,
- "datetime_val" : "2025-05-16 11:11:11",
- "tz_val" : "2025-05-16 11:11:11+08:00",
- "ip_val" : "127.0.0.1"
-}');
+INSERT INTO tpl_demo VALUES
+ (1, PARSE_TO_VARIANT('{"id": "123", "price": 9.999, "ts": "2024-01-01 10:00:00.123456"}')),
+ (2, PARSE_TO_VARIANT('{"id": "abc", "price": "x", "ts": "not a time"}')),
+ (3, PARSE_TO_VARIANT('{"id": 1.7}'));
-SELECT variant_type(v1) FROM test_var_schema;
+SELECT k, v FROM tpl_demo ORDER BY k;
+```
-+---------------------------------------------------------------------------------------------------------------------------------------------------+
-| variant_type(v1) |
-+---------------------------------------------------------------------------------------------------------------------------------------------------+
-| {"datetime_val":"datetimev2","decimal_val":"decimal128i","ip_val":"ipv4","large_int_val":"largeint","string_val":"string","tz_val":"timestamptz"} |
-+---------------------------------------------------------------------------------------------------------------------------------------------------+
+```text
++------+------------------------------------------------------------+
+| k | v |
++------+------------------------------------------------------------+
+| 1 | {"id":123,"price":10.00,"ts":"2024-01-01 10:00:00.123000"} |
+| 2 | {} |
+| 3 | {"id":1} |
++------+------------------------------------------------------------+
```
-`{"date": 2020-01-01}` and `{"ip": 127.0.0.1}` are invalid JSON texts; the correct format is `{"date": "2020-01-01"}` and `{"ip": "127.0.0.1"}`.
+- `"123"` becomes `123`, `1.7` is truncated to `1`, `9.999` is rounded to `10.00`, and the timestamp keeps the declared precision (milliseconds); its text form always shows six fractional digits.
+- `"abc"`, `"x"`, and `"not a time"` cannot be converted, so they are dropped.
+- A JSON number with a fraction is parsed as `DOUBLE` before the conversion, so a `DECIMAL` path can lose precision. Write such values as JSON strings to keep every digit (see the [FAQ](#faq)).
+- Date and IP values must be JSON strings: `{"date": 2020-01-01}` and `{"ip": 127.0.0.1}` are not valid JSON; write `{"date": "2020-01-01"}` and `{"ip": "127.0.0.1"}`.
-Once a Schema Template is specified, if a JSON value conflicts with the declared type and cannot be converted, it will be stored as NULL. For example:
+:::caution
+A value is only converted when its JSON type has a conversion to the declared type. A JSON number on an `IPV4` or `IPV6` path makes the whole write fail. If one write mixes JSON strings and numbers on a `DATE`, `DATETIME`, `IPV4`, or `IPV6` path, even the valid values on that path can be dropped. Write the values of such paths as JSON strings.
+:::
-```sql
-INSERT INTO test_var_schema VALUES (1, '{
- "decimal_val" : "1.11111111",
- "ip_val" : "127.xxxxxx.xxxx",
- "large_int_val" : "aaabbccc"
-}');
+### Reading template paths
--- Only decimal_val remains
-SELECT * FROM test_var_schema;
+`v['path']` keeps the `VARIANT` type even for a declared path; Doris does not cast it to the declared type automatically. CAST explicitly when you need the declared type, and CAST to the declared type in filters so that the query can use zone maps and indexes on the path:
-+------+-----------------------------+
-| id | v1 |
-+------+-----------------------------+
-| 1 | {"decimal_val":1.111111110} |
-+------+-----------------------------+
+```sql
+SELECT CAST(v['ts'] AS DATETIME(3)) AS ts, -- 2024-01-01 10:00:00.123
+ CAST(v['ts'] AS STRING) AS ts_text, -- 2024-01-01 10:00:00.123000
+ VARIANT_TYPE(v['price']) AS type -- decimal
+FROM tpl_demo
+WHERE CAST(v['id'] AS INT) = 123;
```
-Schema only guides the persisted storage type. Query expressions that are not written to a table still keep their actual runtime types:
+The Schema Template only decides how values are stored. The same JSON computed in a query keeps its parsed type:
```sql
--- The quoted JSON member is a STRING. PARSE_TO_VARIANT is used because
--- CAST(string AS VARIANT) always preserves the whole input as a string.
-SELECT variant_type(PARSE_TO_VARIANT('{"a" : "12345"}')['a']);
+SELECT VARIANT_TYPE(PARSE_TO_VARIANT('{"price": 9.999}')['price']); -- double
```
-Wildcard matching and order:
+### Pattern matching
+
+A field name is a glob pattern by default (`MATCH_NAME_GLOB`): `*` matches any sequence of characters, including the `.` between nested keys, and `?` matches one character. For example, `'m*'` matches `m1` and also `m2.x`. When a path matches several fields, the first field in definition order is used:
```sql
-CREATE TABLE test_var_schema (
+CREATE TABLE test_var_pattern (
id BIGINT NOT NULL,
v1 VARIANT<
'enumString*' : STRING,
@@ -357,10 +526,10 @@ CREATE TABLE test_var_schema (
)
PROPERTIES ("replication_num" = "1");
--- If enumString1 matches both patterns, the first matching pattern in definition order (STRING) is used
+-- enumString1 matches both enumString* and enum*; the first one (STRING) is used.
```
-If a column name contains `*` and you want to match it by its literal name (not as a prefix wildcard), use:
+To match a name that contains `*` literally, use `MATCH_NAME`:
```sql
v1 VARIANT<
@@ -368,34 +537,92 @@ v1 VARIANT<
> NULL
```
-Matched subpaths participate in Subcolumnization by default and are exposed as columns. If too many paths match and generate excessive columns, consider enabling `variant_enable_typed_paths_to_sparse` (see “Configuration”).
+Matched paths participate in Subcolumnization by default. If too many paths match and generate too many subcolumns, consider `variant_enable_typed_paths_to_sparse` (see [Column properties](#column-properties)).
+
+The Schema Template cannot be changed after the column is created. See [ALTER TABLE](#alter-table).
+
+## ALTER TABLE
-## Type conflicts and promotion rules
+`ALTER TABLE` supports:
-When incompatible types appear on the same path (e.g., the same field shows up as both integer and string), the type is promoted to JSONB to avoid information loss:
+- `ADD COLUMN` of a nullable VARIANT column, with or without a Schema Template and properties.
+- `DROP COLUMN`, `RENAME COLUMN`, and `MODIFY COLUMN ... COMMENT '...'`.
+- `ADD INDEX` and `DROP INDEX`. An index with `field_pattern` can only be defined in `CREATE TABLE`.
+- On a column without a Schema Template: changing `NOT NULL` to `NULL`, and changing `variant_doc_materialization_min_rows`. `MODIFY COLUMN` takes the complete column definition; a property it omits takes the value of the matching `default_variant_*` session variable, so restate every property whose value differs.
+
+Not supported:
+
+- `ADD COLUMN ... VARIANT NOT NULL`: a `NOT NULL` column added by `ALTER` needs a default value, and VARIANT only allows `DEFAULT NULL`. Define `NOT NULL` VARIANT columns in `CREATE TABLE`.
+- Adding a Schema Template to an existing column. On a column that has a Schema Template, `MODIFY COLUMN` can only change the comment; any other change fails, even one that restates the same template.
+- Changing any property other than `variant_doc_materialization_min_rows`, changing `NULL` to `NOT NULL`, and converting between VARIANT and other types.
+- `BUILD INDEX` on a VARIANT column.
+
+For these changes, create a table with the new definition and copy the data with `INSERT INTO ... SELECT`; wrap a `STRING` source column in `PARSE_TO_VARIANT`.
```sql
-{"a" : 12345678}
-{"a" : "HelloWorld"}
--- a will be promoted to JSONB
+ALTER TABLE t ADD COLUMN v2 VARIANT NOT NULL;
+-- ERROR: Field 'v2' doesn't have a default value
+
+-- v is a VARIANT column without a Schema Template.
+ALTER TABLE t MODIFY COLUMN v VARIANT<'id': INT>;
+-- ERROR: Can not change variant schema templates
```
-Promotion rules:
+## Column properties
+
+Column properties are set in `properties(...)` inside the VARIANT type:
+
+```sql
+CREATE TABLE example_table (
+ id INT,
+ data_variant VARIANT<
+ 'path_1' : INT,
+ 'path_2' : STRING,
+ properties(
+ 'variant_max_subcolumns_count' = '2048',
+ 'variant_enable_typed_paths_to_sparse' = 'true',
+ 'variant_sparse_hash_shard_count' = '64'
+ )
+ >
+);
+```
+
+| Property | Default | ALTER | Description |
+| --- | --- | --- | --- |
+| `variant_max_subcolumns_count` | `2048` | No | Maximum number of dynamic paths that go through Subcolumnization in one data file; paths with more non-null values are chosen first, and the other paths are stored in sparse columns. `0` means no limit. Range 0 to 100000; stay at or below 10000. The default is enough for most workloads; if the workload truly needs a much larger number of subcolumns, prefer [DOC mode](./variant-workload-guide.md#doc-mode-template). |
+| `variant_enable_typed_paths_to_sparse` | `false` | No | By default, Schema Template paths always go through Subcolumnization and do not count toward `variant_max_subcolumns_count`. When `true`, they count toward the limit and can be stored in sparse columns. |
+| `variant_sparse_hash_shard_count` | `1` | No | Number of physical sparse columns that sparse paths are distributed to by hash. Range 0 to 1024; `0` is treated as `1`. |
+| `variant_max_sparse_column_statistics_size` | `10000` | No | Maximum number of sparse paths in one data file whose statistics are recorded. Beyond it, a query on a path that has no statistics cannot skip the sparse columns. Range 1 to 50000. |
+| `variant_enable_doc_mode` | `false` | No | Enables DOC mode. It cannot be set together with `variant_max_subcolumns_count`, `variant_enable_typed_paths_to_sparse`, `variant_max_sparse_column_statistics_size`, or `variant_sparse_hash_shard_count`. |
+| `variant_doc_materialization_min_rows` | `0` | Only without a Schema Template | DOC mode only. A write with fewer rows stores only the document; Subcolumnization happens once compaction merges files up to the threshold. `0` performs Subcolumnization at write time. Range 0 to 1000000000. |
+| `variant_doc_hash_shard_count` | `64` | No | DOC mode only. Number of columns that the stored document is split into. Range 0 to 1024; `0` is treated as `1`. |
+
+The **ALTER** column tells whether `ALTER TABLE ... MODIFY COLUMN` can change the property; see [ALTER TABLE](#alter-table). See [Wide columns](#wide-columns) for how to use the sparse and DOC mode properties.
+
+### Session variables
+
+| Variable | Default | Description |
+| --- | --- | --- |
+| `default_variant_max_subcolumns_count` | `2048` | Value of `variant_max_subcolumns_count` for a VARIANT column defined without it. |
+| `default_variant_enable_typed_paths_to_sparse` | `false` | Value of `variant_enable_typed_paths_to_sparse` for a column defined without it. |
+| `default_variant_sparse_hash_shard_count` | `0` | Value of `variant_sparse_hash_shard_count` for a column defined without it; `0` is treated as `1`. |
+| `default_variant_max_sparse_column_statistics_size` | `10000` | Value of `variant_max_sparse_column_statistics_size` for a column defined without it. |
+| `default_variant_enable_doc_mode` | `false` | Value of `variant_enable_doc_mode` for a column defined without it. |
+| `default_variant_doc_materialization_min_rows` | `0` | Value of `variant_doc_materialization_min_rows` for a column defined without it. |
+| `default_variant_doc_hash_shard_count` | `64` | Value of `variant_doc_hash_shard_count` for a column defined without it. |
+| `describe_extend_variant_column` | `false` | When `true`, `DESC` also lists the subcolumns of VARIANT columns. |
-| Source type | Current type | Final type |
-| -------------- | ------------- | ------------ |
-| `TinyInt` | `BigInt` | `BigInt` |
-| `TinyInt` | `Double` | `Double` |
-| `TinyInt` | `String` | `JSONB` |
-| `TinyInt` | `Array` | `JSONB` |
-| `BigInt` | `Double` | `JSONB` |
-| `BigInt` | `String` | `JSONB` |
-| `BigInt` | `Array` | `JSONB` |
-| `Double` | `String` | `JSONB` |
-| `Double` | `Array` | `JSONB` |
-| `Array<Double>`| `Array<String>`| `Array<Jsonb>` |
+The `default_variant_*` variables apply when a column is defined, by `CREATE TABLE` or by `ALTER TABLE`. Changing them does not affect existing columns, but it does affect a later `MODIFY COLUMN` that omits the property.
-If you need strict types (for stable indexing and storage), declare them via Schema Template.
+### BE configuration
+
+| Configuration | Default | Description |
+| --- | --- | --- |
+| `variant_max_json_key_length` | `255` | Maximum length in bytes of a JSON object key. A longer key is a parse error. Range 1 to 65535. |
+| `variant_throw_exeception_on_invalid_json` | `false` | When `false`, text that the JSON parser rejects is kept as a VARIANT string. When `true`, it is a parse error. |
+| `variant_enable_duplicate_json_path_check` | `false` | When `false`, a key that repeats in an object is a parse error. When `true`, the first value is kept and the repeats are ignored. |
+
+All three can be changed at runtime. See [Parse errors](#parse-errors) for how a parse error is reported.
## Variant indexes
@@ -405,13 +632,15 @@ 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.
+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.
+
```sql
...
PROPERTIES("replication_num" = "1", "bloom_filter_columns" = "v");
-- Use BloomFilter for equality/IN filters
-SELECT * FROM tbl WHERE v['id'] = 12345678;
-SELECT * FROM tbl WHERE v['id'] IN (1, 2, 3);
+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):
@@ -430,7 +659,7 @@ SELECT * FROM tbl WHERE v['id_2'] MATCH 'Apache';
### Index by subpath
-In 3.1.x/4.0 and later, you can specify index properties for certain VARIANT subpaths, and even configure both tokenized and non-tokenized inverted indexes for the same path. Path-specific indexes require the path type to be declared via Schema Template.
+You can specify index properties for certain VARIANT subpaths, and even configure both tokenized and non-tokenized inverted indexes for the same path. Path-specific indexes require the path type to be declared via Schema Template.
```sql
-- Common properties: field_pattern (target path), analyzer, parser, support_phrase, etc.
@@ -460,16 +689,18 @@ SELECT * FROM tbl WHERE v['pattern_1'] MATCH 'Doris';
SELECT * FROM tbl WHERE v['pattern_1'] = 'Doris';
```
-Note: 2.1.7+ supports only InvertedIndex V2 properties (fewer files, lower write IOPS; suitable for disaggregated storage/compute). 2.1.8+ removes offline Build Index.
-
### When indexes don’t work
1. Type changes cause index loss: if a subpath changes to an incompatible type (e.g., INT → JSONB), the index is lost. Fix by pinning types and indexes via Schema Template.
-2. Query type mismatch:
+2. The filter does not compare the path with its stored type:
```sql
- -- v['id'] is actually STRING; using INT equality causes index not to be used
+ -- An implicit numeric comparison is evaluated row by row and uses no index
SELECT * FROM tbl WHERE v['id'] = 123456;
+
+ -- CAST to the stored type instead
+ SELECT * FROM tbl WHERE CAST(v['id'] AS BIGINT) = 123456;
```
+ If `v['id']` is stored as a string, compare it with a string: `v['id'] = '123456'`.
3. Misconfigured index: indexes apply to subpaths, not the entire VARIANT column.
```sql
-- VARIANT itself cannot be indexed as a whole
@@ -485,111 +716,11 @@ Note: 2.1.7+ supports only InvertedIndex V2 properties (fewer files, lower write
SELECT * FROM tbl WHERE v_str MATCH 'Doris';
```
-## INSERT and load
-
-### INSERT INTO VALUES
-
-```sql
-CREATE TABLE IF NOT EXISTS variant_tbl (
- k BIGINT,
- v VARIANT
-) PROPERTIES("replication_num" = "1");
-
-INSERT INTO variant_tbl VALUES (1, '{"a" : 123}');
-
-select * from variant_tbl;
-+------+-----------+
-| k | v |
-+------+-----------+
-| 1 | {"a":123} |
-+------+-----------+
-
--- v['a'] is a VARIANT
-select v['a'] from variant_tbl;
-+--------+
-| v['a'] |
-+--------+
-| 123 |
-+--------+
-
--- Accessing a non-existent key returns NULL
-select v['a']['no_such_key'] from variant_tbl;;
-+-----------------------+
-| v['a']['no_such_key'] |
-+-----------------------+
-| NULL |
-+-----------------------+
-
-```
-
-### Load (Stream Load)
-
-```bash
-# Line-delimited JSON (one JSON record per line)
-curl --location-trusted -u root: -T gh_2022-11-07-3.json \
- -H "read_json_by_line:true" -H "format:json" \
- http://127.0.0.1:8030/api/test_variant/github_events/_stream_load
-```
-
-See also: `https://doris.apache.org/docs/dev/data-operate/import/complex-types/variant`
-
-After loading, verify with `SELECT count(*)` or sample with `SELECT * ... LIMIT 1`. For high-throughput ingestion, prefer RANDOM bucketing and enable Group Commit.
-
-## Output
-
-The JSON text returned when reading a VARIANT column is not byte-for-byte identical to the JSON text that was written in: inside a JSON object, keys are emitted in sorted (lexicographic) order regardless of the order they appeared in the input JSON.
-
-```sql
-INSERT INTO variant_tbl VALUES
- (2, '{ "b": 2, "a": 1, "c": { "y": 20, "x": 10 } }');
-
-SELECT v FROM variant_tbl WHERE k = 2;
-+-----------------------------------+
-| v |
-+-----------------------------------+
-| {"a":1,"b":2,"c":{"x":10,"y":20}} |
-+-----------------------------------+
-```
-
-Sorting applies at every level — top-level keys become `a`, `b`, `c`, and the nested object's keys become `x`, `y`.
-
-## Supported operations
-
-In Doris 4.2 and later, whole VARIANT values support hash-based grouping and deduplication, but they do not support comparison, join-key, or ordering semantics.
-
-| Operation on a whole VARIANT value | Support | Notes |
-| --- | --- | --- |
-| `GROUP BY` | Supported | Uses the grouping and hash rules described above. |
-| `DISTINCT`, `COUNT(DISTINCT ...)` | Supported | Logically equivalent values are deduplicated. |
-| `INTERSECT`, `EXCEPT`, `UNION DISTINCT` | Supported | Uses the same rules described above. |
-| `COUNT(*)`, `COUNT(variant)` | Supported | `COUNT(variant)` excludes outer SQL `NULL` in the normal SQL manner. |
-| `IF`, `CASE`, `IFNULL`, `COALESCE` | Supported | Conditional expressions can return and consume VARIANT values. |
-| VARIANT in transient `ARRAY`, `MAP`, or `STRUCT` expressions | Supported | This does not allow these nested types in persisted table schemas. |
-| `EXPLODE_VARIANT_ARRAY`, `EXPLODE`/`EXPLODE_OUTER` on `ARRAY<VARIANT>` | Supported | Emits VARIANT elements and preserves SQL NULL versus Variant/JSON `null`. |
-| `=`, `!=`, `<=>`, `<`, `<=`, `>`, `>=` | Not supported | Extract and CAST a comparable subpath on both sides. |
-| Join key | Not supported | CAST the required subpath to the same concrete type on both inputs. |
-| `ORDER BY`, Sort/TopN key | Not supported | CAST the required subpath before ordering. |
-| Window partition/order key | Not supported | A whole VARIANT value cannot be a window key. |
-| `MIN(variant)`, `MAX(variant)` | Not supported | CAST a scalar subpath before aggregation. |
-
-For comparison, filtering, arithmetic, and ordering, extract the required subpath and CAST it to a concrete type explicitly or implicitly:
-
-```sql
--- Explicit CAST
-SELECT CAST(v['arr'] AS ARRAY<TEXT>) FROM tbl;
-SELECT * FROM tbl WHERE CAST(v['decimal'] AS DECIMAL(27, 9)) = 1.111111111;
-SELECT * FROM tbl WHERE CAST(v['date'] AS DATE) = '2021-01-02';
-
--- Implicit CAST
-SELECT * FROM tbl WHERE v['bool'];
-SELECT * FROM tbl WHERE v['str'] MATCH 'Doris';
-```
-
## Wide columns
When ingested data contains many distinct JSON keys, the number of subcolumns produced by Subcolumnization can grow rapidly; at scale this may cause metadata bloat, higher write/merge cost, and query slowdowns. To address “wide columns” (too many subcolumns), VARIANT provides two mechanisms: **Sparse columns** and **DOC encoding**.
-For workload selection guidance, see [Variant Workload Guide](./variant-workload-guide). This section only explains the mechanisms and their related properties.
+For workload selection guidance, see [Variant Workload Guide](./variant-workload-guide.md). This section only explains the mechanisms and their related properties.
Note: these two mechanisms are mutually exclusive—enabling DOC encoding disables sparse columns, and vice versa.
@@ -604,7 +735,7 @@ Note: these two mechanisms are mutually exclusive—enabling DOC encoding disabl
**Reference notes**
- If most keys have similar non-null ratios (little sparsity contrast), it’s hard to identify truly sparse paths and the benefit of sparse columns is reduced.
-- `variant_max_subcolumns_count` defaults to `2048`, which is already enough for most workloads. Avoid raising it aggressively just to pre-allocate more extracted subcolumns; if the workload truly needs large-scale Subcolumnization, prefer <a href="./variant-workload-guide#doc-mode-template">DOC mode</a>. The practical upper bound is still recommended to stay ≤ `10000`.
+- `variant_max_subcolumns_count` defaults to `2048`, which is already enough for most workloads. Avoid raising it aggressively just to pre-allocate more extracted subcolumns; if the workload truly needs large-scale Subcolumnization, prefer [DOC mode](./variant-workload-guide.md#doc-mode-template). The practical upper bound is still recommended to stay ≤ `10000`.
- `variant_sparse_hash_shard_count` can be roughly estimated as “number of sparse paths / 128”. Example: total JSON keys ≈ 10,000, `variant_max_subcolumns_count = 2000`, then sparse paths ≈ 8000, so `variant_sparse_hash_shard_count` can start around `8000/128`.
### DOC encoding (DOC mode)
@@ -617,64 +748,6 @@ Note: these two mechanisms are mutually exclusive—enabling DOC encoding disabl
- For ultra-wide workloads, DOC mode is also the more stable choice when Subcolumnization scale approaches ten-thousand subcolumns. Compared with default eager Subcolumnization, compaction memory can drop by about two-thirds, and sparse wide-column ingest throughput can improve by about 5-10x.
- When a `VARIANT` column is very wide and queries often read the whole document, DOC mode can improve `SELECT variant_col` performance by orders of magnitude compared with reconstructing the document from many subcolumns.
-**Reference notes**
-
-- DOC mode requires `variant_enable_doc_mode = true`.
-- In DOC mode, typed paths declared via Schema Template are limited to numeric, string, and array types.
-- `variant_doc_hash_shard_count` can be roughly estimated as “total JSON keys / 128”.
-
-See the “Configuration” section below for the full property list.
-
-## Limitations
-
-- **Wide tables optimization**: For wide tables with a large number of dynamic sub-columns (e.g., more than 2000 columns) generated by the `VARIANT` type, it is highly recommended to enable **Storage Format V3** by specifying `"storage_format" = "V3"` in the table `PROPERTIES`. This decouples column metadata from the Segment Footer, speeding up file opening and reducing memory overhead.
-- JSON key length ≤ 255.
-- Cannot be a primary key or sort key.
-- Persisted table schemas cannot nest VARIANT within other types (for example, `ARRAY<VARIANT>` or `STRUCT<VARIANT>`). Transient expression results can use the supported nested-container operations listed above.
-- Outside DOC mode, reading the entire VARIANT column scans all subpaths. For very wide columns, direct `SELECT variant_col` is generally not recommended unless DOC mode is enabled. If a column has many subpaths, consider storing the original JSON string in an extra STRING/JSONB column for whole-object searches like `LIKE`:
-
-```sql
-CREATE TABLE example_table (
- id INT,
- data_variant VARIANT
-);
-SELECT * FROM example_table WHERE data_variant LIKE '%doris%';
-
--- Better: keep the original JSON string for whole-object matching
-CREATE TABLE example_table (
- id INT,
- data_string STRING,
- data_variant VARIANT
-);
-SELECT * FROM example_table WHERE data_string LIKE '%doris%';
-```
-
-## Configuration
-
-Starting from 3.1+, VARIANT supports type-level properties on columns:
-
-```sql
-CREATE TABLE example_table (
- id INT,
- data_variant VARIANT<
- 'path_1' : INT,
- 'path_2' : STRING,
- properties(
- 'variant_max_subcolumns_count' = '2048',
- 'variant_enable_typed_paths_to_sparse' = 'true',
- 'variant_sparse_hash_shard_count' = '64'
- )
- >
-);
-```
-
-<table>
-<tr><td>Property</td><td>Description</td></tr>
-<tr><td>`variant_max_subcolumns_count`</td><td>Max number of paths that can go through Subcolumnization. Above the threshold, new paths may be stored in a shared data structure. Default: 2048 (Recommended), which is already enough for most workloads. Avoid setting it too large. If the workload truly needs very large extracted-subcolumn scale, prefer <a href="./variant-workload-guide#doc-mode-template">DOC mode</a>. 0 means no limit; do not exceed 10000.</td></tr>
-<tr><td>`variant_enable_typed_paths_to_sparse`</td><td>By default, typed paths always participate in Subcolumnization (and do not count against `variant_max_subcolumns_count`). When set to `true`, typed paths also count toward the threshold and may be moved to the shared structure.</td></tr>
-<tr><td>`variant_sparse_hash_shard_count`</td><td>Shard count for sparse columns. Distributes sparse subpaths across multiple sparse columns to improve read performance. Default: 1; tune based on the number of sparse subpaths.</td></tr>
-</table>
-
```sql
CREATE TABLE example_table (
id INT,
@@ -690,35 +763,32 @@ CREATE TABLE example_table (
);
```
-<table>
-<tr><td>Property</td><td>Description</td></tr>
-<tr><td>`variant_enable_doc_mode`</td><td>Enable DOC encoding mode. When `true`, the original JSON is stored as a stored field to quickly return the whole JSON document. DOC mode is mutually exclusive with sparse columns. Default: `false`.</td></tr>
-<tr><td>`variant_doc_materialization_min_rows`</td><td>Minimum row threshold to trigger Subcolumnization in DOC mode. When rows are below this value, only the original JSON is stored; after compaction merges files to reach the threshold, Subcolumnization is performed. Helps reduce overhead for small-batch writes.</td></tr>
-<tr><td>`variant_doc_hash_shard_count`</td><td>Shard count for DOC encoding. The original JSON is split into the specified number of columns for storage and reassembled when querying the whole JSON. Default: 64; tune based on JSON size and concurrency.</td></tr>
-</table>
+**Reference notes**
-Behavior at limits and tuning suggestions:
+- DOC mode requires `variant_enable_doc_mode = true`.
+- In DOC mode, Schema Template fields are limited to strings, integers, `FLOAT`, `DOUBLE`, `BOOLEAN`, and arrays of these types.
+- `variant_doc_hash_shard_count` can be roughly estimated as “total JSON keys / 128”.
-1. After exceeding the threshold, new paths are written into the shared structure; Rowset merges may also recycle some paths into the shared structure.
+### Behavior at limits and tuning suggestions
+
+1. After exceeding the threshold, new paths are written into sparse columns; Rowset merges may also move some paths into sparse columns.
2. The system prefers to keep paths with higher non-null ratios and higher access frequencies in Subcolumnization.
3. Close to 10,000 paths in Subcolumnization requires strong hardware (≥128G RAM, ≥32C per node recommended). If the workload is already near this range, prefer evaluating DOC mode first.
4. Ingestion tuning: increase client `batch_size` appropriately, or use Group Commit (increase `group_commit_interval_ms`/`group_commit_data_bytes` as needed).
-5. If partition pruning is not needed, consider RANDOM bucketing and enabling single-tablet loading to reduce compaction write amplification.
+5. If bucket pruning is not needed, consider RANDOM bucketing and enabling single-tablet loading to reduce compaction write amplification.
6. BE tuning knobs: `max_cumu_compaction_threads` (≥8), `vertical_compaction_num_columns_per_group=500` (improves vertical compaction but increases memory), `segment_cache_memory_percentage=20` (improves metadata cache efficiency).
7. Watch Compaction Score; if it keeps rising, compaction is lagging—reduce ingestion pressure.
8. Avoid large `SELECT *` on VARIANT; prefer specific projections like `SELECT v['path']`.
-Note: If you see Stream Load error `[DATA_QUALITY_ERROR]Reached max column size limit 2048` (only on 2.1.x and 3.0.x), it means the merged tablet schema reached its column limit. You may increase `variant_max_merged_tablet_schema_size` (not recommended beyond 4096; requires strong hardware).
-
-## Inspect number of columns and types
+## Inspect subcolumns and types
-Approach 1: use `variant_type` to inspect per-row schema (more precise, higher cost):
+Approach 1: use [VARIANT_TYPE](../../../sql-functions/scalar-functions/variant-functions/variant-type.md) to get the type of a value or of one path, row by row. It reads every row it checks:
```sql
-SELECT variant_type(v) FROM variant_tbl;
+SELECT VARIANT_TYPE(v), VARIANT_TYPE(v['a']) FROM variant_tbl LIMIT 10;
```
-Approach 2: extended `DESC` to show subpaths extracted through Subcolumnization:
+Approach 2: extended `DESC` to show the subpaths extracted through Subcolumnization and their storage types:
```sql
SET describe_extend_variant_column = true;
@@ -729,14 +799,37 @@ DESC variant_tbl;
DESCRIBE ${table_name} PARTITION ($partition_name);
```
-Use both: Approach 1 is precise; Approach 2 is efficient.
+Approach 1 shows the type of each value; Approach 2 shows how each path is stored.
+
+## Limitations
+
+- **Wide tables optimization**: For wide tables with a large number of dynamic sub-columns (e.g., more than 2000 columns) generated by the `VARIANT` type, it is highly recommended to enable **Storage Format V3** by specifying `"storage_format" = "V3"` in the table `PROPERTIES`. This decouples column metadata from the Segment Footer, speeding up file opening and reducing memory overhead.
+- A JSON key can be at most 255 bytes long by default (`variant_max_json_key_length`).
+- A VARIANT column cannot be a key, partition, or bucketing column, and it cannot be nested in another type in a table schema (see [Define a VARIANT column](#define-a-variant-column)).
+- Outside DOC mode, reading the entire VARIANT column scans all subpaths. For very wide columns, direct `SELECT variant_col` is generally not recommended unless DOC mode is enabled. If a column has many subpaths, consider storing the original JSON string in an extra STRING/JSONB column for whole-object searches like `LIKE`:
+
+```sql
+CREATE TABLE example_table (
+ id INT,
+ data_variant VARIANT
+);
+SELECT * FROM example_table WHERE data_variant LIKE '%doris%';
+
+-- Better: keep the original JSON string for whole-object matching
+CREATE TABLE example_table (
+ id INT,
+ data_string STRING,
+ data_variant VARIANT
+);
+SELECT * FROM example_table WHERE data_string LIKE '%doris%';
+```
## Compared with JSON type
- Storage: JSON is stored as JSONB (row-oriented). VARIANT uses Subcolumnization on write (higher compression, smaller size).
- Query: JSON requires parsing. VARIANT scans columns directly and is usually much faster.
-ClickBench (43 queries):
+A modified ClickBench (43 queries):
- Storage: VARIANT saves ~65% vs JSON.
- Query: VARIANT is 8x+ faster than JSON, close to predefined static columns.
@@ -758,9 +851,17 @@ ClickBench (43 queries):
## FAQ
-1. Are `null` in VARIANT and SQL `NULL` different?
- - No. They are equivalent.
-2. Why doesn’t my query/index work?
- - Check whether you CAST paths to the correct types; whether the type was promoted to JSONB due to conflicts; or whether you mistakenly expect an index on the whole VARIANT instead of on subpaths.
-3. Why does DECIMAL lose precision when written into a VARIANT column?
- - When writing to a VARIANT column, the subcolumn type is not inferred as DECIMAL — numeric values are stored as DOUBLE, which can drop trailing decimals. Even declaring the subpath as DECIMAL via the Schema Template (e.g. `pm25 VARIANT<'xxx': DECIMAL(6, 2)>`) does not fully guarantee precision, because the value is first parsed as DOUBLE and then converted to DECIMAL on the write path. If the JSON value is written as a string (e.g. `'{"num": "12.345"}'`) together with a matching Sche [...]
+1. Are `null` in VARIANT and SQL `NULL` the same?
+ - No. A JSON `null` computed in a query is a VARIANT `null` value, while a missing path is SQL `NULL`. After the value is stored, `null` members of objects outside arrays are removed and read back as SQL `NULL`. See [NULL semantics](#null-semantics).
+2. Why does `v['a']` return `NULL` after `INSERT INTO t VALUES (1, '{"a": 1}')`?
+ - `INSERT` stores a string as a VARIANT string without parsing it, and so does `INSERT INTO ... SELECT` from `s3()`, `hdfs()`, or another string column. Use `PARSE_TO_VARIANT('{"a": 1}')`, or load the data with a load job such as Stream Load. See [Write data](#write-data).
+3. Why is a whole JSON document stored as a string, although it is valid JSON?
+ - The document probably contains an integer outside [-2^63, 2^64 - 1] or a number outside the `DOUBLE` range. The parser rejects such a document, and by default it is kept as a string. See [Parse errors](#parse-errors).
+4. Why doesn’t my query/index work?
+ - Check whether you CAST paths to their stored types (implicit numeric comparisons cannot use indexes), whether the type was promoted to JSONB due to conflicts, and whether you mistakenly expect an index on the whole VARIANT instead of on subpaths.
+5. Why does `ORDER BY v['a']` put `"10"` after `9`, or `GROUP BY v['a']` separate `1` and `"1"`?
+ - VARIANT ordering and equality first look at the kind of the value: numbers sort before strings, and a number never equals a string. CAST the path to one type when you need numeric or lexical semantics. See [Comparison, grouping, and ordering](#comparison-grouping-and-ordering).
+6. Why does `COALESCE(v['a'], 0)` return `0.000000000` when `v['a']` is a string, and `IF(..., v['a'], 1)` return `NULL`?
+ - Mixing VARIANT with another type converts the VARIANT values to the other type, which is `DECIMAL(38, 9)` for an integer. A string such as `"abc"` cannot be converted and becomes `NULL`, so `COALESCE` returns `0` as `0.000000000`. Write `COALESCE(v['a'], CAST(0 AS VARIANT))` to keep a VARIANT result, or CAST `v['a']` to the type you want.
+7. Why does DECIMAL lose precision when written into a VARIANT column?
+ - JSON numbers with a fraction are inferred as `DOUBLE`, not `DECIMAL`, so trailing digits can be lost. Declaring the path as `DECIMAL` in a Schema Template, for example `v VARIANT<'num': DECIMAL(9, 3)>`, does not fully help, because the value is parsed as `DOUBLE` first. Write the value as a JSON string inside the document, for example `PARSE_TO_VARIANT('{"num": "12.345"}')`; it is then converted directly to `DECIMAL(9, 3)` without loss.
diff --git a/docs/sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md b/docs/sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md
index 6280866ee03..d9fed41af12 100644
--- a/docs/sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md
+++ b/docs/sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md
@@ -16,7 +16,7 @@ Use this guide when you are deciding how to model a new `VARIANT` workload. It h
- If the JSON is very wide, should I start with default behavior, sparse columns, or DOC mode?
- Which settings should I leave at defaults, and which ones should I change first?
-If you already know you want `VARIANT` and only need syntax or type rules, go to [VARIANT](./VARIANT). If you want the smallest runnable import example, go to [Import Variant Data](../../../../data-operate/import/complex-types/variant).
+If you already know you want `VARIANT` and only need syntax or type rules, go to [VARIANT](./VARIANT.md). If you want the smallest runnable import example, go to [Import Variant Data](../../../../data-operate/import/complex-types/variant.md).
:::tip Why choose VARIANT
`VARIANT` keeps JSON flexible, but Doris can still apply Subcolumnization to frequently used paths. That lets common filters, aggregations, and path-level indexes work well without freezing the whole document schema in advance. On very wide JSON, storage-layer optimizations keep Subcolumnization practical at much larger path counts.
@@ -34,7 +34,7 @@ If you already know you want `VARIANT` and only need syntax or type rules, go to
Prefer static columns when these conditions dominate:
- The schema is stable and known in advance.
-- Core fields are regularly used as join keys, sort keys, or tightly controlled typed columns.
+- Core fields are regularly used as join keys, sort keys, or tightly controlled typed columns. VARIANT values can be joined and sorted, but typed columns are faster and keep typed semantics.
- The main requirement is to archive raw JSON, not to analyze by path.
## Four Questions First
@@ -60,7 +60,7 @@ You have a wide-JSON problem when path count keeps growing and starts to create
## Key Concepts
-Before reading the storage modes below, make sure these terms are clear. Each is explained in 2-3 lines; for implementation details, see [VARIANT](./VARIANT).
+Before reading the storage modes below, make sure these terms are clear. Each is explained in 2-3 lines; for implementation details, see [VARIANT](./VARIANT.md).
**Subcolumnization.** When data is written into a `VARIANT` column, Doris automatically discovers JSON paths and extracts hot paths as independent columnar subcolumns for efficient analytics.
@@ -212,6 +212,7 @@ Use it when ingest throughput is the first priority, the workload frequently nee
Watch for:
- DOC mode is not the default answer for every wide-JSON workload. If hot-path analytics dominates, sparse columns usually fit better.
- DOC mode and sparse columns are mutually exclusive. They cannot be enabled at the same time.
+- Apart from `DROP`, `RENAME`, and comment changes, a column that has a Schema Template cannot be altered, so its `variant_doc_materialization_min_rows` cannot change later. Choose the value when you create the table.
### Schema Template
@@ -243,6 +244,7 @@ Use it when only a few fields are business-critical and those paths need stricte
Watch for:
- Do not turn the whole JSON schema into a static template. That defeats the point of `VARIANT`.
- Schema Template should cover key paths only; the rest stays dynamic.
+- The Schema Template cannot be changed after the column is created. Decide the key paths before you load production data.
## Performance
@@ -277,7 +279,8 @@ Key takeaways:
### Query Phase
- **Do not use `SELECT *` as the main query pattern for very wide `VARIANT` columns.** Without DOC mode, `SELECT *` or `SELECT variant_col` must reconstruct large JSON from all subcolumns, which is much slower than specifying paths like `SELECT v['path']`.
-- **Always CAST subpaths when the query depends on type.** Type inference may not match expectations. If `v['id']` is actually stored as STRING but you compare with an integer literal, indexes will not be used and the result may be wrong.
+- **CAST subpaths to their stored type in filters.** A comparison such as `v['id'] = 123` casts the path implicitly and is evaluated row by row, so zone maps, BloomFilter, and indexes are not used. Write `CAST(v['id'] AS BIGINT) = 123`, with the type the path is stored as (check it with `DESC` after `SET describe_extend_variant_column = true`). A string path compared with a string literal needs no CAST.
+- **Know which comparison you get.** Comparing a path with a literal casts the path to a type chosen from the literal. Comparing two VARIANT values, or grouping, joining, or sorting by a VARIANT value, uses VARIANT semantics instead: numbers sort before strings, and `1` never equals `"1"`. See [Comparison, grouping, and ordering](./VARIANT.md#comparison-grouping-and-ordering).
### Operations Phase
@@ -290,11 +293,11 @@ Key takeaways:
After creating a table, use this minimal sequence to verify everything works:
```sql
--- Insert sample data
+-- Insert sample data. INSERT stores a plain string as a VARIANT string, so parse the JSON text.
INSERT INTO event_log VALUES
- ('2025-01-01 10:00:00', 1001, 'click', '{"page": "home", "user_id": 42, "duration_ms": 320}'),
- ('2025-01-01 10:00:01', 1002, 'purchase', '{"item": "widget", "price": 9.99, "user_id": 42}'),
- ('2025-01-01 10:00:02', 1003, 'click', '{"page": "search", "user_id": 99, "query": "doris variant"}');
+ ('2025-01-01 10:00:00', 1001, 'click', PARSE_TO_VARIANT('{"page": "home", "user_id": 42, "duration_ms": 320}')),
+ ('2025-01-01 10:00:01', 1002, 'purchase', PARSE_TO_VARIANT('{"item": "widget", "price": 9.99, "user_id": 42}')),
+ ('2025-01-01 10:00:02', 1003, 'click', PARSE_TO_VARIANT('{"page": "search", "user_id": 99, "query": "doris variant"}'));
-- Verify data
SELECT payload['user_id'], payload['page'] FROM event_log;
@@ -303,13 +306,13 @@ SELECT payload['user_id'], payload['page'] FROM event_log;
SET describe_extend_variant_column = true;
DESC event_log;
--- Check per-row types
-SELECT variant_type(payload) FROM event_log;
+-- Check the type of one path, row by row
+SELECT variant_type(payload['user_id']) FROM event_log;
```
## Related Reading
-- [VARIANT](./VARIANT)
-- [Import Variant Data](../../../../data-operate/import/complex-types/variant)
-- [Storage Format V3](../../../../table-design/storage-format)
+- [VARIANT](./VARIANT.md)
+- [Import Variant Data](../../../../data-operate/import/complex-types/variant.md)
+- [Storage Format V3](../../../../table-design/storage-format.md)
- [SEARCH Function](../../../../table-design/index/inverted-index/search-function.md)
diff --git a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
index c535bd0bee2..9809dfb6b4e 100644
--- a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
+++ b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
@@ -2,7 +2,7 @@
{
"title": "ELEMENT_AT",
"language": "en",
- "description": "The ELEMENTAT function is used to extract the element value from an array or map based on the specified index or key."
+ "description": "ELEMENT_AT extracts an element from an ARRAY, MAP, STRUCT, or VARIANT by index, key, or field name, with 1-based and negative array indexes."
}
---
@@ -29,20 +29,20 @@ ELEMENT_AT(container, key_or_index)
- For `MAP`: The key type (`K`) of the `MAP`, which can be any supported primitive type.
- For `STRUCT`: A constant integer field position (starting from **1**) or a constant string field name (matched **case-insensitively**).
- For `VARIANT` object access: A string key.
- - For a VARIANT array in Doris 4.2 and later: An integer index; positive indexes are 1-based and negative indexes count backward from the end.
+ - For a VARIANT array in Doris 5.0.0 and later: An integer index; positive indexes are 1-based and negative indexes count backward from the end.
## Return Value
- For `ARRAY`: Returns the element at the specified index (`T` type).
- For `MAP`: Returns the value corresponding to the specified key (`V` type).
- For `STRUCT`: Returns the specified subfield value.
-- For `VARIANT`: Returns a `VARIANT` type value.
+- For `VARIANT`: Returns a `VARIANT` type value. A missing key, index `0`, an out-of-range index, a string key on an array, an integer index on an object, and a key on a scalar value all return `NULL`.
- If the index or key does not exist, returns `NULL` (for `STRUCT`, an out-of-bound position or a non-existent field name reports an error).
- If the parameter is `NULL`, returns `NULL`.
## Notes
-1. **ARRAY and, in Doris 4.2 and later, VARIANT array indexes start from 1**, not 0.
+1. **ARRAY and, in Doris 5.0.0 and later, VARIANT array indexes start from 1**, not 0.
2. Negative indexes are supported for ARRAY and VARIANT array access: `-1` represents the last element, `-2` the second-to-last, and so on.
3. The `ELEMENT_AT(container, key_or_index)` function behaves the same as `container[key_or_index]` (see examples for details).
@@ -52,6 +52,8 @@ SELECT ELEMENT_AT(parse_to_variant('[10, 20, 30]'), 2); -- 20
SELECT ELEMENT_AT(parse_to_variant('[10, 20, 30]'), -1); -- 30
```
+4. For `VARIANT`, a JSON `null` member of a value computed in a query is returned as a VARIANT `null`, which is not SQL `NULL`. In data read from a table, `null` members of objects outside arrays are not stored, so the same access returns SQL `NULL`. See [NULL semantics](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#null-semantics).
+
## Examples
1. The `ELEMENT_AT` function works the same as `[]`.
@@ -119,7 +121,7 @@ SELECT ELEMENT_AT(parse_to_variant('[10, 20, 30]'), -1); -- 30
+--------------------------------------------------+
```
-5. When accessing a subfield of a `VARIANT`, if the `VARIANT` value is not an OBJECT, an empty value is returned.
+5. When accessing a subfield of a `VARIANT`, if the `VARIANT` value is not an OBJECT, `NULL` is returned.
```SQL
SELECT ELEMENT_AT(PARSE_TO_VARIANT('{"a": 1, "b": 2}'), "a");
@@ -133,6 +135,6 @@ SELECT ELEMENT_AT(parse_to_variant('[10, 20, 30]'), -1); -- 30
+-------------------------------------------+
| ELEMENT_AT(PARSE_TO_VARIANT('123'), "") |
+-------------------------------------------+
- | |
+ | NULL |
+-------------------------------------------+
```
diff --git a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/parse-to-variant.md b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/parse-to-variant.md
index b2c25fc115e..2b6bd4a4c2c 100644
--- a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/parse-to-variant.md
+++ b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/parse-to-variant.md
@@ -8,7 +8,7 @@
## Description
-`PARSE_TO_VARIANT` parses one complete JSON value into `VARIANT`. It accepts JSON objects, arrays, strings, numbers, booleans, and the JSON literal `null`. This function is available in Doris 4.1.4 and later.
+`PARSE_TO_VARIANT` parses one complete JSON value into `VARIANT`. It accepts JSON objects, arrays, strings, numbers, booleans, and the JSON literal `null`. This function is available in Doris 4.1.4 and later; this page describes its behavior in Doris 5.0.0 and later.
## Syntax
@@ -27,8 +27,11 @@ PARSE_TO_VARIANT(<json_value>)
Returns a `VARIANT` value.
- SQL `NULL` input returns SQL `NULL`.
-- The JSON literal `null` returns Variant/JSON `null`, which is different from SQL `NULL`.
-- Invalid JSON, duplicate object keys rejected by the current validation settings, unsupported depth, or another validation error causes the query to fail.
+- The JSON literal `null` returns a VARIANT `null`, which is different from SQL `NULL`.
+- Text that is not valid JSON is returned as a VARIANT string. So is JSON that contains an integer outside [-2^63, 2^64 - 1] or a number outside the `DOUBLE` range: the whole text becomes one string. This is controlled by the BE configuration `variant_throw_exeception_on_invalid_json` (default `false`); when it is `true`, such input makes the query fail.
+- An empty string returns an empty object `{}`.
+- A document nested so deeply that the JSON parser itself rejects it (about 1,000 levels) is invalid JSON and is also returned as a string.
+- The query fails when an object key is longer than `variant_max_json_key_length` bytes (BE configuration, default 255), when an object contains duplicate keys (unless the BE configuration `variant_enable_duplicate_json_path_check` is `true`, which keeps the first value), when nesting is deeper than 128 levels, or when a string is not valid UTF-8.
## Example
@@ -42,11 +45,11 @@ SELECT CAST(
```
```text
-+----------------------------------------+
-| value |
-+----------------------------------------+
-| {"id":42,"tags":["doris","sql"]} |
-+----------------------------------------+
++----------------------------------+
+| value |
++----------------------------------+
+| {"id":42,"tags":["doris","sql"]} |
++----------------------------------+
```
Parse a JSON/JSONB expression:
@@ -97,17 +100,32 @@ SELECT PARSE_TO_VARIANT(NULL) IS NULL AS is_sql_null;
+-------------+
```
-Invalid JSON returns an error:
+Text that is not valid JSON is kept as a string:
```sql
-SELECT PARSE_TO_VARIANT('{"id":');
+SELECT CAST(PARSE_TO_VARIANT('{"id":') AS STRING) AS value,
+ VARIANT_TYPE(PARSE_TO_VARIANT('{"id":')) AS type;
```
```text
-ERROR: Parse json document failed
++--------+--------+
+| value | type |
++--------+--------+
+| {"id": | string |
++--------+--------+
+```
+
+Duplicate keys return an error:
+
+```sql
+SELECT PARSE_TO_VARIANT('{"id": 1, "id": 2}');
+```
+
+```text
+ERROR 1105 (HY000): errCode = 2, detailMessage = [INVALID_ARGUMENT]Parse json document failed at row 0, error: [INVALID_ARGUMENT]Duplicate Variant object key
```
## Usage Notes
-- Use [TRY_PARSE_TO_VARIANT](./try-parse-to-variant) when invalid input should become SQL `NULL` instead of failing the query.
-- `PARSE_TO_VARIANT` explicitly parses JSON. By contrast, `CAST(string AS VARIANT)` preserves the input as a VARIANT string and does not parse JSON; see the [VARIANT CAST rules](../../../basic-element/sql-data-types/semi-structured/VARIANT#cast-rules).
+- Use [TRY_PARSE_TO_VARIANT](./try-parse-to-variant.md) when a parse error should return SQL `NULL` instead of failing the query.
+- `PARSE_TO_VARIANT` explicitly parses JSON. By contrast, `CAST(string AS VARIANT)` and `INSERT` of a string into a VARIANT column keep the input as a VARIANT string and do not parse JSON. To write JSON text with `INSERT`, wrap it in `PARSE_TO_VARIANT`. See [Write data](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#write-data) and [CAST to VARIANT](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#cast-to-variant).
diff --git a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md
index 69b0db29f29..6fd5a86857f 100644
--- a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md
+++ b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md
@@ -2,13 +2,13 @@
{
"title": "TRY_PARSE_TO_VARIANT",
"language": "en",
- "description": "Tries to parse one complete JSON value into VARIANT and returns SQL NULL when parsing or validation fails."
+ "description": "Tries to parse one JSON value into VARIANT. Over-long or duplicate keys return SQL NULL instead of an error; text that is not valid JSON is kept as a string."
}
---
## Description
-`TRY_PARSE_TO_VARIANT` tries to parse one complete JSON value into `VARIANT`. The `TRY_` prefix means that a parsing or validation error returns SQL `NULL` instead of failing the query. This function is available in Doris 4.1.4 and later.
+`TRY_PARSE_TO_VARIANT` tries to parse one complete JSON value into `VARIANT`. The `TRY_` prefix means that a parse error returns SQL `NULL` instead of failing the query. By default, text that is not valid JSON is not a parse error; see Return Value. This function is available in Doris 4.1.4 and later; this page describes its behavior in Doris 5.0.0 and later.
## Syntax
@@ -27,29 +27,31 @@ TRY_PARSE_TO_VARIANT(<json_value>)
Returns a nullable `VARIANT` value.
- Valid input returns the parsed VARIANT value.
-- Invalid JSON or another parsing or validation error returns SQL `NULL`.
+- These parse errors return SQL `NULL`: an object key longer than `variant_max_json_key_length` bytes (BE configuration, default 255), duplicate keys in one object (unless the BE configuration `variant_enable_duplicate_json_path_check` is `true`, which keeps the first value), nesting deeper than 128 levels, and a string that is not valid UTF-8.
+- Text that is not valid JSON, and JSON that contains an integer outside [-2^63, 2^64 - 1] or a number outside the `DOUBLE` range, are returned as a VARIANT string, the same as [PARSE_TO_VARIANT](./parse-to-variant.md). They return SQL `NULL` only when the BE configuration `variant_throw_exeception_on_invalid_json` is `true` (default `false`).
+- An empty string returns an empty object `{}`.
- SQL `NULL` input returns SQL `NULL`.
-- The valid JSON literal `null` returns Variant/JSON `null`, not SQL `NULL`.
+- The JSON literal `null` returns a VARIANT `null`, not SQL `NULL`.
## Example
-Keep valid values and convert invalid JSON to SQL `NULL`:
+Keep valid values and turn parse errors into SQL `NULL`:
```sql
SELECT CAST(
TRY_PARSE_TO_VARIANT('{"id": 1}')
AS STRING
) AS valid_value,
- TRY_PARSE_TO_VARIANT('{"id":') IS NULL AS invalid_is_null,
+ TRY_PARSE_TO_VARIANT('{"id": 1, "id": 2}') IS NULL AS duplicate_is_null,
TRY_PARSE_TO_VARIANT(NULL) IS NULL AS input_is_null;
```
```text
-+-------------+-----------------+---------------+
-| valid_value | invalid_is_null | input_is_null |
-+-------------+-----------------+---------------+
-| {"id":1} | 1 | 1 |
-+-------------+-----------------+---------------+
++-------------+-------------------+---------------+
+| valid_value | duplicate_is_null | input_is_null |
++-------------+-------------------+---------------+
+| {"id":1} | 1 | 1 |
++-------------+-------------------+---------------+
```
Parse JSON/JSONB input:
@@ -69,22 +71,23 @@ SELECT CAST(
+------------+
```
-JSON `null` and SQL `NULL` remain distinct:
+JSON `null` is not SQL `NULL`, and text that is not valid JSON is kept as a string:
```sql
SELECT TRY_PARSE_TO_VARIANT('null') IS NULL AS json_null_is_sql_null,
- TRY_PARSE_TO_VARIANT('{') IS NULL AS error_is_sql_null;
+ VARIANT_TYPE(TRY_PARSE_TO_VARIANT('{"id":')) AS invalid_json_type;
```
```text
+-----------------------+-------------------+
-| json_null_is_sql_null | error_is_sql_null |
+| json_null_is_sql_null | invalid_json_type |
+-----------------------+-------------------+
-| 0 | 1 |
+| 0 | string |
+-----------------------+-------------------+
```
## Usage Notes
-- Use [PARSE_TO_VARIANT](./parse-to-variant) when invalid input should fail the query and expose the data-quality error.
-- This function converts only parsing and validation failures to SQL `NULL`; it does not change the meaning of a valid JSON `null` value.
+- Use [PARSE_TO_VARIANT](./parse-to-variant.md) when a parse error should fail the query and expose the data-quality problem.
+- This function converts only parse errors to SQL `NULL`; it does not change the meaning of a valid JSON `null` value.
+- Load jobs such as Stream Load parse JSON text with the same rules as this function. See [Parse errors](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#parse-errors).
diff --git a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/variant-type.md b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/variant-type.md
index 6697a781cac..d89be183b6a 100644
--- a/docs/sql-manual/sql-functions/scalar-functions/variant-functions/variant-type.md
+++ b/docs/sql-manual/sql-functions/scalar-functions/variant-functions/variant-type.md
@@ -2,14 +2,14 @@
{
"title": "VARIANT_TYPE",
"language": "en",
- "description": "The VARIANTTYPE function returns the actual type of a VARIANT value."
+ "description": "VARIANT_TYPE returns the type name of a VARIANT value, such as object, array, string, bigint, or double, to help debug and analyze VARIANT data."
}
---
## Function
-The `VARIANT_TYPE` function returns the actual type of a `VARIANT` value.
-This function is typically used for debugging or analyzing the structure of `VARIANT` data, assisting in type determination and data processing.
+The `VARIANT_TYPE` function returns the type name of a `VARIANT` value.
+It is typically used for debugging or analyzing the structure of `VARIANT` data, assisting in type determination and data processing. This page describes its behavior in Doris 5.0.0 and later.
## Syntax
@@ -19,18 +19,33 @@ VARIANT_TYPE(variant_value)
## Parameters
-- `variant_value`: A value of type `VARIANT`.
+- `variant_value`: A value of type `VARIANT`. To get the type of a nested value, pass the path, for example `VARIANT_TYPE(v['a'])`.
## Return Value
-- Returns a string representing the actual type of the `VARIANT` value.
- - The string follows the `{"key":"value"}` structure.
- - The key represents the subfield path, and the value represents the type.
+Returns a `STRING` with the type name of the value itself. For an object or an array, it returns `object` or `array`; it does not list the types of the members.
+
+| Result | Value |
+| --- | --- |
+| `object`, `array` | A JSON object or array |
+| `string` | A string |
+| `bool` | `true` or `false` |
+| `tinyint`, `smallint`, `int`, `bigint` | An integer, reported with the smallest type that holds it |
+| `decimal` | A decimal, including an integer too large for `BIGINT` and a `LARGEINT` value of up to 38 digits |
+| `float`, `double` | A floating-point number |
+| `date` | A date |
+| `timestamp` | A timestamp with time zone |
+| `timestamp_ntz` | A timestamp without time zone, such as a `DATETIME` or `TIMESTAMP_NS` value |
+| `null` | A VARIANT `null` (JSON `null`) |
+| `binary`, `time`, `uuid` | Values that neither JSON parsing nor CAST in Doris produces; they can come from VARIANT data written by other systems, such as Parquet files |
+
+SQL `NULL` input returns SQL `NULL`.
## Notes
-1. Used to find the actual type stored in a `VARIANT` column.
-2. For each row in the table, the subfields are read to obtain the type. In practice, use `LIMIT` to restrict the number of rows to avoid slow execution.
+1. Use it to find the actual type of values in a `VARIANT` column. For values read from a table, the result reflects the stored value; for example, a `DATE` in the root value is stored as a string. See [What storage keeps](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#what-storage-keeps).
+2. The result is the type of the value, not the storage type of the path: an integer read from a `BIGINT` path can be `tinyint`. To see the storage type of each subcolumn in a table, use `SET describe_extend_variant_column = true;` and `DESC`.
+3. The function reads every row, so use `LIMIT` to restrict the number of rows in practice.
## Example
@@ -45,13 +60,26 @@ PROPERTIES (
"replication_num" = "1"
);
-INSERT INTO variant_table VALUES(1, '{"a": 10, "b": 1.2, "c" : "ddddd"}'), (2, NULL);
+INSERT INTO variant_table VALUES
+ (1, PARSE_TO_VARIANT('{"a": 10, "b": 1.2, "c": "ddddd"}')),
+ (2, PARSE_TO_VARIANT('[1, 2]')),
+ (3, NULL);
+
+SELECT k,
+ VARIANT_TYPE(v) AS root_type,
+ VARIANT_TYPE(v['a']) AS a_type,
+ VARIANT_TYPE(v['b']) AS b_type,
+ VARIANT_TYPE(v['c']) AS c_type
+FROM variant_table
+ORDER BY k;
+```
-SELECT VARIANT_TYPE(v) FROM variant_table;
-+-------------------------------------------+
-| VARIANT_TYPE(v) |
-+-------------------------------------------+
-| {"a":"tinyint","b":"double","c":"string"} |
-| NULL |
-+-------------------------------------------+
+```text
++------+-----------+---------+--------+--------+
+| k | root_type | a_type | b_type | c_type |
++------+-----------+---------+--------+--------+
+| 1 | object | tinyint | double | string |
+| 2 | array | NULL | NULL | NULL |
+| 3 | NULL | NULL | NULL | NULL |
++------+-----------+---------+--------+--------+
```
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/import/complex-types/variant.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/import/complex-types/variant.md
index 6cb784a942c..089701d93a3 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/import/complex-types/variant.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/import/complex-types/variant.md
@@ -2,7 +2,7 @@
{
"title": "VARIANT",
"language": "zh-CN",
- "description": "如何将 CSV 与 JSON 数据导入 Doris VARIANT 列？提供建表、Stream Load 命令、类型推导验证完整步骤。",
+ "description": "如何将 CSV 与 JSON 数据导入 Doris VARIANT 列？本文按步骤介绍建表、执行 Stream Load 命令，以及验证 VARIANT 子列类型推导结果的完整流程。",
"keywords": [
"Doris VARIANT 导入",
"CSV 导入 VARIANT",
@@ -28,18 +28,53 @@
| 你的需求 | 建议阅读 |
| --- | --- |
| 快速完成 CSV / JSON 导入 | 继续阅读本文 |
-| 选择默认模式、Sparse、DOC mode 或 Schema Template | [VARIANT 使用与配置指南](../../../sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide) |
-| 查询 VARIANT 语法、索引、限制或配置参考 | [VARIANT](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT) |
+| 选择默认模式、Sparse、DOC mode 或 Schema Template | [VARIANT 使用与配置指南](../../../sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md) |
+| 查询 VARIANT 语法、索引、限制或配置参考 | [VARIANT](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md) |
## 使用限制
-- 当前仅支持 **CSV** 和 **JSON** 两种数据格式导入 `VARIANT` 列。
+- 下文示例导入的是 **CSV** 和 **JSON** 文件。导入作业会把写入 `VARIANT` 列的任何字符串字段按 JSON 解析，因此只要源列是字符串（例如 Parquet 的 `STRING` 列），其他格式也同样适用。Arrow 格式不能导入 `VARIANT` 列。
+
+## 导入时值如何处理
+
+- **`NOT NULL` 列：** VARIANT 值为 SQL `NULL` 的行会被过滤，包括解析失败而得到 SQL `NULL` 的值。被过滤的行计入 `max_filter_ratio`（默认 `0`），因此默认情况下导入作业会失败。
+- **INSERT 的行为不同：** `INSERT` 不解析字符串。`INSERT INTO t VALUES (1, '{"a": 1}')` 写入的是 VARIANT 字符串 `{"a": 1}`，从 `s3()`、`hdfs()`、`local()` 执行 `INSERT INTO ... SELECT` 也是如此。需要写入对象时请使用 `PARSE_TO_VARIANT`。
+
+写入表时值会被规范化：数组之外、值为 `null`、`{}` 或 `[]` 的对象成员会被移除。同一个值在查询中计算时保留所有成员：
+
+```sql
+CREATE TABLE variant_norm (k INT, v VARIANT)
+DUPLICATE KEY(k)
+DISTRIBUTED BY HASH(k) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
+
+INSERT INTO variant_norm VALUES (1, PARSE_TO_VARIANT('{"a": null, "b": {}, "c": [], "d": 1}'));
+
+SELECT PARSE_TO_VARIANT('{"a": null, "b": {}, "c": [], "d": 1}') AS computed;
+SELECT v AS stored, v['a'] IS NULL AS a_is_null FROM variant_norm;
+```
+
+```text
++--------------------------------+
+| computed |
++--------------------------------+
+| {"a":null,"b":{},"c":[],"d":1} |
++--------------------------------+
+
++---------+-----------+
+| stored | a_is_null |
++---------+-----------+
+| {"d":1} | 1 |
++---------+-----------+
+```
+
+解析错误和其他规范化规则请参阅[解析错误](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md#parse-errors)与[写入后值的规范化](../../../sql-manual/basic-element/sql-data-types/semi-structured/VARIANT.md#what-storage-keeps)。
## 存储格式建议（V3）
<!-- 知识类型: 架构选型决策 -->
-对于新建的 `VARIANT` 表，尤其是宽 JSON 场景，建议直接使用 **Storage Format V3**，除非你有明确的理由使用其他格式。设计原因详见 [Storage Format V3](../../../table-design/storage-format)。
+对于新建的 `VARIANT` 表，尤其是宽 JSON 场景，建议直接使用 **Storage Format V3**，除非你有明确的理由使用其他格式。设计原因详见 [Storage Format V3](../../../table-design/storage-format.md)。
建表时通过 `PROPERTIES` 显式开启：
@@ -218,53 +253,64 @@ created_at: 2020-11-14 02:00:00
默认 `DESC` 输出仅展示顶层 VARIANT 列，不展开内部子列：
-``` sql
+```sql
mysql> desc test_variant;
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-| Field | Type | Null | Key | Default | Extra |
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-| id | BIGINT | No | true | NULL | |
-| type | VARCHAR(*) | Yes | false | NULL | NONE |
-| actor | VARIANT | Yes | false | NULL | NONE |
-| created_at | DATETIME | Yes | false | NULL | NONE |
-| payload | VARIANT | Yes | false | NULL | NONE |
-| public | BOOLEAN | Yes | false | NULL | NONE |
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-6 rows in set (0.07 sec)
++------------+---------------------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++------------+---------------------------+------+-------+---------+-------+
+| id | bigint | No | true | NULL | |
+| type | varchar(30) | Yes | false | NULL | NONE |
+| actor | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| repo | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| payload | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| public | boolean | Yes | false | NULL | NONE |
+| created_at | datetime | Yes | false | NULL | NONE |
++------------+---------------------------+------+-------+---------+-------+
+7 rows in set
```
+上面把列出所有列属性的 VARIANT 类型字符串简写为 `variant<PROPERTIES (...)>`。
+
开启 `describe_extend_variant_column` 后，可以查看 VARIANT 推导出的子列类型：
-``` sql
+```sql
mysql> set describe_extend_variant_column = true;
-Query OK, 0 rows affected (0.01 sec)
+Query OK, 0 rows affected (0.00 sec)
mysql> desc test_variant;
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-| Field | Type | Null | Key | Default | Extra |
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-| id | BIGINT | No | true | NULL | |
-| type | VARCHAR(*) | Yes | false | NULL | NONE |
-| actor | VARIANT | Yes | false | NULL | NONE |
-| actor.avatar_url | TEXT | Yes | false | NULL | NONE |
-| actor.display_login | TEXT | Yes | false | NULL | NONE |
-| actor.id | INT | Yes | false | NULL | NONE |
-| actor.login | TEXT | Yes | false | NULL | NONE |
-| actor.url | TEXT | Yes | false | NULL | NONE |
-| created_at | DATETIME | Yes | false | NULL | NONE |
-| payload | VARIANT | Yes | false | NULL | NONE |
-| payload.action | TEXT | Yes | false | NULL | NONE |
-| payload.before | TEXT | Yes | false | NULL | NONE |
-| payload.comment.author_association | TEXT | Yes | false | NULL | NONE |
-| payload.comment.body | TEXT | Yes | false | NULL | NONE |
-....
-+------------------------------------------------------------+------------+------+-------+---------+-------+
-406 rows in set (0.07 sec)
++-----------------------+---------------------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-----------------------+---------------------------+------+-------+---------+-------+
+| id | bigint | No | true | NULL | |
+| type | varchar(30) | Yes | false | NULL | NONE |
+| actor | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| repo | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| payload | variant<PROPERTIES (...)> | Yes | false | NULL | NONE |
+| public | boolean | Yes | false | NULL | NONE |
+| created_at | datetime | Yes | false | NULL | NONE |
+| actor.avatar_url | text | Yes | false | NULL | NONE |
+| actor.display_login | text | Yes | false | NULL | NONE |
+| actor.gravatar_id | text | Yes | false | NULL | NONE |
+| actor.id | bigint | Yes | false | NULL | NONE |
+| actor.login | text | Yes | false | NULL | NONE |
+| actor.url | text | Yes | false | NULL | NONE |
+| payload.before | text | Yes | false | NULL | NONE |
+| payload.commits | array<text> | Yes | false | NULL | NONE |
+| payload.distinct_size | bigint | Yes | false | NULL | NONE |
+| payload.head | text | Yes | false | NULL | NONE |
+| payload.push_id | bigint | Yes | false | NULL | NONE |
+| payload.ref | text | Yes | false | NULL | NONE |
+| payload.size | bigint | Yes | false | NULL | NONE |
+| repo.id | bigint | Yes | false | NULL | NONE |
+| repo.name | text | Yes | false | NULL | NONE |
+| repo.url | text | Yes | false | NULL | NONE |
++-----------------------+---------------------------+------+-------+---------+-------+
+23 rows in set
```
也可按 Partition 维度展示推导结果：
-``` sql
+```sql
DESCRIBE ${table_name} PARTITION ($partition_name);
```
@@ -272,7 +318,7 @@ DESCRIBE ${table_name} PARTITION ($partition_name);
### Q1：VARIANT 支持哪些导入数据格式？
-当前 `VARIANT` 列的导入仅支持 **CSV** 与 **JSON** 两种格式。其他格式需先转换后再导入。
+导入作业会把写入 `VARIANT` 列的任何字符串字段按 JSON 解析，与文件格式无关；本文示例使用 **CSV** 与 **JSON**。使用 `INSERT`（包括从表函数执行 `INSERT INTO ... SELECT`）时，请用 `PARSE_TO_VARIANT` 包裹字符串值。
### Q2：什么场景必须使用 Storage Format V3？
@@ -294,7 +340,7 @@ SET describe_extend_variant_column = true;
| 格式 | 关键 Header |
| --- | --- |
-| CSV | `-H "column_separator:|"` |
+| CSV | `-H "column_separator:\|"` |
| JSON | `-H "format:json"` |
### Q5：如何确认 Stream Load 是否导入成功？
@@ -303,3 +349,12 @@ SET describe_extend_variant_column = true;
- `Status` 为 `Success` 表示导入成功；
- `NumberLoadedRows` 应等于 `NumberTotalRows`，且 `NumberFilteredRows` 为 `0`。
+
+### Q6：为什么 `INSERT` 把 JSON 文本存成了字符串？
+
+`INSERT` 通过 `CAST(string AS VARIANT)` 把字符串转换为 VARIANT，该转换保留字符串本身，不做解析。Stream Load 等导入作业会解析 JSON 文本；使用 `INSERT` 时，请用 `PARSE_TO_VARIANT` 包裹文本：
+
+```sql
+INSERT INTO testdb.test_variant (id, actor)
+VALUES (1, PARSE_TO_VARIANT('{"id": 282080, "login": "brianchandotcom"}'));
+```
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 e0b697d548a..166b270385f 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
@@ -2,15 +2,13 @@
{
"title": "VARIANT",
"language": "zh-CN",
- "description": "VARIANT 用于存储半结构化 JSON 数据，并支持带类型的路径访问、CAST、基于 Hash 的分组与去重，以及部分 SQL 操作。"
+ "description": "VARIANT 用于存储半结构化 JSON 数据。本页是 VARIANT 的参考文档，涵盖写入与解析、CAST、NULL 语义、比较与排序、Schema Template、ALTER TABLE、属性、索引和限制。"
}
---
-## VARIANT
+## 描述 {#overview}
-## 描述
-
-VARIANT 类型用于存储半结构化 JSON 数据，可包含不同基础类型（整数、字符串、布尔等）以及一层数组与嵌套对象。写入时会自动基于 JSON Path 推断子列结构与类型，并对高频路径执行子列列式提取（Subcolumnization），使其以独立子列的形式参与分析，兼顾灵活性与性能。
+VARIANT 类型用于存储半结构化 JSON 数据，可以包含对象、数组、字符串、数值、布尔值和 `null`。写入时，Doris 会推断每个 JSON 路径的类型，并对高频路径执行子列列式提取（Subcolumnization），把它们存成独立的列式子列。查询这些路径时只读取所需的子列。
:::tip 为什么使用 VARIANT
如果文档结构会持续变化，但查询仍集中在少数热点路径上，`VARIANT` 的优势主要体现在三点：
@@ -19,269 +17,418 @@ VARIANT 类型用于存储半结构化 JSON 数据，可包含不同基础类型
- 关键路径可以建立路径级索引，支持全文检索，同时继续受益于 Doris 的稀疏索引裁剪能力。
- 面向宽列场景的存储优化，让万级子列规模的自动子列列式提取（Subcolumnization）保持可用。若参与子列列式提取（Subcolumnization）的路径接近 10000，对硬件要求会明显提高，通常应优先评估 DOC mode。
-如果你还在决定默认模式、Sparse、DOC mode 还是 Schema Template，建议先阅读 [VARIANT 使用与配置指南](./variant-workload-guide)。本页主要提供语法、类型规则、索引、限制和配置参考。
+如果你还在决定默认模式、Sparse、DOC mode 还是 Schema Template，建议先阅读 [VARIANT 使用与配置指南](./variant-workload-guide.md)。本页提供写入与解析、类型规则、CAST、NULL 与比较语义、ALTER、索引、限制和配置的参考。
:::
-## 使用 VARIANT 类型
+:::info 版本说明
+本页描述 Doris 5.0.0 及之后版本中的 VARIANT。与 Doris 4.x 相比，最容易影响已有 SQL 的差异有：
+
+- `INSERT` 会把字符串作为 VARIANT 字符串写入，不再按 JSON 解析，从 `s3()`、`hdfs()` 等表函数执行 `INSERT INTO ... SELECT` 也是如此。用 `INSERT` 写入 JSON 文本时请使用 `PARSE_TO_VARIANT`；Stream Load 等导入作业仍会解析 JSON。
+- 整个 VARIANT 值支持 `GROUP BY`、`DISTINCT` 和集合运算；两个 VARIANT 值之间可以用 `=`、`!=`、`<=>` 比较，也可以作为 Join 键、`ORDER BY` 键和窗口键。
+- `VARIANT_TYPE` 返回单个类型名称（如 `object`），不再返回从路径到类型的映射。
+- VARIANT 数组可以用整数下标访问，下标从 1 开始。
-### 建表语法
+Doris 4.x 的行为请参阅本页的 4.x 版本。
+:::
-建表时将列类型声明为 VARIANT：
+## 快速上手 {#quick-start}
```sql
-CREATE TABLE IF NOT EXISTS ${table_name} (
- k BIGINT,
- v VARIANT
+CREATE TABLE events (
+ id BIGINT,
+ v VARIANT
)
-PROPERTIES("replication_num" = "1");
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
+
+-- INSERT 会把字符串字面量保留为 VARIANT 字符串，因此 JSON 文本需要显式解析。
+INSERT INTO events VALUES
+ (1, PARSE_TO_VARIANT('{"user": {"id": 42, "name": "alice"}, "tags": ["doris", "sql"], "score": 9.5}')),
+ (2, PARSE_TO_VARIANT('{"user": {"id": 7, "name": "bob"}, "score": 3}'));
+
+SELECT id,
+ CAST(v['user']['name'] AS STRING) AS name,
+ v['tags'][1] AS first_tag
+FROM events
+WHERE CAST(v['score'] AS DOUBLE) > 5;
+```
+
+```text
++------+-------+-----------+
+| id | name | first_tag |
++------+-------+-----------+
+| 1 | alice | doris |
++------+-------+-----------+
+```
+
+- `v['user']['name']` 和 `v['tags'][1]` 返回 `VARIANT` 值。数组下标从 1 开始。
+- 对路径做比较或计算之前，先把它 CAST 为具体类型。
+- Stream Load 等导入作业会自动解析 JSON 文本。参见[写入数据](#write-data)。
+
+## 定义 VARIANT 列 {#define-a-variant-column}
+
+```sql
+column_name VARIANT
+column_name VARIANT< field_definition [, field_definition ...] >
+column_name VARIANT< properties('key' = 'value' [, ...]) >
+column_name VARIANT< field_definition [, ...], properties('key' = 'value' [, ...]) >
+
+field_definition:
+ [MATCH_NAME | MATCH_NAME_GLOB] 'path_or_pattern' : data_type [COMMENT 'comment']
```
-通过 Schema Template 约束部分 Path 的类型（更多见“扩展类型”）：
+- `field_definition` 列表就是 [Schema Template](#schema-template)，用于固定部分路径的存储类型。
+- `properties(...)` 用于设置列级存储属性，参见[列属性](#column-properties)。
+- VARIANT 列可以是 `NULL` 或 `NOT NULL`，默认值只能是 `NULL`。
```sql
-CREATE TABLE IF NOT EXISTS ${table_name} (
+CREATE TABLE IF NOT EXISTS example_tbl (
k BIGINT,
- v VARIANT <
- 'id' : INT, -- path 为 id 的子列被限制为 INT 类型
- 'message*' : STRING, -- 前缀匹配 message* 的子列被限制为 STRING 类型
- 'tags*' : ARRAY<TEXT> -- 前缀匹配 tags* 的子列被限制为 ARRAY<TEXT> 类型
- >
+ v VARIANT<
+ 'id' : INT, -- 路径 id 以 INT 存储
+ 'message*' : STRING, -- 匹配 message* 的路径以 STRING 存储
+ 'tags*' : ARRAY<TEXT>, -- 匹配 tags* 的路径以 ARRAY<TEXT> 存储
+ properties('variant_max_subcolumns_count' = '2048')
+ > NULL
)
-PROPERTIES("replication_num" = "1");
+DUPLICATE KEY(k)
+DISTRIBUTED BY HASH(k) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
```
-### 查询语法
+VARIANT 列在表中的使用范围：
-```sql
--- 访问嵌套字段（返回类型为 VARIANT，需要显式或隐式 CAST 才能聚合/比较）
-SELECT v['properties']['title'] FROM ${table_name};
+| 用法 | 是否支持 | 说明 |
+| --- | --- | --- |
+| Duplicate Key、Unique Key、Aggregate Key 表的 Value 列 | 支持 | 在 Aggregate Key 表中，聚合类型必须是 `REPLACE` 或 `REPLACE_IF_NOT_NULL`。 |
+| Key 列、分区列、分桶列 | 不支持 | |
+| 在表结构中嵌套在其他类型内（`ARRAY<VARIANT>`、`MAP`、`STRUCT`） | 不支持 | 查询结果仍可以是 `ARRAY<VARIANT>`，例如 `COLLECT_LIST(v)` 的结果。 |
+| 默认值 | 只能是 `NULL` | `DEFAULT '{}'` 等非 NULL 默认值会被拒绝。 |
--- 聚合前显式 CAST 为确定类型
-SELECT CAST(v['properties']['title'] AS STRING) AS title
-FROM ${table_name}
-GROUP BY title;
+## 写入数据 {#write-data}
--- 数组查询示例
-SELECT *
-FROM ${table_name}
-WHERE ARRAY_CONTAINS(CAST(v['tags'] AS ARRAY<TEXT>), 'Doris');
-```
+`INSERT` 和 `CAST` 会把字符串保留为 VARIANT 字符串；`PARSE_TO_VARIANT`、`TRY_PARSE_TO_VARIANT` 以及 Stream Load 等导入作业会解析 JSON 文本。导入示例请参阅[导入 VARIANT 数据](../../../../data-operate/import/complex-types/variant.md)。
-## 创建和访问值
+### 解析错误 {#parse-errors}
-:::info 版本说明
-本节所述行为适用于 Doris 4.2 及后续版本。
-:::
+[PARSE_TO_VARIANT](../../../sql-functions/scalar-functions/variant-functions/parse-to-variant.md)、[TRY_PARSE_TO_VARIANT](../../../sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md) 和导入作业使用同一个 JSON 解析器。导入作业处理错误的方式与 `TRY_PARSE_TO_VARIANT` 相同：
+
+| 输入 | `PARSE_TO_VARIANT` | `TRY_PARSE_TO_VARIANT` 与导入作业 |
+| --- | --- | --- |
+| 合法 JSON | 解析后的值 | 解析后的值 |
+| 非法 JSON 文本，如 `hello` 或 `{"id":` | 保留为 VARIANT 字符串 | 保留为 VARIANT 字符串 |
+| 包含超出 [-2^63, 2^64 - 1] 的整数，或超出 `DOUBLE` 范围的数值的 JSON，如 `{"a": 1, "n": 100000000000000000000}` | 整段文本保留为 VARIANT 字符串，因此 `v['a']` 返回 `NULL` | 整段文本保留为 VARIANT 字符串 |
+| 空字符串 | 空对象 `{}` | 空对象 `{}` |
+| 嵌套超过 128 层 | 报错 | SQL `NULL` |
+| 对象 key 超过 `variant_max_json_key_length` 字节（BE 配置，默认 255） | 报错 | SQL `NULL` |
+| 同一对象中有重复 key | 报错 | SQL `NULL` |
+| 不是合法 UTF-8 的字符串 | 报错 | SQL `NULL` |
-VARIANT 值可以从 JSON 文本、JSON/JSONB 值或带确定类型的 SQL 表达式创建：
+嵌套深到 JSON 解析器本身拒绝的文档（约 1000 层）属于非法 JSON，因此会保留为字符串。
-- 如果要将字符串或 JSON/JSONB 表达式解析为结构化 VARIANT 值，请使用 [PARSE_TO_VARIANT](../../../sql-functions/scalar-functions/variant-functions/parse-to-variant)。
-- 如果要将受支持的 SQL 值转换为 VARIANT，请使用 `CAST(expression AS VARIANT)`。字符串会保留为 VARIANT 字符串值，该 CAST 不解析 JSON。
+有两个 BE 配置会改变上述规则：
-### 解析 JSON 文本
+- `variant_throw_exeception_on_invalid_json`（默认 `false`）：设置为 `true` 后，解析器无法接受的文本（包括含超范围数值的 JSON）不再保留为 VARIANT 字符串，而是对 `PARSE_TO_VARIANT` 报错，对 `TRY_PARSE_TO_VARIANT` 和导入作业返回 SQL `NULL`。
+- `variant_enable_duplicate_json_path_check`（默认 `false`）：设置为 `true` 后，对象中重复的 key 保留第一个值，不再报错。含 `.` 的 key 与嵌套路径重复时，写入也不再失败，只会存储其中一个值。
+
+如需让含超范围整数的文档保持结构，可以先转换为 JSON：`CAST(CAST(text AS JSON) AS VARIANT)`。不超过 38 位的整数保持为精确的数值，更大但仍在 `LARGEINT` 范围内的整数变为字符串，再大的整数变为 `DOUBLE`。`PARSE_TO_VARIANT(CAST(text AS JSON))` 无法解决这个问题，因为它会重新解析 JSON 文本。
```sql
-SELECT PARSE_TO_VARIANT('{"user": {"id": 42}, "active": true}');
-SELECT PARSE_TO_VARIANT('[10, 20, 30]');
-SELECT PARSE_TO_VARIANT(CAST('{"user": {"id": 42}}' AS JSON));
+SELECT VARIANT_TYPE(PARSE_TO_VARIANT('{"id": 1}')) AS valid_json, -- object
+ VARIANT_TYPE(PARSE_TO_VARIANT('{"id":')) AS invalid_json, -- string
+ VARIANT_TYPE(CAST('{"id": 1}' AS VARIANT)) AS string_cast; -- string
```
-如果非法 JSON 应该返回 SQL `NULL` 而不是使查询失败，请使用 [TRY_PARSE_TO_VARIANT](../../../sql-functions/scalar-functions/variant-functions/try-parse-to-variant)。
+### 写入后值的规范化 {#what-storage-keeps}
+
+值写入表时会被规范化，读回的值可能与写入前计算出的值不同：
-### 访问对象和数组
+| 写入前 | 从表中读回 |
+| --- | --- |
+| 值为 JSON `null` 的对象成员，如 `{"a": null, "b": 1}` | 该成员被移除：`{"b":1}`；`v['a']` 返回 SQL `NULL`。 |
+| 值为空对象或空数组的对象成员，以及按这些规则变空的对象，如 `{"a": {}, "b": [], "c": {"d": null}}` | 被移除：`{}` |
+| 值为只含 `null` 元素的数组的对象成员，如 `{"p": [null, null], "q": 1}` | 可能被移除：`{"q":1}` |
+| 数组内的值，如 `{"arr": [{"a": null}, {}, [], null]}` | 原样保留；`v['arr'][1]['a']` 是 VARIANT `null`。 |
+| 根值为 JSON `null`，如 `PARSE_TO_VARIANT('null')` | 空对象 `{}` |
+| 根值为空数组 `[]` 或空对象 `{}` | 保留 |
+| 根值中的 `DATE`、`DATETIME` 值（如 `CAST(date_col AS VARIANT)`），以及所在路径上还有其他类型值的日期 | 以文本形式存储，读回时是字符串。只存日期的对象路径会保留日期类型。 |
+| 同一路径上混有布尔值和数值 | 布尔值可能读回为 `1` 或 `0`，取决于同一次写入中值的先后顺序以及 Compaction |
+| 含 `.` 的 key，如 `{"a.b": 1}` | 按嵌套路径存储：`{"a":{"b":1}}`，`v['a.b']` 和 `v['a']['b']` 都返回 `1`。如果一个文档同时包含 key `a.b` 和 `a` 下的 key `b`，整条 INSERT 或整个导入作业都会失败，除非 `variant_enable_duplicate_json_path_check` 为 `true`。 |
+| 对象 key | 按字节序返回 |
-对象字段可以使用字符串 key 访问。在 Doris 4.2 及后续版本中，VARIANT 数组的正数索引从 1 开始，负数索引从数组末尾倒数。提取出的值仍是 `VARIANT`，如需按确定类型比较、计算或聚合，请先 CAST。
+这些规则会受同一次写入的其他行以及 DOC mode 影响，因此不要依赖 `null` 值或空容器在写入后仍然保留。在 Schema Template 中声明过的路径会转换为声明的类型，参见 [Schema Template](#schema-template)。
+
+## 类型推断与类型冲突 {#type-inference-and-type-conflicts}
+
+没有 Schema Template 时，Doris 在解析 JSON 时推断每个值的类型，`VARIANT_TYPE` 返回的就是这个类型：
+
+| JSON 值 | 类型 |
+| --- | --- |
+| `BIGINT` 范围内的整数 | `tinyint`、`smallint`、`int` 或 `bigint` 中能容纳该值的最小类型 |
+| 超出 `BIGINT` 范围、不超过 18446744073709551615 的整数 | `decimal` |
+| 带小数部分或指数的数值 | `double` |
+| 超出 [-2^63, 2^64 - 1] 的整数，或超出 `DOUBLE` 范围的数值 | 不作为数值处理：整段文本都是非法 JSON（参见[解析错误](#parse-errors)） |
+| 字符串 | `string` |
+| `true`、`false` | `bool` |
+| `null` | `null` |
+| 数组、对象 | `array`、`object` |
+
+数据存储时，每个路径只有一种存储类型：
+
+- 整数存为 `BIGINT`，需要时存为 `LARGEINT`；浮点数存为 `DOUBLE`，定点数存为 `DECIMAL`，字符串存为 `STRING`，布尔值存为 `BOOLEAN`，由标量组成的数组存为 `ARRAY<T>`。
+- 对象数组、嵌套数组，以及值的类型不一致的路径（例如整数与浮点数、数值与字符串、标量与数组），存为 `JSONB`（`DESC` 中显示为 `json`）。元素类型冲突的数组存为 `ARRAY<JSONB>`。
```sql
-SELECT CAST(PARSE_TO_VARIANT('{"user": {"id": 42}}')['user']['id'] AS BIGINT);
-SELECT ELEMENT_AT(PARSE_TO_VARIANT('[10, 20, 30]'), 1); -- 10
-SELECT ELEMENT_AT(PARSE_TO_VARIANT('[10, 20, 30]'), -1); -- 30
+{"a" : 12345678}
+{"a" : "HelloWorld"}
+-- a 存为 JSONB
```
-对象和数组访问的详细说明请参见 [ELEMENT_AT](../../../sql-functions/scalar-functions/variant-functions/element-at)。
-
-## CAST 规则
+`JSONB` 路径保留所有值，但失去类型化存储的能力：索引和基于类型的裁剪都不再作用于该路径。布尔值是一个特例：如果路径上的第一个值是布尔值、后续的值是数值，该路径可能按数值存储，布尔值读回为 `1` 或 `0`。需要稳定类型的路径，请在 Schema Template 中声明。
-VARIANT 的 CAST 包括两个方向：把受支持的 SQL 值转换为 VARIANT，以及把 VARIANT 中兼容的值转换为具体 SQL 类型。
+`VARIANT_TYPE` 报告的是每个值的类型，因此从 `BIGINT` 路径读出的整数仍可能是 `tinyint`。要查看每个路径的存储类型，请执行 `SET describe_extend_variant_column = true;` 后再执行 `DESC table_name;`，参见[查看子列与类型](#inspect-subcolumns-and-types)。
-### 其他类型 CAST 为 VARIANT
+## 访问路径与输出 {#access-paths-and-output}
-| 源类型 | 行为 |
-| --- | --- |
-| `CHAR`、`VARCHAR`、`STRING` | 将输入保留为 VARIANT 字符串，不解析看起来像 JSON 的文本。 |
-| `BOOLEAN` | 保留 Boolean 值。 |
-| `TINYINT`、`SMALLINT`、`INT`、`BIGINT`、`LARGEINT` | 保留整数值。 |
-| `FLOAT`、`DOUBLE` | 保留浮点数值。 |
-| `DECIMALV2`、`DECIMAL(p, s)`（`p <= 38`） | 保留 Decimal 值，但需满足下文限制。 |
-| `DATE`、`DATETIME`、`TIMESTAMP_NS`、`TIMESTAMPTZ` | 保留对应的逻辑类型和值。 |
-| `IPV4`、`IPV6` | 保留 IP 地址值。 |
-| `JSON` / `JSONB` | 将结构化值直接转换为 VARIANT；如果输入包含 VARIANT 无法表示的 JSONB 值类型，BE 会报错。 |
-| `ARRAY<T>` | 当 `T` 为 `VARIANT` 或也在该白名单中时递归转换每个元素，并保留 SQL NULL 元素。 |
-
-仅支持上表列出的源类型。其他源类型，包括 `MAP`、`STRUCT`、`TIME`、precision 超过 38 的 Decimal，以及包含不支持元素类型的数组，都会由 BE 报错。
+- `v['key']` 和 `v['a']['b']` 读取对象成员；`v['arr'][1]` 读取数组元素，下标从 1 开始，`-1` 表示最后一个元素。[ELEMENT_AT](../../../sql-functions/scalar-functions/variant-functions/element-at.md) 与之等价。
+- 结果是 `VARIANT` 值。key 不存在、下标为 `0` 或越界、对数组使用字符串 key、对对象使用整数下标、对标量值使用 key，都返回 SQL `NULL`。
+- 在计算出的值中，含 `.` 的 key 是一个整体：`v['a.b']` 读取 key `a.b`，而 `v['a']['b']` 读取 `a` 下的 `b`。存储不保留这种区分，参见[写入后值的规范化](#what-storage-keeps)。
+- 路径不会自动展开数组。对于 `{"a": [{"b": 1}]}`，`v['a']['b']` 返回 `NULL`，应写作 `v['a'][1]['b']`。
```sql
--- 字符串会保留为 VARIANT 字符串根值，即使内容看起来像 JSON。
-SELECT CAST(CAST('{"id": 1}' AS VARIANT) AS STRING) AS string_value,
- VARIANT_TYPE(CAST('{"id": 1}' AS VARIANT)) AS root_type;
--- string_value：{"id": 1}；root_type：string
+SELECT v['user']['id'] AS id, -- 42
+ v['tags'][-1] AS last_tag, -- sql
+ v['user']['missing'] AS missing -- NULL
+FROM events
+WHERE id = 1;
+```
--- 需要结构化 VARIANT 值时，显式解析 JSON 文本。
-SELECT PARSE_TO_VARIANT('{"id": 1}') AS parsed_object;
--- {"id":1}
+常见写法是把路径 CAST 为查询需要的类型：
--- JSON/JSONB 输入按结构转换。
-SELECT CAST(CAST('{"id": 1}' AS JSON) AS VARIANT) AS parsed_object;
--- {"id":1}
+```sql
+SELECT * FROM tbl WHERE ARRAY_CONTAINS(CAST(v['tags'] AS ARRAY<TEXT>), 'Doris');
+SELECT * FROM tbl WHERE CAST(v['date'] AS DATE) = '2021-01-02';
+SELECT * FROM tbl WHERE v['bool']; -- 隐式 CAST 为 BOOLEAN
+SELECT * FROM tbl WHERE v['str'] MATCH 'Doris'; -- 使用该路径上的倒排索引
```
-字符串 CAST 不解析 JSON，因此非法 JSON 文本仍是合法的 VARIANT 字符串。如需严格解析 JSON，请使用 `PARSE_TO_VARIANT`；如需在解析失败时返回 SQL `NULL`，请使用 `TRY_PARSE_TO_VARIANT`。
+读取整个 VARIANT 值会返回 JSON 文本。对象 key 按字节序输出，且不含空白，因此与输入文本并非按字节完全一致。字符串根值输出时不带引号。
-### VARIANT CAST 为其他类型
+```sql
+CREATE TABLE variant_tbl (k INT, v VARIANT)
+DUPLICATE KEY(k)
+DISTRIBUTED BY HASH(k) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
-VARIANT 可以 CAST 为兼容的标量、JSON/JSONB 或数组类型：
+INSERT INTO variant_tbl VALUES (1, PARSE_TO_VARIANT('{ "b": 2, "a": 1, "c": { "y": 20, "x": 10 } }'));
-| 目标类型 | 行为 |
-| --- | --- |
-| `BOOLEAN` | 转换兼容的 Boolean 或标量根值。 |
-| `TINYINT`、`SMALLINT`、`INT`、`BIGINT`、`LARGEINT` | 将兼容的标量根值转换为指定整数类型。 |
-| `FLOAT`、`DOUBLE` | 转换兼容的数值根。 |
-| `DECIMALV2`、`DECIMAL(p, s)` | 将兼容的数值根转换为指定 Decimal 类型。 |
-| `DATE`、`DATETIME`、`TIMESTAMP_NS`、`TIMESTAMPTZ` | 转换兼容的日期时间根值。 |
-| `CHAR`、`VARCHAR`、`STRING` | 标量根值返回对应文本，对象和数组返回 JSON 文本。Variant/JSON `null` 返回字符串 `null`，外层 SQL `NULL` 仍是 SQL `NULL`。 |
-| `IPV4`、`IPV6` | 将兼容的 IP 地址根值转换为指定的 IP 地址类型。 |
-| `JSON` / `JSONB` | 按结构转换；如果 VARIANT 值包含 JSON/JSONB 无法表示的类型，BE 会报错。 |
-| `ARRAY<T>` | 当 `T` 为 `VARIANT` 或也在该白名单中时逐元素转换；不兼容元素遵循目标类型的 CAST 规则。 |
-
-仅支持上表列出的目标类型。其他目标类型，包括 `MAP`、`STRUCT` 和 `TIME`，都会由 BE 报错。对于受支持的目标类型，值形状不兼容、文本非法或数值越界时，按照对应 CAST 模式报错或返回 SQL `NULL`。
+SELECT v FROM variant_tbl WHERE k = 1;
+-- {"a":1,"b":2,"c":{"x":10,"y":20}}
+```
-```sql
-SELECT CAST(PARSE_TO_VARIANT('42') AS BIGINT) AS id;
--- 42
+## CAST {#cast}
-SELECT CAST(PARSE_TO_VARIANT('[1, null, 3]') AS ARRAY<INT>) AS values;
--- [1, NULL, 3]
+### 其他类型 CAST 为 VARIANT {#cast-to-variant}
+
+| 源类型 | 结果 |
+| --- | --- |
+| `CHAR`、`VARCHAR`、`STRING` | VARIANT 字符串，不解析 JSON 文本。字符串必须是合法的 UTF-8，否则 CAST 报错。 |
+| `BOOLEAN` | 布尔值。 |
+| `TINYINT`、`SMALLINT`、`INT`、`BIGINT` | 整数。 |
+| `LARGEINT` | 定点数。绝对值超过 10^38 - 1 的值会变成字符串。 |
+| `FLOAT`、`DOUBLE` | 浮点数。 |
+| `DECIMALV2`、`DECIMAL(p, s)`（`p <= 38`） | 定点数。 |
+| `DATE`、`DATETIME(p)`、`TIMESTAMP_NS` | 日期，或不带时区的时间戳。 |
+| `IPV4`、`IPV6` | 字符串，内容为该值的文本形式。 |
+| `JSON` / `JSONB` | 保持原结构。包含 VARIANT 无法表示的值（如 `DECIMAL256` 数值），或对象中有重复 key 时，CAST 报错。 |
+| `ARRAY<T>` | 数组，逐个转换元素。`T` 必须是 `VARIANT` 或本表中的类型。 |
+| `MAP`、`STRUCT`、`TIME`、`TIMESTAMPTZ`、`VARBINARY` 等其他类型 | 不支持，语句报错。 |
+
+源值还必须是其类型的合法值，非法值会被拒绝，不会被自动修复。CAST 不会解析字符串：`CAST('{"id": 1}' AS VARIANT)` 得到字符串 `{"id": 1}`。需要解析 JSON 文本时请使用 `PARSE_TO_VARIANT`。
+
+### VARIANT CAST 为其他类型 {#cast-from-variant}
+
+| 目标类型 | 结果 |
+| --- | --- |
+| `BOOLEAN` | 布尔值保持不变；数值非零即为 `true`；字符串按 `CAST(string AS BOOLEAN)` 的规则转换。 |
+| `TINYINT`、`SMALLINT`、`INT`、`BIGINT`、`LARGEINT` | 整数。定点数和浮点数的小数部分被截断（`1.5` 变为 `1`）；布尔值变为 `1` 或 `0`；`"123"` 这样的数字字符串会被转换。 |
+| `FLOAT`、`DOUBLE`、`DECIMAL(p, s)` | 数值和数字字符串。`DECIMAL` 按 scale `s` 舍入。超出 `FLOAT` 范围的值变为 `Infinity`。 |
+| `DATE`、`DATETIME(p)`、`TIMESTAMP_NS` | 日期时间值、日期时间格式的字符串，以及 `20240102` 这样的数值。 |
+| `TIMESTAMPTZ(p)` | 日期时间值，以及日期时间格式的字符串。 |
+| `IPV4`、`IPV6` | IP 地址格式的字符串。 |
+| `CHAR`、`VARCHAR`、`STRING` | 字符串根值原样返回，不带引号；对象和数组返回 JSON 文本；其他标量按对应 SQL 类型的格式输出：布尔根值为 `1` 或 `0`，`DATETIME` 值带 6 位小数，`TIMESTAMP_NS` 值带 9 位小数。VARIANT `null` 返回字符串 `null`。 |
+| `JSON` / `JSONB` | 保持原结构。没有 JSON 对应类型的值（如日期、时间戳）会变成 JSON 字符串；带时区的时间戳按会话时区格式化。 |
+| `ARRAY<T>` | 按元素转换的数组，无法转换的元素变为 `NULL`。内容为 JSON 数组的字符串（如 `"[1, 2]"`）也会被转换。其他值返回 `NULL`。 |
+| `MAP`、`STRUCT`、`TIME` 等其他类型 | 不支持，语句报错。 |
+
+值无法转换为目标类型时返回 SQL `NULL`，开启 `enable_strict_cast` 时也是如此。例外是值的类型与目标类型之间根本没有转换，例如把数值 CAST 为 `TIMESTAMPTZ`、`IPV4`、`IPV6`，或把日期、时间戳 CAST 为 `BOOLEAN`：这时语句会报错。
-SELECT CAST(PARSE_TO_VARIANT('{"id": 1}') AS JSON) AS json_value;
--- {"id":1}
+```sql
+SELECT CAST(PARSE_TO_VARIANT('"123"') AS INT) AS from_string, -- 123
+ CAST(PARSE_TO_VARIANT('"abc"') AS INT) AS not_a_number, -- NULL
+ CAST(PARSE_TO_VARIANT('1.5') AS INT) AS truncated, -- 1
+ CAST(PARSE_TO_VARIANT('300') AS TINYINT) AS overflow, -- NULL
+ CAST(PARSE_TO_VARIANT('{"a": 1}') AS INT) AS from_object, -- NULL
+ CAST(PARSE_TO_VARIANT('[1, "2", null, "x"]') AS ARRAY<INT>) AS arr; -- [1, 2, null, null]
+
+SELECT CAST(PARSE_TO_VARIANT('true') AS STRING) AS bool_root, -- 1
+ CAST(PARSE_TO_VARIANT('{"b": true}') AS STRING) AS object_text, -- {"b":true}
+ CAST(PARSE_TO_VARIANT('"abc"') AS STRING) AS string_root, -- abc
+ CAST(PARSE_TO_VARIANT('null') AS STRING) AS variant_null; -- null
```
-### Decimal 与日期时间转换限制
+### Decimal 与日期时间值 {#decimal-and-datetime-values}
+
+把以下类型转换为 VARIANT 时：
-| Doris 输入类型 | VARIANT 支持情况 |
+| Doris 类型 | 行为 |
| --- | --- |
| 旧版 `DECIMALV2` | 精确保留 precision 不超过 27、scale 不超过 9 的值。 |
-| `DECIMAL(p, s)` | 精确保留 `1 <= p <= 38` 且 `0 <= s <= p` 的值；不支持需要超过 38 位 precision 的值。 |
-| `DATE` | 保留为不含时间和时区的日历日期。 |
-| 旧版 `DATETIME` | 保留到秒，不进行时区调整。 |
-| `DATETIME(p)` | 支持 `0 <= p <= 6`，不进行时区调整。 |
-| `TIMESTAMP_NS` | 保留固定纳秒精度且不进行时区调整；值必须在 TIMESTAMP_NS 取值范围内。 |
-| `TIMESTAMPTZ(p)` | 支持 `0 <= p <= 6`，保留带时区调整的 timestamp 语义。 |
-| precision 超过 38 的 Decimal | 不支持作为 VARIANT 输入。 |
-| `TIME` | 不支持作为 VARIANT 输入。 |
+| `DECIMAL(p, s)` | 精确保留 `1 <= p <= 38` 且 `0 <= s <= p` 的值；不支持 precision 超过 38 的 Decimal。 |
+| `DATE` | 不含时间和时区的日历日期。 |
+| `DATETIME(p)` | `0 <= p <= 6`，不做时区调整。 |
+| `TIMESTAMP_NS` | 纳秒精度，不做时区调整；值必须在 TIMESTAMP_NS 取值范围内。 |
+| `TIMESTAMPTZ(p)` | 不支持：既不能 CAST 为 VARIANT，也不能在 Schema Template 中声明。 |
+| `TIME` | 不支持。 |
-源值还必须满足对应 Doris 类型本身的合法性要求。precision 超限、日期非法或值不兼容时会报错，不会自动修复。
+## NULL 语义 {#null-semantics}
-## 分组、去重与 Hash 语义
+VARIANT 中有两种 null：
-在 Doris 4.2 及后续版本中，分组、去重和集合运算会把逻辑上等价的 VARIANT 视为同一个值，不受来源 SQL 类型或物理表示影响：
+- **SQL `NULL`** 表示没有值，它来自值为 `NULL` 的列、不存在的路径（如 `v['no_such_key']`）或失败的 CAST。
+- **VARIANT `null`** 是一个值，即 JSON 字面量 `null`，例如 `PARSE_TO_VARIANT('null')` 或数组中的 `null` 元素。`VARIANT_TYPE` 对它返回 `null`。
-- 等价的整数数值表示会被视为同一个值。
-- Decimal 尾随零不影响值，因此 `1.20` 与 `1.2` 会被视为同一个值。
-- `+0`、`-0` 与整数零会被视为同一个值。
-- 对象 key 的顺序不影响是否视为同一个值，但数组元素顺序会影响。
-- Variant/JSON `null` 与 SQL `NULL` 不同。
+| 操作 | SQL `NULL` | VARIANT `null` |
+| --- | --- | --- |
+| `v IS NULL` | `true` | `false` |
+| `COALESCE(v, x)`、`IFNULL(v, x)`，`x` 为 VARIANT | 返回 `x` | 返回该 VARIANT `null` |
+| `COUNT(v)` | 不计入 | 计入 |
+| `GROUP BY v`、`DISTINCT` | 所有 SQL `NULL` 归为一组 | 所有 VARIANT `null` 归为另一组 |
+| `v = x`、等值 Join | 不会匹配 | 与另一个 VARIANT `null` 匹配 |
+| `ORDER BY v` | 由 `NULLS FIRST` 或 `NULLS LAST` 决定位置 | 升序时排在其他所有非 NULL 值之前 |
+| `CAST(v AS STRING)` | SQL `NULL` | 字符串 `null` |
-Hash 类算子也按上述逻辑值规则计算 key。因此，只要两个值按上述规则被视为同一个值，无论来源是解析后的 JSON 还是带类型的 CAST，都会得到相同的内部 Hash Key。该 Hash 只用于 Doris 内部执行，不是稳定的用户侧校验值。
+值写入表后，数组之外、值为 `null` 的对象成员会被移除，读取时返回 SQL `NULL`；根值 `null` 读回为 `{}`（参见[写入后值的规范化](#what-storage-keeps)）。因此对于已存储的数据，`v['a'] IS NULL` 无法区分成员不存在和成员为 `null`。
-这些规则适用于 `GROUP BY`、`DISTINCT`、`COUNT(DISTINCT ...)`、`INTERSECT`、`EXCEPT` 和 `UNION DISTINCT` 等已支持的操作，但不会开放根 VARIANT 比较谓词：直接执行 `VARIANT = VARIANT` 或排序比较仍不支持。
+## 比较、分组与排序 {#comparison-grouping-and-ordering}
-```sql
--- 1 和 1.0 只有一个 distinct logical value。
-SELECT COUNT(DISTINCT value) AS distinct_count
-FROM (
- SELECT PARSE_TO_VARIANT('1') AS value
- UNION ALL
- SELECT PARSE_TO_VARIANT('1.0') AS value
-) AS numeric_values;
--- distinct_count: 1
-
--- 对象 key 顺序被忽略；数组顺序会保留。
-SELECT COUNT(DISTINCT value) AS distinct_count
-FROM (
- SELECT PARSE_TO_VARIANT('{"a": 1, "b": 2}') AS value
- UNION ALL
- SELECT PARSE_TO_VARIANT('{"b": 2, "a": 1}') AS value
-) AS object_values;
--- distinct_count: 1
+VARIANT 值按逻辑值比较，而不是按文本或物理编码比较。相等判断、Hash（`GROUP BY`、`DISTINCT`、Join）和排序（`ORDER BY`、窗口键）使用同一套规则，因此相等的值一定落在同一分组，排序时也处于同一位次。
-SELECT COUNT(DISTINCT value) AS distinct_count
-FROM (
- SELECT PARSE_TO_VARIANT('[1, 2]') AS value
- UNION ALL
- SELECT PARSE_TO_VARIANT('[2, 1]') AS value
-) AS array_values;
--- distinct_count: 2
+### 支持的操作 {#supported-operations}
+
+| 对 VARIANT 值的操作 | 支持情况 | 说明 |
+| --- | --- | --- |
+| 两个 VARIANT 值之间，或与 `NULL` 字面量之间的 `=`、`!=`、`<=>` | 支持 | 也包括子路径，如 `v['a'] = w['a']`。 |
+| 等值 Join、Semi/Anti Join、`IN`/`NOT IN` 子查询 | 支持 | VARIANT Join 键不会生成 Runtime Filter。 |
+| `GROUP BY`、`DISTINCT`、`COUNT(DISTINCT ...)`、`UNION`、`INTERSECT`、`EXCEPT` | 支持 | |
+| `ORDER BY`、`ORDER BY ... LIMIT` | 支持 | |
+| 窗口函数的 `PARTITION BY` 与 `ORDER BY` | 支持 | |
+| `COUNT(v)`、`COLLECT_LIST(v)`、`ARRAY_AGG(v)` | 支持 | |
+| `IF`、`CASE`、`IFNULL`、`COALESCE` | 支持 | 把 VARIANT 与其他类型混用时，VARIANT 值会被转换为另一侧的类型（另一侧为整数时是 `DECIMAL(38, 9)`），无法转换的值变为 `NULL`。 |
+| `CAST(v AS ARRAY<VARIANT>)`、`EXPLODE_VARIANT_ARRAY`，以及对 `ARRAY<VARIANT>` 使用 `EXPLODE`、`EXPLODE_OUTER` | 支持 | |
+| VARIANT 值之间的 `<`、`<=`、`>`、`>=`、`BETWEEN` | 不支持 | 请先 CAST 为具体类型。 |
+| 整个 VARIANT 值与非 VARIANT 值比较，如 `v = 1` | 不支持 | 子路径会被隐式转换：`v['a'] = 1` 可以执行。 |
+| 包含 VARIANT 值的 `IN` 列表，如 `v IN (PARSE_TO_VARIANT('1'))` | 不支持 | 由非 VARIANT 值组成的 `v IN ('a', 'b')` 可以通过隐式转换执行。 |
+| `MIN`、`MAX` | 不支持 | 请先 CAST 子路径。 |
+| 以 VARIANT 作为参数的 `ARRAY(...)`、`MAP(...)`、`NAMED_STRUCT(...)` | 不支持 | |
+
+### 相等规则 {#equality}
+
+- **数值。** 整数、小数部分为零的定点数、值为整数的浮点数相等：`1`、`1.0`、`1.00` 是同一个值。定点数末尾的零不影响取值，`-0.0` 等于 `0`。带小数部分的定点数和浮点数永不相等：`DECIMAL 1.5` 不等于 `DOUBLE 1.5`。带小数部分的 JSON 数值解析为 `DOUBLE`；定点数来自 CAST、Schema Template 路径，以及超出 `BIGINT` 范围的整数。
+- **不同种类的值永不相等。** 数值 `1`、字符串 `"1"` 和 `true` 是三个不同的值。`DATE` 不等于字符串 `"2024-01-01"`，不带时区的时间戳也不等于带时区的时间戳。
+- **字符串** 只有字节完全相同时才相等，区分大小写。
+- **对象** 在 key 集合相同且对应的值都相等时相等，与 key 的顺序无关。
+- **数组** 在长度相同且对应位置的元素都相等时相等，元素顺序有影响。
+- VARIANT `null` 等于另一个 VARIANT `null`；SQL `NULL` 遵循 SQL 规则。
+
+```sql
+SELECT PARSE_TO_VARIANT('1') = PARSE_TO_VARIANT('1.0') AS int_double, -- 1
+ PARSE_TO_VARIANT('1.5') = CAST(CAST(1.5 AS DECIMAL(10, 2)) AS VARIANT) AS dbl_dec, -- 0
+ PARSE_TO_VARIANT('1') = PARSE_TO_VARIANT('"1"') AS num_str, -- 0
+ PARSE_TO_VARIANT('{"a": 1, "b": 2}') = PARSE_TO_VARIANT('{"b": 2, "a": 1}') AS obj, -- 1
+ PARSE_TO_VARIANT('[1, 2]') = PARSE_TO_VARIANT('[2, 1]') AS arr; -- 0
```
-## NULL 语义
+### 排序规则 {#ordering}
-SQL `NULL` 与 Variant/JSON `null` 是不同的值：
+对 VARIANT 值执行 `ORDER BY` 时使用统一的全序。不同种类的值先按种类排序：
-- SQL `NULL` 表示 SQL 值缺失，并遵循普通 SQL NULL 传播规则。
-- Variant/JSON `null` 是一个 VARIANT 值，例如 `PARSE_TO_VARIANT('null')` 的返回值。
-- `TRY_PARSE_TO_VARIANT` 遇到非法输入时返回 SQL `NULL`，这与成功解析 JSON 字面量 `null` 不同。
+```text
+null < boolean < number < string < binary < date < timestamp with time zone
+ < timestamp without time zone < time < UUID < object < array
+```
-## 基本类型
+binary、time 和 UUID 值无法通过 Doris 的 JSON 解析或 CAST 产生，只可能来自其他系统写入的 VARIANT 数据，例如带 VARIANT 列的 Parquet 文件。
-VARIANT 自动推断的子列基础类型包括：
+同一种类内：
-<table>
-<tr><td>支持的类型<br/></td></tr>
-<tr><td>TinyInt<br/></td></tr>
-<tr><td>NULL（等价于 JSON 的 null）<br/></td></tr>
-<tr><td>BigInt(64 bit)<br/>Double<br/></td></tr>
-<tr><td>String(Text)<br/></td></tr>
-<tr><td>Jsonb<br/></td></tr>
-<tr><td>Variant（嵌套对象）<br/></td></tr>
-<tr><td>Array<T>（仅支持一维）<br/></td></tr>
-</table>
+- **布尔值：** `false` 在 `true` 之前。
+- **数值：** 整数（`INT`、`BIGINT` 等）、定点数（`DECIMAL`）和浮点数（`FLOAT`、`DOUBLE`）统一按数值大小排序。`INT 1`、`FLOAT/DOUBLE 1.0` 和 `DECIMAL 1.00` 相等，排序位置相同；定点数末尾的零不影响比较。数值相同且带非零小数部分时，定点数在前：`DECIMAL 1.5` 排在 `FLOAT/DOUBLE 1.5` 前面，两者作为 VARIANT 值不相等。浮点精度也会影响排序，例如 `DOUBLE 0.1` 略大于 `DECIMAL 0.1`。负无穷最小；正无穷和 NaN 最大，NaN 在正无穷之后。
+- **字符串：** 按 UTF-8 字节排序。大写字母排在小写字母之前，`"10"` 排在 `"9"` 之前。
+- **日期与时间戳：** 按时间先后。所有日期都排在所有时间戳之前。
+- **对象：** 按 key 的顺序逐项比较：先比较最小的 key，再比较它的值，然后比较下一个 key，依此类推。一个对象的所有项是另一个对象的前缀时，前者排在前面，因此 `{"a":1}` < `{"a":1,"b":2}` < `{"b":0}`。
+- **数组：** 逐个元素比较；前缀排在前面，因此 `[]` < `[null]` < `[1]` < `[1,2]`。
-简单的 INSERT 示例：
+SQL `NULL` 的位置由 `NULLS FIRST` 或 `NULLS LAST` 决定。
```sql
-INSERT INTO vartab VALUES
- (1, 'null'),
- (2, NULL),
- (3, 'true'),
- (4, '-17'),
- (5, '123.12'),
- (6, '1.912'),
- (7, '"A quote"'),
- (8, '[-1, 12, false]'),
- (9, '{ "x": "abc", "y": false, "z": 10 }'),
- (10, '"2021-01-01"');
+SELECT v, VARIANT_TYPE(v) AS type
+FROM (
+ SELECT PARSE_TO_VARIANT('[1, 2]') AS v UNION ALL
+ SELECT PARSE_TO_VARIANT('{"a": 1}') UNION ALL
+ SELECT PARSE_TO_VARIANT('"9"') UNION ALL
+ SELECT PARSE_TO_VARIANT('"10"') UNION ALL
+ SELECT PARSE_TO_VARIANT('10') UNION ALL
+ SELECT PARSE_TO_VARIANT('9.5') UNION ALL
+ SELECT PARSE_TO_VARIANT('true') UNION ALL
+ SELECT PARSE_TO_VARIANT('null') UNION ALL
+ SELECT NULL
+) t
+ORDER BY v;
```
-提示：日期/时间戳等非标准 JSON 类型在未指定 Schema 时会以字符串形式存储；如需较高计算效率，建议将其提取为静态列或在 Schema Template 中明确声明类型。
+```text
++---------+---------+
+| v | type |
++---------+---------+
+| NULL | NULL |
+| null | null |
+| true | bool |
+| 9.5 | double |
+| 10 | tinyint |
+| 10 | string |
+| 9 | string |
+| {"a":1} | object |
+| [1,2] | array |
++---------+---------+
+```
+
+### 按具体类型比较与按 VARIANT 比较 {#typed-comparison-and-variant-comparison}
-## 扩展类型（Schema Template）
+比较采用 VARIANT 规则还是具体类型的规则，由操作数决定。假设路径 `a` 上有 `1`、`1.0`、`"1"` 三个值：
+
+| 表达式 | 比较方式 | 结果 |
+| --- | --- | --- |
+| `v['a'] = 1` | 对 `v['a']` 隐式 CAST 后按 `DECIMAL(38, 9)` 比较 | 三个值都匹配 |
+| `v['a'] = CAST(1 AS VARIANT)`、`v['a'] = w['a']` | VARIANT | 匹配 `1` 和 `1.0`，不匹配 `"1"` |
+| `GROUP BY v['a']` | VARIANT | `1` 和 `1.0` 同组，`"1"` 另成一组 |
+| `GROUP BY CAST(v['a'] AS STRING)` | `STRING` | 只有一组 `1` |
+| `ORDER BY v['a']` | VARIANT | 先数值，后字符串 |
+| `ORDER BY CAST(v['a'] AS INT)` | `INT` | 按数值排序；无法转换的值变为 `NULL` |
-除基本类型外，VARIANT 还可通过 Schema Template 声明以下扩展类型：
+### 注意事项 {#notes}
-- Number（扩展）
- - Decimal：Decimal32 / Decimal64 / Decimal128 / Decimal256
- - LargeInt
-- Datetime
-- Timestamptz
-- Date
-- IPV4 / IPV6
-- Boolean
-- ARRAY<T>（T 为以上任意类型，仅支持一维）
+- **代价。** VARIANT 键按逻辑值做 Hash 和比较。在一个 4400 万行的测试中，以 VARIANT 为键的 `GROUP BY`、排序和 Join，耗时是对 `CAST(v['path'] AS <type>)` 执行相同操作的 1.2 到 3.7 倍。路径类型确定时，请先 CAST。
+- **结果以存储后的值为准。** [写入后值的规范化](#what-storage-keeps)发生在比较之前：原本为 `null` 的成员已经不存在；根值中的 `DATE` 按字符串比较；同一路径上与数值混在一起的布尔值可能读回为 `1` 或 `0`。类型重要的路径请在 Schema Template 中声明。
+- **混合类型按种类排序，而不是按值排序。** 如果一个路径上既有数值又有数字字符串，`ORDER BY v['a']` 会把所有数值排在所有字符串之前，字符串之间再按字节排序。需要数值序或字典序时，请先 CAST 为同一类型。
+- **看起来相同的值可能不同。** 在 `GROUP BY` 结果中，数值 `1` 与字符串 `"1"` 显示得完全一样，却是两个分组。可以用 `VARIANT_TYPE` 区分。
+- 跨种类的排序由 Doris 定义，目的是保证结果确定。它不属于 JSON 标准，也可能与其他系统不同。
+- VARIANT 值的内部 Hash 只用于执行，不是稳定的用户侧校验值。
-注意：预定义的 Schema 只能在建表时指定，当前不支持通过 ALTER 修改（后续可能支持“新增”子列定义，但不支持修改既有子列类型）。
+## Schema Template {#schema-template}
-示例：
+Schema Template 用于声明部分路径的存储类型。只需声明需要稳定类型或路径级索引的关键路径，文档的其余部分仍保持动态。
```sql
CREATE TABLE test_var_schema (
@@ -291,63 +438,85 @@ CREATE TABLE test_var_schema (
'string_val': STRING,
'decimal_val': DECIMAL(38, 9),
'datetime_val': DATETIME,
- 'tz_val': TIMESTAMPTZ,
'ip_val': IPV4
> NULL
)
PROPERTIES ("replication_num" = "1");
+```
+
+模板字段可以使用以下类型：
+
+- 数值：`TINYINT`、`SMALLINT`、`INT`、`BIGINT`、`LARGEINT`、`FLOAT`、`DOUBLE`，以及 `p <= 38` 的 `DECIMAL(p, s)`
+- `STRING`（或 `TEXT`）
+- `BOOLEAN`
+- `DATE`、`DATETIME(p)`、`TIMESTAMP_NS`
+- `IPV4`、`IPV6`
+- `ARRAY<T>`，`T` 为以上类型之一（仅支持一维）
+
+### 写入模板路径 {#writing-to-template-paths}
+
+在声明过的路径上，每个值都按非严格模式的 CAST 规则转换为声明类型。无法转换的值存为 `NULL`，读回时该路径不存在；这一行的其余部分照常写入。转换可能会改变值：
+
+```sql
+CREATE TABLE tpl_demo (
+ k INT,
+ v VARIANT<'id': INT, 'price': DECIMAL(10, 2), 'ts': DATETIME(3)>
+)
+DUPLICATE KEY(k)
+DISTRIBUTED BY HASH(k) BUCKETS 1
+PROPERTIES ("replication_num" = "1");
-INSERT INTO test_var_schema VALUES (1, '{
- "large_int_val" : "123222222222222222222222",
- "string_val" : "Hello World",
- "decimal_val" : 1.11111111,
- "datetime_val" : "2025-05-16 11:11:11",
- "tz_val" : "2025-05-16 11:11:11+08:00",
- "ip_val" : "127.0.0.1"
-}');
+INSERT INTO tpl_demo VALUES
+ (1, PARSE_TO_VARIANT('{"id": "123", "price": 9.999, "ts": "2024-01-01 10:00:00.123456"}')),
+ (2, PARSE_TO_VARIANT('{"id": "abc", "price": "x", "ts": "not a time"}')),
+ (3, PARSE_TO_VARIANT('{"id": 1.7}'));
-SELECT variant_type(v1) FROM test_var_schema;
+SELECT k, v FROM tpl_demo ORDER BY k;
+```
-+---------------------------------------------------------------------------------------------------------------------------------------------------+
-| variant_type(v1) |
-+---------------------------------------------------------------------------------------------------------------------------------------------------+
-| {"datetime_val":"datetimev2","decimal_val":"decimal128i","ip_val":"ipv4","large_int_val":"largeint","string_val":"string","tz_val":"timestamptz"} |
-+---------------------------------------------------------------------------------------------------------------------------------------------------+
+```text
++------+------------------------------------------------------------+
+| k | v |
++------+------------------------------------------------------------+
+| 1 | {"id":123,"price":10.00,"ts":"2024-01-01 10:00:00.123000"} |
+| 2 | {} |
+| 3 | {"id":1} |
++------+------------------------------------------------------------+
```
-`{"date": 2020-01-01}` 与 `{"ip": 127.0.0.1}` 均为非法 JSON 文本，正确格式应为 `{"date": "2020-01-01"}` 与 `{"ip": "127.0.0.1"}`。
+- `"123"` 变为 `123`，`1.7` 被截断为 `1`，`9.999` 被舍入为 `10.00`，时间戳保留声明的精度（毫秒）；其文本形式总是显示 6 位小数。
+- `"abc"`、`"x"`、`"not a time"` 无法转换，因此被丢弃。
+- 带小数部分的 JSON 数值在转换前会先解析为 `DOUBLE`，因此 `DECIMAL` 路径可能丢失精度。如需保留全部位数，请把这类值写成 JSON 字符串（参见 [FAQ](#faq)）。
+- 日期和 IP 值必须写成 JSON 字符串：`{"date": 2020-01-01}` 与 `{"ip": 127.0.0.1}` 都不是合法 JSON，应写作 `{"date": "2020-01-01"}` 与 `{"ip": "127.0.0.1"}`。
-一旦指定 Schema，若 JSON 实际类型与 Schema 冲突且无法转换，将保存为 NULL。例如：
+:::caution
+只有当值的 JSON 类型能够转换为声明类型时，才会进行转换。`IPV4` 或 `IPV6` 路径上出现 JSON 数值时，整次写入会失败。如果同一次写入在 `DATE`、`DATETIME`、`IPV4` 或 `IPV6` 路径上混有 JSON 字符串和数值，该路径上合法的值也可能被丢弃。这类路径的值请写成 JSON 字符串。
+:::
-```sql
-INSERT INTO test_var_schema VALUES (1, '{
- "decimal_val" : "1.11111111",
- "ip_val" : "127.xxxxxx.xxxx",
- "large_int_val" : "aaabbccc"
-}');
+### 读取模板路径 {#reading-template-paths}
--- 仅 decimal_val 保留
-SELECT * FROM test_var_schema;
+即使路径已声明类型，`v['path']` 仍是 `VARIANT`，Doris 不会自动把它 CAST 为声明类型。需要声明类型时请显式 CAST；在过滤条件中也请 CAST 为声明类型，这样查询才能利用该路径上的 zone map 和索引：
-+------+-----------------------------+
-| id | v1 |
-+------+-----------------------------+
-| 1 | {"decimal_val":1.111111110} |
-+------+-----------------------------+
+```sql
+SELECT CAST(v['ts'] AS DATETIME(3)) AS ts, -- 2024-01-01 10:00:00.123
+ CAST(v['ts'] AS STRING) AS ts_text, -- 2024-01-01 10:00:00.123000
+ VARIANT_TYPE(v['price']) AS type -- decimal
+FROM tpl_demo
+WHERE CAST(v['id'] AS INT) = 123;
```
-Schema 仅指导“存储层”的持久化类型。没有写入表的查询表达式仍保留实际的运行时类型：
+Schema Template 只决定值如何存储。同样的 JSON 在查询中计算时保留解析得到的类型：
```sql
--- JSON 成员带引号，因此类型为 STRING。这里使用 PARSE_TO_VARIANT，
--- 因为 CAST(string AS VARIANT) 始终把整个输入保留为字符串。
-SELECT variant_type(PARSE_TO_VARIANT('{"a" : "12345"}')['a']);
+SELECT VARIANT_TYPE(PARSE_TO_VARIANT('{"price": 9.999}')['price']); -- double
```
-通配符与匹配顺序：
+### 通配符匹配 {#pattern-matching}
+
+字段名默认是 glob 模式（`MATCH_NAME_GLOB`）：`*` 匹配任意字符序列，包括嵌套 key 之间的 `.`；`?` 匹配单个字符。例如 `'m*'` 既匹配 `m1`，也匹配 `m2.x`。一个路径匹配多个字段时，使用定义顺序中的第一个：
```sql
-CREATE TABLE test_var_schema (
+CREATE TABLE test_var_pattern (
id BIGINT NOT NULL,
v1 VARIANT<
'enumString*' : STRING,
@@ -357,10 +526,10 @@ CREATE TABLE test_var_schema (
)
PROPERTIES ("replication_num" = "1");
--- 若 enumString1 同时匹配上述两个 pattern，则采用定义顺序中第一个匹配到的类型（STRING）
+-- enumString1 同时匹配 enumString* 和 enum*，使用第一个（STRING）。
```
-如列名中包含 `*` 且希望按名称精确匹配，可使用：
+如需按字面值匹配包含 `*` 的名称，请使用 `MATCH_NAME`：
```sql
v1 VARIANT<
@@ -368,50 +537,110 @@ v1 VARIANT<
> NULL
```
-匹配成功的子路径默认会参与子列列式提取（Subcolumnization），并展开为独立列。若匹配子列过多导致列数暴增，建议开启 `variant_enable_typed_paths_to_sparse`（见“配置”）。
+匹配成功的路径默认参与子列列式提取（Subcolumnization）。如果匹配的路径过多、生成了过多子列，可以考虑开启 `variant_enable_typed_paths_to_sparse`（参见[列属性](#column-properties)）。
+
+列创建后不能修改 Schema Template，参见 [ALTER TABLE](#alter-table)。
+
+## ALTER TABLE {#alter-table}
-## 类型冲突与提升规则
+`ALTER TABLE` 支持：
-当同一路径出现不兼容类型（如同一字段既出现整数又出现字符串）时，将提升为 JSONB 类型以避免信息丢失：
+- `ADD COLUMN` 新增可为 NULL 的 VARIANT 列，可以带 Schema Template 和属性。
+- `DROP COLUMN`、`RENAME COLUMN` 和 `MODIFY COLUMN ... COMMENT '...'`。
+- `ADD INDEX` 和 `DROP INDEX`。带 `field_pattern` 的索引只能在 `CREATE TABLE` 中定义。
+- 对没有 Schema Template 的列：把 `NOT NULL` 改为 `NULL`，以及修改 `variant_doc_materialization_min_rows`。`MODIFY COLUMN` 需要给出完整的列定义，未写出的属性取对应 `default_variant_*` 会话变量的值，因此取值与会话变量不同的属性都要写出。
+
+不支持：
+
+- `ADD COLUMN ... VARIANT NOT NULL`：通过 `ALTER` 新增的 `NOT NULL` 列需要默认值，而 VARIANT 只允许 `DEFAULT NULL`。`NOT NULL` 的 VARIANT 列请在 `CREATE TABLE` 中定义。
+- 为已有的列添加 Schema Template。带 Schema Template 的列，`MODIFY COLUMN` 只能修改注释，其他修改都会失败，原样重写同一个模板也不行。
+- 修改 `variant_doc_materialization_min_rows` 以外的属性、把 `NULL` 改为 `NOT NULL`，以及在 VARIANT 与其他类型之间转换。
+- 在 VARIANT 列上执行 `BUILD INDEX`。
+
+需要这些修改时，请按新定义建表，再用 `INSERT INTO ... SELECT` 复制数据；源列为 `STRING` 时用 `PARSE_TO_VARIANT` 包裹。
```sql
-{"a" : 12345678}
-{"a" : "HelloWorld"}
--- a 将被提升为 JSONB
+ALTER TABLE t ADD COLUMN v2 VARIANT NOT NULL;
+-- ERROR: Field 'v2' doesn't have a default value
+
+-- v 是没有 Schema Template 的 VARIANT 列。
+ALTER TABLE t MODIFY COLUMN v VARIANT<'id': INT>;
+-- ERROR: Can not change variant schema templates
```
-转换规则如下表格：
+## 列属性 {#column-properties}
-| 源类型 | 当前类型 | 最终类型 |
-| -------------- | -------------- | -------------- |
-| `TinyInt` | `BigInt` | `BigInt` |
-| `TinyInt` | `Double` | `Double` |
-| `TinyInt` | `String` | `JSONB` |
-| `TinyInt` | `Array` | `JSONB` |
-| `BigInt` | `Double` | `JSONB` |
-| `BigInt` | `String` | `JSONB` |
-| `BigInt` | `Array` | `JSONB` |
-| `Double` | `String` | `JSONB` |
-| `Double` | `Array` | `JSONB` |
-| `Array<Double>`| `Array<String>`| `Array<Jsonb>` |
+列属性写在 VARIANT 类型内的 `properties(...)` 中：
-若需严格限制子列类型（以稳定索引和存储），请结合 Schema Template 明确声明类型。
+```sql
+CREATE TABLE example_table (
+ id INT,
+ data_variant VARIANT<
+ 'path_1' : INT,
+ 'path_2' : STRING,
+ properties(
+ 'variant_max_subcolumns_count' = '2048',
+ 'variant_enable_typed_paths_to_sparse' = 'true',
+ 'variant_sparse_hash_shard_count' = '64'
+ )
+ >
+);
+```
+
+| 属性 | 默认值 | ALTER | 描述 |
+| --- | --- | --- | --- |
+| `variant_max_subcolumns_count` | `2048` | 否 | 单个数据文件中参与子列列式提取（Subcolumnization）的动态路径数上限；非空值多的路径优先入选，其余路径存入稀疏列。`0` 表示不限制。取值范围 0 到 100000，建议不超过 10000。默认值已足够覆盖大多数 workload；如果确实需要大规模子列，优先考虑 [DOC mode](./variant-workload-guide.md#doc-mode-template)。 |
+| `variant_enable_typed_paths_to_sparse` | `false` | 否 | 默认情况下，Schema Template 路径一定参与子列列式提取，且不计入 `variant_max_subcolumns_count`。设置为 `true` 后，这些路径也计入上限，并可能存入稀疏列。 |
+| `variant_sparse_hash_shard_count` | `1` | 否 | 稀疏路径按 Hash 分散到的物理稀疏列数量。取值范围 0 到 1024，`0` 按 `1` 处理。 |
+| `variant_max_sparse_column_statistics_size` | `10000` | 否 | 单个数据文件中记录统计信息的稀疏路径数上限。超过后，查询没有统计信息的路径时无法跳过稀疏列。取值范围 1 到 50000。 |
+| `variant_enable_doc_mode` | `false` | 否 | 开启 DOC mode。不能与 `variant_max_subcolumns_count`、`variant_enable_typed_paths_to_sparse`、`variant_max_sparse_column_statistics_size`、`variant_sparse_hash_shard_count` 同时设置。 |
+| `variant_doc_materialization_min_rows` | `0` | 仅限没有 Schema Template 的列 | 仅用于 DOC mode。行数少于该值的写入只存储文档，待 Compaction 把文件合并到该阈值后再执行子列列式提取。`0` 表示写入时即执行子列列式提取。取值范围 0 到 1000000000。 |
+| `variant_doc_hash_shard_count` | `64` | 否 | 仅用于 DOC mode。存储的文档被拆分到的列数。取值范围 0 到 1024，`0` 按 `1` 处理。 |
-## Variant 索引
+表中 **ALTER** 列表示 `ALTER TABLE ... MODIFY COLUMN` 能否修改该属性，参见 [ALTER TABLE](#alter-table)。稀疏列和 DOC mode 相关属性的用法参见[宽列](#wide-columns)。
-### 索引选择
+### 会话变量 {#session-variables}
+
+| 变量 | 默认值 | 描述 |
+| --- | --- | --- |
+| `default_variant_max_subcolumns_count` | `2048` | 定义 VARIANT 列时未指定 `variant_max_subcolumns_count` 所使用的值。 |
+| `default_variant_enable_typed_paths_to_sparse` | `false` | 未指定 `variant_enable_typed_paths_to_sparse` 时使用的值。 |
+| `default_variant_sparse_hash_shard_count` | `0` | 未指定 `variant_sparse_hash_shard_count` 时使用的值；`0` 按 `1` 处理。 |
+| `default_variant_max_sparse_column_statistics_size` | `10000` | 未指定 `variant_max_sparse_column_statistics_size` 时使用的值。 |
+| `default_variant_enable_doc_mode` | `false` | 未指定 `variant_enable_doc_mode` 时使用的值。 |
+| `default_variant_doc_materialization_min_rows` | `0` | 未指定 `variant_doc_materialization_min_rows` 时使用的值。 |
+| `default_variant_doc_hash_shard_count` | `64` | 未指定 `variant_doc_hash_shard_count` 时使用的值。 |
+| `describe_extend_variant_column` | `false` | 为 `true` 时，`DESC` 还会列出 VARIANT 列的子列。 |
+
+`default_variant_*` 变量在定义列时生效，包括 `CREATE TABLE` 和 `ALTER TABLE`。修改这些变量不影响已有的列，但会影响之后省略了该属性的 `MODIFY COLUMN`。
+
+### BE 配置 {#be-configuration}
+
+| 配置项 | 默认值 | 描述 |
+| --- | --- | --- |
+| `variant_max_json_key_length` | `255` | JSON 对象 key 的最大字节数，超过即为解析错误。取值范围 1 到 65535。 |
+| `variant_throw_exeception_on_invalid_json` | `false` | 为 `false` 时，JSON 解析器无法接受的文本保留为 VARIANT 字符串；为 `true` 时，按解析错误处理。 |
+| `variant_enable_duplicate_json_path_check` | `false` | 为 `false` 时，对象中重复的 key 是解析错误；为 `true` 时，保留第一个值，忽略重复出现的 key。 |
+
+这三项都支持运行时修改。解析错误的处理方式参见[解析错误](#parse-errors)。
+
+## Variant 索引 {#variant-indexes}
+
+### 索引选择 {#choosing-indexes}
VARIANT 支持对子列建立 BloomFilter 与 Inverted Index 两类索引。
- 高基数等值/IN 过滤：优先使用 BloomFilter（更省存储、写入更高效）。
- 需要分词、短语、范围检索：使用 Inverted Index，并根据需求设置 `parser`/`analyzer` 等属性。
+只有当过滤条件按路径的存储类型比较时，才能使用索引或 BloomFilter。数值路径需要显式 CAST；字符串路径与字符串字面量比较则不需要 CAST。
+
```sql
...
PROPERTIES("replication_num" = "1", "bloom_filter_columns" = "v");
-- 利用 BloomFilter 做等值/IN 过滤
-SELECT * FROM tbl WHERE v['id'] = 12345678;
-SELECT * FROM tbl WHERE v['id'] IN (1, 2, 3);
+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 后，所有子列将继承相同的索引属性（如分词方式）。
@@ -428,9 +657,9 @@ SELECT * FROM tbl WHERE v['id_1'] MATCH 'Doris';
SELECT * FROM tbl WHERE v['id_2'] MATCH 'Apache';
```
-### 根据子路径指定索引
+### 根据子路径指定索引 {#index-by-subpath}
-在 3.1.x/4.0 及之后的版本中，可为 VARIANT 的部分子列单独指定索引属性，甚至在同一路径上同时配置“分词与不分词”的两种倒排索引。指定 Path 索引需配合 Path 类型（Schema Template）使用。
+可为 VARIANT 的部分子列单独指定索引属性，甚至在同一路径上同时配置“分词与不分词”的两种倒排索引。指定路径索引需要在 Schema Template 中声明该路径的类型。
```sql
-- 常用属性：field_pattern（目标子路径）、analyzer、parser、support_phrase 等
@@ -446,7 +675,7 @@ SELECT * FROM tbl WHERE v['content'] MATCH 'Doris';
SELECT * FROM tbl WHERE v['content'] = 'Doris';
```
-支持通配符的 Path 索引：
+支持通配符的路径索引：
```sql
CREATE TABLE IF NOT EXISTS tbl (
@@ -460,16 +689,18 @@ SELECT * FROM tbl WHERE v['pattern_1'] MATCH 'Doris';
SELECT * FROM tbl WHERE v['pattern_1'] = 'Doris';
```
-注意：2.1.7+ 仅支持 InvertedIndex V2 属性（文件更少、写入 IOPS 更低，适配存算分离）。2.1.8+ 不再支持离线 Build Index 构建。
-
-### 索引失效问题
+### 索引失效问题 {#when-indexes-dont-work}
1. 类型变更导致索引丢失：子列类型发生不兼容变更（如 INT→JSONB）会丢失索引。可通过 Schema Template 固定类型与索引。
-2. 查询类型不匹配：
+2. 过滤条件没有按路径的存储类型比较：
```sql
- -- v['id'] 实际为 STRING，按 INT 进行等值会导致索引失效
+ -- 隐式数值比较逐行求值，不使用索引
SELECT * FROM tbl WHERE v['id'] = 123456;
+
+ -- 应 CAST 为存储类型
+ SELECT * FROM tbl WHERE CAST(v['id'] AS BIGINT) = 123456;
```
+ 如果 `v['id']` 以字符串存储，请与字符串比较：`v['id'] = '123456'`。
3. 索引配置错误：索引作用于“子列”，对 VARIANT 整体无效。
```sql
-- v 本身不具备索引能力
@@ -485,129 +716,29 @@ SELECT * FROM tbl WHERE v['pattern_1'] = 'Doris';
SELECT * FROM tbl WHERE v_str MATCH 'Doris';
```
-## insert 与导入
-
-### INSERT INTO VALUES
-
-```sql
-CREATE TABLE IF NOT EXISTS variant_tbl (
- k BIGINT,
- v VARIANT
-) PROPERTIES("replication_num" = "1");
-
-INSERT INTO variant_tbl VALUES (1, '{"a" : 123}');
-
-select * from variant_tbl;
-+------+-----------+
-| k | v |
-+------+-----------+
-| 1 | {"a":123} |
-+------+-----------+
-
--- 其中 v['a'] 是 Variant 类型
-select v['a'] from variant_tbl;
-+--------+
-| v['a'] |
-+--------+
-| 123 |
-+--------+
-
--- v['a']['no_such_key'] 对于不存在的 JSON 键，将返回 NULL
-select v['a']['no_such_key'] from variant_tbl;;
-+-----------------------+
-| v['a']['no_such_key'] |
-+-----------------------+
-| NULL |
-+-----------------------+
-
-```
-
-### 导入（Stream Load）
-
-```bash
-# 以按行 JSON 为例（每行一条 JSON 记录）
-curl --location-trusted -u root: -T gh_2022-11-07-3.json \
- -H "read_json_by_line:true" -H "format:json" \
- http://127.0.0.1:8030/api/test_variant/github_events/_stream_load
-```
-
-参考 [variant](../../../../data-operate/import/complex-types/variant.md)
-
-导入完成后可用 `SELECT count(*)` 或 `SELECT * ... LIMIT 1` 验证。为提升高并发导入性能，推荐建表选择 RANDOM 分桶并开启 Group Commit（参见官方“Group Commit”文档）。
-
-## 输出
-
-从 VARIANT 列读出的 JSON 文本与写入时的 JSON 文本并非按字节完全一致：JSON object 内的 key 会按字典序输出，与输入 JSON 中的顺序无关。
-
-```sql
-INSERT INTO variant_tbl VALUES
- (2, '{ "b": 2, "a": 1, "c": { "y": 20, "x": 10 } }');
-
-SELECT v FROM variant_tbl WHERE k = 2;
-+-----------------------------------+
-| v |
-+-----------------------------------+
-| {"a":1,"b":2,"c":{"x":10,"y":20}} |
-+-----------------------------------+
-```
-
-排序在每一层都会生效——顶层 key 输出为 `a`、`b`、`c`，嵌套 object 内 key 输出为 `x`、`y`。
-
-## 支持的运算
-
-在 Doris 4.2 及后续版本中，整个 VARIANT 值支持基于 Hash 的分组和去重，但不支持比较、Join Key 或排序语义。
-
-| 对整个 VARIANT 值执行的操作 | 支持情况 | 说明 |
-| --- | --- | --- |
-| `GROUP BY` | 支持 | 使用上文所述的分组与 Hash 规则。 |
-| `DISTINCT`、`COUNT(DISTINCT ...)` | 支持 | 逻辑等价的值会被去重。 |
-| `INTERSECT`、`EXCEPT`、`UNION DISTINCT` | 支持 | 使用上文所述的相同规则。 |
-| `COUNT(*)`、`COUNT(variant)` | 支持 | `COUNT(variant)` 按普通 SQL 规则排除外层 SQL `NULL`。 |
-| `IF`、`CASE`、`IFNULL`、`COALESCE` | 支持 | 条件表达式可以返回和消费 VARIANT 值。 |
-| 临时 `ARRAY`、`MAP` 或 `STRUCT` 表达式中包含 VARIANT | 支持 | 这不表示持久化表结构可以使用这些嵌套类型。 |
-| `EXPLODE_VARIANT_ARRAY`、对 `ARRAY<VARIANT>` 使用 `EXPLODE`/`EXPLODE_OUTER` | 支持 | 输出 VARIANT 元素，并保留 SQL `NULL` 与 Variant/JSON `null` 的区别。 |
-| `=`、`!=`、`<=>`、`<`、`<=`、`>`、`>=` | 不支持 | 请在两侧提取可比较的子路径，并 CAST 为具体类型。 |
-| Join Key | 不支持 | 请把两侧所需子路径 CAST 为相同的具体类型。 |
-| `ORDER BY`、Sort/TopN Key | 不支持 | 排序前请先 CAST 所需子路径。 |
-| 窗口分区键或排序键 | 不支持 | 整个 VARIANT 值不能作为窗口 Key。 |
-| `MIN(variant)`、`MAX(variant)` | 不支持 | 聚合前请先 CAST 标量子路径。 |
-
-如需比较、过滤、计算或排序，请提取所需子路径，再显式或隐式 CAST 为具体类型：
-
-```sql
--- 显式 CAST
-SELECT CAST(v['arr'] AS ARRAY<TEXT>) FROM tbl;
-SELECT * FROM tbl WHERE CAST(v['decimal'] AS DECIMAL(27, 9)) = 1.111111111;
-SELECT * FROM tbl WHERE CAST(v['date'] AS DATE) = '2021-01-02';
-
--- 隐式 CAST
-SELECT * FROM tbl WHERE v['bool'];
-SELECT * FROM tbl WHERE v['str'] MATCH 'Doris';
-```
-
-## 宽列
+## 宽列 {#wide-columns}
当导入数据包含大量不同的 JSON key 时，通过子列列式提取（Subcolumnization）生成的子列会迅速增多；当规模达到一定程度，可能出现元数据膨胀、写入/合并开销增大、查询性能下降等问题。为应对“宽列”（子列过多），VARIANT 提供两种机制：**稀疏列** 与 **DOC 编码**。
-如果你要决定什么时候选 Sparse、什么时候选 DOC mode，请先看 [VARIANT 使用与配置指南](./variant-workload-guide)。本节只说明机制本身及其相关属性。
+如果你要决定什么时候选 Sparse、什么时候选 DOC mode，请先看 [VARIANT 使用与配置指南](./variant-workload-guide.md)。本节只说明机制本身及其相关属性。
注意：这两种机制**互斥**——启用 DOC 编码后将无法使用稀疏列机制，反之亦然。
-### 稀疏列机制
+### 稀疏列机制 {#sparse-columns}
**机制说明**
- 系统会按“非空比例/稀疏度”对路径排序：高频（不稀疏）路径优先执行子列列式提取（Subcolumnization），并存为独立子列；可执行 Subcolumnization 的最大子列数量由 `variant_max_subcolumns_count` 指定，其余低频（稀疏）路径会被合并存放到稀疏列中。
-- 如果对子列指定了预定义 Schema，默认情况下该子列不会被放入稀疏列中；可通过 `variant_enable_typed_paths_to_sparse` 允许“指定了类型”的子列进入稀疏列。
+- 在 Schema Template 中声明过的路径，默认不会被放入稀疏列；可通过 `variant_enable_typed_paths_to_sparse` 允许这些路径进入稀疏列。
- 稀疏列支持 sharding：通过将稀疏子路径分散到多个稀疏列中，降低单列读取负担、提升读取效率；可通过 `variant_sparse_hash_shard_count` 指定稀疏列的实际存储个数。
**参考说明**
- JSON key 总量大，但各个 JSON key 的“非空比例/稀疏度”都比较接近、缺乏区分度：这种情况下很难区分哪些列是真正稀疏的，稀疏列机制的效果会被降低。
-- `variant_max_subcolumns_count` 默认就是 `2048`，已经足够覆盖大多数 workload。不要为了预留更多自动提取子列而激进调大；如果场景确实需要大规模子列列式提取（Subcolumnization），优先参考 <a href="./variant-workload-guide#doc-mode-template">DOC mode</a>。实践上仍建议不超过 **10000**。
+- `variant_max_subcolumns_count` 默认就是 `2048`，已经足够覆盖大多数 workload。不要为了预留更多自动提取子列而激进调大；如果场景确实需要大规模子列列式提取（Subcolumnization），优先参考 [DOC mode](./variant-workload-guide.md#doc-mode-template)。实践上仍建议不超过 **10000**。
- `variant_sparse_hash_shard_count` 的设置可按“进入稀疏列的总列数 / 128”粗略估算。例如：VARIANT 中所有 JSON key 为 1 万，设置 `variant_max_subcolumns_count = 2000`，进入稀疏列的总列数约为 8000，则 `variant_sparse_hash_shard_count` 可参考 `8000/128`。
-### DOC 编码机制
+### DOC 编码机制 {#doc-encoding-doc-mode}
**机制说明**
@@ -617,64 +748,6 @@ SELECT * FROM tbl WHERE v['str'] MATCH 'Doris';
- 对超宽列 workload，DOC mode 也是更稳定的选择，尤其是在 Subcolumnization 规模接近万列时。相比默认的即时 Subcolumnization，compaction 内存可下降约 2/3，在稀疏宽列导入场景下导入性能可提升约 5~10 倍。
- 当 `VARIANT` 列非常宽、查询又经常读取整条文档时，DOC mode 相比从大量子列重组文档，`SELECT variant_col` 的效率可获得数量级提升。
-**参考说明**
-
-- 需开启 `variant_enable_doc_mode`。
-- 使用 DOC 编码机制时，指定了预定义 Schema 的子列类型只能是数值类型、字符串类型、array 类型。
-- `variant_doc_hash_shard_count` 的设置可按 “JSON key 的总个数 / 128” 粗略估算。
-
-两种机制的详细使用见下方“配置”章节。
-
-## 限制
-
-- **大宽表优化**：对于会通过子列列式提取（Subcolumnization）生成大量独立子列的宽表场景（例如超过 2000 列），强烈建议开启 **V3 存储格式**。通过在建表 `PROPERTIES` 中指定 `"storage_format" = "V3"`，可以将列元数据与 Segment Footer 解耦，加快文件打开速度并降低内存占用。
-- JSON key 长度 ≤ 255。
-- 不支持作为主键或排序键。
-- 持久化表结构不能把 VARIANT 嵌套在其他类型中（如 `ARRAY<VARIANT>`、`STRUCT<VARIANT>`）；临时表达式结果可以使用上表列出的嵌套容器能力。
-- 在未启用 DOC mode 时，读取整个 VARIANT 列会扫描所有子字段。对于超宽列，一般不建议直接 `SELECT variant_col`；如果整列读取是主要查询模式，建议优先使用 DOC mode。若列包含大量子字段，也可额外存储原始 JSON 的 STRING/JSONB 列，以优化如 `LIKE` 等整体匹配：
-
-```sql
-CREATE TABLE example_table (
- id INT,
- data_variant VARIANT
-);
-SELECT * FROM example_table WHERE data_variant LIKE '%doris%';
-
--- 更优做法：额外保留原始 JSON 字符串列用于整体检索
-CREATE TABLE example_table (
- id INT,
- data_string STRING,
- data_variant VARIANT
-);
-SELECT * FROM example_table WHERE data_string LIKE '%doris%';
-```
-
-## 配置
-
-在 3.1+ 支持在 VARIANT 类型上声明列级别属性：
-
-```sql
-CREATE TABLE example_table (
- id INT,
- data_variant VARIANT<
- 'path_1' : INT,
- 'path_2' : STRING,
- properties(
- 'variant_max_subcolumns_count' = '2048',
- 'variant_enable_typed_paths_to_sparse' = 'true',
- 'variant_sparse_hash_shard_count' = '64'
- )
- >
-);
-```
-
-<table>
-<tr><td>稀疏列属性</td><td>描述</td></tr>
-<tr><td>`variant_max_subcolumns_count`</td><td>控制可参与子列列式提取（Subcolumnization）的路径数上限；超过后新增路径可能存放于共享数据结构。默认 2048（推荐），已经覆盖大多数 workload；避免设置得过大。若场景确实需要大规模自动提取子列，优先参考 <a href="./variant-workload-guide#doc-mode-template">DOC mode</a>。0 表示不限制；不推荐超过 10000。</td></tr>
-<tr><td>`variant_enable_typed_paths_to_sparse`</td><td>默认指定了 Path 类型后，该 Path 一定会参与子列列式提取（Subcolumnization），且不计入 `variant_max_subcolumns_count`。设置为 `true` 后也会计入阈值，可能被收敛到共享结构。</td></tr>
-<tr><td>`variant_sparse_hash_shard_count`</td><td>控制稀疏列的分片数量。将稀疏子列分散存储到多个稀疏列中，以提升查询性能。默认值为 1，建议根据稀疏子列数量适当调整。</td></tr>
-</table>
-
```sql
CREATE TABLE example_table (
id INT,
@@ -690,48 +763,68 @@ CREATE TABLE example_table (
);
```
-<table>
-<tr><td>DOC 编码属性</td><td>描述</td></tr>
-<tr><td>`variant_enable_doc_mode`</td><td>是否启用 DOC 编码模式。设置为 `true` 时，原始 JSON 会作为存储字段保存，用于快速返回整个 JSON 文档；启用后将无法使用稀疏列机制。默认值为 `false`。</td></tr>
-<tr><td>`variant_doc_materialization_min_rows`</td><td>DOC 编码模式下触发子列列式提取（Subcolumnization）的最小行数阈值。当写入行数低于该值时，仅存储原始 JSON；当文件合并后行数达到该阈值时，才执行 Subcolumnization，用于减少小批量写入时的开销。</td></tr>
-<tr><td>`variant_doc_hash_shard_count`</td><td>控制 DOC 编码的分片数量。原始 JSON 会被拆散存储到指定数量的列中，查询整个 JSON 时再组装这些分片。默认值为 64，可根据 JSON 大小和并发需求调整。</td></tr>
-</table>
+**参考说明**
+
+- 需开启 `variant_enable_doc_mode`。
+- DOC mode 下，Schema Template 字段只能是字符串、整数、`FLOAT`、`DOUBLE`、`BOOLEAN` 以及由这些类型组成的数组。
+- `variant_doc_hash_shard_count` 的设置可按 “JSON key 的总个数 / 128” 粗略估算。
-达到上限后的行为与调优建议：
+### 达到上限后的行为与调优建议 {#behavior-at-limits-and-tuning-suggestions}
-1. 超过上限后，新路径写入共享结构；Rowset 合并后也可能触发部分路径回收为共享结构。
+1. 超过上限后，新路径写入稀疏列；Rowset 合并后也可能把部分路径转入稀疏列。
2. 系统会优先让非空比例高、访问频率高的路径保留在子列列式提取（Subcolumnization）中。
3. 若参与子列列式提取（Subcolumnization）的路径接近 10000，对硬件要求较高（建议单机 ≥128G 内存、≥32C）。如果 workload 已经接近这个规模，建议优先评估 DOC mode。
4. 写入侧调优：适度增大客户端 batch_size，或使用 Group Commit（按需增大 `group_commit_interval_ms`/`group_commit_data_bytes`）。
5. 若无分桶裁剪需求，建议采用 RANDOM 分桶，并开启 single tablet 导入以降低 compaction 写放大。
6. BE 配置可按导入压力调整 `max_cumu_compaction_threads`（建议 ≥8）、`vertical_compaction_num_columns_per_group=500`（提升纵向合并效率，增加内存占用）、`segment_cache_memory_percentage=20`（提升元数据缓存命中）。
7. 关注 Compaction Score；若持续上升说明 Compaction 跟不动，需要降低导入压力。
-8. 避免大范围 `SELECT *` 或直接扫描 VARIANT；尽量使用具体路径投影 `SELECT v['path']`。
+8. 避免大范围 `SELECT *`；尽量使用具体路径投影 `SELECT v['path']`。
-另：当出现 Stream Load 报错 `[DATA_QUALITY_ERROR]Reached max column size limit 2048` 时（只有 2.1.x 和 3.0.x 版本会出现该报错），说明合并后的 Tablet Schema 达到列数上限。可按需调整 BE 配置 `variant_max_merged_tablet_schema_size`（不建议超过 4096，需较高配置机器）。
+## 查看子列与类型 {#inspect-subcolumns-and-types}
-## 查看列数、列类型
-
-方案一：使用 `variant_type` 查看行级 Schema（开销更大但更精确）：
+方案一：使用 [VARIANT_TYPE](../../../sql-functions/scalar-functions/variant-functions/variant-type.md) 逐行查看某个值或某个路径的类型。它会读取检查的每一行：
```sql
-SELECT variant_type(v) FROM variant_tbl;
+SELECT VARIANT_TYPE(v), VARIANT_TYPE(v['a']) FROM variant_tbl LIMIT 10;
```
-方案二：扩展 `DESC` 展示已完成子列列式提取（Subcolumnization）的子路径：
+方案二：扩展 `DESC`，展示已完成子列列式提取（Subcolumnization）的子路径及其存储类型：
```sql
SET describe_extend_variant_column = true;
DESC variant_tbl;
```
-``` sql
+```sql
DESCRIBE ${table_name} PARTITION ($partition_name);
```
-两种方式可结合使用：方案一精确、方案二高效。
+方案一查看的是每个值的类型，方案二查看的是每个路径的存储方式。
+
+## 限制 {#limitations}
-## 对比 JSON 类型
+- **大宽表优化**：对于会通过子列列式提取（Subcolumnization）生成大量独立子列的宽表场景（例如超过 2000 列），强烈建议开启 **V3 存储格式**。通过在建表 `PROPERTIES` 中指定 `"storage_format" = "V3"`，可以将列元数据与 Segment Footer 解耦，加快文件打开速度并降低内存占用。
+- JSON key 默认最长 255 字节（`variant_max_json_key_length`）。
+- VARIANT 列不能作为 Key 列、分区列或分桶列，也不能在表结构中嵌套在其他类型内（参见[定义 VARIANT 列](#define-a-variant-column)）。
+- 在未启用 DOC mode 时，读取整个 VARIANT 列会扫描所有子字段。对于超宽列，一般不建议直接 `SELECT variant_col`；如果整列读取是主要查询模式，建议优先使用 DOC mode。若列包含大量子字段，也可额外存储原始 JSON 的 STRING/JSONB 列，以优化如 `LIKE` 等整体匹配：
+
+```sql
+CREATE TABLE example_table (
+ id INT,
+ data_variant VARIANT
+);
+SELECT * FROM example_table WHERE data_variant LIKE '%doris%';
+
+-- 更优做法：额外保留原始 JSON 字符串列用于整体检索
+CREATE TABLE example_table (
+ id INT,
+ data_string STRING,
+ data_variant VARIANT
+);
+SELECT * FROM example_table WHERE data_string LIKE '%doris%';
+```
+
+## 对比 JSON 类型 {#compared-with-json-type}
- 存储：JSON 类型以 JSONB（行存）写入；VARIANT 写入时执行子列列式提取（Subcolumnization），压缩率更高、存储更小。
- 查询：JSON 需解析；VARIANT 直接列式扫描，通常显著更快。
@@ -756,11 +849,19 @@ DESCRIBE ${table_name} PARTITION ($partition_name);
| 第二次查询 (hot) | 86.02s | 94.82s | 789.24s |
| 第三次查询 (hot) | 83.03s | 92.29s | 743.69s |
-## FAQ
-
-1. VARIANT 中的 `null` 与 SQL `NULL` 有区别吗？
- - 没有区别，两者等价。
-2. 为什么我的查询/索引没有生效？
- - 请检查是否对路径做了正确的 CAST、是否因为类型冲突被提升为 JSONB、或是否误以为给 VARIANT“整体”建的索引可用于子列。
-3. 为什么 DECIMAL 写入 VARIANT 列时出现小数位/精度丢失？
- - 写入 VARIANT 列时，在推断子列类型时不会推断为 DECIMAL，数值会以 DOUBLE 存储，因而可能丢失末位小数。即使通过 Schema Template 将子路径显式声明为 DECIMAL（例如 `pm25 VARIANT<'xxx': DECIMAL(6, 2)>`），写入路径也会先解析为 DOUBLE 再转换为 DECIMAL，仍不能完全保证精度。如果在 JSON 中将该字段写成字符串形式（例如 `'{"num": "12.345"}'`），并配合 Schema Template 声明为对应的 DECIMAL（例如 `DECIMAL(9, 3)`），写入时会直接由字符串解析为 DECIMAL，可以保证精度。
+## FAQ {#faq}
+
+1. VARIANT 中的 `null` 与 SQL `NULL` 相同吗？
+ - 不相同。查询中计算出的 JSON `null` 是 VARIANT `null` 值，而不存在的路径是 SQL `NULL`。值写入表后，数组之外、值为 `null` 的对象成员会被移除，读取时返回 SQL `NULL`。参见 [NULL 语义](#null-semantics)。
+2. 为什么执行 `INSERT INTO t VALUES (1, '{"a": 1}')` 后，`v['a']` 返回 `NULL`？
+ - `INSERT` 会把字符串作为 VARIANT 字符串写入，不做解析；从 `s3()`、`hdfs()` 或其他字符串列执行 `INSERT INTO ... SELECT` 也是如此。请使用 `PARSE_TO_VARIANT('{"a": 1}')`，或通过 Stream Load 等导入作业导入数据。参见[写入数据](#write-data)。
+3. 为什么一整个合法的 JSON 文档被存成了字符串？
+ - 文档中可能包含超出 [-2^63, 2^64 - 1] 的整数，或超出 `DOUBLE` 范围的数值。解析器会拒绝这样的文档，默认情况下它会保留为字符串。参见[解析错误](#parse-errors)。
+4. 为什么我的查询/索引没有生效？
+ - 请检查是否把路径 CAST 为其存储类型（隐式的数值比较无法使用索引）、是否因为类型冲突被提升为 JSONB、或是否误以为给 VARIANT“整体”建的索引可用于子列。
+5. 为什么 `ORDER BY v['a']` 把 `"10"` 排在 `9` 之后，或者 `GROUP BY v['a']` 把 `1` 和 `"1"` 分成两组？
+ - VARIANT 的排序和相等判断首先看值的种类：数值排在字符串之前，数值永远不等于字符串。需要数值语义或字典序时，请把路径 CAST 为同一类型。参见[比较、分组与排序](#comparison-grouping-and-ordering)。
+6. 为什么 `v['a']` 是字符串时，`COALESCE(v['a'], 0)` 返回 `0.000000000`，而 `IF(..., v['a'], 1)` 返回 `NULL`？
+ - 把 VARIANT 与其他类型混用时，VARIANT 值会被转换为另一侧的类型，另一侧是整数时为 `DECIMAL(38, 9)`。`"abc"` 这样的字符串无法转换，变为 `NULL`，所以 `COALESCE` 返回的 `0` 显示为 `0.000000000`。请写成 `COALESCE(v['a'], CAST(0 AS VARIANT))` 以保持 VARIANT 结果，或把 `v['a']` CAST 为所需类型。
+7. 为什么 DECIMAL 写入 VARIANT 列时出现小数位/精度丢失？
+ - JSON 中带小数部分的数值会推断为 `DOUBLE` 而不是 `DECIMAL`，因此可能丢失末位小数。即使在 Schema Template 中把路径声明为 `DECIMAL`（例如 `v VARIANT<'num': DECIMAL(9, 3)>`），写入时也会先解析为 `DOUBLE` 再转换，仍不能完全保证精度。应在 JSON 文档中把该值写成字符串，例如 `PARSE_TO_VARIANT('{"num": "12.345"}')`，写入时会直接由字符串转换为 `DECIMAL(9, 3)`，不丢精度。
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md
index cdc711a2ef5..943c0a8febc 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/semi-structured/variant-workload-guide.md
@@ -2,7 +2,7 @@
{
"title": "VARIANT 使用与配置指南",
"language": "zh-CN",
- "description": "帮助用户判断何时使用 VARIANT、宽列场景如何在默认模式、Sparse 和 DOC mode 之间做选择，以及如何确定起步配置。"
+ "description": "帮助用户判断何时使用 VARIANT、宽列场景如何在默认模式、Sparse 和 DOC mode 之间做选择，何时使用 Schema Template，以及如何确定起步配置。"
}
---
@@ -16,7 +16,7 @@
- JSON 很宽时，应该先用默认模式、Sparse，还是 DOC mode？
- 哪些配置先保持默认，哪些配置才值得优先调整？
-如果你已经确定要使用 `VARIANT`，只是想查语法或类型规则，请直接看 [VARIANT](./VARIANT)。如果你只需要一个最小可运行的导入示例，请看 [导入 Variant 数据](../../../../data-operate/import/complex-types/variant)。
+如果你已经确定要使用 `VARIANT`，只是想查语法或类型规则，请直接看 [VARIANT](./VARIANT.md)。如果你只需要一个最小可运行的导入示例，请看 [导入 Variant 数据](../../../../data-operate/import/complex-types/variant.md)。
:::tip 为什么使用 VARIANT
`VARIANT` 保留了 JSON 的灵活性，同时又能让常用路径通过子列列式提取（Subcolumnization）获得类似普通列的裁剪、聚合和索引能力，不必先把整份文档固化成静态 schema。对超宽 JSON，存储层优化也让更大规模的 Subcolumnization 保持可控。
@@ -34,7 +34,7 @@
当以下条件更占主导时，优先考虑静态列：
- schema 稳定，并且可以提前定义清楚。
-- 核心字段经常作为 Join Key、排序键，或者必须严格类型治理。
+- 核心字段经常作为 Join Key、排序键，或者必须严格类型治理。VARIANT 值虽然可以参与 Join 和排序，但静态类型列更快，也保留类型本身的语义。
- 主要诉求是原样存档 JSON，而不是按路径做分析。
## 先回答四个问题
@@ -60,7 +60,7 @@
## 关键概念
-阅读下面的存储模式之前，先确认以下术语清晰。每个概念用 2-3 行讲清边界；实现细节请参考 [VARIANT](./VARIANT)。
+阅读下面的存储模式之前，先确认以下术语清晰。每个概念用 2-3 行讲清边界；实现细节请参考 [VARIANT](./VARIANT.md)。
**子列列式提取（Subcolumnization）。** 写入 `VARIANT` 列时，Doris 会自动发现 JSON Path，并对热点路径执行子列列式提取，使其以独立子列的形式参与分析。
@@ -212,6 +212,7 @@ PROPERTIES (
注意：
- DOC mode 不是所有宽 JSON 场景的默认答案。若核心诉求是热点路径分析，通常还是 Sparse 更合适。
- DOC mode 和 Sparse 互斥，不能同时开启。
+- 带 Schema Template 的列除了 `DROP`、`RENAME` 和修改注释之外不能 ALTER，因此之后也无法修改它的 `variant_doc_materialization_min_rows`，请在建表时确定这个值。
### Schema Template 模式
@@ -243,6 +244,7 @@ PROPERTIES (
注意：
- 不要试图把整个 JSON 都静态模板化，这会削弱 `VARIANT` 的意义。
- Schema Template 只用于关键路径，其余保持动态。
+- 列创建后不能修改 Schema Template。请在导入生产数据之前确定关键路径。
## 性能
@@ -277,7 +279,8 @@ PROPERTIES (
### 查询阶段
- **不要把 `SELECT *` 当成超宽 `VARIANT` 列的主查询模式。** 没有 DOC mode 时，`SELECT *` 或 `SELECT variant_col` 需要从所有子列重组 JSON，远慢于指定路径查询如 `SELECT v['path']`。
-- **查询依赖路径类型时，务必显式 CAST。** 自动推断的类型可能与预期不一致。如果 `v['id']` 实际存储为 STRING，但你用整数字面量做比较，索引不会被使用，结果也可能不正确。
+- **过滤条件中把子路径 CAST 为其存储类型。** `v['id'] = 123` 这样的比较会隐式转换路径并逐行求值，无法利用 zone map、BloomFilter 和索引。请写成 `CAST(v['id'] AS BIGINT) = 123`，其中的类型用路径的存储类型（执行 `SET describe_extend_variant_column = true` 后用 `DESC` 查看）。字符串路径与字符串字面量比较时不需要 CAST。
+- **弄清楚比较使用的是哪种语义。** 路径与字面量比较时，路径会根据字面量 CAST 为具体类型；而比较两个 VARIANT 值，或按 VARIANT 值分组、Join、排序时，使用 VARIANT 语义：数值排在字符串之前，`1` 永远不等于 `"1"`。参见[比较、分组与排序](./VARIANT.md#comparison-grouping-and-ordering)。
### 运维阶段
@@ -290,11 +293,11 @@ PROPERTIES (
建表后，用以下最小序列验证一切正常：
```sql
--- 插入示例数据
+-- 插入示例数据。INSERT 会把普通字符串作为 VARIANT 字符串写入，因此需要解析 JSON 文本。
INSERT INTO event_log VALUES
- ('2025-01-01 10:00:00', 1001, 'click', '{"page": "home", "user_id": 42, "duration_ms": 320}'),
- ('2025-01-01 10:00:01', 1002, 'purchase', '{"item": "widget", "price": 9.99, "user_id": 42}'),
- ('2025-01-01 10:00:02', 1003, 'click', '{"page": "search", "user_id": 99, "query": "doris variant"}');
+ ('2025-01-01 10:00:00', 1001, 'click', PARSE_TO_VARIANT('{"page": "home", "user_id": 42, "duration_ms": 320}')),
+ ('2025-01-01 10:00:01', 1002, 'purchase', PARSE_TO_VARIANT('{"item": "widget", "price": 9.99, "user_id": 42}')),
+ ('2025-01-01 10:00:02', 1003, 'click', PARSE_TO_VARIANT('{"page": "search", "user_id": 99, "query": "doris variant"}'));
-- 验证数据
SELECT payload['user_id'], payload['page'] FROM event_log;
@@ -303,13 +306,13 @@ SELECT payload['user_id'], payload['page'] FROM event_log;
SET describe_extend_variant_column = true;
DESC event_log;
--- 查看每行类型
-SELECT variant_type(payload) FROM event_log;
+-- 逐行查看某个路径的类型
+SELECT variant_type(payload['user_id']) FROM event_log;
```
## 延伸阅读
-- [VARIANT](./VARIANT)
-- [导入 Variant 数据](../../../../data-operate/import/complex-types/variant)
-- [Storage Format V3](../../../../table-design/storage-format)
-- [SEARCH Function](../../../../table-design/index/inverted-index/search-function)
+- [VARIANT](./VARIANT.md)
+- [导入 Variant 数据](../../../../data-operate/import/complex-types/variant.md)
+- [Storage Format V3](../../../../table-design/storage-format.md)
+- [SEARCH Function](../../../../table-design/index/inverted-index/search-function.md)
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
index 1899533f092..03f8dcb96b9 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/element-at.md
@@ -29,22 +29,22 @@ ELEMENT_AT(container, key_or_index)
- 对于 `MAP`：为 `MAP` 中的键类型（`K`），可为任意支持的基础类型。
- 对于 `STRUCT`：为常量整数（字段位置，从 **1** 开始）或常量字符串（字段名，按**大小写不敏感**匹配）。
- 对于 `VARIANT` 对象访问：为字符串类型的 key；
- - 对于 Doris 4.2 及后续版本中的 VARIANT 数组：为整数索引，正数索引从 1 开始，负数索引从数组末尾倒数。
+ - 对于 Doris 5.0.0 及后续版本中的 VARIANT 数组：为整数索引，正数索引从 1 开始，负数索引从数组末尾倒数。
## 返回值
- 若为 `ARRAY`，返回数组中对应索引的元素（`T` 类型）；
- 若为 `MAP`，返回对应键的值（`V` 类型）；
- 若为 `STRUCT`，返回指定的子列值；
-- 若为 `VARIANT`， 返回 `VARIANT` 类型；
+- 若为 `VARIANT`，返回 `VARIANT` 类型。key 不存在、下标为 `0` 或越界、对数组使用字符串 key、对对象使用整数下标、对标量值使用 key，都返回 `NULL`；
- 如果索引或键不存在，返回 `NULL`（对于 `STRUCT`，位置越界或字段名不存在会报错）；
- 如果参数为 `NULL`，返回 `NULL`。
## 使用说明
-1. **ARRAY 数组以及 Doris 4.2 及后续版本中的 VARIANT 数组，索引都从 1 开始**，不是从 0 开始；
+1. **ARRAY 数组以及 Doris 5.0.0 及后续版本中的 VARIANT 数组，索引都从 1 开始**，不是从 0 开始；
2. ARRAY 和 VARIANT 数组都支持负数索引，`-1` 表示最后一个元素，`-2` 表示倒数第二个，以此类推；
-3. `ELEMENT_AT(container, key_or_index)` 函数的功能与 `container[key_or_index]` 作用一致（详细见示例）。
+3. `ELEMENT_AT(container, key_or_index)` 函数的功能与 `container[key_or_index]` 作用一致（详细见示例）；
```sql
SELECT ELEMENT_AT(parse_to_variant('[10, 20, 30]'), 1); -- 10
@@ -52,6 +52,8 @@ SELECT ELEMENT_AT(parse_to_variant('[10, 20, 30]'), 2); -- 20
SELECT ELEMENT_AT(parse_to_variant('[10, 20, 30]'), -1); -- 30
```
+4. 对于 `VARIANT`，在查询中计算出的值里，值为 JSON `null` 的成员会返回 VARIANT `null`，它不是 SQL `NULL`；表中不会存储数组之外、值为 `null` 的对象成员，因此对从表中读取的数据做同样的访问会返回 SQL `NULL`。参见 [NULL 语义](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#null-semantics)。
+
## 示例
1. `ELEMENT_AT` 函数的功能与 `[]` 作用一致。
@@ -119,7 +121,7 @@ SELECT ELEMENT_AT(parse_to_variant('[10, 20, 30]'), -1); -- 30
+--------------------------------------------------+
```
-5. 访问 `VARIANT` 的某个子列，如果 `VARIANT` 的值不是 OBJECT，返回空
+5. 访问 `VARIANT` 的某个子列，如果 `VARIANT` 的值不是 OBJECT，返回 `NULL`
```SQL
SELECT ELEMENT_AT(PARSE_TO_VARIANT('{"a": 1, "b": 2}'), "a");
@@ -133,6 +135,6 @@ SELECT ELEMENT_AT(parse_to_variant('[10, 20, 30]'), -1); -- 30
+-------------------------------------------+
| ELEMENT_AT(PARSE_TO_VARIANT('123'), "") |
+-------------------------------------------+
- | |
+ | NULL |
+-------------------------------------------+
```
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/parse-to-variant.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/parse-to-variant.md
index e5d06dc43e0..b7dc7680524 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/parse-to-variant.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/parse-to-variant.md
@@ -8,7 +8,7 @@
## 功能
-`PARSE_TO_VARIANT` 将一个完整 JSON 值解析为 `VARIANT`，支持 JSON 对象、数组、字符串、数字、布尔值和 JSON 字面量 `null`。该函数自 Doris 4.1.4 起支持。
+`PARSE_TO_VARIANT` 将一个完整 JSON 值解析为 `VARIANT`，支持 JSON 对象、数组、字符串、数字、布尔值和 JSON 字面量 `null`。该函数自 Doris 4.1.4 起支持；本页描述的是 Doris 5.0.0 及之后版本中的行为。
## 语法
@@ -27,8 +27,11 @@ PARSE_TO_VARIANT(<json_value>)
返回 `VARIANT` 值。
- 输入为 SQL `NULL` 时返回 SQL `NULL`。
-- 输入为 JSON 字面量 `null` 时返回 Variant/JSON `null`，它与 SQL `NULL` 不同。
-- JSON 非法、对象 key 重复且当前校验设置不允许、嵌套深度超限或发生其他校验错误时，查询失败。
+- 输入为 JSON 字面量 `null` 时返回 VARIANT `null`，它与 SQL `NULL` 不同。
+- 非法 JSON 文本会作为 VARIANT 字符串返回；包含超出 [-2^63, 2^64 - 1] 的整数或超出 `DOUBLE` 范围的数值的 JSON 也是如此，整段文本变为一个字符串。该行为由 BE 配置 `variant_throw_exeception_on_invalid_json`（默认 `false`）控制；设置为 `true` 后，这类输入会使查询失败。
+- 输入为空字符串时返回空对象 `{}`。
+- 嵌套深到 JSON 解析器本身拒绝的文档（约 1000 层）属于非法 JSON，同样作为字符串返回。
+- 以下情况查询失败：对象 key 超过 `variant_max_json_key_length` 字节（BE 配置，默认 255）；同一对象中有重复 key（BE 配置 `variant_enable_duplicate_json_path_check` 为 `true` 时保留第一个值，不报错）；嵌套超过 128 层；字符串不是合法的 UTF-8。
## 示例
@@ -42,11 +45,11 @@ SELECT CAST(
```
```text
-+----------------------------------------+
-| value |
-+----------------------------------------+
-| {"id":42,"tags":["doris","sql"]} |
-+----------------------------------------+
++----------------------------------+
+| value |
++----------------------------------+
+| {"id":42,"tags":["doris","sql"]} |
++----------------------------------+
```
解析 JSON/JSONB 表达式：
@@ -97,17 +100,32 @@ SELECT PARSE_TO_VARIANT(NULL) IS NULL AS is_sql_null;
+-------------+
```
-非法 JSON 会报错：
+非法 JSON 文本会保留为字符串：
```sql
-SELECT PARSE_TO_VARIANT('{"id":');
+SELECT CAST(PARSE_TO_VARIANT('{"id":') AS STRING) AS value,
+ VARIANT_TYPE(PARSE_TO_VARIANT('{"id":')) AS type;
```
```text
-ERROR: Parse json document failed
++--------+--------+
+| value | type |
++--------+--------+
+| {"id": | string |
++--------+--------+
+```
+
+重复 key 会报错：
+
+```sql
+SELECT PARSE_TO_VARIANT('{"id": 1, "id": 2}');
+```
+
+```text
+ERROR 1105 (HY000): errCode = 2, detailMessage = [INVALID_ARGUMENT]Parse json document failed at row 0, error: [INVALID_ARGUMENT]Duplicate Variant object key
```
## 使用说明
-- 如果希望把非法输入转换为 SQL `NULL`，请使用 [TRY_PARSE_TO_VARIANT](./try-parse-to-variant)。
-- `PARSE_TO_VARIANT` 会显式解析 JSON。相比之下，`CAST(string AS VARIANT)` 会把输入保留为 VARIANT 字符串，不解析 JSON，详见 [VARIANT CAST 规则](../../../basic-element/sql-data-types/semi-structured/VARIANT#cast-规则)。
+- 如果希望解析错误返回 SQL `NULL` 而不是使查询失败，请使用 [TRY_PARSE_TO_VARIANT](./try-parse-to-variant.md)。
+- `PARSE_TO_VARIANT` 会显式解析 JSON。相比之下，`CAST(string AS VARIANT)` 以及通过 `INSERT` 把字符串写入 VARIANT 列，都会把输入保留为 VARIANT 字符串，不解析 JSON。用 `INSERT` 写入 JSON 文本时，请用 `PARSE_TO_VARIANT` 包裹。详见[写入数据](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#write-data)与[其他类型 CAST 为 VARIANT](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#cast-to-variant)。
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md
index d1bc0c5a6b0..40e0eea6238 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/try-parse-to-variant.md
@@ -2,13 +2,13 @@
{
"title": "TRY_PARSE_TO_VARIANT",
"language": "zh-CN",
- "description": "TRY_PARSE_TO_VARIANT 尝试把完整 JSON 值解析为 VARIANT，并在解析或校验失败时返回 SQL NULL，而不是使当前查询直接失败。"
+ "description": "TRY_PARSE_TO_VARIANT 把一个完整的 JSON 值解析为 VARIANT。遇到超长 key、重复 key 等解析错误时返回 SQL NULL 而不是报错；非法 JSON 文本默认保留为 VARIANT 字符串。"
}
---
## 功能
-`TRY_PARSE_TO_VARIANT` 尝试把一个完整 JSON 值解析为 `VARIANT`。函数名中的 `TRY_` 表示：发生解析或校验错误时返回 SQL `NULL`，而不是使查询失败。该函数自 Doris 4.1.4 起支持。
+`TRY_PARSE_TO_VARIANT` 尝试把一个完整 JSON 值解析为 `VARIANT`。函数名中的 `TRY_` 表示：发生解析错误时返回 SQL `NULL`，而不是使查询失败。默认情况下，非法 JSON 文本不算解析错误，详见“返回值”。该函数自 Doris 4.1.4 起支持；本页描述的是 Doris 5.0.0 及之后版本中的行为。
## 语法
@@ -27,29 +27,31 @@ TRY_PARSE_TO_VARIANT(<json_value>)
返回可为 NULL 的 `VARIANT` 值。
- 合法输入返回解析后的 VARIANT 值。
-- 非法 JSON 或其他解析、校验错误返回 SQL `NULL`。
+- 以下解析错误返回 SQL `NULL`：对象 key 超过 `variant_max_json_key_length` 字节（BE 配置，默认 255）、同一对象中有重复 key（BE 配置 `variant_enable_duplicate_json_path_check` 为 `true` 时保留第一个值，不返回 NULL）、嵌套超过 128 层、字符串不是合法的 UTF-8。
+- 非法 JSON 文本，以及包含超出 [-2^63, 2^64 - 1] 的整数或超出 `DOUBLE` 范围的数值的 JSON，与 [PARSE_TO_VARIANT](./parse-to-variant.md) 一样作为 VARIANT 字符串返回。只有当 BE 配置 `variant_throw_exeception_on_invalid_json` 为 `true`（默认 `false`）时，才返回 SQL `NULL`。
+- 输入为空字符串时返回空对象 `{}`。
- 输入为 SQL `NULL` 时返回 SQL `NULL`。
-- 合法的 JSON 字面量 `null` 返回 Variant/JSON `null`，不是 SQL `NULL`。
+- JSON 字面量 `null` 返回 VARIANT `null`，不是 SQL `NULL`。
## 示例
-保留合法值，并把非法 JSON 转换为 SQL `NULL`：
+保留合法值，并把解析错误转换为 SQL `NULL`：
```sql
SELECT CAST(
TRY_PARSE_TO_VARIANT('{"id": 1}')
AS STRING
) AS valid_value,
- TRY_PARSE_TO_VARIANT('{"id":') IS NULL AS invalid_is_null,
+ TRY_PARSE_TO_VARIANT('{"id": 1, "id": 2}') IS NULL AS duplicate_is_null,
TRY_PARSE_TO_VARIANT(NULL) IS NULL AS input_is_null;
```
```text
-+-------------+-----------------+---------------+
-| valid_value | invalid_is_null | input_is_null |
-+-------------+-----------------+---------------+
-| {"id":1} | 1 | 1 |
-+-------------+-----------------+---------------+
++-------------+-------------------+---------------+
+| valid_value | duplicate_is_null | input_is_null |
++-------------+-------------------+---------------+
+| {"id":1} | 1 | 1 |
++-------------+-------------------+---------------+
```
解析 JSON/JSONB 输入：
@@ -69,22 +71,23 @@ SELECT CAST(
+------------+
```
-JSON `null` 与 SQL `NULL` 仍然不同：
+JSON `null` 不等于 SQL `NULL`；非法 JSON 文本会保留为字符串：
```sql
SELECT TRY_PARSE_TO_VARIANT('null') IS NULL AS json_null_is_sql_null,
- TRY_PARSE_TO_VARIANT('{') IS NULL AS error_is_sql_null;
+ VARIANT_TYPE(TRY_PARSE_TO_VARIANT('{"id":')) AS invalid_json_type;
```
```text
+-----------------------+-------------------+
-| json_null_is_sql_null | error_is_sql_null |
+| json_null_is_sql_null | invalid_json_type |
+-----------------------+-------------------+
-| 0 | 1 |
+| 0 | string |
+-----------------------+-------------------+
```
## 使用说明
-- 如果非法输入应该使查询失败并暴露数据质量问题，请使用 [PARSE_TO_VARIANT](./parse-to-variant)。
-- 本函数只会把解析和校验错误转换为 SQL `NULL`，不会改变合法 JSON `null` 的含义。
+- 如果解析错误应该使查询失败并暴露数据质量问题，请使用 [PARSE_TO_VARIANT](./parse-to-variant.md)。
+- 本函数只会把解析错误转换为 SQL `NULL`，不会改变合法 JSON `null` 的含义。
+- Stream Load 等导入作业按与本函数相同的规则解析 JSON 文本，参见[解析错误](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#parse-errors)。
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/variant-type.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/variant-type.md
index c58fdd72326..7ddf65879c4 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/variant-type.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/variant-functions/variant-type.md
@@ -2,14 +2,14 @@
{
"title": "VARIANT_TYPE",
"language": "zh-CN",
- "description": "VARIANTTYPE 函数用于返回 VARIANT 类型值的实际类型。 该函数通常用于调试或分析 VARIANT 数据的结构，辅助进行类型判断和数据处理。"
+ "description": "VARIANT_TYPE 函数返回 VARIANT 值的类型名称，例如 object、array、string、bigint、double。该函数通常用于调试或分析 VARIANT 数据的结构，辅助进行类型判断和数据处理。"
}
---
## 功能
-`VARIANT_TYPE` 函数用于返回 `VARIANT` 类型值的实际类型。
-该函数通常用于调试或分析 `VARIANT` 数据的结构，辅助进行类型判断和数据处理。
+`VARIANT_TYPE` 函数用于返回 `VARIANT` 值的类型名称。
+该函数通常用于调试或分析 `VARIANT` 数据的结构，辅助进行类型判断和数据处理。本页描述的是 Doris 5.0.0 及之后版本中的行为。
## 语法
@@ -19,42 +19,67 @@ VARIANT_TYPE(variant_value)
## 参数
-- `variant_value`：一个 `VARIANT` 类型的值。
+- `variant_value`：一个 `VARIANT` 类型的值。要查看嵌套值的类型，请传入路径，例如 `VARIANT_TYPE(v['a'])`。
## 返回值
-- 返回一个字符串，表示该 `VARIANT` 值的实际类型。
- - 字符串的结构是 `{"key":"value"}` 结构
- - key 表示子列的 path，value 表示类型
+返回一个 `STRING`，表示该值本身的类型名称。对于对象或数组，返回 `object` 或 `array`，不会列出其成员的类型。
+
+| 结果 | 对应的值 |
+| --- | --- |
+| `object`、`array` | JSON 对象或数组 |
+| `string` | 字符串 |
+| `bool` | `true` 或 `false` |
+| `tinyint`、`smallint`、`int`、`bigint` | 整数，按能容纳该值的最小类型报告 |
+| `decimal` | 定点数，包括超出 `BIGINT` 范围的整数，以及不超过 38 位的 `LARGEINT` 值 |
+| `float`、`double` | 浮点数 |
+| `date` | 日期 |
+| `timestamp` | 带时区的时间戳 |
+| `timestamp_ntz` | 不带时区的时间戳，如 `DATETIME`、`TIMESTAMP_NS` 值 |
+| `null` | VARIANT `null`（JSON `null`） |
+| `binary`、`time`、`uuid` | Doris 的 JSON 解析和 CAST 都不会产生这些类型的值，它们可能来自其他系统写入的 VARIANT 数据，例如 Parquet 文件 |
+
+输入为 SQL `NULL` 时返回 SQL `NULL`。
## 使用说明
-1. 用于查找 `VARIANT` 类型的列中实际存储的类型；
-2. 对于表中的每一行都会读取子列获取类型，实际使用中用 LIMIT 限制行数以避免执行速度太慢。
+1. 用于查看 `VARIANT` 列中值的实际类型。对于从表中读取的值，结果反映的是存储后的值，例如根值中的 `DATE` 会以字符串存储。参见[写入后值的规范化](../../../basic-element/sql-data-types/semi-structured/VARIANT.md#what-storage-keeps)。
+2. 结果是值的类型，而不是路径的存储类型：从 `BIGINT` 路径读出的整数可能是 `tinyint`。如需查看表中每个子列的存储类型，请使用 `SET describe_extend_variant_column = true;` 后执行 `DESC`。
+3. 函数会读取每一行，实际使用中请用 `LIMIT` 限制行数。
## 示例
- ```SQL
- CREATE TABLE variant_table(
- k INT,
- v VARIANT NULL
- )
- DUPLICATE KEY(`k`)
- DISTRIBUTED BY HASH(`k`) BUCKETS 1
- PROPERTIES (
- "replication_num" = "1"
- );
-
- INSERT INTO variant_table VALUES(1, '{"a": 10, "b": 1.2, "c" : "ddddd"}'), (2, NULL);
-
- SELECT VARIANT_TYPE(v) FROM variant_table;
- +-------------------------------------------+
- | VARIANT_TYPE(v) |
- +-------------------------------------------+
- | {"a":"tinyint","b":"double","c":"string"} |
- | NULL |
- +-------------------------------------------+
-
- ```
-
+```SQL
+CREATE TABLE variant_table(
+ k INT,
+ v VARIANT NULL
+)
+DUPLICATE KEY(`k`)
+DISTRIBUTED BY HASH(`k`) BUCKETS 1
+PROPERTIES (
+ "replication_num" = "1"
+);
+
+INSERT INTO variant_table VALUES
+ (1, PARSE_TO_VARIANT('{"a": 10, "b": 1.2, "c": "ddddd"}')),
+ (2, PARSE_TO_VARIANT('[1, 2]')),
+ (3, NULL);
+
+SELECT k,
+ VARIANT_TYPE(v) AS root_type,
+ VARIANT_TYPE(v['a']) AS a_type,
+ VARIANT_TYPE(v['b']) AS b_type,
+ VARIANT_TYPE(v['c']) AS c_type
+FROM variant_table
+ORDER BY k;
+```
+```text
++------+-----------+---------+--------+--------+
+| k | root_type | a_type | b_type | c_type |
++------+-----------+---------+--------+--------+
+| 1 | object | tinyint | double | string |
+| 2 | array | NULL | NULL | NULL |
+| 3 | NULL | NULL | NULL | NULL |
++------+-----------+---------+--------+--------+
+```
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org