Working with Nested Types in ClickHouse: Inserting and Querying Semi-Structured Data

  

ClickHouse is a powerful columnar database optimized for OLAP workloads. While it is fundamentally designed for structured data, it supports semi-structured data using Nested types. This feature is useful when you want to store arrays of related fields (like key-value pairs or repeated fields) in a structured table format.

 

This post explores how to:

·      Define nested types

·      Insert data into nested columns

·      Query nested structures

 

1. What Are Nested Types in ClickHouse?

The Nested data type is a way to group related array columns under a common name. It behaves like a table inside a column. Under the hood, a nested type is just a set of parallel arrays.

 

Syntax

Nested(name Type, name2 Type, ...)

   

2. Creating a Table with Nested Types

Let’s create a table with nested type with below example.

 

Example: Store a user with multiple phone numbers and labels (like "home", "work").

 

Step 1: Create database demo_db.

 

CREATE DATABASE IF NOT EXISTS demo_db;

   

Step 2: Create users table.

 

CREATE TABLE demo_db.users (
    id UInt32,
    name String,
    phones Nested(
        label String,
        number String
    )
) ENGINE = MergeTree
ORDER BY id;

   

Step 3: Inserting Data into Nested Columns.

When inserting into a nested column, you provide arrays of values for each subcolumn.

 

INSERT INTO demo_db.users VALUES
(1, 'Alice', ['home', 'work'], ['123-456', '789-012']),
(2, 'Bob', ['mobile'], ['345-678']);

   

Step 4: Selecting from Nested Columns

ClickHouse flattens nested structures into their array columns.

 

SELECT
    id,
    name,
    phones.label,
    phones.number
FROM demo_db.users;

krishna :) SELECT
    id,
    name,
    phones.label,
    phones.number
FROM demo_db.users;

SELECT
    id,
    name,
    phones.label,
    phones.number
FROM demo_db.users

Query id: f98bca7d-0591-4006-a9b3-f14d3fc2358e

   ┌─id─┬─name──┬─phones.label────┬─phones.number─────────┐
1. │  1 │ Alice │ ['home','work'] │ ['123-456','789-012'] │
2. │  2 │ Bob   │ ['mobile']      │ ['345-678']           │
   └────┴───────┴─────────────────┴───────────────────────┘

2 rows in set. Elapsed: 0.002 sec.

   

Flattening Nested Data with ARRAY JOIN

To unnest the data, i.e., get one row per phone.

 

SELECT
    id,
    name,
    phones.label,
    phones.number
FROM demo_db.users
ARRAY JOIN phones;

krishna :) SELECT
    id,
    name,
    phones.label,
    phones.number
FROM demo_db.users
ARRAY JOIN phones;

SELECT
    id,
    name,
    phones.label,
    phones.number
FROM demo_db.users
ARRAY JOIN phones

Query id: 8267efcb-59d7-45f8-a8a6-4cbad05e897d

   ┌─id─┬─name──┬─phones.label─┬─phones.number─┐
1. │  1 │ Alice │ home         │ 123-456       │
2. │  1 │ Alice │ work         │ 789-012       │
3. │  2 │ Bob   │ mobile       │ 345-678       │
   └────┴───────┴──────────────┴───────────────┘

3 rows in set. Elapsed: 0.007 sec.

   

When using ARRAY JOIN with nested types:

 

·      You must use fully-qualified names like phones.label, phones.number.

·      If you want simpler names in output, use aliases (AS label, AS number).

 

SELECT
    id,
    name,
    phones.label as label,
    phones.number as phone_number
FROM demo_db.users
ARRAY JOIN phones;

krishna :) SELECT
    id,
    name,
    phones.label as label,
    phones.number as phone_number
FROM demo_db.users
ARRAY JOIN phones;

SELECT
    id,
    name,
    phones.label AS label,
    phones.number AS phone_number
FROM demo_db.users
ARRAY JOIN phones

Query id: 9962be7b-f0e4-41aa-b1af-6c31a61ad602

   ┌─id─┬─name──┬─label──┬─phone_number─┐
1. │  1 │ Alice │ home   │ 123-456      │
2. │  1 │ Alice │ work   │ 789-012      │
3. │  2 │ Bob   │ mobile │ 345-678      │
   └────┴───────┴────────┴──────────────┘

3 rows in set. Elapsed: 0.002 sec. 

  

  

Previous                                                    Next                                                    Home