1. 为什么我会在ClickHouse里认真对待JSON类型选择的现实考量1.1 String存JSON的老姿势为什么不够用很多人最开始接触ClickHouse里的JSON都是同一套做法建表时用一个String类型字段把整段JSON文本往里一塞查询的时候靠JSONExtractString、JSONExtractInt这些函数现取。这套方案在小数据量下确实没什么毛病但一旦表到了亿级行、JSON嵌套超过三层问题就开始扎堆涌现。我梳理一下实际业务中踩到的痛点查询时每次都要对整段JSON做文本解析哪怕你只需要其中一个嵌套字段解析开销也一点不少如果在WHERE条件里对JSON字段做函数运算ClickHouse基本没法走索引只能全表扫描查询从秒级直接变成分钟级JSON内部没有字段约束路径写错了不报错返回NULL或者空字符串最容易在数据统计时埋雷String类型存储重复key太浪费压缩比也不理想。我之前接手过一张累计120亿行的埋点表payload字段就是String存的JSON。业务方要按payload里的app_version做过滤一条SQL在20个并发下跑出40多秒。说实话这在ClickHouse里属于很不正常的现象。后来把高频字段抽成独立列查询直接掉到了2秒以内。核心原因不是ClickHouse慢而是把JSON解析成本全部压到了查询链路里。1.2 JSON字段类型到底解决了什么问题ClickHouse后来引入了原生JSON字段类型现在已经稳定可用。它最大的变化是插入数据时就把JSON字符串拆开按路径拆成子列存储。比如data.a、data.b.c每个路径单独存一份列数据。查询的时候直接读子列不需要在查询阶段重新解析整个JSON。这个设计对“一条JSON里只有少数几个路径会被高频查询”的场景增益非常明显。读取的数据量变小列存和压缩的优势也都发挥出来了。同时它还支持稀疏序列化一个路径在很多行里都不存在时不会老老实实为每一行都存一份默认值而是只记录有值的位置和值。你存几亿行“事件详情JSON”每个事件都只有少量扩展字段这个存储红利是实打实的。1.3 字段类型选型参考表我根据自己的实践整理了一张选型参考表遇到具体场景可以直接对照场景推荐方案原因路径固定20个以内、查询集中JSON字段类型子列读取快稀疏存储省空间路径极多且不固定比如日志埋点String JSONExtract 物化列避免动态子列膨胀导致表结构失控插入量极大、查询极少String写入成本低不需要额外解析需要频繁做行列转换String JSONExtractArrayRaw转换函数基于字符串JSON兼容性最好需要按JSON内部字段做范围过滤、排序JSON字段类型或物化普通列避免WHERE函数操作导致索引失效2. JSON字段类型的底层存储结构与查询代价拆解2.1 子列机制JSON类型是怎么实现按路径读取的JSON字段类型的核心思路是把一个JSON文档拆开每个叶子路径变成一个子列。举个实际例子CREATE TABLE test_json ( id UInt64, data JSON ) ENGINE MergeTree ORDER BY id;插入一条数据INSERT INTO test_json VALUES (1, {a: 1, b: {c: x, d: [1,2]}, e: {f: 0.5}});写入时ClickHouse会识别出data.a、data.b.c、data.b.d、data.e.f这些路径并各自维护子列。查询时可以直接用路径SELECT data.a, data.b.c, data.e.f FROM test_json;这就是JSON字段类型和“String JSONExtract”最本质的区别一个在写入时解析并拆分一个在查询时临时解析。子列的生成对用户是透明的你不需要手动声明有哪些路径插入什么就自动建什么。2.2 稀疏序列化带来的存储红利用JSON类型大部分路径不存在的行并不会老老实实占一份空间。ClickHouse的稀疏序列化会按块判断如果一个块里某个路径只有零散几行有值它只存储非默认值的位置和值而不是全部值。这个机制直接解决了一个常见问题字段多但每个字段又只有少数行有值String存JSON天天被诟病体积膨胀。我之前做过一次对比测试100万条埋点数据每条埋点里有一个extra.test_id只有1%的埋点有这个字段。String方案里这个key在每个字符串里都占字符整列体积蹭蹭涨JSON类型方案里对应子列几乎不占空间。最终整体存储减少接近一半。不过要注意的是稀疏序列化对“大部分行都有值”的路径反而是负担。ClickHouse会自己判断阈值但如果你的JSON路径绝大多数行都有值我更建议直接用普通字段别为了“灵活”两个字牺牲查询性能。2.3 路径爆炸的代价动态子列是把双刃剑JSON类型最怕的是“自由透顶”的数据。比如业务在一个事件里塞了userId_1、userId_2这种动态拼接的key那么每个新key都会产生一个新子列。累积到几万个path时表元数据、part文件数量、后台合并都会被拖累。我实测过一张表积累了20多万个不同路径后写入的小part数量明显增多查询计划变大甚至会出现接近“Too many columns”的报错。这种情况下JSON类型非但不是优化反而是灾难。所以我的经验是JSON路径可控、查询字段稳定用JSON类型很舒服路径不可控老老实实String JSONExtract。真要两头好处都占就在写入链路里做一层规整把动态key折叠到固定集合里再落JSON类型。3. JSONExtract函数族从JSON字符串里取值的正确姿势3.1 常用函数一览与返回值对照如果你还在用旧版本或者决定用String存JSONJSONExtract函数族就是主战场。列一下最常用的几个函数返回值典型场景JSONExtractString(json, path)String取字符串字段JSONExtractInt(json, path)Int64取整数JSONExtractUInt(json, path)UInt64取无符号整数JSONExtractFloat(json, path)Float64取小数JSONExtractBool(json, path)Bool取布尔值JSONExtractArrayRaw(json, path)Array(String)取数组每个元素是原始JSON文本JSONExtractKeysAndValues(json, Type)Array(Tuple(String, Type))把键值对展开JSONHas(json, path)UInt8判断路径是否存在JSONType(json, path)String查看路径值的JSON类型JSONLength(json, path)UInt64取数组长度函数路径支持$.a.b和a.b两种写法嵌套用点数组用中括号。我的习惯是全部用不带$的写法简洁不容易错。3.2 路径写法与类型不匹配的典型坑路径写法本身不难但有一个细节很容易翻车数组下标从0开始。比如SELECT JSONExtractString([{name: apple}, {name: banana}], [1].name);返回的是banana不是apple。如果你在BI平台或者脚本里传路径一定要确认是从0还是从1开始数两边规则不一致的时候排查起来非常折磨人。类型不匹配是另一个高频坑。JSON里值是123带引号你用JSONExtractInt去取返回的不是123而是0。必须先JSONExtractString取出来再通过toInt64之类的转换函数做二次处理。反过来也一样数字类型的值用JSONExtractString取得到的是字符串123参与数值计算前必须先做类型转换。3.3 调试技巧怎么确认取到的值是对的有同事问过我JSONExtract取值之后怎么查看取到的值到底对不对最直接的办法是把SQL拆成单条记录跑一遍SELECT payload, JSONHas(payload, a.b) AS has_b, JSONType(payload, a.b) AS type_b, JSONExtractString(payload, a.b) AS val_b FROM event_log LIMIT 10 FORMAT Vertical;FORMAT Vertical在命令行下会把每个字段单独一行打印路径是否存在、类型是什么、值是什么一目了然。更稳妥的做法是配合JSONHas做存在性判断避免把NULL当0参与计算。这套调试流程我基本每次写JSON相关SQL都会走一遍成本极低但能挡掉大部分隐形错误。4. 基于JSON函数实现行列转换从数组展开到键值对透视4.1 第一类行列转换JSON数组展开成多行ClickHouse做行列转换核心是arrayJoin。arrayJoin可以把一个数组展开成多行SELECT arrayJoin([1,2,3]) AS x;结果就是3行。这个机制配合JSONExtractArrayRaw就能把一条记录里的JSON数组拆成明细行。先建一个示例表CREATE TABLE event_log ( event_date Date, user_id UInt64, payload String ) ENGINE MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, user_id);插入两条数据INSERT INTO event_log VALUES (2024-05-01, 1001, {items:[{sku:A,price:10},{sku:B,price:20}]}), (2024-05-01, 1002, {items:[{sku:C,price:15}]});现在要把每个items元素变成一行明细SELECT event_date, user_id, JSONExtractString(item, sku) AS sku, JSONExtractFloat(item, price) AS price FROM event_log ARRAY JOIN JSONExtractArrayRaw(payload, items) AS item;结果event_dateuser_idskuprice2024-05-011001A102024-05-011001B202024-05-011002C15这里的关键点是JSONExtractArrayRaw返回的是Array(String)每个元素是最内层JSON对象的原始文本还没被解析成结构化字段。所以下一步需要用JSONExtractString、JSONExtractFloat去取里面的字段。第一次写的人总想着item.sku不行item现在只是字符串。4.2 第二类行列转换嵌套键值对拆行如果JSON里是一个动态键值对对象比如INSERT INTO event_log VALUES (2024-05-01, 2001, {cf:{click:3,view:10,buy:1}});想把这个对象的每个指标拆成一行用JSONExtractKeysAndValuesWITH {cf:{click:3,view:10,buy:1}} AS payload SELECT kv.1 AS metric_name, kv.2 AS metric_value FROM ( SELECT JSONExtractKeysAndValues(JSONExtractString(payload, cf), UInt64) AS kvs ) ARRAY JOIN kvs AS kv;这里有一个非常容易搞混的点JSONExtractKeysAndValues的第二个参数是值类型不是路径。所以对于嵌套对象要先通过JSONExtractString把子对象字符串取出来再传给JSONExtractKeysAndValues。4.3 第三类行列转换多行聚合回列展开之后经常又要把某几个值聚合成宽表。把上面拆出的click、view、buy变成三列就是典型的sumIfGROUP BYSELECT user_id, sumIf(metric_value, metric_name click) AS click_cnt, sumIf(metric_value, metric_name view) AS view_cnt, sumIf(metric_value, metric_name buy) AS buy_cnt FROM ( SELECT user_id, kv.1 AS metric_name, kv.2 AS metric_value FROM event_log ARRAY JOIN JSONExtractKeysAndValues(JSONExtractString(payload, cf), UInt64) AS kv ) GROUP BY user_id;这个写法把“JSON键值对拆行”和“行转列聚合”串起来了实际报表场景里非常常用。你可以理解为先纵向拆开再横向聚合一张宽表就出来了。4.4 多层嵌套的展开方法再复杂一点JSON数组里的对象还嵌套数组{orders:[ {id:1,items:[{sku:A},{sku:B}]}, {id:2,items:[{sku:C}]} ]}要同时展开orders和items用两个ARRAY JOIN串联SELECT JSONExtractInt(order_info, id) AS order_id, JSONExtractString(item, sku) AS sku FROM event_log ARRAY JOIN JSONExtractArrayRaw(payload, orders) AS order_info ARRAY JOIN JSONExtractArrayRaw(order_info, items) AS item;第二个ARRAY JOIN作用在第一个展开后的每一行上所以能自然形成一对多展开。需要注意顺序先展开外层再展开内层。如果内层是空数组这一行不会出现在结果里要和LEFT ARRAY JOIN配合才能保留外层行。5. 场景演练解析埋点日志JSON的完整SQL方案5.1 数据形态和业务目标我用一个电商埋点例子把上面的知识完整串一遍。原始表CREATE TABLE raw_event_log ( event_date Date, user_id UInt64, page_url String, events String ) ENGINE MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, user_id);events字段是JSON数组每个元素长这样[ {type:expose,sku:A,ts:1710000000,extra:{position:1}}, {type:cart,sku:A,ts:1710000060,extra:{position:1}}, {type:pay,sku:A,ts:1710000120,extra:{position:1,amount:19.9}} ]业务目标统计每天每个SKU的曝光数、加购数、支付数以及支付金额合计。5.2 第一步展开JSON数组生成明细先用INSERT SELECT把events展开成明细行写到一张干净的表CREATE TABLE event_detail ( event_date Date, user_id UInt64, sku String, event_type String, event_ts DateTime, position UInt32, amount Float64 ) ENGINE MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, sku);然后执行展开INSERT INTO event_detail SELECT event_date, user_id, JSONExtractString(event_item, sku) AS sku, JSONExtractString(event_item, type) AS event_type, toDateTime(JSONExtractInt(event_item, ts)) AS event_ts, toUInt32OrZero(JSONExtractString(event_item, extra.position)) AS position, toFloat64OrZero(JSONExtractString(event_item, extra.amount)) AS amount FROM raw_event_log ARRAY JOIN JSONExtractArrayRaw(events) AS event_item;这里有几个细节extra.position是嵌套字段直接用extra.position路径就能取到如果JSON里position带引号JSONExtractInt会取不到值所以先用JSONExtractString再toUInt32OrZero兜底。如果确认是纯数字直接用JSONExtractInt更高效ARRAY JOIN JSONExtractArrayRaw(events)没有传path代表把整个顶层数组展开这是JSONExtractArrayRaw的默认行为。5.3 第二步按天加SKU聚合漏斗指标明细表建好后指标查询就简单了SELECT event_date, sku, countIf(event_type expose) AS expose_cnt, countIf(event_type cart) AS cart_cnt, countIf(event_type pay) AS pay_cnt, sumIf(amount, event_type pay) AS pay_amount FROM event_detail GROUP BY event_date, sku ORDER BY event_date, sku;countIf和sumIf这种写法一行SQL就完成了“行转列式”的指标透视比先把数据拆成三条再分别统计再拼起来干净得多。如果数据量还在可控范围直接查明细表没问题如果报表天天跑、数据天天涨就要考虑物化视图。5.4 第三步用物化视图固化解析逻辑物化视图可以在数据写入时自动完成展开和清洗CREATE MATERIALIZED VIEW mv_event_detail TO event_detail AS SELECT event_date, user_id, JSONExtractString(event_item, sku) AS sku, JSONExtractString(event_item, type) AS event_type, toDateTime(JSONExtractInt(event_item, ts)) AS event_ts, toUInt32OrZero(JSONExtractString(event_item, extra.position)) AS position, toFloat64OrZero(JSONExtractString(event_item, extra.amount)) AS amount FROM raw_event_log ARRAY JOIN JSONExtractArrayRaw(events) AS event_item;物化视图最大的坑在于历史数据不会自动回填。新建的物化视图只处理建立之后的新写入数据历史数据需要自己手动执行一次INSERT INTO event_detail SELECT ...补数。另外物化视图里写了ARRAY JOIN明细表会膨胀但换来的是查询性能稳定对报表类需求非常值。6. 我踩过的坑与性能调优笔记6.1 路径不存在时静默返回NULL一个算错数的案例有次线上统计某功能的转化率SQL写的是SELECT event_date, countIf(JSONExtractString(payload, extra.channel) nova) AS nova_cnt FROM event_log GROUP BY event_date;结果连续几天nova_cnt是0。排查下来发现payload里的路径其实是extra.channel_id不是extra.channel。JSONExtractString路径不存在时返回空字符串空串不等于nova于是全部算成0。问题在于这个SQL不报错如果不主动验证很容易被忽略。从那以后凡是涉及JSON提取的统计我都会先抽出一行看看JSONHas和JSONType的结果确认路径和类型都对再写正式统计SQL。6.2 missing field报错的根源排查遇到“failed to deserialize the JSON body into the target type: input: missing field”这个报错时很多人第一反应以为是SQL写错了其实不是。这个报错通常发生在用JSONEachRow格式导入数据或者某些客户端往ClickHouse喂JSON数据的时候。报错里的“missing field”说明解析器在数据里找不到表结构要求的某个字段。常见原因和解决办法数据行里确实缺字段但表结构是严格模式不允许缺字段。要么补全字段要么调整导入格式设置字段名大小写不匹配ClickHouse字段是大小写敏感的数据里字段顺序和表结构不一致如果用JSONEachRow并且设置了按顺序解析也会报错。input_format_skip_unknown_fields1能跳过未知字段但不能解决必填字段缺失的情况。所以遇到这个报错先看表结构字段和数据是否对得上而不是去怀疑JSONExtract函数。6.3 查询性能的三个实测结论第一能用子列就别用函数解析。如果表用了JSON类型查询直接写data.a、data.b.c不要写JSONExtractString(data, a)后者会失去子列优势。第二WHERE里对JSON字段做函数运算会破坏索引和主键优化。正确做法是把高频过滤字段物化成普通列或者用物化视图提前解析。数据量过亿后这条差别非常明显。第三行列转换查询吃CPU主要在JSONExtractArrayRaw上。如果明细表已经展开过就不要每次从原始JSON表现算。宁可多存一份明细也别每次查都跑展开。还有一个容易被忽视的点写入时的part数量。大批量插入原始JSON时如果分区键设得不好小part堆积会影响查询。JSON类型动态路径多的时候尤其明显。控制分区粒度、合理设置ORDER BY能减少很多merge压力。6.4 版本差异JSON类型不是所有版本都能用JSON字段类型在早期版本需要开启allow_experimental_json_type1正式化之后才默认可用。我现在会先用SELECT version();确认环境版本再决定方案。老版本没有JSON类型但JSONExtract函数族是齐全的行列转换照样能做。如果你的环境版本比较老就直接用String JSONExtract方案不用纠结。如果版本允许也不代表所有表都该用JSON类型。测试环境先建两张同样数据的表一张String、一张JSON跑一下典型查询和写入对比part数量和查询耗时再拍板。最后分享一个我的工作习惯每次写JSON相关SQL前先花一分钟把样本数据格式化看一眼再用JSONType确认类型最后才写正式SQL。这套流程帮我避掉了大部分“路径写错不报错”的坑。ClickHouse的JSON能力这几年进步很快但本质还是那句话——JSON是存储格式不是免维护的schema该做的约束和规划一样都不能少。