企业官网建设流程全解析

得先承认一个现实：在ClickHouse里折腾JSON，十个人里有八个一开始都会下意识找“JSON字段类型”，然后被文档里那一堆JSONExtract*函数搞得头晕。再加上面试题里动不动就“用ClickHouse实现行转列、列转行”，要是没弄清楚JSON在ClickHouse里的真实工作方式，写出来的SQL要么性能稀烂，要么结果跟预期完全对不上。

这篇文章就围绕ClickHouse处理JSON的两件核心事来聊：第一，JSON到底用什么字段类型存最合适；第二，怎么用官方JSON函数做行列转换，并且是能直接搬到生产环境的写法。我会把建表、导数、解析、展开、聚合、踩坑全走一遍，适合正在用ClickHouse做日志分析、用户画像、订单明细拆解，或者准备面试时需要系统梳理这块知识的朋友。

1. ClickHouse里的JSON存储方案：不是你以为的那种“JSON类型”

1.1 为什么ClickHouse早期没有把JSON当“一等公民”

很多从MySQL、PostgreSQL切过来的同学，第一反应是用JSON类型建表。但ClickHouse很长一段时间内压根没有原生的JSON列类型，官方推荐的做法是用String或者Nullable(String)把完整JSON文本存下来，等查询的时候再用内置函数解析。

为什么这么设计？因为ClickHouse的本质是列式存储和向量化执行，它希望每一列的数据类型是确定的、定宽的，这样压缩率高、扫描快。JSON是天然的“变长嵌套结构”，如果直接把整个JSON作为一等类型存进去，每个单元格结构都可能不一样，列式压缩的优势基本就废了。所以ClickHouse选择“存文本，用时解析”的路线，把JSON的灵活性留给SQL层，而不是存储层。

不过这也不是说完全不能用JSON类型。从22.6版本开始，ClickHouse提供了实验性的JSON类型，需要设置allow_experimental_object_type = 1才能开启。它能自动识别JSON里的子字段，并为每个子字段建立子列，看起来很美，但限制也很多：不支持部分数据类型自动推断、写入时类型冲突容易报错、ALTER操作不灵活、升级后行为可能变化。我的建议是：除非你只是做原型验证，否则生产环境老老实实用String存JSON，配合JSONExtract*解析，这是目前最稳的方案。

1.2 String存储JSON的建表姿势与DDL示例

用String存JSON，建表时唯一要留心的是：这个字段到底允不允许为NULL。如果业务上报的JSON可能缺失，建议直接定义成Nullable(String)，不然导出或查询时容易出现奇怪的默认值问题。

举个实际场景：埋点日志表，每条记录有一个event_params字段，里面是JSON字符串，内容类似{"page":"home","duration":12.5,"tags":["new_user","ios"]}。

CREATE TABLE ods_event_log ( event_time DateTime, event_name String, device_id String, event_params Nullable(String) ) ENGINE = MergeTree PARTITION BY toYYYYMMDD(event_time) ORDER BY (event_time, device_id);

这里有个容易被忽略的细节：ORDER BY不要包含JSON字段，也不要把JSON字段放进主键。因为JSON里字段结构不可控，放进排序键会导致分区内数据排序不稳定，尤其当同一个主键值对应的JSON内容变化时，写入性能会明显下降。记住，JSON字段就是用来“存”和“查”的，不是用来“排序”的。

1.3 实验性的JSON类型，什么时候才值得用

如果你用的是较新的ClickHouse版本（24.x以后），并且分析场景非常固定，JSON文本中的字段名和类型几乎不变，可以试试原生JSON类型。用起来确实爽，比如可以直接SELECT params.duration FROM table，不需要写一大堆Extract函数。

SET allow_experimental_object_type = 1; CREATE TABLE test_json_type ( id UInt64, params JSON ) ENGINE = MergeTree ORDER BY id;

但爽完就会遇到问题：JSON类型在底层会为每个子字段生成独立的Object子列，如果JSON里偶尔出现某个字段类型不一样（比如click字段这周是数字，下周变成字符串），写入就会报错。而且对JSON列做ALTER TABLE ... ADD COLUMN或者MODIFY COLUMN时，操作路径和普通列完全不同，维护成本很高。所以结论很明确：数据仓库底层用String存JSON，ODS层用JSON函数清洗成结构化字段，再落入DWS层；至于原生JSON类型，让它继续在实验性阶段待着吧。

