Skip to main content

Data types

Supported data types in MotherDuck works the same as in DuckDB. See Data Types in the DuckDB documentation for complete details.

VARIANT type​

Starting with DuckDB 1.5, the VARIANT type provides a high-performance way to store and query semi-structured data. It is a strongly typed alternative to storing data as plain JSON or VARCHAR columns that delivers significantly faster reads and writes.

How VARIANT works​

When data is stored as VARIANT, DuckDB automatically "shreds" (decomposes) the semi-structured values into their underlying typed columns in Parquet files. This means a column of mixed JSON objects is stored as efficiently typed columnar data rather than opaque strings, enabling:

  • Columnar compression on the underlying typed values
  • Predicate pushdown using row group statistics
  • Faster reads by scanning only the fields you reference in your query

Using VARIANT​

-- Create a table with a VARIANT column
CREATE TABLE events (
    id INTEGER,
    payload VARIANT
);

-- Insert JSON data -- it is automatically converted to VARIANT
INSERT INTO events VALUES
    (1, '{"user": "alice", "action": "click", "ts": "2026-03-19T10:00:00Z"}'::VARIANT),
    (2, '{"user": "bob", "action": "purchase", "amount": 42.50}'::VARIANT);

-- Query individual fields
SELECT
    id,
    payload->>'user' AS user_name,
    payload->>'action' AS action
FROM events;

VARIANT in DuckLake​

DuckLake tables support VARIANT columns starting with DuckLake 0.4. This combination is particularly effective for workloads with high-volume semi-structured data because DuckLake's Parquet-backed storage takes full advantage of VARIANT shredding.

For complete details on the VARIANT type, see VARIANT in the DuckDB documentation.