搞数据的人一定都有这种感受业务方扔过来的数据十个里有八个长着 JSON 的样。ClickHouse 又是出了名的强类型列存两者一撞第一个反应就是拿 String 硬存再掏出 JSONExtract 系列函数一层层剥。我之前在项目里处理埋点日志、订单快照、广告回调数据基本天天和 JSON 打交道。今天就把 JSON 字段类型和基于 JSON 函数做行列转换这套打法一次说清楚。这篇文章适合正在用 ClickHouse 处理半结构化数据、想在宽表化和明细展开之间自由切换的读者。无论你是刚接触 ClickHouse还是已经踩过几个 JSON 坑下面这些建表方式、函数细节和实战 SQL 都能直接抄去改改用。1. JSON 字段类型ClickHouse 处理半结构化数据的正确姿势1.1 先弄清问题的源头强类型列存与 JSON 的矛盾ClickHouse 的核心优势是列式存储加向量化执行每一列的数据类型在建表时就固定了这样才能做到高压缩比和极快的扫描。可 JSON 偏偏是另一个物种结构不固定、字段可嵌套、同一字段在不同行里可能是字符串也可能是数字。这种无序和结构化和列存天然相冲。早期面对这种矛盾唯一的路就是“String 一把梭”。把整个 JSON 文本塞进 String 列查询时再用 JSONExtractString、JSONExtractInt 这类函数现取现用。这个方案很稳到今天依然大量存在于生产环境缺点也很明显每次查询都在做运行时 JSON 解析字段抽取得越深、扫描行数越多性能损耗越像滚雪球。而且一旦上游变更字段名或类型你必须有兜底逻辑否则查询结果就是一片 0 和空字符串。后来 ClickHouse 从 23.6 版本开始逐步引入了原生 JSON 类型。它的底层设计思路很有意思不是把整个 JSON 当作一个大 blob 存起来而是把 JSON 里的每个叶子路径拆成一个独立的子列subcolumn每个子列再按 ClickHouse 自己的类型推断去落盘。用大白话说就是把一个“神秘莫测的文档”拆成了一个个“规矩的列”既维持了列存的性能优势又保住了 JSON 的灵活性。1.2 原生 JSON 类型怎么用要在旧版本里开启原生 JSON 类型必须显式打开开关SET allow_experimental_json_type 1;如果你的 ClickHouse 是 24.8 或更高版本这个开关基本可以不用管默认就能用。建表方式如下CREATE TABLE events_json ( id UInt64, event_time DateTime, payload JSON ) ENGINE MergeTree() ORDER BY event_time;写入时正常插入 JSON 字符串即可INSERT INTO events_json VALUES (1, 2025-01-10 10:00:00, {user_id:1001,action:click,page:/home,items:[{sku:SKU-A,price:29.9}]}), (2, 2025-01-10 10:05:00, {user_id:1002,action:view,page:/product/42,items:[]});查询时可以直接用点路径访问内部字段不用写一长串函数SELECT id, payload.user_id AS user_id, payload.action AS action, payload.page AS page FROM events_json;结果就是正常的列user_id 是 UInt64action 是 String。这就是原生 JSON 类型最直观的优势读写和普通列几乎没区别ClickHouse 会自动根据插入的内容推断子列类型并维护元数据。嵌套数组也能处理。比如想拿到 items 里的 sku 列表SELECT id, payload.items.sku AS skus FROM events_json;这种路径访问在底层对应真实存在的子列因此配合索引和压缩都比 String 硬解析高效。不过要注意JSON 类型的动态推断也有代价如果同一路径不同行写入的类型不一致ClickHouse 会尽量兼容成更宽的类型但如果类型差得太远比如一会是字符串一会是数组就可能出现类型漂移查询时需要手动 CAST 兜底。1.3 String存JSON vs 原生JSON类型怎么选我自己的经验是这两个方案不是“谁取代谁”而是分场景选择。对比项String JSON函数原生 JSON 类型查询可读性函数嵌套较多路径越长越难看直接点路径访问直观运行时解析开销每次查询都解析子列预解析扫描更快字段类型安全靠函数默认值兜底容易埋坑动态推断类型仍需注意漂移压缩率一整个文本压缩重复字段压缩率尚可独立子列按类型压缩通常更高版本稳定性全版本稳定实验性功能老版本可能受限适用场景临时分析、嵌套极深、字段极不规律高频字段访问、需要物化列、长期存储如果只是临时看一眼数据、或者上游 JSON 结构每天一个样用 String 存是最省心的。但如果你要把 JSON 里的某些字段当作核心维度天天聚合、关联我强烈建议建普通列或物化列把那几个字段提取出来而不是每次都现场解析。原生 JSON 类型适合那些字段很多但每个字段访问频率参差不齐的场景它会自动把“常用字段”变成子列查询时只扫描你用到的部分。2. JSON 相关函数把字符串变成可用数据2.1 提取函数全家桶JSONExtract 家族JSONExtract 系列是 String 存 JSON 方案里的主力军。常见的有这几个函数作用字段缺失时返回JSONExtractString(json, path...)提取字符串值空字符串JSONExtractInt(json, path...)提取整数0JSONExtractUInt(json, path...)提取无符号整数0JSONExtractFloat(json, path...)提取浮点数0JSONExtractBool(json, path...)提取布尔值falseJSONExtractArrayRaw(json, path...)提取数组每个元素为原始 JSON 字符串空数组JSONExtractKeysAndValues(json, type)提取对象的键值对数组空数组JSONExtract(json, path, type)通用提取指定返回类型类型默认值路径参数可以直接按层级写例如从订单 JSON 里取收货城市SELECT JSONExtractString(raw_json, address, city) AS city FROM orders;等价于把 JSON 当对象逐层往下走。也可以用点号路径在 ClickHouse 里JSONExtractString(raw_json, address.city)也是支持的但我个人更推荐多参数写法可读性更好也避免路径里出现点号导致歧义。如果你习惯 SQL 标准里那种$.path写法ClickHouse 也提供了 JSON_VALUE 和 JSON_QUERY。区别在于 JSON_VALUE 只能返回标量值JSON_QUERY 可以返回一段 JSON 子文档。实际项目里我大部分时间还是用 JSONExtract因为它对缺失字段的默认值处理更可预期不会抛异常。2.2 判断、检查与遍历类函数除了直接提取另外几个函数在行列转换里会频繁用到建议记牢。JSONHas 判断某个路径是否存在返回 1 或 0。这个函数特别适合处理“这个字段只有部分行才有”的场景避免把所有缺失都当默认值处理。SELECT id, JSONHas(raw_json, coupon) AS has_coupon FROM orders;JSONType 返回某个路径的值类型结果可能是 String、Int、Array、Object 等。数据清洗时用它来筛掉类型异常的行非常方便。SELECT id, JSONType(raw_json, price) AS price_type FROM orders WHERE JSONType(raw_json, price) ! Int;JSONExtractKeysAndValues 是把一个 JSON 对象拆成键值对数组的重点函数。第二个参数是指定 value 的类型比如 String、Int、Float。返回值是Array(Tuple(String, type))结构每个元素是一个元组第一个元素是 key第二个是 value。这个函数配合 arrayJoin就是“对象展开成多行”的核心工具。JSONExtractArrayRaw 则是把 JSON 数组里的每个对象抽出来变成一行一个原始 JSON 字符串后续再对这段字符串继续提取就能实现多层嵌套解析。2.3 函数解析的性能代价有一件事我必须强调在 String 列上调用 JSONExtract本质是边扫描边解析CPU 开销比直接读列大得多。你写一个查询对同一行 JSON 调了五次不同的 JSONExtractClickHouse 就要把这行 JSON 解析五遍这非常亏。所以我有几个实际建议能一次提取就不要分多次。比如要取同一个 JSON 里的多个字段写成多个 JSONExtract 没问题但更深层的逻辑是尽量让解析次数与扫描行数成正比而不是与“字段数×行数”成正比。高频查询的 JSON 字段用物化列提前提取。建表时可以加一个 MATERIALIZED 列把 JSON 里最常用的 user_id、action 直接提取成独立列配合索引和存储查询性能能有数量级提升。不要在 WHERE 里对同一路径反复调用 JSONExtract。如果确实要过滤先考虑物化列或者接受全表扫描但保证 JSON 列在内存里被高效缓存。如果 JSON 超大且路径很深考虑在导入前做白名单裁剪只保留业务真正关心的字段。数据量一上来省下的解析时间非常可观。3. 实战用JSON函数完成行列转换3.1 列转行把数组和对象展开成明细行之前我在订单中心场景里接到一个很典型的需求上游把一次订单的全部商品明细塞到了一个 JSON 数组里要按商品维度做报表。这就是标准的“一行展开成多行”ClickHouse 里核心就是 ARRAY JOIN。先建一张测试表CREATE TABLE order_cart ( order_id UInt64, order_time DateTime, cart_json String ) ENGINE MergeTree() ORDER BY order_id; INSERT INTO order_cart VALUES (1, 2025-01-10 11:00:00, {user_id:1001,items:[{sku:SKU-A,price:29.9,qty:2},{sku:SKU-B,price:49.9,qty:1}],address:{city:Hangzhou,district:Xihu}}), (2, 2025-01-10 11:05:00, {user_id:1002,items:[{sku:SKU-A,price:29.9,qty:1},{sku:SKU-C,price:99.0,qty:3}],address:{city:Shanghai,district:Pudong}});用 JSONExtractArrayRaw 把 items 数组抽出来再用 ARRAY JOIN 展开成一行一个商品SELECT order_id, JSONExtractString(item, sku) AS sku, JSONExtractFloat(item, price) AS price, JSONExtractInt(item, qty) AS qty, JSONExtractFloat(item, price) * JSONExtractInt(item, qty) AS amount FROM order_cart ARRAY JOIN JSONExtractArrayRaw(cart_json, items) AS item;这段 SQL 很值得逐句看JSONExtractArrayRaw 先取出整个 items 数组返回Array(String)ARRAY JOIN 把它当作一行行的数据源每一行 item 变量就变成了数组中的一个 JSON 字符串接下来三个 JSONExtract 继续从这段商品 JSON 里抽字段。最终结果就是把两条订单记录拆成了五条商品明细行这就是典型的“列转行”。如果 JSON 不是一个数组而是一个对象想把它“摊平”成多列或“炸开”成多行可以用 JSONExtractKeysAndValues 配合 ARRAY JOINSELECT order_id, kv.1 AS address_key, kv.2 AS address_value FROM order_cart ARRAY JOIN JSONExtractKeysAndValues(cart_json, address) AS kv;这条 SQL 的效果是把 address 对象里的 city、district 各自变成一行。需要注意 JSONExtractKeysAndValues 的第二个参数必须指定 value 的类型如果对象里的值既有字符串又有数字建议用 String 先兜底后面再按需 CAST。3.2 行转列从明细Event透视出宽表行转列Pivot在 ClickHouse 里没有那种通用的 pivot 语法基本靠聚合函数加条件计数来实现。最常见的场景是把多行不同类型的事件按某个维度聚合成一行多列。假设埋点表是典型的明细事件表CREATE TABLE user_events ( event_id UInt64, event_time DateTime, payload String ) ENGINE MergeTree() ORDER BY event_time; INSERT INTO user_events VALUES (101, 2025-01-10 10:00:00, {user_id:1001,action:click,page:/home}), (102, 2025-01-10 10:05:00, {user_id:1002,action:view,page:/product/42}), (103, 2025-01-10 10:10:00, {user_id:1001,action:buy,page:/checkout});想按天、按用户统计 view / click / buy 的次数并把三个行为做成三列SELECT toDate(event_time) AS day, JSONExtractInt(payload, user_id) AS user_id, countIf(JSONExtractString(payload, action) view) AS view_cnt, countIf(JSONExtractString(payload, action) click) AS click_cnt, countIf(JSONExtractString(payload, action) buy) AS buy_cnt FROM user_events GROUP BY day, user_id ORDER BY day, user_id;这条 SQL 的思路很直白先把 JSON 里的 user_id 和 action 都抽成普通字段再用 GROUP BY 聚合用 countIf 对每个行为各计一次数。整个查询把多行 event 合并成一行实现了“行转列”。更刁钻一点的需求是 JSON 里的 key 本身就是动态的比如每次请求带着不同的额外参数。这种情况下你可以先用 JSONExtractKeysAndValues 把对象炸开成 KV再按 KV 做透视。例如SELECT event_id, sumIf(CAST(kv.2 AS Float64), kv.1 source) AS source_value, sumIf(CAST(kv.2 AS Float64), kv.1 campaign) AS campaign_value FROM ( SELECT event_id, kv FROM user_events ARRAY JOIN JSONExtractKeysAndValues(payload, extra) AS kv ) GROUP BY event_id;这里先把 payload.extra 对象展开成 key-value 行再用 sumIf 把需要的 key 聚合成列。value 是 String 类型所以必须先 CAST 成 Float64。这是“动态 JSON key 做行转列”的通用套路理解了之后可以套到很多场景里。3.3 组合案例从购物车JSON到销售明细宽表很多需求是行转列和列转行交替出现的。我写过最典型的一个需求是这样的上游订单表把整个购物车塞成 JSON要先炸开成商品明细行再按 SKU 聚合成销售宽表。完整 SQL 可以直接参考WITH order_item AS ( SELECT order_id, order_time, JSONExtractString(item, sku) AS sku, JSONExtractFloat(item, price) AS price, JSONExtractInt(item, qty) AS qty, JSONExtractFloat(item, price) * JSONExtractInt(item, qty) AS amount FROM order_cart ARRAY JOIN JSONExtractArrayRaw(cart_json, items) AS item ) SELECT toDate(order_time) AS day, sku, count(DISTINCT order_id) AS order_count, sum(qty) AS total_qty, sum(amount) AS total_amount FROM order_item GROUP BY day, sku ORDER BY day, total_amount DESC;这段逻辑分两层CTE 里先把 each 订单的 items 数组炸开成商品明细外层再做按天、按 SKU 的聚合。底层数据从两条订单变成了五条明细再聚合成三行销售汇总。整个过程就是列转行和行转列的组合也是我在线上跑得最多的 JSON 转换 SQL 形态之一。4. 常见问题与排查技巧4.1 类型推断与空值处理JSON 解析最坑的地方就是空值和类型。JSONExtractString 在字段缺失或类型不匹配时不会报错而是悄悄返回空字符串JSONExtractInt 则返回 0。这个默认行为方便归方便但经常误导人你没法区分“这个用户真的消费了 0 元”和“这个字段根本不存在”。我的处理习惯是对关键字段先 JSONHas 判断一下再做业务逻辑。比如SELECT order_id, JSONExtractInt(payload, user_id) AS user_id, JSONHas(payload, coupon) AS has_coupon, if(has_coupon, JSONExtractFloat(payload, coupon, discount), 0) AS discount FROM orders;另一个常见问题是 JSON 里数字与字符串混用。上游有时把 id 传成字符串1001有时传成数字1001。JSONExtractInt 对数字字符串会尝试解析这是好的但如果你用 JSONExtractString 去取数字返回的就是字符串形式的数字排序和比较可能出问题。我的建议是ID 类字段统一用 JSONExtractInt展示类字段用 JSONExtractString两边口径要一致否则 join 的时候对不上排查起来非常头疼。4.2 导入报错与格式问题如果你用 JSONEachRow 格式导入数据可能遇到一个很典型的报错failed to deserialize the json body into the target type: input: missing field。这个报错的意思是你给的 JSON 请求体里缺少了目标表 schema 要求存在的字段。我遇到过的原因有两类。一类是上游漏字段比如表里有order_id列但这行 JSON 里没有order_idkey另一类是配置文件里 columns 列表写错导致字段对不上。解决办法是先核对表结构和你导入的 JSON key 是否一致确认缺失字段能否用默认值如果可以在导入设置里打开input_format_skip_unknown_fields并把缺失列设置为允许默认值。还是那句话导入前先做一层 JSON 校验用 Python 的 json.loads 或 jq 跑一遍别把脏数据直接灌进 ClickHouse。等数据进了表再发现问题修正成本会翻好几倍。4.3 性能优化与版本注意在 String 列上大量使用 JSONExtract 做聚合性能问题迟早会来找你。我的几个实操办法按优先级排序高频字段物化。这不是可选项是必选项。如果你每天有几百亿行日志每行都要提取 user_id 和 action那就在建表时加两个物化列从 JSON 里提取好再存成普通列。查询直接走普通列速度完全不是一个量级。能用 JSON 类型就用 JSON 类型。原生 JSON 类型在建表时就把叶子字段拆成子列等于变相实现了“自动物化”。前提是版本足够新。优化 JSON 本身的长度。只保留业务需要的字段别把上游一百个字段全塞进表里。JSON 越短解析越快压缩率越高。版本方面也要留个心眼原生 JSON 类型在 23.6 到 24.x 期间行为调整过不少次早期版本很多特性不稳定。如果你在生产环境用旧版本又想要原生 JSON 类型先在公司测试环境用真实数据量和查询模式跑一遍压测再上线。另外遇到 part 层面的诡异问题比如某个分区数据查不到、磁盘占用异常可以先查system.parts看看 part 名称、分区路径和 active 状态排查是不是 part 合并或损坏引起的问题而不是一头扎进 SQL 里调来调去。4.4 常见问题速查表现象原因解决方案JSONExtract 返回空字符串/0路径不存在或类型不匹配用 JSONHas 判断存在性再取值同一个字段一会是字符串一会是数字上游数据不规范统一 CAST 口径或导入前清洗JSONExtractArrayRaw 结果为空路径不是数组或数组为空先 JSONType 确认类型导入报 missing fieldJSON key 与表字段不匹配核对 schema打开 skip_unknown_fields查询慢且 JSON 函数写得多运行时解析开销大物化列 / 原生 JSON 类型 / 裁剪字段原生 JSON 类型用不了版本过旧或未开启开关升级版本SET allow_experimental_json_type1我在实际项目里用过 String 加 JSONExtract 的组合接近两年后来慢慢把高频场景切到原生 JSON 类型上整体的查询体验和性能都有了明显改善。如果你也正在做类似的转换建议先从一张小表开始验证版本行为再逐步铺开。这个方向的功能演进很快保持关注官方 changelog能少走不少弯路。