如何在 BigQuery 中使用偏移量和序号从包含不同数据类型的数组中选取元素

Question

基于这个访问数组元素的页面，我创建了一个名为some_numbers的数组，其中包含两种数据类型(字符串和整数(。这是从数组中选择一个元素的查询，但它不起作用。

WITH sequences AS
  (SELECT array<struct<subject as string, marks as int64>>[("mat",87),("phy",45),("che",45),("zoo",78),("eng",87)] AS some_numbers
   UNION ALL SELECT array<struct<subject as string, marks as int64>>[("mat",87),("phy",45),("che",45),("zoo",78),("eng",87)] AS some_numbers
   UNION ALL SELECT array<struct<subject as string, marks as int64>>[("mat",87),("phy",45),("che",45),("zoo",78),("eng",87)] AS some_numbers)
SELECT some_numbers,
       some_numbers[OFFSET(1)] AS offset_1,
       some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;

我有以下错误：

错误：

语法错误：预期的"，"或">"，但在 [3：32] 处获得关键字 AS –

Answer 1

几乎相同的查询，但有效：

WITH sequences AS (
   SELECT           [STRUCT("mat" AS subject,87 AS marks),("phy",45),("che",45),("zoo",78),("eng",87)] AS some_numbers
   UNION ALL SELECT [STRUCT("mat" AS subject,87 AS marks),("phy",45),("che",45),("zoo",78),("eng",87)] AS some_numbers
   UNION ALL SELECT [STRUCT("mat" AS subject,87 AS marks),("phy",45),("che",45),("zoo",78),("eng",87)] AS some_numbers
)
SELECT some_numbers,
       some_numbers[OFFSET(1)] AS offset_1,
       some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;

(对于以后的问题，请包括获得的错误和预期结果(