A struct is a named-field data structure whose fields have defined types. DuckDB uses structs to represent consistent key–value records and to work with complex nested data efficiently. section 1.7.2 section 1.2
Use structs when:
- Representing one nested record with fields such as an event ID, player, timestamp, and expected-goals value. section 5.3
- Reading JSON whose properties are inferred as arrays of STRUCT values. section 5.3
- Normalizing inconsistent inferred types by casting an entire STRUCT to an explicit schema. section 5.3
Flatten nested shot records
JSON files contain home and away shot arrays. Unnest both arrays, combine their records, and cast numeric fields explicitly: CREATE OR REPLACE VIEW shots AS FROM ( FROM 'xg/shots_*.json' SELECT unnest(h) AS row UNION ALL FROM 'xg/shots_*.json' SELECT unnest(a) AS row ) SELECT CAST(row AS STRUCT( id BIGINT, "minute" BIGINT, result VARCHAR, X DOUBLE, Y DOUBLE, xG DOUBLE, player VARCHAR, h_a VARCHAR, player_id BIGINT, situation VARCHAR, season BIGINT, shotType VARCHAR, match_id BIGINT, h_team VARCHAR, a_team VARCHAR, h_goals BIGINT, a_goals BIGINT, date TIMESTAMP, player_assisted VARCHAR, lastAction VARCHAR)) AS row; Then expose the fields as columns with `SELECT row.* FROM shots`. The flattened result can support ordinary grouping and aggregation, such as total expected goals by team. section 5.3 section 5.3 section 5.3
column_name = h
column_type = STRUCT(
id BIGINT, "minute" BIGINT, result VARCHAR,
X VARCHAR, Y VARCHAR, xG VARCHAR,
player VARCHAR, h_a VARCHAR, player_id BIGINT,
situation VARCHAR, season BIGINT, shotType VARCHAR, match_id BIGINT,
h_team VARCHAR, a_team VARCHAR, h_goals BIGINT, a_goals BIGINT,
date TIMESTAMP, player_assisted VARCHAR, lastAction VARCHAR)[]
null = YES
key =
default =
extra =
column_name = a
column_type = STRUCT(
id BIGINT, "minute" BIGINT, result VARCHAR,
X VARCHAR, Y VARCHAR, xG VARCHAR,
player VARCHAR, h_a VARCHAR, player_id BIGINT,
situation VARCHAR, season BIGINT, shotType VARCHAR, match_id BIGINT,
h_team VARCHAR, a_team VARCHAR, h_goals BIGINT, a_goals BIGINT,
date TIMESTAMP, player_assisted VARCHAR, lastAction VARCHAR)[]
null = YES
key =
default =
extra =
Inspect the inferred STRUCT schema before unnesting or casting nested JSON records. section 5.3
