Вложенные структуры данных

Елена Дворядкина

ADQM/ClickHouse предоставляет тип данных Nested для хранения и обработки вложенных структур данных, который может служить альтернативой типам Tuple и Array(Tuple). Он позволяет моделировать сложные структуры данных без необходимости создания/хранения дополнительных таблиц и использования сложных запросов.

Вложенная структура данных Nested представляет собой таблицу внутри ячейки основной таблицы. Каждой строке основной таблицы может соответствовать любое количество строк во вложенной структуре данных.

Обзор

Чтобы описать в таблице столбец типа Nested, необходимо указать параметры вложенной структуры — имена столбцов и соответствующие типы данных.

Ниже приведен пример синтаксиса запроса CREATE TABLE для создания таблицы с вложенной структурой данных в столбце (с целью упрощения в примере используются краткие названия столбцов и конкретные типы данных):

CREATE TABLE test_table (
    a String,
    b UInt32,
    ...
    n Nested (
        c String,
        d Int64,
        ...
    )
)
ENGINE = MergeTree ORDER BY tuple();

К столбцам вложенной структуры данных можно обращаться в запросах, используя формат <nested_structure>.<nested_column> (где <nested_structure> — название столбца таблицы типа Nested, <nested_column> — название столбца вложенной структуры).

Формат данных столбцов вложенной структуры определяется настройкой flatten_nested, значение которой может быть равным 1 (по умолчанию) или 0.

flatten_nested = 1

Столбцы вложенной структуры имеют тип Array(Type), то есть хранят массивы значений соответствующих типов данных одинаковой длины. В этом случае при чтении и обработке значений вложенных столбцов можно использовать функции для работы с массивами, в том числе выражение ARRAY JOIN.

Посмотреть, какие типы данных определены для столбцов созданной таблицы, можно с помощью запроса SHOW CREATE TABLE:

SHOW CREATE TABLE test_table;
   ┌─statement─────────────────────────┐
1. │ CREATE TABLE default.test_table   │
   │ (                                 │
   │    `a` String,                    │
   │    `b` UInt32,                    │
   │    `n.c` Array(String),           │
   │    `n.d` Array(Int64)             │
   │ )                                 │
   │ ENGINE = MergeTree                │
   │ ORDER BY tuple()                  │
   │ SETTINGS index_granularity = 8192 │
   └───────────────────────────────────┘

Вставка данных

Для вставки данных в столбец типа Nested необходимо отдельно передать массив значений для каждого столбца вложенной структуры (система будет автоматически проверять, что все предаваемые массивы значений имеют одинаковую длину). Например:

INSERT INTO test_table VALUES ('a', 1, ['c1', 'c2', 'c3'], [100, 200, 300]);

В таблицу со столбцами, которые содержат вложенные структуры, можно также вставлять данные JSON с той же структурой. Например, используйте для этого в запросах формат JSONEachRow:

INSERT INTO test_table FORMAT JSONEachRow {"a":"a1", "b":2, "n.c":["c4", "c5"], "n.d":[101, 202]};

Чтобы передать данные как иерархический объект JSON, предварительно установите input_format_import_nested_json=1:

SET input_format_import_nested_json=1;
INSERT INTO test_table FORMAT JSONEachRow
{
    "a":"a2",
    "b":"3",
    "n": {
        "c": ["c6", "c7"],
        "d": [111, 222]
    }
};

Чтение данных

Запрос всех данных из таблицы выводит отдельно каждый столбец вложенной структуры:

SELECT * FROM test_table;
   ┌─a──┬─b─┬─n.c──────────────┬─n.d───────────┐
1. │ a  │ 1 │ ['c1','c2','c3'] │ [100,200,300] │
2. │ a1 │ 2 │ ['c4','c5']      │ [101,202]     │
3. │ a2 │ 3 │ ['c6','c7']      │ [111,222]     │
   └────┴───┴──────────────────┴───────────────┘

flatten_nested = 0

Данные всех столбцов вложенной структуры хранятся как один массив кортежей, то есть фактически столбец Nested имеет тип Array(Tuple). В этом случае поддерживается произвольный уровень вложенности.

Если пересоздать таблицу test_table, установив перед этим flatten_nested = 0, в выводе запроса SHOW CREATE TABLE для столбца n будет показан тип Nested, хотя фактически это Array(Tuple(…​)):

   ┌─statement──────────────────────────┐
1. │ CREATE TABLE default.test_table    │
   │ (                                  │
   │     `a` String,                    │
   │     `b` UInt32,                    │
   │     `n` Nested(c String, d Int64)  │
   │ )                                  │
   │ ENGINE = MergeTree                 │
   │ ORDER BY tuple()                   │
   │ SETTINGS index_granularity = 8192  │
   └────────────────────────────────────┘

