BigQuery: Create a table from arrays

Question

I want to create a table with two columns from two different arrays.

Array1: [1,2,3]

Array2: [11,12,13]

Required Table:

By using this query, I got 1 row

SELECT 
[1,2,3] AS S1,
[11,12,13] AS S2

I tried UNNEST, but got cartesian product

SELECT S1, S2
FROM 
UNNEST([1,2,3]) AS S1,
UNNEST([11,12,13]) AS S2

How can I create required table (pic 1) from arrays?

Thanks

Answer 1

One method uses a join and WITH OFFSET:

SELECT S1, S2, o1, o2
FROM UNNEST([1, 2, 3]) s1 WITH OFFSET o1 LEFT JOIN
     UNNEST([11, 12, 13]) S2 WITH OFFSET o2
     ON o1 = o2;

It would be nice if UNNEST() in BigQuery supported multiple arguments, as some other databases do.