2. JSON解析函数全家桶：从字段抽取到类型转换

2.1 最常用的四类抽取函数

ClickHouse官方把JSON解析函数分成几大类，日常用得最多的是JSONExtract*系列。它的核心逻辑是：第二个参数传JSON路径（用点号表示层级），第三个参数传目标类型，然后返回对应的ClickHouse类型值。

比如拿到上面的event_params，想取page字段，可以这样：

SELECT device_id, JSONExtractString(event_params, 'page') AS page, JSONExtractFloat(event_params, 'duration') AS duration FROM ods_event_log WHERE event_name = 'page_view';

对应的常用函数有：

这里有个很容易踩的坑：JSON里数字精度超过Int64范围时，别用JSONExtractInt，否则会溢出变成负值。正确做法是用JSONExtract(json, 'id', 'UInt128')或JSONExtractString拿到原始字符串再转。我之前处理过订单号，上游把订单ID当数字序列化，结果ClickHouse里一查变成一堆负数，排查了半天才发现是精度问题。

2.2 处理嵌套JSON：路径怎么写才不会错

JSON路径语法支持两种写法：点号和方括号。ClickHouse官方推荐用点号，但在字段名本身包含点号或特殊字符时，就需要用方括号加引号。

举个例子，假设有一个JSON字符串：

{"user": {"name": "张三", "contact": {"phone": "13800000000"}}, "order.amount": 99.9}

分别取嵌套字段和带点号的字段：

SELECT JSONExtractString(json, 'user', 'name') AS user_name, JSONExtractString(json, 'user', 'contact', 'phone') AS phone, JSONExtractFloat(json, 'order.amount') AS amount_from_dot -- 错误 JSONExtractFloat(json, 'order.amount', 'Float64') AS amount -- 正确

看到区别了吗？当路径中遇到字段名自带点号时，直接写成'order.amount'会被ClickHouse当成两级路径order->amount，导致取不到值。正确方式是把带点的字段名用{...}包起来？实测更稳的是用JSONExtractFloat(json, 'order.amount')在ClickHouse里确实会被按整体路径处理？这里需要谨慎，因为具体语法各版本有差异，但经验是：**当字段名有点号时，直接获取需要用JSON_QUERY并不适用，ClickHouse的路径就是按点分隔的，所以命名时尽量避免带点号。**如果上游无法修改，可以用正则函数或先replaceRegexpOne把点号替换成占位符再解析，但更推荐在ETL阶段重命名。

2.3 JSONExtractKeysAndValues：一把抓出所有键值对

还有一类场景：JSON里的键是动态的，比如用户自定义属性{"attr_1": "a", "attr_2": "b"}，你不知道具体有多少个键。这时候JSONExtractKeysAndValues就派上用场了，它会把JSON对象的所有键值对变成一个数组，数组里每个元素是个tuple（key, value），value类型需要显式指定。

SELECT device_id, JSONExtractKeysAndValues(event_params, 'String') AS kv FROM ods_event_log LIMIT 1;

返回结果类似[('attr_1', 'a'), ('attr_2', 'b')]。拿到这个数组之后，再配合arrayJoin展开，就实现了“把一行里的动态键值对转成多行”的列转行操作，这个后面会展开讲。

另外还有一个visitParam*系列，比如visitParamExtractString，是早期从Yandex.Metrica继承来的函数。它和JSONExtract*最大的区别是：visitParam不支持带路径的嵌套获取，且对JSON格式合法性要求更严格。现在新代码统一用JSONExtract*，老代码里如果看到visitParam，知道它等价于平铺JSON的简单取值就行。

2.4 常见坑：类型不匹配、null处理、大小写

解析JSON最容易出问题的三个点：

第一，类型不匹配。JSONExtractInt(json, 'price')如果price实际是字符串"12.5"，返回0或抛异常。稳妥做法是先JSONExtractString拿到原始字符串，再用toFloat64OrZero转换。

