Arrays, Maps & JSON — learning.lab

ClickHouse isn't limited to flat scalar columns. Arrays, tuples, maps, nested structures, and a dynamic JSON type all exist — each stored in a way that stays consistent with the column-oriented model rather than falling back to an opaque blob.

Array(T): two parallel columns, not a serialized blob

An Array(T) column is physically stored as two columns: a flat values array holding every element from every row concatenated together, and an offsets array recording where each row's slice ends. There is no per-row serialization/deserialization step — reading the array for row N is just reading a range out of the shared values array using the offsets.

CREATE TABLE sessions
(
    session_id UInt64,
    page_views Array(String)
)
ENGINE = MergeTree ORDER BY session_id;

INSERT INTO sessions VALUES
    (1, ['/home', '/pricing']),
    (2, ['/home']);

SELECT length(page_views), page_views[1] FROM sessions;

Tuple(T1, T2, ...): a fixed-shape group of columns

A Tuple groups a fixed number of typed elements — each element is effectively its own sub-column internally, so Tuple(Float64, Float64) for a coordinate pair stores two independent, independently compressible columns rather than one combined structure.

Map(K, V): syntax sugar over two arrays, not a hash table

CREATE TABLE events
(
    event_id UInt64,
    tags     Map(String, String)
)
ENGINE = MergeTree ORDER BY event_id;

INSERT INTO events VALUES (1, {'env': 'prod', 'region': 'eu'});

SELECT tags['env'] FROM events;

Common mistake

Map(K, V) is implemented as an Array(Tuple(K, V)) under the hood — key lookup with tags['env'] is a linear scan over that row's entries, not an O(1) hash lookup. It's a convenient way to model open-ended key/value attributes on a row, but it is not a substitute for a real hash index, and it isn't a good fit for maps with hundreds of entries per row that need fast random access.

Nested(...): parallel arrays that stay in sync

A Nested column is syntactic sugar for a set of Array columns that all share the same length per row — useful for modeling a one-to-many relationship (like line items on an order) without a separate joined table.

CREATE TABLE orders
(
    order_id UInt64,
    items Nested
    (
        sku   String,
        price Float64,
        qty   UInt32
    )
)
ENGINE = MergeTree ORDER BY order_id;

-- items.sku, items.price and items.qty are each Array(...) columns,
-- guaranteed to have matching lengths per row
SELECT order_id, items.sku, items.price FROM orders;

ARRAY JOIN: turning array elements into rows

To analyze array elements individually rather than as a group, ARRAY JOIN expands each element into its own row, duplicating the rest of the row's columns alongside it — useful for computing per-page-view stats out of the sessions table above:

SELECT session_id, page
FROM sessions
ARRAY JOIN page_views AS page;

-- session_id=1, page='/home'
-- session_id=1, page='/pricing'
-- session_id=2, page='/home'

JSON: a real column type, not just a String

The JSON type stores semi-structured data as a set of dynamically discovered subcolumns rather than one opaque text blob — paths that appear consistently across rows get their own typed, columnar storage internally, so querying payload.user.id doesn't require re-parsing a string on every read the way String + JSONExtract would. Reach for it when incoming event shapes vary and you don't want to pre-define every field, and reach for a proper typed schema (plain columns, or Nested) whenever the shape is actually known ahead of time — a fixed schema is still faster and more predictable to query.