Using BigQuery SQL to split nested values on multiple columns

Question

I have the following table that contains an ID and two columns with KEYS in one column and VALUES in the other. I need to split / unnest the rows so that I can selectively obtain the value from a specified key.

There will always be the same number of values to keys, but the number may be different for each record.

Input:

ID   | KEYS          | VALUES
X1   | ['a','b','c'] | ['1','2','3']
X2   | ['a','b','f'] | ['1','2','6']
X3   | ['b','e','f'] | ['2','5','6']

Everything I have tried yields duplication of the keys where the value is not aligned to the key correctly.

Desired Output:

ID   | KEY | VAL
X1   | a   | 1
X1   | b   | 2
X1   | c   | 3
X2   | a   | 1
X2   | b   | 2
X2   | f   | 6
X3   | b   | 2
X3   | e   | 5
X3   | f   | 6

Answer 1

Use below simple approach

select id, key, val
from your_table,
unnest(keys) as key with offset
join unnest(values) as val with offset
using(offset)    

if applied to sample data in your question - output is