title_wip

- 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] -- or using 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.

## JSONExtract functions with Variant[​](#jsonextract-functions-with-variant "Direct link to JSONExtract functions with Variant")

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)')] │
└────────────────────────────────────────┴───────────────────────────────────────────────────────┘
```

## Functions with Variant arguments[​](#functions-with-variant-arguments "Direct link to Functions with Variant arguments")

Most functions in ClickHouse automatically support `Variant` type arguments through a **default implementation for Variant**.
Starting from version `26.1` onwards, when a function that doesn't explicitly handle Variant types receives a Variant column, ClickHouse:

1. Extracts each variant type from the Variant column
2. Executes the function separately for each variant type
3. Combines results appropriately based on result types

This allows you to use regular functions with Variant columns without special handling.

**Example:**

```
CREATE TABLE test (v Variant(UInt32, String)) ENGINE = Memory;
INSERT INTO test VALUES (42), ('hello'), (NULL);
SELECT *, toTypeName(v) FROM test WHERE v = 42;
```

```
   ┌─v──┬─toTypeName(v)───────────┐
1. │ 42 │ Variant(String, UInt32) │
   └────┴─────────────────────────┘
```

The comparison operator is automatically applied to each variant type separately, allowing filtering on Variant columns.

**Result type behavior:**

The result type depends on what the function returns for each variant:

- **Different result types**: `Variant(T1, T2, ...)`

  ```
  CREATE TABLE test2 (v Variant(UInt64, Float64)) ENGINE = Memory;
  INSERT INTO test2 VALUES (42::UInt64), (42.42);
  SELECT v + 1 AS result, toTypeName(result) FROM test2;
  ```

  ```
  ┌─result─┬─toTypeName(plus(v, 1))──┐
  │     43 │ Variant(Float64, UInt64) │
  │  43.42 │ Variant(Float64, UInt64) │
  └────────┴─────────────────────────┘
  ```
- **Type incompatibility**: `NULL` for incompatible variants

  ```
  CREATE TABLE test3 (v Variant(Array(UInt32), UInt32)) ENGINE = Memory;
  INSERT INTO test3 VALUES ([1,2,3]), (42);
  SELECT v + 10 AS result, toTypeName(result) FROM test3;
  ```

  ```
  ┌─result─┬─toTypeName(plus(v, 10))─┐
  │   ᴺᵁᴸᴸ │ Nullable(UInt64)        │
  │     52 │ Nullable(UInt64)        │
  └────────┴─────────────────────────┘
  ```

Note

**Error handling:** When a function cannot process a variant type, only type-related errors (ILLEGAL\_TYPE\_OF\_ARGUMENT,
TYPE\_MISMATCH, CANNOT\_CONVERT\_TYPE, NO\_COMMON\_TYPE) are caught and result in NULL for those rows. Other errors like
division by zero or out of memory are raised normally to prevent silently hiding real problems.

- [Creating Variant](#creating-variant)- [Reading Variant nested types as subcolumns](#reading-variant-nested-types-as-subcolumns)- [Conversion between a Variant column and other columns](#conversion-between-a-variant-column-and-other-columns)
      - [Converting a String column to a Variant column](#converting-a-string-column-to-a-variant-column)- [Converting an ordinary column to a Variant column](#converting-an-ordinary-column-to-a-variant-column)- [Converting a Variant column to an ordinary column](#converting-a-variant-column-to-an-ordinary-column)- [Converting a Variant to another Variant](#converting-a-variant-to-another-variant)- [Reading Variant type from the data](#reading-variant-type-from-the-data)- [Comparing values of Variant type](#comparing-values-of-variant-data)- [JSONExtract functions with Variant](#jsonextract-functions-with-variant)- [Functions with Variant arguments](#functions-with-variant-arguments)

Was this page helpful?