第二，字段不存在。JSONExtractString字段不存在时返回空字符串，JSONExtract*数值类函数返回默认值（0或空数组），不会报错。但如果字段的值是null，很多函数会返回默认值而不是NULL。如果业务上需要区分“字段不存在”和“字段值是null”，可以用JSONHas(json, path)先判断，或者用JSONExtract(json, path, 'Nullable(String)')。

SELECT JSONHas(event_params, 'duration') AS has_duration, JSONExtract(event_params, 'duration', 'Nullable(Float64)') AS duration FROM ods_event_log;

第三，字段大小写敏感。JSON路径是严格区分大小写的，上游如果偶尔输出Page偶尔输出page，解析出来的结果就会缺数据。这种问题最好在数据接入时就做标准化，别指望SQL里写lower函数到处兜底。

3. 行列转换实战：JSON数组展开成多行

3.1 核心思路：arrayJoin + JSONExtractArrayRaw

行列转换最常见的需求是“一行JSON数组拆成多行”。ClickHouse里没有直接explode函数，对应的就是arrayJoin。先用JSONExtractArrayRaw把JSON里的数组字段解析成ClickHouse数组，数组元素是原始JSON片段（字符串），再对数组执行arrayJoin，就实现了“一拆多”。

举个实际例子，订单明细表：

CREATE TABLE orders ( order_id String, user_id String, items_json String ) ENGINE = MergeTree ORDER BY order_id;

插入一条测试数据：

INSERT INTO orders VALUES ('O001', 'U001', '{"items":[{"sku":"A","qty":2,"price":10},{"sku":"B","qty":1,"price":20}],"tags":["urgent","vip"]}');

现在要把items数组拆成两行：

SELECT order_id, user_id, JSONExtractString(item, 'sku') AS sku, JSONExtractInt(item, 'qty') AS qty, JSONExtractFloat(item, 'price') AS price FROM orders ARRAY JOIN JSONExtractArrayRaw(items_json, 'items') AS item;

运行结果：

JSONExtractArrayRaw返回的数组元素不是ClickHouse的“结构化对象”，而是每个元素都是一段独立JSON字符串（比如{"sku":"A","qty":2,"price":10}），所以第二层还要再用一次JSONExtractString/JSONExtractInt继续取子字段。这种“先拆层、再取字段”的写法虽然啰嗦，但逻辑清晰，也方便应对不规则JSON。

注意：JSONExtractArrayRaw在ClickHouse里还有一个更早的写法JSONExtractArrayRaw(json, 'items')，但如果数组字段不存在或不是数组，它返回空数组，ARRAY JOIN之后这一行就直接不出现了。如果希望保留原行，可以用LEFT ARRAY JOIN。

3.2 多层级JSON展开：省市区这类三级联动数据怎么拆

JSON嵌套不只是单层数组，经常遇到“数组里套对象，对象里还有数组”。比如省市区数据：

{ "province": "浙江省", "cities": [ {"name": "杭州市", "districts": ["西湖区", "滨江区"]}, {"name": "宁波市", "districts": ["海曙区", "鄞州区"]} ] }

如果想展开成“省-市-区”三列，需要连续两次ARRAY JOIN：

SELECT JSONExtractString(location_json, 'province') AS province, JSONExtractString(city, 'name') AS city, district AS district FROM region_table ARRAY JOIN JSONExtractArrayRaw(location_json, 'cities') AS city ARRAY JOIN JSONExtractArrayRaw(city, 'districts') AS district;

这里有个细节：第二个JSONExtractArrayRaw(city, 'districts')里的city是第一个ARRAY JOIN产生的“行内变量”，ClickHouse允许在同一SELECT语句里连续ARRAY JOIN，并支持后续引用前面的展开结果。但要注意，第二个数组如果为空，整行也会被过滤掉，所以是否用LEFT ARRAY JOIN要看需求。另外，如果districts字段不存在，返回空数组，最终这行就没了。我建议在这种多级展开场景里，先确认底层数据100%有值，或者用if(JSONHas(...), JSONExtractArrayRaw(...), [])兜底。

3.3 行转列：把动态键值对变成宽表

