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.