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

Question

如果我知道 BigQuery 中有两个大小相等的数组。如何将它们压缩到一个结构数组或两个元素数组或类似数组中？

以下查询为我提供了 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

我想得到这样的东西:

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

Answer 1

使用 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;