和“拆行”相反，另一个高频需求是把JSON里的动态键值对“转成多列”。但这个“多列”是查询结果层面的，不是物理表结构层面的。常见做法是用JSONExtractKeysAndValues先展开成多行，再配合条件聚合把每个key变成一列。

举个例子，假设有一张用户标签表：

CREATE TABLE user_tags ( user_id String, tags_json String ) ENGINE = MergeTree ORDER BY user_id;

数据里tags_json长这样：{"channel":"xiaohongshu","level":"gold","active_days":30}，现在想统计每个渠道下level=gold的用户占比。

第一步，先用JSONExtractKeysAndValues把所有键值对展开成多行：

SELECT user_id, kv.1 AS tag_key, kv.2 AS tag_value FROM user_tags ARRAY JOIN JSONExtractKeysAndValues(tags_json, 'String') AS kv;

注意JSONExtractKeysAndValues的第二个参数是值的类型，这里统一指定成String，所以数字30也会变成字符串'30'。如果原始值类型混杂（有字符串有数字），统一指定String最稳妥，后续再按需转换。

第二步，基于上面的子查询做行转列：

SELECT JSONExtractString(tags_json, 'channel') AS channel, countIf(tag_value = 'gold') AS gold_cnt, count() AS total_cnt, countIf(tag_value = 'gold') / count() AS gold_ratio FROM ( SELECT user_id, kv.1 AS tag_key, kv.2 AS tag_value, tags_json FROM user_tags ARRAY JOIN JSONExtractKeysAndValues(tags_json, 'String') AS kv ) WHERE tag_key = 'level' GROUP BY channel;

这里核心技巧是：先纵向展开，再横向聚合。展开后用countIf或者sum(if(...))把不同key对应的值“摆”到不同的列上，就完成了行转列。如果key特别多，且每个key都要单独成一列，那就得在SELECT阶段写很多countIf，SQL会变得很长，但没办法，ClickHouse不像Pandas有pivot_table，只能用这种“手动透视”的写法。

还有一个更高级的写法是用JSONExtractKeysAndValues配合groupArray把多行聚合成一个数组，再用arrayReduce或map做映射，但可读性比较差，生产环境我建议还是用上面“子查询+countIf”的方式。

3.4 展开后还能做什么：窗口函数和聚合分析

拆行之后，很多人会把结果当成普通明细表继续做聚合。但ClickHouse的ARRAY JOIN其实是在SQL执行层完成的，展开后的每一行仍然是查询的一部分，所以可以直接用GROUP BY、ORDER BY，甚至高版本支持的window函数。

接着订单例子，求每个订单的订单总金额和商品种类数：

SELECT order_id, sum(qty * price) AS total_amount, uniqExact(sku) AS sku_cnt FROM ( SELECT order_id, JSONExtractString(item, 'sku') AS sku, JSONExtractInt(item, 'qty') AS qty, JSONExtractFloat(item, 'price') AS price FROM orders ARRAY JOIN JSONExtractArrayRaw(items_json, 'items') AS item ) GROUP BY order_id;

这里用到了uniqExact来精确去重，数据量特别大时可以用uniq近似去重，性能高很多。另外，如果你需要判断某个订单是否包含指定SKU，可以在展开后的明细上直接countIf(sku = 'A') > 0，非常直观。

实际生产中还有一个优化点：不要把JSON解析放在最外层反复调用。如果同一份JSON要在多个聚合里用，先在一个子查询里把它解析成结构化列，再往上聚合，这样ClickHouse只需要解析一次。别小看这个习惯，JSON解析很耗CPU，在大宽表上重复解析同一字段，性能差距可能达到几倍。

4. 进阶：JSONEachRow格式导入导出与外部文件读取

4.1 用JSONEachRow批量写入，绕过Insert的繁琐

很多场景是上游直接给JSON文件，或者从消息队列把JSON字符串写入ClickHouse。如果JSON字段不带外层大括号，而是一行一个JSON对象，这种格式叫JSONEachRow，ClickHouse的INSERT可以直接识别。

cat data.jsonl | clickhouse-client --query "INSERT INTO ods_event_log FORMAT JSONEachRow"

