Search code examples
sqlgoogle-cloud-platformgoogle-bigquerybigquery-udf

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'.

Image1

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?

Solution

  • 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

    enter image description here

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