问题背景 #

我有一个ClickHouse表，结构和数据如下：

原始表数据 #

注意：json列里的cost值既有字符串类型（如"170.00000"）也有数值类型（如170）。

预期结果 #

我需要将JSON列规范化，同时保留每行的id，最终得到如下格式的结果：

我试过基础的JSON函数，但它们处理不了我这种数组格式，想问问有没有办法把JSON数组按行展开？

解决方案 #

当然可以！在ClickHouse里，你可以用JSONExtractArrayRaw配合arrayJoin来展开数组，再用JSONExtractString和类型转换函数来提取和统一字段类型，具体SQL如下：

SELECT
    id,
    JSONExtractString(item, 'productId') AS productId,
    toInt64OrNull(JSONExtractRaw(item, 'cost')) AS cost -- 统一转成整数类型，适配字符串/数值两种cost格式
FROM
    your_table_name, -- 替换成你的实际表名
    arrayJoin(JSONExtractArrayRaw(json)) AS item -- 替换`json`为你的JSON列名
WHERE
    cost IS NOT NULL -- 过滤掉可能的无效cost值
ORDER BY
    id, productId;

代码说明 #

1. JSONExtractArrayRaw(json)：把JSON数组转成字符串数组，每个元素对应原数组里的单个JSON对象字符串；
2. arrayJoin(...) AS item：将数组的每个元素拆成单独的行，同时自动关联保留原始的id；
3. JSONExtractString(item, 'productId')：从每个JSON对象字符串中提取productId字段；
4. toInt64OrNull(JSONExtractRaw(item, 'cost'))：先提取cost的原始值（兼容字符串/数值格式），再转换为整数类型，OrNull后缀能避免格式错误导致的查询失败；
5. WHERE cost IS NOT NULL可以过滤掉转换失败的无效数据，ORDER BY用来对齐预期结果的排序逻辑。

适配不同场景 #

如果你的cost需要保留小数精度，可以把toInt64OrNull换成toFloat64OrNull：

toFloat64OrNull(JSONExtractRaw(item, 'cost')) AS cost

如果你的JSON列是String类型（而非ClickHouse原生JSON类型），上述函数依然适用，因为ClickHouse的JSON函数可以直接处理JSON格式的字符串内容。

这样就能完美得到你想要的规范化结果啦！