先讲一个调了一下午的现象。同事的SQL跑了快一个小时还没出数业务在旁边催SLA眼看要破。打开他的代码满屏都是get_json_object一个JSON字符串里取十几个字段每个字段单独调一次同一个埋点日志被反复截断解析了快二十轮。这种写法在数据平台里太常见了——上游接口返回一个复杂JSON下游急着要结构化于是先在Spark SQL里硬啃JSONPath。这篇文章就是围绕这类场景展开的聊Spark SQL里处理复杂JSON的几种实用姿势嵌套的order结构怎么彻底拆开、items数组怎么一行变多行、extra_tags这种动态key怎么灵活取以及生产环境里那些文档上不会写但一定会踩的坑。适合正在写ETL清洗逻辑的同学也适合被JSON字段折磨的报表开发。1. 建一张能复现的表四行日志还原真实埋点1.1 数据构造正常、空数组、脏字符串、缺金额为了把问题说清楚我自己造了一批贴近真实的用户下单埋点日志。所谓埋点日志就是前端每次交互都会往消息队列丢一条JSON后端统一清洗后落到数仓。这里我用临时表的方式建了四行数据每一行都代表一类真实会遇到的情况。我将用到的数据表ods_user_order_log字段就两个log_id 和 detail核心JSON字符串外加一个分区字段dt。下面的SQL在你本地Spark环境里可以直接跑。CREATE OR REPLACE TEMPORARY VIEW ods_user_order_log AS SELECT L001 AS log_id, {user_id:1001,event_time:2024-05-20 12:00:00,order:{order_id:A001,status:PAID,total_amount:399.00,items:[{sku_id:S001,name:无线鼠标,price:199.00,qty:1,attrs:{color:深空灰,connect:蓝牙}},{sku_id:S002,name:机械键盘,price:200.00,qty:1,attrs:{color:白色,layout:104键}}]},extra_tags:{channel:app,version:v2.3.1},tracking:{page:order_list,actions:[click,add_to_cart,order_submit]}} AS detail, 2024-05-20 AS dt UNION ALL SELECT L002 AS log_id, {user_id:1002,event_time:2024-05-20 12:05:00,order:{order_id:B002,status:PAID,total_amount:100.00,items:[]},extra_tags:{channel:h5,version:v1.0.0},tracking:{page:search_result,actions:[click]}} AS detail, 2024-05-20 AS dt UNION ALL SELECT L003 AS log_id, this is not a valid json AS detail, 2024-05-20 AS dt UNION ALL SELECT L004 AS log_id, {user_id:1003,event_time:2024-05-20 12:30:00,order:{order_id:C003,status:CANCELLED,items:[{sku_id:S003,name:显示器,price:1299.00,qty:2,attrs:{size:27寸}}]},extra_tags:{channel:web,version:v3.0.0},tracking:{page:product_detail,actions:[click,order_submit]}} AS detail, 2024-05-20 AS dt;四行数据各有各的心思。L001是标准数据order里有两个商品attrs里还藏了一层颜色、连接方式、键盘布局之类的细分属性L002订单存在但items是空数组这种场景经常出现在“用户下单了但订单里没有有效商品”或者“购物车被清空”这类边界情况L003更狠detail压根不是JSON就是一句普通字符串模拟上游解析异常或者传输截断L004故意省掉了order.total_amount表示一个埋点里某字段缺失。很多教程只给你看“完美数据”实际写代码时打垮你的恰恰是这些不完美。后面每个案例我都会带着这些脏数据一起跑你才能看到真实结果。1.2 三个复杂类型一眼识别Struct、Array、Map拿到一条JSON后我建议先别急着写SQL先把它的结构剥出来。这条detail里其实同时塞了Spark SQL最常见的三种复合类型一是Struct也就是嵌套结构体。比如order本身就是一个对象里面再挂order_id、status、items等子字段。可以把它理解成Excel里的一行或者C语言里的结构体访问方式是点号比如order.order_id。二是Array数组。order.items是一个数组里面装的是若干个商品对象。要拆数组必须用到explode这一类函数把它变成多行。三是Map键值对。extra_tags这种字段最典型key可以随意新增今天加个渠道、明天加个实验分组你没法预先穷举列名所以用Map承载最合适。访问方式是方括号比如extra_tags[channel]。再往下看attrs又是Map类型color、connect、layout这些key不是固定的某个商品可能只有颜色没有连接方式所以用Map很合理。把这三层关系理清楚了后面选函数就有依据不会乱用。2. 四个函数的定位边界能少写一半解析代码2.1 get_json_object 的两条硬伤get_json_object是很多人接触的第一个JSON函数它的作用是按JSONPath从一个JSON字符串里取一个字段的值。SELECT get_json_object(detail, $.user_id) AS user_id_str, get_json_object(detail, $.order.status) AS order_status, get_json_object(detail, $.order.items[0].name) AS first_item FROM ods_user_order_log;这个函数用起来直观但有两个硬伤必须清楚。硬伤一是它一次只能取一个字段返回值永远是字符串。你要取10个字段就得写10个get_json_object每个都在对同一个JSON做路径解析。同事那种跑一小时的SQL就是被这种写法拖垮的——单条数据看着快数据量上来之后重复解析的成本非常可观。硬伤二是它对“数组里的所有元素”这种需求很尴尬。比如你想取items里每个name路径写成$.order.items[*].nameget_json_object并不能直接返回一个数组给你。它适合的是那种“取一两个字段就走”的轻量场景别拿它当复杂JSON的主武器。顺带提醒一句路径里如果字段名带点号或者特殊字符比如“tenant.id”写法会很别扭这也是很多人突然解析不出值的原因之一。2.2 json_tuple 适合平铺JSONjson_tuple一次可以取同一层的多个字段返回多列底层是单次JSON解析性能比get_json_object逐个取好不少。SELECT log_id, json_tuple(detail, user_id, event_time, extra_tags) AS (user_id, event_time, extra_tags) FROM ods_user_order_log;注意json_tuple只能取当前层级的key不能写user_id.order_id这种嵌套路径。如果你的JSON本身层次浅、字段平铺用它效率很高但一旦遇到嵌套和数组它就力不从心了。我的经验是它适合解决“简单JSON字段比较多”的取数不适合复杂结构。2.3 from_json / to_json 才是处理嵌套数据的主路径处理嵌套、数组、类型敏感这些需求真正的主角是from_json和to_json。from_json把JSON字符串按你给定的schema解析成一个结构化列类型是Struct。解析完之后所有字段都是结构体里的成员访问方式从“路径字符串”变成“点号取列”解析只做一次之后Spark内部按列式访问性能和质量都上一个档次。to_json则反向操作把结构体重新编码成JSON字符串。通常用在清洗加工之后把处理好的数据回写给下游系统或者重新落成一个干净JSON列。这两个函数组合起来是复杂JSON处理的完整闭环。我把用法放在后面的具体案例里讲单独拎出来记忆会显得空。2.4 先定边界再用函数避免“一把梭”实际开发中我一般先回答三个问题再选函数这个JSON会不会有嵌套、会有数组吗、字段类型必须保持原样吗。三个回答如果都是“轻量场景”get_json_object和json_tuple就够了。只要有一个是“是”就直接跳到from_json不要试图靠路径函数硬凑。这个选择看起来简单但很多线上故障就是边界没定清楚有人在字段类型敏感的场景用get_json_object取数字取回来是字符串后面对账对不上有人用explode炸数组没考虑空数组会丢行。这些细节比函数本身更容易翻车后面我用案例展开。3. 案例一多层嵌套拆行订单明细从一行变N行3.1 from_json 解析完整埋点协议先把最经典的场景做掉把用户订单埋点里的所有层级拆干净。第一步给detail定义一个schema用from_json解析。SELECT log_id, from_json( detail, structuser_id:bigint,event_time:string,order:structorder_id:string,status:string,total_amount:double,items:arraystructsku_id:string,name:string,price:double,qty:int,attrs:mapstring,string,extra_tags:mapstring,string,tracking:structpage:string,actions:arraystring ) AS d FROM ods_user_order_log;schema看着长其实逻辑和我们第一节拆的结构完全对应外层是user_id、event_time、order、extra_tags、trackingorder内部又是一个Struct其中items是Array 每个商品里attrs是Mapstring,string。这段schema是手写的写的时候注意每层括号和逗号。经验之谈手写schema最容易错在“这个逗号属于哪个层级”上我的习惯是先把JSON树画出来再补类型而不是对着字符串硬数括号。from_json解析完成之后detail这一列变成一个完整结构体d后面所有字段都能用d.user_id、d.order.order_id这种方式访问。type字段也跟着schema走了user_id是bigint而不是字符串total_amount是double。3.2 lateral view explode 展开 items 数组结构体解析出来了但items在数组里还窝着。订单表要做商品级分析必须把它拆成行。用lateral view explode。WITH parsed AS ( SELECT log_id, from_json( detail, structuser_id:bigint,event_time:string,order:structorder_id:string,status:string,total_amount:double,items:arraystructsku_id:string,name:string,price:double,qty:int,attrs:mapstring,string,extra_tags:mapstring,string,tracking:structpage:string,actions:arraystring ) AS d FROM ods_user_order_log ) SELECT d.user_id, d.order.order_id, item.sku_id, item.name, item.price, item.qty, round(item.price * item.qty, 2) AS item_amount FROM parsed LATERAL VIEW explode(d.order.items) t AS item;lateral view这种写法可能让新手发怵其实就是“把这一行的数组炸开多出来的行横向关联回原行”。explode产生的item就是items数组里的单个商品元素item.name和item.qty都是结构体里的字段。执行之后L001这条日志会产出两行L004产出两行L002的空数组不会产出行。最终结果类似log_iduser_idorder_idsku_idnamepriceqtyitem_amountL0011001A001S001无线鼠标199.001199.00L0011001A001S002机械键盘200.001200.00L0041003C003S003显示器1299.0022598.00这才是商品粒度该有的样子一行一个商品订单主信息跟着重复一遍。订单和商品一对多的关系在表里就是通过这种方式表达。3.3 用明细聚合做金额对账揪出缺失字段明细拆出来了接下来可以顺手做一个数据质量检查把商品明细的金额汇总和订单头里的total_amount对账差值不为0或者对不上的订单基本就是数据链路有问题的信号。WITH parsed AS ( SELECT log_id, from_json( detail, structuser_id:bigint,event_time:string,order:structorder_id:string,status:string,total_amount:double,items:arraystructsku_id:string,name:string,price:double,qty:int,attrs:mapstring,string,extra_tags:mapstring,string,tracking:structpage:string,actions:arraystring ) AS d FROM ods_user_order_log ), item_level AS ( SELECT d.order.order_id, round(sum(item.price * item.qty), 2) AS calc_amount FROM parsed LATERAL VIEW explode(d.order.items) t AS item WHERE d.order IS NOT NULL GROUP BY d.order.order_id ) SELECT i.order_id, i.calc_amount, p.d.order.total_amount, round(i.calc_amount - p.d.order.total_amount, 2) AS diff FROM item_level i JOIN parsed p ON i.order_id p.d.order.order_id;这里有几层东西值得说。item_level这个CTE里explode之前加了WHERE d.order IS NOT NULL是为了挡掉解析失败的行不然L003这种脏数据进来直接空白。对账结果里L001的明细金额399.00和订单头399.00对得上diff是0L004明细金额2598.00total_amount是null所以diff也是null。这个null本身就是一个质量告警上游埋点少传了字段。在实际数仓里这类对账SQL我会挂在调度上每天跑一次diff不为0的订单落进一张质量问题表再丢给上游排查。用同样的数据做明细分析和总量核对这是数据开发的基本功。3.4 空数组不产行该用 explode_outer 时别犹豫上面那条SQL对L002有个“看不见的影响”B002订单的items是空数组explode之后一行都不产生所以对账结果里根本没有B002。如果业务考核口径是“所有订单都要出现”这就漏单了。这时候需要explode_outer。它和explode的唯一区别是数组为空或null时explode不产行explode_outer会产一行但炸开后的字段是null。SELECT d.order.order_id, item.sku_id, item.name FROM parsed LATERAL VIEW explode_outer(d.order.items) t AS item WHERE d.order IS NOT NULL;执行后B002会保留一行sku_id和name都是null。这个语义非常重要你要的是“订单级清单”还是“商品级明细”决定了该用explode还是explode_outer。很多丢数事故就出在这一个单词上。如果还要保留每个商品在数组里的原始位置用posexplode。它多返一列pos表示数组下标处理“第几个商品”这种业务很管用。4. 案例二schema 从哪来以及清洗后如何回写 JSON4.1 schema_of_json 快速拿到协议 schemafrom_json要写一长串schema很多人望而生畏。其实Spark提供了一个工具函数schema_of_json能从一条JSON里把schema推断出来。SELECT schema_of_json(detail) AS schema_str FROM ods_user_order_log WHERE detail LIKE {% LIMIT 1;返回的结果就是类似structuser_id:bigint,event_time:string,...的一长串可以直接复制到from_json的第二个参数里。我自己开发时的工作流是先拿样例数据跑一遍schema_of_json看到输出结构再人工确认字段类型最后固化成代码里的常量。这个流程比对着JSON瞎写schema省太多事了。需要提醒的是schema_of_json依据的是传入的这条数据如果源数据里不同行的结构不一致比如有的行有version字段有的没有按这一条推断出来的schema可能缺字段。所以实际使用中我会多抽几条数据对比确认协议稳定后再用推断结果。更稳妥的验证方式是直接把schema_of_json的结果拼到from_json里解析后抽查几条看有没有null。4.2 把 schema 固化成常量别复制粘贴schema写出来之后下一个坑就是到处复制粘贴。同一张表的协议在任务A里写一遍在任务B里又写一遍往往因为中间改过一个字段两边就失同步了对账对不上你还不知道该查谁。工程化的做法是把schema定义成一份代码级常量。比如在Scala任务里就是一行val在Python任务里就是一个公共变量所有用到这张表from_json的地方都引用同一个常量。SQL脚本我会放在仓库里专门建一个配置文件解析前先从配置中心拉这个schema字符串。这套动作看起来只是代码规范但在复杂JSON场景里价值极大。schema一旦发生变化你只需要改一个地方所有下游任务自动统一不用半夜翻日志找哪条SQL漏改字段。4.3 to_json named_struct 回写下游报文解析后的结构化数据加工完往往还要回写成JSON交给下游消息队列或者落库。to_json就是干这个的它接收一个结构体列输出JSON字符串。实际操作中我们通常用named_struct来手工构造一个结构体把需要回写的字段组装起来。SELECT log_id, to_json( named_struct( user_id, d.user_id, order_id, d.order.order_id, status, d.order.status, item_count, size(d.order.items), total_amount, d.order.total_amount, channel, d.extra_tags[channel] ) ) AS processed_json FROM parsed WHERE d.order IS NOT NULL;这条SQL把JSON解析、字段提取、数组大小计算、map取值、回写全部串联起来。named_struct里的第一项是JSON里的字段名第二项是值可以是一个普通列也可以是一个表达式。执行结果里L001会得到{user_id:1001,order_id:A001,status:PAID,item_count:2,total_amount:399.0,channel:app}这就是下游消费者真正想看的干净报文。注意这里的channel是从extra_tags这个map里取的如果这个key不存在取出来就是null不会报错。需要默认值时可以用coalesce(d.extra_tags[channel], unknown)包一层。4.4 ignoreNullFields 影响输出字段完整性to_json输出时有一个配置项经常被忽略spark.sql.jsonGenerator.ignoreNullFields默认值是true意思是结构体里值为null的字段生成JSON时直接不输出。还是拿L004说它的order里没有total_amountfrom_json解析后total_amount是null。默认配置下to_json返回的JSON里压根不会出现total_amount这个key。如果下游严格按照协议检查字段发现少key就会报错或者解析出来的json里没有字段程序写得严一点就炸了。这种时候把配置关掉null字段也能输出成total_amount:null字段结构就完整了。SET spark.sql.jsonGenerator.ignoreNullFields false;关掉之后同样是L004输出会变成{user_id:1003,order_id:C003,status:CANCELLED,item_count:1,total_amount:null,channel:web}。到底是“缺字段”还是“值为null”这两个语义对下游完全不同。建议在项目启动时就把这个配置项谁该用true、谁该用false定死不要默认。5. 案例三动态 key 与数组变换Map 和高阶函数一次解决5.1 Map 类型处理不定字段方括号取值与炸开成键值回到extra_tags这个字段。它里面存的是渠道、版本这些动态key字段列表随时可能增加。比如这个版本埋点加了渠道下个版本可能加个实验分组你要是在表结构里把每个key都定义成一列协议一换就得改表。用Map类型承载这类数据天然就对了。访问某个固定key用方括号加key名SELECT d.user_id, d.extra_tags[channel] AS channel, d.extra_tags[version] AS app_version, map_keys(d.extra_tags) AS tag_keys, map_values(d.extra_tags) AS tag_values FROM parsed WHERE d.user_id IS NOT NULL;map_keys和map_values可以把所有key和value分别列出来。如果想知道整张表一共有哪些来源渠道更直接的做法是把map炸开成key-value两列SELECT d.user_id, tag_key, tag_value FROM parsed LATERAL VIEW explode(d.extra_tags) t AS tag_key, tag_value;这条SQL执行完每条日志的每个tag变成一行比如L001的channel、version变成两行。之后你想统计各个渠道的用户数直接对tag_keychannel做group by就行。这比在代码里写死字段名灵活得多也符合埋点数据“字段不稳定”的现实。5.2 transform/filter 对行为列表做变换tracking.actions是一个Array 里面是用户行为序列。对数组做过滤、映射、判断Spark SQL在3.0以后支持了lambda高阶函数写起来非常舒服。SELECT d.user_id, size(d.tracking.actions) AS action_cnt, filter(d.tracking.actions, x - x ! click) AS meaningful_actions, transform(d.tracking.actions, x - upper(x)) AS upper_actions FROM parsed WHERE d.user_id IS NOT NULL;filter里的x - x ! click就是lambda表达式x表示数组里的每一个元素条件成立则保留。transform则是把数组里的每个元素做一次映射这里转成大写。两个函数组合起来一条SQL就把“去掉无意义点击行为、统一行为大小写”这两个需求都解决了。如果你还在用Spark 2.xSQL层面对lambda支持不完整常见的替代方案是先用explode把数组炸成行做过滤和变换之后再collect_list聚合回去。虽然能实现但代码长不少。所以能用3.0就尽量别老守着旧版本。5.3 array_contains 快速判断关键行为做行为分析时最常问的一个问题是这个用户有没有下过单对数组来说array_contains一行就能搞定。SELECT d.user_id, array_contains(d.tracking.actions, order_submit) AS has_submit FROM parsed WHERE d.user_id IS NOT NULL;执行结果是L001为trueL002为falseL004为true。这个函数避免了先explode再group by的笨办法也是把“数组里的信息”提炼成布尔标记的常用手段。在用户路径分析、漏斗转化里这种标记列几乎是必备的。6. 避坑指南静默null、类型错位、数据膨胀、schema推断6.1 解析失败不会报错静默返回 null 最隐蔽写Spark SQL处理JSON最容易踩的坑就是不是所有解析失败都会报错。from_json遇到不合法JSON不会抛异常而是默默返回一个null。后果是你以为数据都处理好了其实有相当一部分行在解析阶段已经变成了null。L003那条数据就是这么消失的。在没有任何监控的情况下你会发现“订单数怎么少了”“用户数对不上”却找不到原因因为SQL没红也没黄就是数字不对。排查思路很简单做一个解析成功率的校验SELECT count(*) AS total_cnt, count(d) AS parsed_cnt, count(d) / count(*) AS parse_rate FROM ( SELECT from_json( detail, structuser_id:bigint,event_time:string,order:structorder_id:string,status:string,total_amount:double,items:arraystructsku_id:string,name:string,price:double,qty:int,attrs:mapstring,string,extra_tags:mapstring,string,tracking:structpage:string,actions:arraystring ) AS d FROM ods_user_order_log ) t;count(d)会忽略null行所以total_cnt和parsed_cnt的差就是解析失败的行数。挂到调度里parse_rate低于阈值就告警。这个监控花不了几分钟但能省掉无数排查数对不齐的深夜。6.2 get_json_object 返回永远字符串join 前必须 castget_json_object不管取出来的值原本是数字还是布尔返回的都是字符串。比如$.user_id取出来是1001不是1001。如果你拿它去join一张user_id是bigint的用户维表两边类型对不上join结果直接为空。这类问题尤其隐蔽SQL不报错结果就是少数据。排查的时候你可能会怀疑维表有问题其实问题出在类型没转。SELECT cast(get_json_object(detail, $.user_id) AS bigint) AS user_id_int FROM ods_user_order_log;结论很简单只要用get_json_object取了数值类型就老老实实cast。省这一步后面可能付出的是几小时的排查时间。6.3 explode 的行数膨胀要提前算账explode把一行撑成N行N取决于数组长度。正常商品订单还好但遇到那种一个订单塞了几十件、上百件商品的场景行数直接放大几十倍。如果再和别的表join膨胀系数会叠加相乘任务很容易卡死或者输出爆炸。我的建议是explode之前先做一轮裁剪。比如先过滤掉无效订单、先按业务需求把不需要的订单类型挡在外面把输入规模压到最小再炸。如果是商品级分析更理想的做法是在明细层先算好指标再join维表不要在炸开后的宽表上反复做关联。另外要记住explode遇到null数组不产行需要保主行就用explode_outer想要下标就用posexplode。这三个函数语义完全不同选错一个结果就是多几条、少几条或者错几位的问题。6.4 别在超大表上反复 schema_of_jsonschema_of_json很方便但它需要真的读数据、做推断成本不低。如果每条任务都对生产大表跑一遍schema_of_json等于每次都对全表做一次额外的JSON扫描对资源和调度都不友好。正确的姿势是开发阶段在抽样表或临时的小集合上跑schema_of_json把schema固化好之后所有生产任务直接用这份固定schema。不要在生产代码里动态推断。关于动态推断还有一个隐藏问题如果某天一条异常数据混进来schema_of_json的推断结果就可能漂移下游引用不存在的字段又是一轮故障。收个尾说点实在的这篇写了这么长核心其实就三件事把JSON当协议管起来、优先用from_json做结构化解析、提前想清楚空数组和null的语义。我见过太多复杂JSON处理翻车的现场最后追根溯源要么是没定义schema就乱取数要么是没区分null和缺失就下结论要么是explode之后没算膨胀系数就join。这些坑在文档里通常不会写但生产环境里一个都躲不掉。如果你现在也被一堆JSON字符串折磨我的建议很土先静下心把schema写或者推出来再用from_json解析最后才谈函数优化。数据结构不变清楚后面所有操作都是空中楼阁。