ClickHouse中如何将字典数组拆分为列（Kafka解析场景）

需求说明 #

现有存储在ClickHouse中的数据，其中data字段是包含多个Map的数组，需要将数组展开为多行，同时提取每个Map中的id和name字段，将原单行数据转换为多行明细数据。

原表结构与测试数据 #

CREATE TABLE json(name String, data Array(Map(String, String)) ) ENGINE = Memory;

INSERT INTO json FORMAT JSONEachRow {"name": "asd", "data":[{"id":"1", "name": "test1"},{"id":"2", "name": "test2"}]};

原查询结果（单行）：

name  data 
asd   [{'id':'1', 'name': 'test1'},{'id':'2', 'name': 'test2'}]

解决方案 #

使用ARRAY JOIN展开数组，再从Map中提取对应字段：

基础查询语句

SELECT
    name,
    item['id'] AS id,
    item['name'] AS item_name
FROM json
ARRAY JOIN data AS item;

等价写法（使用mapGet函数）

SELECT
    name,
    mapGet(item, 'id') AS id,
    mapGet(item, 'name') AS item_name
FROM json
ARRAY JOIN data AS item;

查询结果 #

name  id  item_name
asd   1   test1
asd   2   test2

适配Kafka Engine场景 #

如果是从Kafka消费数据时直接转换格式，可以通过Kafka引擎表+物化视图实现自动展开：

1. 创建Kafka引擎表（消费原始数据）：

CREATE TABLE kafka_json (
    name String,
    data Array(Map(String, String))
) ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'your_kafka_broker:9092',
    kafka_topic_list = 'your_target_topic',
    kafka_group_name = 'clickhouse_consumer_group',
    kafka_format = 'JSONEachRow';

2. 创建物化视图自动展开并存储到目标表：

-- 先创建目标结果表
CREATE TABLE target_table (
    name String,
    id String,
    item_name String
) ENGINE = MergeTree()
ORDER BY name;

-- 创建物化视图关联Kafka表与目标表
CREATE MATERIALIZED VIEW mv_kafka_json TO target_table AS
SELECT
    name,
    item['id'] AS id,
    item['name'] AS item_name
FROM kafka_json
ARRAY JOIN data AS item;

此后Kafka中的数据会被自动消费、展开并存储到target_table中，直接查询该表即可得到多行明细数据。

内容的提问来源于stack exchange，提问作者LOTR