select
json_extract(value, '$.BlockFareboxId') as block_farebox_id,
json_extract(value, '$.CommStatus') as comm_status,
json_extract(value, '$.Destination') as destination,
json_extract(value, '$.Deviation') as deviation,
json_extract(value, '$.Direction') as direction,
json_extract(value, '$.DirectionLong') as direction_long,
json_extract(value, '$.DisplayStatus') as display_status,
json_extract(value, '$.StopId') as stop_id,
json_extract(value, '$.CurrentStatus') as current_status,
json_extract(value, '$.DriverName') as driver_name,
json_extract(value, '$.GPSStatus') as gps_status,
json_extract(value, '$.Heading') as heading,
json_extract(value, '$.LastStop') as last_stop,
json_extract(value, '$.LastUpdated') as last_updated,
json_extract(value, '$.Latitude') as latitude,
json_extract(value, '$.Longitude') as longitude,
json_extract(value, '$.Name') as name,
json_extract(value, '$.OccupancyStatus') as occupancy_status,
json_extract(value, '$.OnBoard') as on_board,
json_extract(value, '$.OpStatus') as op_status,
json_extract(value, '$.RouteId') as route_id,
json_extract(value, '$.RunId') as run_id,
json_extract(value, '$.Speed') as speed,
json_extract(value, '$.TripId') as trip_id,
json_extract(value, '$.VehicleId') as vehicle_id,
json_extract(value, '$.SeatingCapacity') as seating_capacity,
json_extract(value, '$.TotalCapacity') as total_capacity,
json_extract(value, '$.PropertyName') as property_name,
json_extract(value, '$.OccupancyStatusReportLabel') as occupancy_status_report_label
from snapshots
join json_each(snapshots.response_body, '$.Vehicles')