Bigquery - 如何将两个数组压缩成一个？

If I have two arrays in BigQuery that I know are of equal size. How can i zip them into one array of structs or an array of two element arrays or similar?

如果我在BigQuery中有两个数组,我知道它们的大小相同。我如何将它们压缩成一个结构数组或两个元素数组或类似的数组?

The following query gives me all possible combinations of x and y which is not what I want.

以下查询给出了x和y的所有可能组合,这不是我想要的。

WITH test AS (
  SELECT
    ['a', 'b', 'c'] as xs,
    [1, 2, 3] as ys
)
SELECT struct(x, y) as pairs 
FROM test, unnest(xs) as x, unnest(ys) as y

I would like to get something like this:

我想得到这样的东西:

+--------+--------+
| pair.x | pair.y |
+--------+--------+
| a      | 1      |
| b      | 2      |
| c      | 3      |
+--------+--------+

1 个解决方案

#1

Use WITH OFFSET and the bracket operator:

使用WITH OFFSET和括号运算符:

WITH test AS (
  SELECT
    ['a', 'b', 'c'] as xs,
    [1, 2, 3] as ys
)
SELECT struct(x, ys[OFFSET(off)] as y) as pairs 
FROM test, unnest(xs) as x WITH OFFSET off;