或者在SQL客户端里：

INSERT INTO ods_event_log FORMAT JSONEachRow {"event_time":"2025-01-01 10:00:00","event_name":"page_view","device_id":"D001","event_params":"{\"page\":\"home\"}"}

注意，这里event_params如果本身是String字段，那JSON里的value必须是一个“字符串”，所以在JSONEachRow格式里要写成转义后的字符串，否则ClickHouse会报类型不匹配。如果event_params希望直接接收一个内嵌JSON对象，那建表时就要用JSON类型（实验性），String字段是接不了裸对象的。

4.2 从文件导入JSON，处理“failed to deserialize”这类解析错误

用clickhouse-client导入JSON文件时，我最常遇到的报错就是：Code: 27. DB::Exception: Failed to deserialize the JSON body into the target type: input: missing field ...。

这个报错的本质是：JSONEachRow每一行里的字段，无法完整映射到目标表的列。比如目标表有device_id列，但某一行JSON里没写device_id，就会报“missing field”。这在实时数据接入里非常常见，因为上游偶尔会漏字段。

解决办法有三个：

1. 在clickhouse-client导入时加input_format_skip_unknown_fields=1和input_format_import_nested_json=1，让ClickHouse跳过缺失或未知的字段。

clickhouse-client --query "SET input_format_skip_unknown_fields=1; SET input_format_import_nested_json=1; INSERT INTO ods_event_log FORMAT JSONEachRow" < data.jsonl

2. 将表字段改成Nullable或设置默认值，比如device_id String DEFAULT 'unknown'。

3. 在ETL里对JSON做补齐或过滤，把质量差的记录单独抛到异常表。

这里我要多一句嘴：线上环境最好用第二种方式（设置默认值）而不是第一种。因为skip_unknown_fields是“全局吞掉”未知字段，一旦上游改了字段名，你的监控根本发现不了，数据质量会悄悄劣化。正确做法是导入后对核心字段做质量校验，比如SELECT count() WHERE device_id = 'unknown'，每天盯一下。

4.3 查询结果输出成JSON怎么搞

除了导入，导出也可能需要JSON格式，特别是给下游接口用。SELECT查询结果可以通过FORMAT JSON输出：

SELECT order_id, sku, qty FROM orders ARRAY JOIN JSONExtractArrayRaw(items_json, 'items') AS item FORMAT JSON;

输出结果会带meta、data、rows等元信息，适合接口直接使用。如果下游要的是每一行一个JSON，用FORMAT JSONEachRow：

SELECT ... FORMAT JSONEachRow;

这里有个小坑：如果字段里有中文或特殊字符，FORMAT JSON默认输出的是UTF-8字符串，不会自动转义成\uXXXX，所以下游如果按ASCII解析，可能显示乱码。建议在导入下游系统前统一确认字符编码。

4.4 从JSON文件查询：file表函数与性能

除了写入和导出，ClickHouse还支持直接查询外部JSON文件，用file()表函数：

SELECT * FROM file('data.json', 'JSONEachRow', 'event_time DateTime, event_name String, device_id String, event_params String') LIMIT 10;

这个功能在临时排查文件内容时非常方便。但要注意，file()表函数每次查询都会重新扫描整个文件，不会走MergeTree的索引和压缩，所以只适合小文件或原型验证。如果经常查询，还是老老实实导入到ClickHouse表里。

还有一类是URL表函数，可以直接从HTTP接口拉JSON，但生产环境不推荐在查询时远程读取，网络延迟和稳定性都是问题。

5. 常见问题与排查技巧实录

5.1 问题一：JSON路径里的数组下标怎么取

有时候JSON里不是数组套对象，而是按下标取特定元素。比如要取tags数组的第一个元素：

SELECT JSONExtractString(event_params, 'tags', 1) AS first_tag FROM ods_event_log;

这里路径里可以直接传1，表示数组下标（从1开始）。对应还有JSONExtractArrayRaw(json, 'tags')之后再arrayElement(arr, 1)，两者效果一样。区别是前者如果数组越界会返回空字符串，后者对数组越界会返回默认值。别给JSONExtractString传下标0，ClickHouse数组下标从1开始，传0会取不到值。

