Working with nested data type in ClickHouse

The nested data type in ClickHouse is used to store multiple tabular data inside a table. This is useful when a column is required to store multi-valued attributes. This article will look at the basic operations involving nested data types.

Creating a table

The table creation is similar to the regular syntax. Declare the nested column as Nested and declare the columns in the nested column along with their data type.

CREATE TABLE nested_example
(
ID UInt32,
nested_column Nested
(nested_id UInt32,
nested_name String)
)
Engine = MergeTree()
ORDER BY ID;

Inserting the data

At the time of insertion, specify the nested column data in square brackets (arrays) as in the below example.

chistadata :) INSERT INTO nested_example VALUES (1, [1,2,3], ['a', 'b', 'c']);
              

INSERT INTO nested_example FORMAT Values

Query id: bf2bf7d6-f7f6-4669-8a4a-e9f3bfd1031f

Ok.

1 row in set. Elapsed: 0.006 sec. 

Reading the data

Reading the data is via the familiar SELECT statement. The nested columns are specified using dot operators.

chistadata :) SELECT ID, nested_column.nested_id FROM nested_example WHERE nested_column.nested_id[1]=1 ;
              

SELECT
    ID,
    nested_column.nested_id
FROM nested_example
WHERE (nested_column.nested_id[1]) = 1

Query id: f0c23f7e-2f15-47a1-9752-89d3a1f45a36

┌─ID─┬─nested_column.nested_id─┐
│  1 │ [1,2,3]                 │
└────┴─────────────────────────┘

1 row in set. Elapsed: 0.013 sec. 

Thus, we have seen how to operate with nested data type in ClickHouse. We have used single nesting level in this article. It is also possible to have multiple nesting levels based flatten_nested ( when set as 0) setting.