The Shape You Cannot Write Down

The previous chapter’s advice has a boundary, and it is worth walking straight into it.

A struct is a schema. Its field names and their types are part of the type, checked at plan time. That is exactly what you want for a client with a country and a city. It is useless when the shape of the data is different on every row.

Here is that case, and it is not contrived — it is what an event stream looks like:

{"id":1,"kind":"click",    "attrs":{"x":10,"y":20}}
{"id":2,"kind":"purchase", "attrs":{"sku":"A17","cents":3000,"currency":"USD"}}
{"id":3,"kind":"ping",     "attrs":null}
{"id":4,"kind":"tagged",   "attrs":["a","b","c"]}
{"id":5,"kind":"flag",     "attrs":true}

attrs is an object, then a different object, then null, then an array, then a boolean. No struct describes that. There is no schema to write down, because there is no schema.

DuckDB has three answers, they are not interchangeable, and one of them is new enough that most of what you will read about it is out of date. Everything here was run against DuckDB 1.5.5.

What the reader does on its own

Point read_json at that file and it makes a decision:

describe select * from read_json('var/events.json');

id     BIGINT
kind   VARCHAR
attrs  JSON

select id, attrs from read_json('var/events.json');

(1, '{"x":10,"y":20}')
(2, '{"sku":"A17","cents":3000,"currency":"USD"}')
(3, None)
(4, '["a","b","c"]')
(5, 'true')

attrs came back as JSON, which in DuckDB is a text type with parsing functions attached. Compare that with chapter 4, where a uniformly-shaped tags field became a real VARCHAR[] and geo became a real STRUCT. The inference gave up here, correctly, and fell back to keeping the text.

That is the baseline: correct, lossless, and completely untyped. Nothing inside attrs has a type any more.

Answer one: widen the struct

Before reaching for anything exotic, note that structs absorb some variation:

select * from (values ({'a': 1}), ({'b': 2})) t(x);

({'a': 1,    'b': None})
({'a': None, 'b': 2})

DuckDB merged the two into STRUCT(a INTEGER, b INTEGER) and filled the gaps with NULL. UNION ALL BY NAME does the same across queries.

This is the right answer more often than people expect, and it has a hard limit. The type contains every field that has ever appeared. Three shapes with four fields each is a twelve-field struct, mostly null, and still perfectly workable. Two hundred event types with their own attributes is a struct with hundreds of columns, and every new event type is a schema change.

Use widening when the set of shapes is small, known, and stable. When it is none of those, the type stops describing the data.

Answer two: VARIANT

VARIANT is a single static type that holds a different type per row. It went core in the 1.5 series, so a model or a tutorial older than that will not mention it at all.

select typeof('x'::VARIANT);

VARIANT

Everything casts into it — scalars, structs, lists, JSON:

select {'a': 1, 'b': [1, 2]}::VARIANT;   -- {'a': 1, 'b': [1, 2]}
select [1, 2]::VARIANT;                   -- [1, 2]
select '{"a":1}'::JSON::VARIANT;          -- {'a': 1}

The interesting function is the one that asks what a row actually holds:

select id, variant_typeof(attrs) from ev order by id;

(1, 'OBJECT(x, y)')
(2, 'OBJECT(sku, cents, currency)')
(3, 'VARIANT_NULL')
(4, 'ARRAY(3)')
(5, 'BOOL_TRUE')

One column, five different types, all of them known. typeof on that column returns VARIANT — the static type, which never changes. variant_typeof returns the runtime type, per row, and it is precise: not “object” but OBJECT(sku, cents, currency), not “array” but ARRAY(3).

That is the whole idea. The type system stops trying to describe the column and starts describing each value.

Extraction, and the third failure mode

Reach into a VARIANT with the same syntax as a struct:

select id, attrs.x, attrs.sku from ev order by id;

(1, 10,   None)
(2, None, 'A17')
(3, None, None)
(4, None, None)
(5, None, None)

One query asking for two fields that never coexist. Row 1 has x, row 2 has sku, and neither asking produced an error.

Which completes a pattern worth putting side by side, because these three lines are the entire decision:

select ({'a':1}).zzz;                   -- Binder Error: Could not find key "zzz"
select map([1],['a'])[9];               -- NULL
select ({'a':1}::VARIANT).zzz;          -- NULL

STRUCT checks at plan time. MAP and VARIANT check never. A struct’s field list is schema, so a typo is caught before any data is read. A variant’s contents are data, so a missing field is a missing value.

Values come out already typed enough to compute with:

