How to find the index of an element in an Array in BigQuery

is it possible to find the index of an element in a array in BigQuery?

The idea is to find the index of an element to grab a different element in the row below.

So for example, for this case I need to find the index of ‘status2’ to grab the respective entering date of the status after status2, that would be ‘2021-03-20’.

The thing is, the status could jump from status2 to status4 or status5 and so on, so I don’t know what will be the next status, but I know that the date I need will always be the date after status2.

Is there a way to do that in a query?

Answer

You can use OFFSET for this, as in below example

select element, date, status
from your_table t, 
t.status status with offset as offset1
left join t.entered_date date with offset as offset2
on offset1 + 1 = offset2
where status = 'status2'    

if applied to sample data in your question – output is

Hope you can adopt above technique to whatever exactly use case you have