Big Query Extracting date from string

Question

| orders |

|sold [2022-06-09 to 2022-06-17] |

What I am trying to get is

orders	date1	date2
sold [2022-06-09 to 2022-06-17]	2022-06-09	2022-06-17

select
    Orders, REGEXP_EXTRACT_ALL(Orders,r'([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])') dates
from table

But BQ gives me the result in one row then split into 2 rows

Row	Orders	Dates
1	text with dates	2022-06-09
		2022-09-17

when exported to csv it is

Orders	Dates
text with dates	[2022-06-09,2022-06-27]

I have tried to select left/right to get the fields I want but get an array error from Big Query

Pointers Appreciated

Answer 1

Use below approach

select Orders, dates[offset(0)] as date1, dates[offset(1)] as date2
from table, 
UNNEST([struct(REGEXP_EXTRACT_ALL(Orders,r'([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])') as dates)])    

if applied to sample data in your question - output is