select ({'a': 1}::VARIANT).a + 1;       -- 2
select ({'a': {'b': 2}}::VARIANT).a.b;  -- 2
select ([10, 20]::VARIANT)[1];          -- 10

No cast needed for the arithmetic, and nesting and indexing work all the way down.

And because the runtime type is a value, you can query it:

select id from ev where variant_typeof(attrs) like 'OBJECT%';

(1,)
(2,)

Filtering rows by their shape. None of the other options can express that at all. It is what to reach for when a heterogeneous column starts misbehaving: find the rows whose shape you did not expect.

It survives storage, too:

copy (select id, attrs from ev) to 'var/ev.parquet' (format parquet);
select id, variant_typeof(attrs) from 'var/ev.parquet' order by id;

(1, 'OBJECT(x, y)')
(2, 'OBJECT(cents, sku)')
(3, 'VARIANT_NULL')
(4, 'ARRAY(3)')
(5, 'BOOL_TRUE')

Per-row types intact through a write and a read, with nothing declared. One small detail to notice: row 2 came back as OBJECT(cents, sku) where it went in as OBJECT(sku, cents). Field order is normalised. If any logic of yours depends on the order of an object’s keys, it will not survive the round trip.

The measurement that should temper your enthusiasm

VARIANT sounds like it should be the answer to everything, so it is worth knowing what it costs. A million rows, one field extracted and summed, three ways:

select sum((j->>'$.a')::BIGINT) from big_j;      -- JSON text
select sum(v.a::BIGINT) from big_v;               -- VARIANT
select sum(i) from big_j;                         -- a plain BIGINT column

json    ->> + cast :     16 ms
variant .a + cast  :     70 ms
plain BIGINT col   :      1 ms

All three returned 499999500000, so this is the same work three ways.

Two things fall out, and the first is the surprising one. JSON text was four times faster than VARIANT here. DuckDB’s JSON reader is heavily optimised, and this data is perfectly uniform, which is JSON’s best case and not the case VARIANT exists for. Do not read this as “JSON is faster than VARIANT” in general — read it as VARIANT is not a performance feature. It buys type fidelity, not speed.

The second is the one that should change what you do. A plain BIGINT column was 70 times faster than the VARIANT and 16 times faster than the JSON. Any field you query often does not belong in a semi-structured blob at all. Promote it to a column, and leave the genuinely variable remainder behind.

Size is close to a wash — 9.0 MB for the VARIANT column against 9.8 MB for the JSON, written to Parquet.

Choosing, in one pass

The five options now form a ladder, from most structure to least:

A real column. Anything you filter, join or group on regularly. 70× faster than pulling it out of a variant, every time.

A STRUCT. Fixed fields, known at design time, typo caught at plan time. The default for anything with a shape you can name.

A widened STRUCT. A small, stable set of shapes. Cheap and readable until the field count runs away.

A MAP. Dynamic keys, but a uniform value type. Per-tenant settings where every value is a string; HTTP headers.

A VARIANT. Dynamic keys and dynamic value types. The event stream at the top of this chapter. The only option that can tell you what each row actually holds.

Plain JSON. Text you are passing through, or a field nobody queries. Fast to extract from, and everything inside it is untyped.

The practical shape for a real event pipeline is a combination rather than a choice. id, kind and ts become real columns, because everything filters on them. attrs becomes a VARIANT, because nothing can describe it. Then promote a field out of attrs into its own column the moment a query starts depending on it.

A note on GEOMETRY

The same “the type is core, the operations are not” pattern from chapter 9 is worth restating here, because GEOMETRY looks like it belongs in this chapter’s list:

select typeof('POINT(1 2)'::GEOMETRY);   -- GEOMETRY
select ST_Point(1, 2);                    -- Catalog Error

You can store geometries in a bare install and compute nothing about them. VARIANT is genuinely core in both senses — the type and its functions are in the binary. GEOMETRY is only half there until you INSTALL spatial.

Final thoughts

The three storage answers in this chapter and the last are really one question asked at three different times. A struct decides the shape when you write the query. A map decides it when you write the row. A variant does not decide it at all, and hands you variant_typeof so you can ask.

The line that matters most is the one about failures. A missing struct field is a binder error before any data is read; a missing variant field is a NULL. That is not inconsistency. It is the difference between schema and data made visible, and picking the wrong one gets you either a rigid column or a silent null where you wanted a shout.

And keep the 70× in view. VARIANT is how you store what you cannot describe — not how you store what you query.

Next: DuckDB Inside Python — replacement scans, zero-copy Arrow, and why a DataFrame variable name works as a table name.