此类型表示由其他数据类型构成的联合类型。类型 Variant(T1, T2, ..., TN) 表示该类型的每一行都包含一个值，该值要么是类型 T1，要么是 T2，……，要么是 TN，或者都不是（即为 NULL 值）。
嵌套类型的顺序无关紧要：Variant(T1, T2) = Variant(T2, T1)。
嵌套类型可以是除 Nullable(...)、LowCardinality(Nullable(...)) 和 Variant(...) 之外的任意类型。
注意
不建议将相似的类型作为变体（例如不同的数值类型，如 Variant(UInt32, Int64)，或不同的日期类型，如 Variant(Date, DateTime)），
因为处理这类类型的值可能会导致歧义。默认情况下，创建此类 Variant 类型会抛出异常，但可以通过设置 allow_suspicious_variant_types 来放宽此限制。
创建 Variant 类型
在表的列定义中使用 Variant 类型：
CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT v FROM test;
┌─v─────────────┐
│ ᴺᵁᴸᴸ │
│ 42 │
│ Hello, World! │
│ [1,2,3] │
└───────────────┘
对普通列使用 CAST：
SELECT toTypeName(variant) AS type_name, 'Hello, World!'::Variant(UInt64, String, Array(UInt64)) as variant;
┌─type_name──────────────────────────────┬─variant───────┐
│ Variant(Array(UInt64), String, UInt64) │ Hello, World! │
└────────────────────────────────────────┴───────────────┘
在参数之间不存在公共类型时使用函数 if/multiIf（需要启用设置 use_variant_as_common_type）：
SET use_variant_as_common_type = 1;
SELECT if(number % 2, number, range(number)) as variant FROM numbers(5);
┌─variant───┐
│ [] │
│ 1 │
│ [0,1] │
│ 3 │
│ [0,1,2,3] │
└───────────┘
SET use_variant_as_common_type = 1;
SELECT multiIf((number % 4) = 0, 42, (number % 4) = 1, [1, 2, 3], (number % 4) = 2, 'Hello, World!', NULL) AS variant FROM numbers(4);
┌─variant───────┐
│ 42 │
│ [1,2,3] │
│ Hello, World! │
│ ᴺᵁᴸᴸ │
└───────────────┘
如果数组元素或映射值没有共同类型，请使用函数 'array/map'（需启用设置 use_variant_as_common_type）：
SET use_variant_as_common_type = 1;
SELECT array(range(number), number, 'str_' || toString(number)) as array_of_variants FROM numbers(3);
┌─array_of_variants─┐
│ [[],0,'str_0'] │
│ [[0],1,'str_1'] │
│ [[0,1],2,'str_2'] │
└───────────────────┘
SET use_variant_as_common_type = 1;
SELECT map('a', range(number), 'b', number, 'c', 'str_' || toString(number)) as map_of_variants FROM numbers(3);
┌─map_of_variants───────────────┐
│ {'a':[],'b':0,'c':'str_0'} │
│ {'a':[0],'b':1,'c':'str_1'} │
│ {'a':[0,1],'b':2,'c':'str_2'} │
└───────────────────────────────┘
以子列形式读取 Variant 中的嵌套类型
Variant 类型支持使用类型名作为子列，从 Variant 列中读取单个嵌套类型。
因此，如果有列 variant Variant(T1, T2, T3)，可以使用语法 variant.T2 读取类型为 T2 的子列。
当 T2 可以出现在 Nullable 中时，该子列的类型为 Nullable(T2)，否则为 T2。该子列的
大小与原始 Variant 列相同，在原始 Variant 列不是类型 T2 的所有行中，它将包含 NULL 值
（如果 T2 不能出现在 Nullable 中，则为对应的空值）。
也可以使用函数 variantElement(variant_column, type_name) 读取 Variant 子列。
示例：
CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT v, v.String, v.UInt64, v.`Array(UInt64)` FROM test;
┌─v─────────────┬─v.String──────┬─v.UInt64─┬─v.Array(UInt64)─┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [] │
│ 42 │ ᴺᵁᴸᴸ │ 42 │ [] │
│ Hello, World! │ Hello, World! │ ᴺᵁᴸᴸ │ [] │
│ [1,2,3] │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [1,2,3] │
└───────────────┴───────────────┴──────────┴─────────────────┘
SELECT toTypeName(v.String), toTypeName(v.UInt64), toTypeName(v.`Array(UInt64)`) FROM test LIMIT 1;
┌─toTypeName(v.String)─┬─toTypeName(v.UInt64)─┬─toTypeName(v.Array(UInt64))─┐
│ Nullable(String) │ Nullable(UInt64) │ Array(UInt64) │
└──────────────────────┴──────────────────────┴─────────────────────────────┘
SELECT v, variantElement(v, 'String'), variantElement(v, 'UInt64'), variantElement(v, 'Array(UInt64)') FROM test;
┌─v─────────────┬─variantElement(v, 'String')─┬─variantElement(v, 'UInt64')─┬─variantElement(v, 'Array(UInt64)')─┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [] │
│ 42 │ ᴺᵁᴸᴸ │ 42 │ [] │
│ Hello, World! │ Hello, World! │ ᴺᵁᴸᴸ │ [] │
│ [1,2,3] │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [1,2,3] │
└───────────────┴─────────────────────────────┴─────────────────────────────┴────────────────────────────────────┘
要了解每一行中存储的是哪种变体类型，可以使用函数 variantType(variant_column)。它返回一个 Enum，其中包含每行对应的变体类型名称（如果该行为 NULL，则为 'None'）。
示例：
CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT variantType(v) FROM test;
┌─variantType(v)─┐
│ None │
│ UInt64 │
│ String │
│ Array(UInt64) │
└────────────────┘
SELECT toTypeName(variantType(v)) FROM test LIMIT 1;
┌─toTypeName(variantType(v))──────────────────────────────────────────┐
│ Enum8('None' = -1, 'Array(UInt64)' = 0, 'String' = 1, 'UInt64' = 2) │
└─────────────────────────────────────────────────────────────────────┘
在 Variant 列与其他列之间进行转换
对于类型为 Variant 的列，可以执行 4 种转换。
将 String 列转换为 Variant 列
从 String 到 Variant 的转换是通过从字符串值中解析出一个 Variant 类型的值来完成的：
SELECT '42'::Variant(String, UInt64) AS variant, variantType(variant) AS variant_type
┌─variant─┬─variant_type─┐
│ 42 │ UInt64 │
└─────────┴──────────────┘
SELECT '[1, 2, 3]'::Variant(String, Array(UInt64)) as variant, variantType(variant) as variant_type
┌─variant─┬─variant_type──┐
│ [1,2,3] │ Array(UInt64) │
└─────────┴───────────────┘
SELECT CAST(map('key1', '42', 'key2', 'true', 'key3', '2020-01-01'), 'Map(String, Variant(UInt64, Bool, Date))') AS map_of_variants, mapApply((k, v) -> (k, variantType(v)), map_of_variants) AS map_of_variant_types```
┌─map_of_variants─────────────────────────────┬─map_of_variant_types──────────────────────────┐
│ {'key1':42,'key2':true,'key3':'2020-01-01'} │ {'key1':'UInt64','key2':'Bool','key3':'Date'} │
└─────────────────────────────────────────────┴───────────────────────────────────────────────┘
To disable parsing during conversion from String to Variant you can disable setting cast_string_to_dynamic_use_inference:
SET cast_string_to_variant_use_inference = 0;
SELECT '[1, 2, 3]'::Variant(String, Array(UInt64)) as variant, variantType(variant) as variant_type
┌─variant───┬─variant_type─┐
│ [1, 2, 3] │ String │
└───────────┴──────────────┘
Converting an ordinary column to a Variant column
It is possible to convert an ordinary column with type T to a Variant column containing this type:
SELECT toTypeName(variant) AS type_name, [1,2,3]::Array(UInt64)::Variant(UInt64, String, Array(UInt64)) as variant, variantType(variant) as variant_name
┌─type_name──────────────────────────────┬─variant─┬─variant_name──┐
│ Variant(Array(UInt64), String, UInt64) │ [1,2,3] │ Array(UInt64) │
└────────────────────────────────────────┴─────────┴───────────────┘
Note: converting from String type is always performed through parsing, if you need to convert String column to String variant of a Variant without parsing, you can do the following:
SELECT '[1, 2, 3]'::Variant(String)::Variant(String, Array(UInt64), UInt64) as variant, variantType(variant) as variant_type
┌─variant───┬─variant_type─┐
│ [1, 2, 3] │ String │
└───────────┴──────────────┘
Converting a Variant column to an ordinary column
It is possible to convert a Variant column to an ordinary column. In this case all nested variants will be converted to a destination type:
CREATE TABLE test (v Variant(UInt64, String)) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('42.42');
SELECT v::Nullable(Float64) FROM test;
┌─CAST(v, 'Nullable(Float64)')─┐
│ ᴺᵁᴸᴸ │
│ 42 │
│ 42.42 │
└──────────────────────────────┘
Converting a Variant to another Variant
It is possible to convert a Variant column to another Variant column, but only if the destination Variant column contains all nested types from the original Variant:
CREATE TABLE test (v Variant(UInt64, String)) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('String');
SELECT v::Variant(UInt64, String, Array(UInt64)) FROM test;
┌─CAST(v, 'Variant(UInt64, String, Array(UInt64))')─┐
│ ᴺᵁᴸᴸ │
│ 42 │
│ String │
└───────────────────────────────────────────────────┘
Reading Variant type from the data
All text formats (TSV, CSV, CustomSeparated, Values, JSONEachRow, etc) supports reading Variant type. During data parsing ClickHouse tries to insert value into most appropriate variant type.
Example:
SELECT
v,
variantElement(v, 'String') AS str,
variantElement(v, 'UInt64') AS num,
variantElement(v, 'Float64') AS float,
variantElement(v, 'DateTime') AS date,
variantElement(v, 'Array(UInt64)') AS arr
FROM format(JSONEachRow, 'v Variant(String, UInt64, Float64, DateTime, Array(UInt64))', $$
{"v" : "Hello, World!"},
{"v" : 42},
{"v" : 42.42},
{"v" : "2020-01-01 00:00:00"},
{"v" : [1, 2, 3]}
$$)
┌─v───────────────────┬─str───────────┬──num─┬─float─┬────────────────date─┬─arr─────┐
│ Hello, World! │ Hello, World! │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [] │
│ 42 │ ᴺᵁᴸᴸ │ 42 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [] │
│ 42.42 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 42.42 │ ᴺᵁᴸᴸ │ [] │
│ 2020-01-01 00:00:00 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 2020-01-01 00:00:00 │ [] │
│ [1,2,3] │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [1,2,3] │
└─────────────────────┴───────────────┴──────┴───────┴─────────────────────┴─────────┘
Comparing values of Variant type
Values of a Variant type can be compared only with values with the same Variant type.
The result of operator < for values v1 with underlying type T1 and v2 with underlying type T2 of a type Variant(..., T1, ... T2, ...) is defined as follows:
- If
T1 = T2 = T, the result will be v1.T < v2.T (underlying values will be compared).
- If
T1 != T2, the result will be T1 < T2 (type names will be compared).
Examples:
CREATE TABLE test (v1 Variant(String, UInt64, Array(UInt32)), v2 Variant(String, UInt64, Array(UInt32))) ENGINE=Memory;
INSERT INTO test VALUES (42, 42), (42, 43), (42, 'abc'), (42, [1, 2, 3]), (42, []), (42, NULL);
SELECT v2, variantType(v2) AS v2_type FROM test ORDER BY v2;
┌─v2──────┬─v2_type───────┐
│ [] │ Array(UInt32) │
│ [1,2,3] │ Array(UInt32) │
│ abc │ String │
│ 42 │ UInt64 │
│ 43 │ UInt64 │
│ ᴺᵁᴸᴸ │ None │
└─────────┴───────────────┘
SELECT v1, variantType(v1) AS v1_type, v2, variantType(v2) AS v2_type, v1 = v2, v1 < v2, v1 > v2 FROM test;
┌─v1─┬─v1_type─┬─v2──────┬─v2_type───────┬─equals(v1, v2)─┬─less(v1, v2)─┬─greater(v1, v2)─┐
│ 42 │ UInt64 │ 42 │ UInt64 │ 1 │ 0 │ 0 │
│ 42 │ UInt64 │ 43 │ UInt64 │ 0 │ 1 │ 0 │
│ 42 │ UInt64 │ abc │ String │ 0 │ 0 │ 1 │
│ 42 │ UInt64 │ [1,2,3] │ Array(UInt32) │ 0 │ 0 │ 1 │
│ 42 │ UInt64 │ [] │ Array(UInt32) │ 0 │ 0 │ 1 │
│ 42 │ UInt64 │ ᴺᵁᴸᴸ │ None │ 0 │ 1 │ 0 │
└────┴─────────┴─────────┴───────────────┴────────────────┴──────────────┴─────────────────┘
If you need to find the row with specific Variant value, you can do one of the following:
- Cast value to the corresponding
Variant type:
SELECT * FROM test WHERE v2 == [1,2,3]::Array(UInt32)::Variant(String, UInt64, Array(UInt32));
┌─v1─┬─v2──────┐
│ 42 │ [1,2,3] │
└────┴─────────┘
- Compare
Variant subcolumn with required type:
SELECT * FROM test WHERE v2.`Array(UInt32)` == [1,2,3] -- 或者使用 variantElement(v2, 'Array(UInt32)')
┌─v1─┬─v2──────┐
│ 42 │ [1,2,3] │
└────┴─────────┘
Sometimes it can be useful to make additional check on variant type as subcolumns with complex types like Array/Map/Tuple cannot be inside Nullable and will have default values instead of NULL on rows with different types:
SELECT v2, v2.`Array(UInt32)`, variantType(v2) FROM test WHERE v2.`Array(UInt32)` == [];
┌─v2───┬─v2.Array(UInt32)─┬─variantType(v2)─┐
│ 42 │ [] │ UInt64 │
│ 43 │ [] │ UInt64 │
│ abc │ [] │ String │
│ [] │ [] │ Array(UInt32) │
│ ᴺᵁᴸᴸ │ [] │ None │
└──────┴──────────────────┴─────────────────┘
SELECT v2, v2.`Array(UInt32)`, variantType(v2) FROM test WHERE variantType(v2) == 'Array(UInt32)' AND v2.`Array(UInt32)` == [];
┌─v2─┬─v2.Array(UInt32)─┬─variantType(v2)─┐
│ [] │ [] │ Array(UInt32) │
└────┴──────────────────┴─────────────────┘
Note: values of variants with different numeric types are considered as different variants and not compared between each other, their type names are compared instead.
Example:
SET allow_suspicious_variant_types = 1;
CREATE TABLE test (v Variant(UInt32, Int64)) ENGINE=Memory;
INSERT INTO test VALUES (1::UInt32), (1::Int64), (100::UInt32), (100::Int64);
SELECT v, variantType(v) FROM test ORDER by v;
┌─v───┬─variantType(v)─┐
│ 1 │ Int64 │
│ 100 │ Int64 │
│ 1 │ UInt32 │
│ 100 │ UInt32 │
└─────┴────────────────┘
Note by default Variant type is not allowed in GROUP BY/ORDER BY keys, if you want to use it consider its special comparison rule and enable allow_suspicious_types_in_group_by/allow_suspicious_types_in_order_by settings.
All JSONExtract* functions support Variant type:
SELECT JSONExtract('{"a" : [1, 2, 3]}', 'a', 'Variant(UInt32, String, Array(UInt32))') AS variant, variantType(variant) AS variant_type;
┌─variant─┬─variant_type──┐
│ [1,2,3] │ Array(UInt32) │
└─────────┴───────────────┘
SELECT JSONExtract('{"obj" : {"a" : 42, "b" : "Hello", "c" : [1,2,3]}}', 'obj', 'Map(String, Variant(UInt32, String, Array(UInt32)))') AS map_of_variants, mapApply((k, v) -> (k, variantType(v)), map_of_variants) AS map_of_variant_types
┌─map_of_variants──────────────────┬─map_of_variant_types────────────────────────────┐
│ {'a':42,'b':'Hello','c':[1,2,3]} │ {'a':'UInt32','b':'String','c':'Array(UInt32)'} │
└──────────────────────────────────┴─────────────────────────────────────────────────┘
SELECT JSONExtractKeysAndValues('{"a" : 42, "b" : "Hello", "c" : [1,2,3]}', 'Variant(UInt32, String, Array(UInt32))') AS variants, arrayMap(x -> (x.1, variantType(x.2)), variants) AS variant_types
┌─variants───────────────────────────────┬─variant_types─────────────────────────────────────────┐
│ [('a',42),('b','Hello'),('c',[1,2,3])] │ [('a','UInt32'),('b','String'),('c','Array(UInt32)')] │
└────────────────────────────────────────┴───────────────────────────────────────────────────────┘
