.load sqlite_jq

create table raw_data (
    raw text
);

insert into raw_data select * from jq_each(readfile('airline_routes.json'), '.[]');

create table airports (
    iata char(3) primary key,
    icao char(4),
    latitude float,
    longitude float,
    name varchar(255),
    timezone varchar(255)
);

insert into airports select
    jq(raw, '.iata'),
    jq(raw, '.icao'),
    jq(raw, '.latitude'),
    jq(raw, '.longitude'),
    jq(raw, '.name'),
    jq(raw, '.timezone')
from raw_data;

create table routes (
    origin_iata char(3),
    destination_iata char(3),
    distance int,
    time int,
    carrier varchar(255)
);


insert into routes select
    jq(raw_data.raw, '.iata') as origin_iata,
    json_extract(routes.value, '$[0]') as dest_iata,
    json_extract(routes.value, '$[1]') as distance,
    json_extract(routes.value, '$[2]') as time,
    json_extract(routes.value, '$[3]') as carrier
from raw_data,
jq_each(raw_data.raw, '.routes[] | . as $route | .carriers[] | [ $route.iata, $route.km, $route.min, . ]') as routes;