sql OFFSET 和 ORDINAL

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      |
+----------------------------------+-----------+-----------+