sql array 数组基本用法(二)

访问数组元素

WITH sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
       some_numbers[OFFSET(1)] AS offset_1,
       some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;

some_numbers	offset_1	ordinal_1
[0, 1, 1, 2, 3, 5]	1	0
[2, 4, 8, 16, 32]	4	2
[5, 10]	10	5

查找长度

WITH sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
       ARRAY_LENGTH(some_numbers) AS len
FROM sequences;

some_numbers	len
[0, 1, 1, 2, 3, 5]	6
[2, 4, 8, 16, 32]	5
[5, 10]	2

展平数组

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset
ORDER BY offset;

element	offset
foo	0
bar	1
baz	2
qux	3
corge	4
garply	5
waldo	6
fred	7