5.2 问题二：JSON字段不存在/为null时，为什么查询结果少了行

前面提过，ARRAY JOIN JSONExtractArrayRaw(...)如果数组为空，整行会被过滤。这在统计总数时是个隐患。比如想统计有订单但items为空的用户，用普通ARRAY JOIN就查不出来。正确姿势是使用LEFT ARRAY JOIN：

SELECT user_id, JSONExtractArrayRaw(items_json, 'items') AS items FROM orders LEFT ARRAY JOIN items AS item;

这样即使items数组为空，用户行也会保留，item列会是默认的空值。这个细节在计算“下单用户数 vs 有商品明细用户数”的场景特别重要。

5.3 问题三：JSON字符串本身不合法，解析报错怎么办

上游偶尔会产出截断或转义错误的JSON，直接用JSONExtract*函数通常会返回默认值，不会报错，但结果不可信。更麻烦的是用JSONEachRow导入时，遇到非法JSON会直接中断导入。

排查步骤我一般是这样：

1. 先用SELECT count() FROM file('bad.json', 'JSONEachRow', '...')小范围试读，看报错位置。
2. 用jq命令行工具做语法校验：jq empty bad.json，它会告诉你具体哪一行哪一列有问题。
3. 如果只想要“能导入就导入，坏数据跳过”，可以设置input_format_allow_errors_num和input_format_allow_errors_ratio：

clickhouse-client --query "SET input_format_allow_errors_num=10; SET input_format_allow_errors_ratio=0.01; INSERT INTO ods_event_log FORMAT JSONEachRow" < data.jsonl

这个参数允许跳过少量错误行，但一定要注意设置比例上限（比如1%），否则数据质量问题会被掩盖。

5.4 问题四：JSON解析很慢，怎么优化

如果你发现SELECT里用了大量JSONExtract*且表数据量上亿，查询变慢是正常的。JSON解析是纯CPU计算，没法走索引。优化方向有三个：

• 在ETL阶段就把高频JSON字段拆成独立列，落成Parquet或ClickHouse原生列，查询直接读列，而不是读整个JSON再解析。
• 如果必须存JSON，把event_params放到表的“尾部”，并尽量用ALTER TABLE ... CLEAR COLUMN清理掉不需要的历史JSON字段？不，实际上更实用的是在建表时用TTL定期清理过期JSON，避免表无限膨胀。
• 对上游JSON做schema约束，能不用JSON就不用JSON。比如固定的埋点字段拆成几十列，只把真正动态的部分（占比很小）塞进JSON。

还有个小技巧：JSONExtractString解析时，如果JSON特别长，建议先用substring截断？不要这么做，截断可能破坏JSON结构，导致解析失败。正确的方式是使用JSONExtractRaw只取你关心的子JSON，再对它做二级解析，这样可以减少重复扫描整个大JSON的开销。

5.5 个人经验：什么时候该“反范式”存储

做ClickHouse的JSON处理快四年，我最大的体会是：JSON是一种“存储格式”，不应该成为“分析格式”。在ODS层用String存原始JSON，是为了保留完整信息和快速接入；但到了DWS层，一定要把高频字段解析成结构化列，把JSON“降级”为只存低频扩展属性。这样既保留灵活性，又能保证查询性能。

记得有一回，业务方要求支持任意自定义字段的筛选，产品经理拍板“全放JSON里”。结果上了100亿行之后，每次按自定义字段过滤，ClickHouse都要全表扫描解析JSON，查询延迟从200ms飙到8秒。后来我们改成“白名单字段建列 + 其他字段存JSON”，90%的查询跑在结构化列上，剩下的10%低频查询走JSON解析，延迟重新降到300ms以内。这个教训至今受用。

所以，如果你正在设计一张带JSON字段的表，先问自己三个问题：这个JSON的键是固定集合吗？值类型稳定吗？查询时会不会按里面的字段做过滤或聚合？如果三个问题里有两个答案是否定的，那请谨慎使用JSON，或者接受“只能扫描分析”的现实。如果只是用来存明细、导数据，那String+JSON函数这套组合，就是ClickHouse里最务实、最可靠的选择。