Variant Type

Search Shortcut cmd + k | ctrl + k

Documentation / SQL / Data Types

Variant Type

The VARIANT type stores typed, binary data where each row is self-contained with its own type information. This differs from the JSON type, which is physically stored as text. Because type metadata is embedded per-value, VARIANT provides better compression and query performance than JSON for semi-structured data.

The VARIANT type is inspired by Snowflake's semi-structured VARIANT data type. It is available in Parquet since 2025 and also supported by DuckDB's Parquet reader.

Examples

Storing Different Types in the Same Column

A VARIANT column can hold values of different types across rows:

CREATE TABLE events (id INTEGER, data VARIANT);
INSERT INTO events VALUES
    (1, 42::VARIANT),
    (2, 'hello world'::VARIANT),
    (3, [1, 2, 3]::VARIANT),
    (4, {'name': 'Alice', 'age': 30}::VARIANT);

SELECT * FROM events;

┌───────┬────────────────────────────┐
│  id   │            data            │
│ int32 │          variant           │
├───────┼────────────────────────────┤
│     1 │ 42                         │
│     2 │ hello world                │
│     3 │ [1, 2, 3]                  │
│     4 │ {'name': Alice, 'age': 30} │
└───────┴────────────────────────────┘

Checking the Type of a Value

Use variant_typeof to inspect the underlying type of each row:

SELECT id, data, variant_typeof(data) AS vtype
FROM events;

┌───────┬────────────────────────────┬───────────────────┐
│  id   │            data            │       vtype       │
│ int32 │          variant           │      varchar      │
├───────┼────────────────────────────┼───────────────────┤
│     1 │ 42                         │ INT32             │
│     2 │ hello world                │ VARCHAR           │
│     3 │ [1, 2, 3]                  │ ARRAY(3)          │
│     4 │ {'name': Alice, 'age': 30} │ OBJECT(name, age) │
└───────┴────────────────────────────┴───────────────────┘

Extracting Fields from Nested Variants

Fields can be extracted from nested VARIANT values using dot notation or the variant_extract function:

SELECT data.name FROM events WHERE id = 4;

SELECT variant_extract(data, 'name') AS name FROM events WHERE id = 4;

┌─────────┐
│  name   │
│ variant │
├─────────┤
│ Alice   │
└─────────┘

Parquet Support

DuckDB supports reading VARIANT types from Parquet files, including shredding, a technique that stores nested data as flat values for more efficient access.

About this page

Code of Conduct Trademark Use Blog