Вставка данных

Соответственно данные во вложенную структуру необходимо вставлять как массив кортежей. Например:

INSERT INTO test_table VALUES ('a', 1, [('c1', 100), ('c2', 200), ('c3', 300)]);

Для вставки в таблицу с Nested-столбцом иерархического объекта JSON при flatten_nested = 0 не обязательно активировать опцию input_format_import_nested_json, при этом данные для вложенных столбцов нужно передавать как массив:

INSERT INTO test_table FORMAT JSONEachRow
{
    "a":"a1",
    "b":"2",
    "n": [
        {"c": "c4", "d": 101},
        {"c": "c5", "d": 202}
        ]
};

Чтение данных

Запрос всех данных из таблицы выводит столбец типа Nested, где каждая строка — массив кортежей значений столбцов вложенной структуры:

SELECT * FROM test_table;
   ┌─a──┬─b─┬─n──────────────────────────────────┐
1. │ a  │ 1 │ [('c1',100),('c2',200),('c3',300)] │
2. │ a1 │ 2 │ [('c4',101),('c5',202)]            │
   └────┴───┴────────────────────────────────────┘

Данные отдельных вложенных столбцов можно также запрашивать, используя нотацию с точкой:

SELECT n.c, n.d FROM test_table;
   ┌─n.c──────────────┬─n.d───────────┐
1. │ ['c1','c2','c3'] │ [100,200,300] │
2. │ ['c4','c5']      │ [101,202]     │
   └──────────────────┴───────────────┘

Пример

Создание таблицы

Создайте таблицу orders для хранения данных о заказах в столбцах соответствующих типов:

  • order_id (UInt64) — идентификатор заказа;

  • customer_id (UInt32) — идентификатор покупателя;

  • order_date (Date) — дата заказа.

Добавьте также в таблицу столбец для хранения информации о продуктах, которые включает каждый заказ — столбец items типа Nested с описанием следующих полей вложенной структуры:

  • product_id (UInt32) — идентификатор продукта;

  • product_name (String) — название продукта;

  • quantity (UInt16) — количество единиц продукта в заказе;

  • price_per_unit (Decimal) — стоимость единицы продукта.

Значение параметра flatten_nested — 1 (по умолчанию).

CREATE TABLE orders (
    order_id UInt64,
    customer_id UInt32,
    order_date Date,
    items Nested (
        product_id UInt32,
        product_name String,
        quantity UInt16,
        price_per_unit Decimal(10, 2)
    )
) ENGINE = MergeTree()
ORDER BY (order_date, order_id);

Вставка данных

Вставьте в таблицу тестовые данные (например, несколько заказов за два дня):

INSERT INTO orders VALUES
(1001, 42, '2025-08-07',
    [101, 102, 103],
    ['Laptop', 'Mouse', 'Keyboard'],
    [1, 2, 1],
    [999.99, 19.99, 49.99]),
(1002, 77, '2025-08-07',
    [104, 105],
    ['Monitor', 'Headphones'],
    [2, 1],
    [199.99, 89.99]),
(1003, 55, '2025-08-07',
    [101, 104, 102],
    ['Laptop', 'Monitor', 'Mouse'],
    [1, 1, 1],
    [999.99, 199.99, 19.99]),
(1004, 24, '2025-08-08',
    [102, 105],
    ['Mouse', 'Headphones'],
    [3, 4],
    [19.99, 89.99]),
(1005, 38, '2025-08-08',
    [101, 103, 104],
    ['Laptop', 'Keyboard', 'Monitor'],
    [1, 1, 1],
    [999.99, 49.99, 199.99]);

Чтение данных

Запросите все данные таблицы:

SELECT * FROM orders;
   ┌─order_id─┬─customer_id─┬─order_date─┬─items.product_id─┬─items.product_name──────────────┬─items.quantity─┬─items.price_per_unit──┐
1. │     1001 │          42 │ 2025-08-07 │ [101,102,103]    │ ['Laptop','Mouse','Keyboard']   │ [1,2,1]        │ [999.99,19.99,49.99]  │
2. │     1002 │          77 │ 2025-08-07 │ [104,105]        │ ['Monitor','Headphones']        │ [2,1]          │ [199.99,89.99]        │
3. │     1003 │          55 │ 2025-08-07 │ [101,104,102]    │ ['Laptop','Monitor','Mouse']    │ [1,1,1]        │ [999.99,199.99,19.99] │
4. │     1004 │          24 │ 2025-08-08 │ [102,105]        │ ['Mouse','Headphones']          │ [3,4]          │ [19.99,89.99]         │
5. │     1005 │          38 │ 2025-08-08 │ [101,103,104]    │ ['Laptop','Keyboard','Monitor'] │ [1,1,1]        │ [999.99,49.99,199.99] │
   └──────────┴─────────────┴────────────┴──────────────────┴─────────────────────────────────┴────────────────┴───────────────────────┘

