ClickHouse Cheatsheet 14 — Arrays and Maps

SELECT [1, 2, 3] AS arr;
SELECT length([1,2,3]);
SELECT arr[2];        -- 2 (1-indexed!)
SELECT empty([]);

SELECT arrayJoin(tags), count()
FROM events GROUP BY arrayJoin(tags);

SELECT arrayMap(x -> x * 2, [1,2,3]);
SELECT arrayFilter(x -> x > 1, [1,2,3]);
SELECT arraySum([1,2,3]);
SELECT arrayMax([3,1,2]);
SELECT arrayDistinct([1,1,2,3,3]);
SELECT arraySort([3,1,2]);
SELECT arrayCompact([1,1,2,2,3]);
SELECT arraySlice([1,2,3,4], 2, 2);   -- [2,3]
SELECT arrayConcat([1,2], [3,4]);
SELECT arrayElement([10,20,30], 2);
SELECT indexOf([10,20,30], 20);
SELECT has([1,2,3], 2);
SELECT arrayCount(x -> x > 1, [1,2,3]);

SELECT user_id, groupArray(event) FROM events GROUP BY user_id;
SELECT groupArray(100)(event) FROM events;   -- max 100
SELECT groupArraySample(10)(event) FROM events;

SELECT * FROM events WHERE arrayExists(t -> t = 'admin', tags);
SELECT * FROM events WHERE has(tags, 'admin');

SELECT map('a', 1, 'b', 2) AS m;
SELECT m['a'];
SELECT mapKeys(m);
SELECT mapValues(m);
SELECT mapContains(m, 'a');

CREATE TABLE events (
    properties Map(String, String)
) ENGINE = ...;

SELECT properties['utm_source'] FROM events;

SELECT JSONExtractString('{"name":"Alice"}', 'name');
SELECT JSONExtractInt('{"age":30}', 'age');
SELECT JSONExtractRaw('{"a":[1,2,3]}', 'a');
SELECT JSON_QUERY('...', '$.path.to.field');     -- newer

SELECT data.name, data.age FROM events;

SELECT (1, 'a', 3.14) AS t;
SELECT t.1;       -- 1 (1-indexed)
SELECT untuple(t);

CREATE TABLE orders (
    id UInt64,
    items Nested(
        product_id UInt32,
        quantity UInt32,
        price Float64
    )
) ENGINE = ...;

-- Access
SELECT items.product_id FROM orders;