unnest expands nested list values into individual rows, making JSON arrays easier to inspect, normalize, and query. By default it unpacks one level; recursive := true handles deeper nesting. section 5.3
Shred home and away shot arrays
FROM 'xg/shots_*.json' SELECT unnest(h) AS row UNION ALL FROM 'xg/shots_*.json' SELECT unnest(a) AS row LIMIT 3;
Each unnested value remains a STRUCT. Cast the whole STRUCT when inconsistent inferred types need correction, then expose its fields with row.* and query the result through a view.
Query the flattened result
After creating shotsFlattened with SELECT row.*, aggregate xG and goals by team, filter season = 2022, and order by totalXG.
