在ClickHouse中解析Array(String)类型JSON并生成结构化表求助

假设你的数据源是ClickHouse中的Array(string)类型，以下针对两种常见数据结构场景给出解决方法：

场景1：Array(string)每个元素是含顶层字段+goods列表的完整JSON对象 #

比如数组元素格式类似：

{"id":1,"categ":"electronics","fig":"phone","goods":[{"g_id":101,"code":"P001"},{"g_id":102,"code":"P002"}]}

使用ARRAY JOIN配合JSON解析函数展开并提取字段：

SELECT
    JSONExtractInt(raw_json, 'id') AS id,
    JSONExtractString(raw_json, 'categ') AS categ,
    JSONExtractString(raw_json, 'fig') AS fig,
    JSONExtractInt(goods_item, 'g_id') AS g_id,
    JSONExtractString(goods_item, 'code') AS code
FROM
    your_table
ARRAY JOIN
    -- 展开外层的Array(string)数组
    your_array_column AS raw_json,
    -- 提取每个JSON中的goods数组并展开
    JSONExtractArrayRaw(raw_json, 'goods') AS goods_item

场景2：顶层字段单独存储，goods为Array(string)类型列 #

如果表结构是：

CREATE TABLE your_table (
    id Int64,
    categ String,
    fig String,
    goods Array(String) -- 每个元素是{"g_id":xxx,"code":xxx}格式的字符串
)

直接展开goods数组并解析子字段：

SELECT
    id,
    categ,
    fig,
    JSONExtractInt(goods_item, 'g_id') AS g_id,
    JSONExtractString(goods_item, 'code') AS code
FROM
    your_table
ARRAY JOIN
    goods AS goods_item

关键注意事项 #

• 确保JSON字符串格式合法，否则JSONExtract系列函数会返回NULL
• 根据字段实际类型选择对应解析函数：整数用JSONExtractInt、字符串用JSONExtractString，避免类型不匹配
• 若要保留goods列表为空的行，将ARRAY JOIN替换为LEFT ARRAY JOIN

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