Arrays in ClickHouse

Arrays are collections of items comprising similar data types. ClickHouse supports arrays as its column data type. The maximum allowed size of a ClickHouse array is 1 million. An array can hold elements entirely of numeric data type, string or date/date time type along with Nulls. The arrays are indexed from 1 and the elements are enclosed in square brackets.

Creating Table

Let us create a table with an array column and insert data into it.

CREATE TABLE demo_array (
  ID Int8,
  array_column Array(Float64)) 
ENGINE=MergeTree
ORDER BY ID;

INSERT INTO demo_array VALUES
(1, array(1,2,3)) (2, array(4,5,6.7));

SELECT *
FROM demo_array

Query id: b2569a90-33fc-46ae-bb56-94b7dcaf21b0

┌─ID─┬─array_column─┐
│  1 │ [1,2,3]      │
│  2 │ [4,5,6.7]    │
└────┴──────────────┘

2 rows in set. Elapsed: 0.003 sec. 

Array Operations

1) Creating an array – array() 

SELECT [toDate('2022-01-01'), toDateTime('2022-01-01 00:00:01')]

Query id: bba3d7e2-8fb9-4c27-9609-605d1998766b

┌─array(toDate('2022-01-01'), toDateTime('2022-01-01 00:00:01'))─┐
│ ['2022-01-01 00:00:00','2022-01-01 00:00:01']                  │
└────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.002 sec. 

2) Accessing array element – arrayElement(array, index)

SELECT arrayElement(array(1,2,3,4), 3);

SELECT [1, 2, 3, 4][3]

Query id: 183e82ad-87d5-47cd-a42d-1f7ad8ab402d

┌─arrayElement([1, 2, 3, 4], 3)─┐
│                             3 │
└───────────────────────────────┘

1 row in set. Elapsed: 0.002 sec. 

3) Length of the array –  length()

SELECT length([1, 2, 3, 4])

Query id: 5010d9c7-5f8f-403a-ab4d-119c4f714ef0

┌─length([1, 2, 3, 4])─┐
│                    4 │
└──────────────────────┘

1 row in set. Elapsed: 0.002 sec. 

4) Concatenation – arrayConcat()

SELECT arrayConcat([1, 2, 3], [4, 5, 6])

Query id: 85aa29fd-9f5a-4367-99cd-9a819291b9cf

┌─arrayConcat([1, 2, 3], [4, 5, 6])─┐
│ [1,2,3,4,5,6]                     │
└───────────────────────────────────┘

1 row in set. Elapsed: 0.002 sec.

5) Finding an element – indexOf(array, element)

SELECT indexOf([0, 1, 2, 3, 4], 3)

Query id: cd49d4ae-4efe-4225-9844-b017a855d17d

┌─indexOf([0, 1, 2, 3, 4], 3)─┐
│                           4 │
└─────────────────────────────┘

1 row in set. Elapsed: 0.002 sec. 

References

Harnessing the Power of ClickHouse Arrays – Part 1

https://clickhouse.com/docs/en/sql-reference/data-types/array

https://clickhouse.com/docs/en/sql-reference/functions/array-functions