Flatten nested data in Big Query to a single row

This is what the data looks like

This is what I am trying to achieve

I just need the flattened data to show destination 1 and destination 2 as well as duration 1 and duration 2.

I have used the unnest function in Big Query but it creates multiple rows. I am unable to use any aggregation to group the multiple rows as the data is non-numeric. Thank you for helping!

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT EnquiryReference, 
  Destinations[OFFSET(0)].Name AS Destination1,
  Destinations[SAFE_OFFSET(1)].Name AS Destination2,
  Destinations[OFFSET(0)].Duration AS Duration1,
  Destinations[SAFE_OFFSET(1)].Duration AS Duration2
FROM `project.dataset.table`  

If to apply to sample data from your question

result will be