Bigquery: Find the index at which a specified element is first found within an array

Question

I'm working with Bigquery and I have a dataset with arrays where I'd like to extract the index at which a specified element is first found. I do not find a function in Bigquery to achieve what I want. Dataprep has arrayindexof function which does it, but it's not available in Bigquery at the time of writing. https://cloud.google.com/dataprep/docs/html/ARRAYINDEXOF-Function_136155116

If arrayindexof existed in Bigquery, here's how we could use it.

select arrayindexof(metric, 'b') as index, value[offset(arrayindexof(metric, 'b'))] as b
from (select ['a', 'b', 'c'] as metric, [1, 2, 3] as value
      union all select ['b', 'c'], [4, 5]
      union all select ['c'], [6])

Desired result:

Row|index|   b
--------------
  1|    1|   2
  2|    0|   4
  3| NULL|NULL

Any idea how to achieve the desired result in Bigquery?

Kind regards,

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
select 
  ( select offset 
    from unnest(metric) m with offset 
    where m = 'b'
  ) index, 
  ( select v
    from unnest(metric) m with offset
    join unnest(value) v with offset
    using(offset)
    where m = 'b'
  ) b
from `project.dataset.table` 

If to apply to sample data from your question - output is

Another option (obviously with the same result):

#standardSQL
select index, value[offset(index)] value
from (
  select *,
    ( select offset 
      from unnest(metric) m with offset 
      where m = 'b'
    ) index
  from `project.dataset.table` 
)