Arrays, Maps & JSON

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;

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.