Overview
ARRAY(T) stores variable-length collections whose elements all share the type T. Define the element type when creating a table and use array functions to read or transform the values.
note
Databend arrays are 1-based. arr[1] returns the first element and arr[n] the last.
Examples
CREATE TABLE array_samples (arr ARRAY(INT64));
INSERT INTO array_samples VALUES ([1, 2, 3]), ([10, 20]);
SELECT
arr,
arr[1] AS first_elem,
arr[2] AS second_elem
FROM array_samples;
Result:
┌────────────┬────────────┬──────────────┐
│ arr │ first_elem │ second_elem │
├────────────┼────────────┼──────────────┤
│ [1,2,3] │ 1 │ 2 │
│ [10,20] │ 10 │ 20 │
└────────────┴────────────┴──────────────┘
-- Index 0 always returns NULL because arrays are 1-based.
SELECT arr[0] AS zeroth_elem FROM array_samples;
Result:
┌─────────────┐
│ zeroth_elem │
├─────────────┤
│ NULL │
│ NULL │
└─────────────┘