TopolearnTags, event properties and funnels are naturally arrays, and ClickHouse processes them without exploding rows.
Arrays are first-class in ClickHouse. Literals look like [1, 2, 3], indexing is 1-based (arr[1] is the first element), and out-of-range indexes return the type's default instead of erroring.
Two ways to use them:
- Keep them as arrays and use array functions:
length,has(tags, 'sale'),hasAny,indexOf,arraySort,arrayDistinct,arraySlice. Higher-order functions take a lambda:arrayMap(x -> x * 2, arr),arrayFilter(x -> x > 0, arr),arrayExists,arrayCount,arraySum. - Unfold them into rows with
arrayJoin(arr)in the select list, or theARRAY JOINclause, the counterpart ofUNNEST.LEFT ARRAY JOINkeeps rows whose array is empty.
groupArray goes the other way, collecting a group's values into an array; combined with arraySort or arrayCumSum it covers many sequence and funnel questions.
Map(String, String) stores key-value properties. Read a key with props['utm_source'] (missing keys return an empty string), list keys with mapKeys(props). Under the hood a Map is an array of key-value tuples, so it's ideal for a handful of sparse attributes. For deeply nested or evolving JSON documents, the newer JSON type stores each path as its own subcolumn and is usually faster to query.
Example
SELECT
[3, 1, 2] AS arr,
arr[1] AS first, -- 3 (1-based)
arrayMap(x -> x * 10, arr) AS times10, -- [30,10,20]
arrayFilter(x -> x > 1, arr) AS big, -- [3,2]
has(arr, 2) AS has2, -- 1
map('src', 'ads', 'cmp', 'q3')['src'] AS source; -- ads
-- One row per tag, then count
SELECT arrayJoin(tags) AS tag, count() AS n
FROM (SELECT ['sale', 'new'] AS tags UNION ALL SELECT ['sale'])
GROUP BY tag ORDER BY n DESC;
-- sale 2
-- new 1Remember
- Arrays are 1-based; use has/arrayMap/arrayFilter instead of exploding rows
- arrayJoin / ARRAY JOIN unfold arrays into rows; groupArray folds them back
- Map(K, V) for sparse properties, JSON type for evolving documents
Watch out for
- Assuming arr[0] is the first element; in ClickHouse it is arr[1].