array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]
说明
按位置访问 ARRAY 元素并返回该元素。OFFSET 表示从 0 开始计数,ORDINAL 表示从 1 开始计数。
给定数组要么解释为从 0 开始,要么解释为从 1 开始。访问数组元素时,必须在数组位置之前分别附加 OFFSET 或 ORDINAL;该行为并非默认行为。
如果索引超出范围,OFFSET 和 ORDINAL 都会生成错误。
WITH items AS
(SELECT ["apples", "bananas", "pears", "grapes"] as list
UNION ALL
SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;
+----------------------------------+-----------+-----------+
| list | offset_1 | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas | apples |
| [coffee, tea, milk] | tea | coffee |
| [cake, pie] | pie | cake |
+----------------------------------+-----------+-----------+