Type conversion turns an inferred or stored value into a compatible type so data can support the operations and analyses you need. In nested JSON, DuckDB may infer numeric-looking fields such as X, Y, and xG as VARCHAR; casting the enclosing STRUCT to a corrected schema changes them to DOUBLE. section 5.3
Cast an inferred STRUCT
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; DESCRIBE shots then shows X, Y, and xG as DOUBLE, allowing numeric operations such as SUM(xG). section 5.3
Use type conversion deliberately in file-based workflows:
- Inspect inferred columns before casting, because sampling can miss invalid values or infer numeric data as strings. section 5.3
- Expect a query-time conversion error when a value such as 'abc' cannot become DOUBLE; sometimes that strict failure is desirable. section 5.3
- Distinguish logical types from Parquet physical types: DATE columns may be represented physically as INT32, while strings use BYTE_ARRAY. section 5.5
Listing 5.1 Using CAST on a whole STRUCT to fix the inferred types for some attributes
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, #1 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;
Inspect the complete whole-STRUCT cast that corrects JSON-inferred field types. section 5.3
