Nested columns support

ClickHouse's support for Nested columns has limitations - namely, Nested columns cannot be SELECTed/INSERTed directly. However, they can be worked with using array semantics, and ClickHouse.Client supports it.

For example, given table with following structure:

CREATE TABLE IF NOT EXISTS test.nested (id UInt32, params Nested (param_id UInt8, param_val String)) ENGINE = Memory

For query purposes, ClickHouse will treat it as table with 3 fields:

• id: UInt32,
• params.param_id: Array(UInt8)
• params.param_val Array(String)

Given that, we can use ClickHouseBulkCopy to insert the data into this table in following way:

using var bulkCopy = new ClickHouseBulkCopy(connection) { DestinationTableName = "test.nested" };
// Note again that technically the table has 2 columns, but we are preparing 3-column rows
var row1 = new object[] { 1, new[] { 1, 2, 3 }, new[] { "v1", "v2", "v3" } };
var row2 = new object[] { 2, new[] { 4, 5, 6 }, new[] { "v4", "v5", "v6" } };

await bulkCopy.WriteToServerAsync(new[] { row1, row2 }, CancellationToken.None);

And we can get the results back in same way:

using var reader = await connection.ExecuteReaderAsync("SELECT * FROM test.nested ORDER BY id ASC");
var values = Enumerable.Range(0, reader.FieldCount).Select(reader.GetValue).ToArray(); // First row
Assert.AreEqual(1, values[0]);  // `id` column
CollectionAssert.AreEquivalent(new[] { 1, 2, 3 }, values[1] as IEnumerable); // `params.param_id` pseudo-column
CollectionAssert.AreEquivalent(new[] { "v1", "v2", "v3" }, values[2] as IEnumerable); // `params.param_val` pseudo-column

See also

• ClickHouse docs on Nested
• Bulk insertion