JSONL stores one complete JSON value, typically one object, per line. It supports practical data exploration because DuckDB can read multiple matching files, infer fields, and convert JSON values into queryable vectors. section 5.3
A useful JSONL workflow includes:
- Read a file collection with a wildcard such as xg/shots_*.json. section 5.3
- Unnest nested arrays into rows, combining home and away events with UNION ALL. section 5.3
- Inspect inferred schemas and cast fields such as X, Y, and xG to consistent numeric types. section 5.3
Flattening JSONL match data
FROM 'xg/shots_*.json' SELECT unnest(h) AS row UNION ALL FROM 'xg/shots_*.json' SELECT unnest(a) AS row LIMIT 3; Create a view from the combined rows, select row.* to flatten each STRUCT, then aggregate expected goals and goals by team. section 5.3
.mode line DESCRIBE FROM 'xg/shots_*.json';
Inspect the inferred schema directly in DuckDB CLI before flattening or repairing JSONL data. section 5.3