Чтобы вывести данные в обычном табличном виде, развернув Nested-структуру в несколько строк, используйте в запросе ARRAY JOIN:

SELECT * FROM orders ARRAY JOIN items;
    ┌─order_id─┬─customer_id─┬─order_date─┬─items.product_id─┬─items.product_name─┬─items.quantity─┬─items.price_per_unit─┐
 1. │     1001 │          42 │ 2025-08-07 │              101 │ Laptop             │              1 │               999.99 │
 2. │     1001 │          42 │ 2025-08-07 │              102 │ Mouse              │              2 │                19.99 │
 3. │     1001 │          42 │ 2025-08-07 │              103 │ Keyboard           │              1 │                49.99 │
 4. │     1002 │          77 │ 2025-08-07 │              104 │ Monitor            │              2 │               199.99 │
 5. │     1002 │          77 │ 2025-08-07 │              105 │ Headphones         │              1 │                89.99 │
 6. │     1003 │          55 │ 2025-08-07 │              101 │ Laptop             │              1 │               999.99 │
 7. │     1003 │          55 │ 2025-08-07 │              104 │ Monitor            │              1 │               199.99 │
 8. │     1003 │          55 │ 2025-08-07 │              102 │ Mouse              │              1 │                19.99 │
 9. │     1004 │          24 │ 2025-08-08 │              102 │ Mouse              │              3 │                19.99 │
10. │     1004 │          24 │ 2025-08-08 │              105 │ Headphones         │              4 │                89.99 │
11. │     1005 │          38 │ 2025-08-08 │              101 │ Laptop             │              1 │               999.99 │
12. │     1005 │          38 │ 2025-08-08 │              103 │ Keyboard           │              1 │                49.99 │
13. │     1005 │          38 │ 2025-08-08 │              104 │ Monitor            │              1 │               199.99 │
    └──────────┴─────────────┴────────────┴──────────────────┴────────────────────┴────────────────┴──────────────────────┘

По столбцам вложенной структуры можно применять агрегирование. Например, следующий запрос вычисляет сколько единиц каждого продукта было продано за месяц (количество по всем заказам):

SELECT
    formatDateTime(order_date, '%M') as month,
    items.product_name as product,
    SUM(items.quantity) as units_sold
FROM orders
ARRAY JOIN items
GROUP BY
    formatDateTime(order_date, '%M'),
    items.product_name;
   ┌─month──┬─product────┬─units_sold─┐
1. │ August │ Keyboard   │          2 │
2. │ August │ Headphones │          5 │
3. │ August │ Laptop     │          3 │
4. │ August │ Mouse      │          6 │
5. │ August │ Monitor    │          4 │
   └────────┴────────────┴────────────┘

По полю Nested-структуры можно также фильтровать данные. Например, следующий запрос выводит продажи продукта Laptop за каждый день:

SELECT
    order_date,
    items.product_name AS product ,
    SUM(items.quantity) AS quantity
FROM orders
ARRAY JOIN items
WHERE items.product_name = 'Laptop'
GROUP BY
    order_date,
    items.product_name;
   ┌─order_date─┬─product─┬─quantity─┐
1. │ 2025-08-07 │ Laptop  │        2 │
2. │ 2025-08-08 │ Laptop  │        1 │
   └────────────┴─────────┴──────────┘

Чтобы вернуть только одну строку с количеством проданных единиц каждого продукта для каждой даты, можно использовать подзапрос и функцию groupArray:

SELECT
    order_date,
    groupArray((product, units_sold))
FROM
(
    SELECT
        order_date,
        product,
        sum(product_sales) AS units_sold
    FROM orders
    ARRAY JOIN
        items.product_name AS product,
        items.quantity AS product_sales
    GROUP BY
        order_date,
        product
)
GROUP BY order_date;
   ┌─order_date─┬─groupArray((product, units_sold))────────────────────────────────────────┐
1. │ 2025-08-07 │ [('Laptop',2),('Mouse',3),('Monitor',3),('Keyboard',1),('Headphones',1)] │
2. │ 2025-08-08 │ [('Keyboard',1),('Mouse',3),('Monitor',1),('Headphones',4),('Laptop',1)] │
   └────────────┴──────────────────────────────────────────────────────────────────────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней