得先承认一个现实在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存JSONODS层用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;对应的常用函数有函数返回值类型适用场景JSONExtractString(json, path)String字符串字段JSONExtractInt(json, path)Int64整数JSONExtractFloat(json, path)Float64浮点数JSONExtractUInt(json, path)UInt64无符号整数JSONExtractBool(json, path)Bool布尔值JSONExtract(json, path, Type)指定类型需要复杂类型或数字精度控制时这里有个很容易踩的坑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对象的所有键值对变成一个数组数组里每个元素是个tuplekey, valuevalue类型需要显式指定。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;运行结果order_iduser_idskuqtypriceO001U001A210O001U001B120JSONExtractArrayRaw返回的数组元素不是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 JOINSELECT 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}现在想统计每个渠道下levelgold的用户占比。第一步先用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阶段写很多countIfSQL会变得很长但没办法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对象这种格式叫JSONEachRowClickHouse的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”。这在实时数据接入里非常常见因为上游偶尔会漏字段。解决办法有三个在clickhouse-client导入时加input_format_skip_unknown_fields1和input_format_import_nested_json1让ClickHouse跳过缺失或未知的字段。clickhouse-client --query SET input_format_skip_unknown_fields1; SET input_format_import_nested_json1; INSERT INTO ods_event_log FORMAT JSONEachRow data.jsonl将表字段改成Nullable或设置默认值比如device_id String DEFAULT unknown。在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 JSONEachRowSELECT ... 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传下标0ClickHouse数组下标从1开始传0会取不到值。5.2 问题二JSON字段不存在/为null时为什么查询结果少了行前面提过ARRAY JOIN JSONExtractArrayRaw(...)如果数组为空整行会被过滤。这在统计总数时是个隐患。比如想统计有订单但items为空的用户用普通ARRAY JOIN就查不出来。正确姿势是使用LEFT ARRAY JOINSELECT 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会直接中断导入。排查步骤我一般是这样先用SELECT count() FROM file(bad.json, JSONEachRow, ...)小范围试读看报错位置。用jq命令行工具做语法校验jq empty bad.json它会告诉你具体哪一行哪一列有问题。如果只想要“能导入就导入坏数据跳过”可以设置input_format_allow_errors_num和input_format_allow_errors_ratioclickhouse-client --query SET input_format_allow_errors_num10; SET input_format_allow_errors_ratio0.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或者接受“只能扫描分析”的现实。如果只是用来存明细、导数据那StringJSON函数这套组合就是ClickHouse里最务实、最可靠的选择。
