How to join data from different formats¶
This examples shows how to join a CSV and a JSON file.
We use the files airports.csv and trips.json files that can be found in the S3 bucket raw-tutorial.
Then execute the query:
trips := READ("s3://raw-tutorial/trips.json"); airports := READ("s3://raw-tutorial/airports.csv"); SELECT t.reason, t.dates, start, end FROM trips t, airports start, airports end WHERE t.origin=start.IATA_FAA AND t.destination=end.IATA_FAA
The result contains a nested structure, where start and end fields contain the corresponding information on the airport. Alternative, to obtain a flat structure (i.e. in tabular form), change the SELECT part of the query to:
SELECT t.reason, t.dates, start.Name AS start_name, end.Name AS end_name FROM trips t, airports start, airports end WHERE t.origin=start.IATA_FAA AND t.destination=end.IATA_FAA