最近处理一批订单数据的时候碰到一个很典型的诉求订单表里每个订单的商品明细全部以 JSON 数组字符串形式存在一个items_json字段里业务方要求把近三个月内包含指定商品代码的订单行全部捞出来还要能按品类汇总。换作几年前我大概率会写一段 Python 脚本把几千个订单全部拉到内存里逐个json.loads再组装成行。但这一次我决定直接用 SQL 里的JSON_TABLE函数去拆几行查询语句就把问题解决得干干净净。这篇文章就围绕JSON_TABLE解析存储的 JSON 字符串展开把这个函数的语法、实战、踩坑点和选型边界一次说清楚适合经常和数据库里 JSON 字段打交道的数据分析师、后端开发和 DBA 参考。1. 为什么需要 JSON_TABLE旧方案的别扭之处1.1 JSON 字符串存进数据库不等于数据库能直接用它很多业务系统在存储一个主子对象或者一组不确定列的数据时都喜欢用 JSON 字符串搞定。最常见的就是订单商品明细、表单自定义字段、日志里的 key-value 快照。这种存储方式的好处很明显——表结构不用频繁变更前端给什么就存什么开发阶段非常快。但问题是JSON 在数据库里本质上还是一条文本数据。你SELECT *能看到完整的内容但如果想按 JSON 里某个内层字段做WHERE过滤、跟其他表做JOIN、做完再按某个键聚合SQL 本身是完全无能为力的。数据库不知道$.items[2].product_name是什么概念它只看到一段字符串。JSON_TABLE就是来填这个坑的。它能把存储在字段里的 JSON 文档声明式地映射成一张虚拟的关系表映射完之后你可以像操作普通表一样对它进行SELECT、JOIN、GROUP BY、ORDER BY。从定位上来讲它算 SQL/JSON 标准在数据库里的实现MySQL 8.0 和 Oracle 12c 往后都提供了这个能力。PostgreSQL 有类似功能的jsonb_to_recordsetSQL Server 叫OPENJSON原理都一样只是语法殊途同归。1.2 在 JSON_TABLE 之前我们是怎么硬撑的JSON_TABLE出现以前要拆一个 JSON 数组技术选型大概有这么几种每一个都有明显的痛点。第一种是应用层解析。把表里所有行读出来在代码里用json.loads或者JSON.parse处理。这种做法逻辑上没错但数据量一上来就难受了全表数据要经过网络传到应用服务器内存开销大而且数据库里本来能做的过滤、聚合全都没用上相当于用传输带宽换开发便利。第二种是JSON_EXTRACT加UNION ALL。如果数组固定只有 3 个元素你可以写出三个JSON_EXTRACT分支用UNION ALL拼起来。可一旦数组长度是动态的就变成灾难了。你根本不知道最大长度只能先写 10 个UNION ALL超出就丢数据。第三种是自己造行号表。建一张数字序列表1..N然后用JSON_UNQUOTE(JSON_EXTRACT(doc, CONCAT($[, seq, ])))去取第 N 个元素。这个方案比UNION ALL灵活一些但依然要预估最大数组长度而且可读性非常差别人看到那一串拼接字符串会非常困惑。所以当JSON_TABLE被引入之后这类需求立刻变得清爽起来。你只需要告诉数据库外层的数组路径是什么每一行要映射成哪些列剩下的展开逻辑交给数据库自己完成。这也是这篇文章第一个核心观点JSON 字符串存进来没问题但你要有能力把它拆回关系表JSON_TABLE 就是目前最标准的拆法。2. JSON_TABLE 语法拆解外层路径、COLUMNS 和 NESTED PATH2.1 外层路径表达式先让数据库知道去哪里逛先看一个最基础的完整语法SELECT jt.* FROM orders o, JSON_TABLE( o.items_json, $[*] COLUMNS ( product_name VARCHAR(100) PATH $.product_name, price DECIMAL(10,2) PATH $.price, qty INT PATH $.qty ) ) AS jt;这段 SQL 干的事情很简单items_json字段里存的是一个 JSON 数组比如[{product_name:无线鼠标,price:89.00,qty:2}, {product_name:机械键盘,price:399.00,qty:1}]。$[*]表示遍历数组里的每一个元素COLUMNS里的三个列定义则表示把每个元素的哪些键取出来变成列。外层路径表达式是JSON_TABLE的核心参数它支持从简单到复杂的一系列写法$整个 JSON 文档本身$.product_name取product_name键的值$.items[0]取items数组的第一个元素$.items[*]遍历items数组的所有元素$.items[*].product_name遍历items数组并取每个元素的product_name路径表达式的语义和大多数 JSON 查询语言一致手写几遍基本就能熟悉。这里有一个我的个人习惯在所有路径前我都会写$开头$代表根节点省掉它很容易写错。2.2 COLUMNS 定义把 JSON 键映射成关系型列COLUMNS部分是整条语句的映射清单每个列都要指定三个信息列名、数据类型、路径表达式。其中路径表达式可以用PATH关键字显式指定也可以省略不写。如果省略PATHMySQL 默认会拿列名去匹配 JSON 里的同名键。例如COLUMNS (product_name VARCHAR(100))它会自动取$.product_name。这个省略写法在键名规范的情况下很省事但我不建议在正式查询里大量依赖它——一旦 JSON 结构里键名跟列名不完全一致排查起来非常绕。类型方面需要特别注意。JSON 里的数字、字符串、布尔值、数组、对象都有对应的 SQL 类型可映射。标量值拆成INT、DECIMAL、VARCHAR、DATE都没有问题如果某个键本身还是对象你可以把它映射成JSON类型后续再继续嵌套处理。JSON_TABLE默认的行为是如果某一行路径匹配不到对应列会取NULL并且这一行依旧会出现在结果中。这很重要很多人第一次用都会忽略这个语义以为路径匹配失败就自动丢弃该行实际上恰恰相反。后面我会单独讲这个坑。2.3 NESTED PATH数组里还有数组一拆到底真实业务里的 JSON 几乎不会只有一层。订单的 JSON 里通常除了商品明细还有赠品信息、物流信息、优惠分摊信息。举一个典型的多层嵌套结构{ order_id: 1024, customer: A同学, items: [ {product_id: 1001, product_name: 无线鼠标, price: 89.0, qty: 2}, {product_id: 1002, product_name: 机械键盘, price: 399.0, qty: 1} ], gifts: [ {gift_name: 鼠标垫, gift_qty: 1} ] }现在要同时把items和gifts都拆成行跟订单信息放在一张结果表里就要在COLUMNS内部使用NESTED PATHSELECT jt.order_id, jt.product_name, jt.price, jt.qty, jt.gift_name FROM orders o, JSON_TABLE( o.items_json, $ COLUMNS ( order_id INT PATH $.order_id, NESTED PATH $.items[*] COLUMNS ( product_name VARCHAR(100) PATH $.product_name, price DECIMAL(10,2) PATH $.price, qty INT PATH $.qty ), NESTED PATH $.gifts[*] COLUMNS ( gift_name VARCHAR(100) PATH $.gift_name ) ) ) AS jt;注意这里外层路径已经变成了$不再是$[*]因为order_id、customer这些订单级键就在文档根节点上。NESTED PATH会把数组里的元素都拆到同一行上下文中。多个NESTED PATH同时出现时MySQL 采用的是类似笛卡尔积的组合方式也就是说items拆出 2 行、gifts拆出 1 行时结果集就是 2 行如果items2 个元素、gifts3 个元素结果就是 6 行。这个行数放大的逻辑是判断嵌套路径设计是否合理的关键。3. 实战从订单 JSON 里拆出明细再完成聚合统计3.1 造一张带 JSON 字段的订单表理论讲再多不如直接跑一遍。下面我完整演示一个场景订单表orders结构简单一个业务主键一个 JSON 字段里面存着完整的订单快照。CREATE TABLE orders ( id INT PRIMARY KEY, order_no VARCHAR(32), items_json JSON ); INSERT INTO orders (id, order_no, items_json) VALUES (1, A001, [{product_id:1001,product_name:无线鼠标,category:外设,price:89.00,qty:2},{product_id:2001,product_name:USB-C 扩展坞,category:配件,price:229.00,qty:1}]), (2, A002, [{product_id:1001,product_name:无线鼠标,category:外设,price:89.00,qty:1},{product_id:3001,product_name:降噪耳机,category:音频,price:699.00,qty:1}]), (3, A003, [{product_id:3001,product_name:降噪耳机,category:音频,price:699.00,qty:2}]);这里我用了 MySQL 的JSON类型而不是TEXT。两点差别可以提前说明JSON类型在插入时会校验合法性非法 JSON 直接报错保证了数据从源头就是干净的同时 MySQL 对JSON类型优化了内部二进制存储格式解析时略有优势。如果表里历史字段是VARCHAR/TEXT查询时可以做一次CAST(items_json AS JSON)再喂给JSON_TABLE。3.2 核心查询把数组元素变成行把三张订单里的商品明细全部拆成行SELECT o.id, o.order_no, jt.product_id, jt.product_name, jt.category, jt.price, jt.qty FROM orders o, JSON_TABLE( o.items_json, $[*] COLUMNS ( product_id INT PATH $.product_id, product_name VARCHAR(100) PATH $.product_name, category VARCHAR(50) PATH $.category, price DECIMAL(10,2) PATH $.price, qty INT PATH $.qty ) ) AS jt;结果会是这样idorder_noproduct_idproduct_namecategorypriceqty1A0011001无线鼠标外设89.0021A0012001USB-C 扩展坞配件229.0012A0021001无线鼠标外设89.0012A0023001降噪耳机音频699.0013A0033001降噪耳机音频699.002这里JSON_TABLE展开后的结果集就是一个标准的行表订单维度字段和商品明细字段被揉在了一行里。这是整个函数最核心的价值像打开一个嵌套结构一样把里面的数据铺平成一张可以继续计算的关系表。3.3 进阶操作聚合、过滤和 JOIN拆成行之后平时对普通表做的操作全部可以继续。比如统计每个品类的销量和营业额SELECT jt.category, SUM(jt.qty) AS total_qty, SUM(jt.price * jt.qty) AS total_amount FROM orders o, JSON_TABLE( o.items_json, $[*] COLUMNS ( category VARCHAR(50) PATH $.category, price DECIMAL(10,2) PATH $.price, qty INT PATH $.qty ) ) AS jt GROUP BY jt.category ORDER BY total_amount DESC;也可以按条件过滤比如只保留所有包含降噪耳机的订单行SELECT o.order_no, jt.product_name, jt.qty FROM orders o, JSON_TABLE( o.items_json, $[*] COLUMNS ( product_name VARCHAR(100) PATH $.product_name, qty INT PATH $.qty ) ) AS jt WHERE jt.product_name 降噪耳机;还可以关联另一张商品维度表做带维度的分析。这个能力在面对JSON 字段 现有关系表共存的企业数据库里特别实用等于把 JSON 数据从隔离区拉回到了主数据体系里。我当时处理的数据核对需求就是先JSON_TABLE拆行再JOIN商品代码维度表最后用一条GROUP BY完成了原来 Python 脚本几千行代码才能搞定的事情。4. MySQL 与 Oracle 的 JSON_TABLE 差异换库之前必须先看这里4.1 同一套思路两套细节JSON_TABLE不是 MySQL 独有的Oracle 从 12c 开始支持MySQL 也从 8.0 起加入。它们的核心思路完全一致但具体细节差异很影响写法和排错。下面我用一张表把常见差异列出来对比项MySQL 8.0Oracle 12cON EMPTY 处理不支持路径无值返回 NULL支持NULL ON EMPTY、DEFAULT ... ON EMPTY、ERROR ON EMPTYON ERROR 处理不支持解析错误返回 NULL支持ERROR ON ERROR、NULL ON ERROR、DEFAULT ... ON ERRORNESTED PATH 连接语义默认 LEFT JOIN 式支持OUTER/INNER定制数据源类型需 JSON 类型或 CAST 为 JSONVARCHAR2/CLOB 中合法 JSON 均可直接解析大文本字段JSON 列类型有内部二进制优化CLOB 大字段直接解析类型推断需显式声明列类型可以声明类型也支持直接输出 JSON行来源需要配合 FROM 使用早期版本基本靠逗号隐式连接可以用逗号连接也可以用于 LEFT JOIN举一个 Oracle 的典型写法能明显看到它多了很多控制选项SELECT * FROM orders o, JSON_TABLE( o.items_json, $[*] COLUMNS ( product_name VARCHAR2(100) PATH $.product_name NULL ON EMPTY, price NUMBER PATH $.price ERROR ON ERROR, qty NUMBER PATH $.qty DEFAULT 0 ON ERROR ) ) jt;如果product_name路径不存在返回 NULL如果price路径解析成非数字直接报错如果qty解析失败用 0 兜底。这套语法比 MySQL 更精细尤其是在数据质量不可控的 JSON 字段上ERROR ON ERROR能帮你快速暴露出脏数据适合做数据质量巡检。4.2 迁移时最容易踩的语法坑从 Oracle 迁到 MySQL最常遇到的问题就是 ON EMPTY/ON ERROR 子句大量存在MySQL 完全不认。迁移策略一般有两种一是删掉这些子句接受 NULL 默认行为在后续查询里用WHERE过滤掉 NULL 行二是在 MySQL 里对字段做预处理先把 JSON 里的缺失字段补齐默认值再存。从实际效果看第一种更省事。MySQL 的另一个限制是JSON_TABLE的结果集如果要用在LEFT JOIN右侧版本要在 8.0.14 之后而且ON子句通常只能写恒真条件然后靠WHERE过滤。早期版本基本就是逗号写法和普通CROSS JOIN你可能在改写时感到不习惯但本质上它就是每一行 JSON 都有可能拆出多行的横向扩展用逗号隐式连接是最直白的表达。还有一个容易踩的差异是 Oracle 允许把 CLOB 字段里存的 JSON 字符串直接解析而 MySQL 如果原始字段是TEXT必须先CAST成 JSON 类型。对于历史遗留的表来说这一步千万别漏漏了就直接报参数类型错误。5. 踩坑实录路径失效、NULL 虚行和性能膨胀5.1 路径表达式写错最坑不报错只给你全 NULL我见过最多的低级错误就是路径写错而JSON_TABLE对路径错误非常宽容它不会报错只会返回 NULL。这一点比程序语言里的 JSON 解析要隐蔽得多。比如$[0]想取数组第一个元素写成了$0MySQL 不会立刻告诉你路径不存在结果列全变 NULL你还以为数据错了。排查方法很简单先用JSON_EXTRACT或者JSON_KEYS验证一下你预期的路径是否存在SELECT JSON_EXTRACT(items_json, $.items[0].product_name) FROM orders WHERE id 1;这一步能快速确认路径有没有走过。另一个建议是写JSON_TABLE时把第一列直接映射成整条 JSON或者映射成JSON_QUERY的中间结果方便排查是哪一层路径出了问题。另外要提醒的是路径里的数字索引问题。数组下标从 0 开始是常识但很多人会在$[1]取第二个元素时以为这是第一个。JSON 数组返回顺序跟插入顺序一致取错下标不会报错只会取到不对的数据。这种逻辑上的错比语法错误更难发现。5.2 空数组不产生行但字段缺失会留下 NULL 虚行我前面提过JSON_TABLE在路径匹配不到值时行依然存在值是 NULL。这一点在嵌套路径展开时特别有欺骗性。举个例子如果外层有 3 个订单其中第 2 个订单的items是空数组那么在NESTED PATH $.items[*]展开时这个订单根本不会出现在结果里因为空数组没有可迭代的元素。这被认为是合理的按行裁剪。但如果某个订单的items键直接不存在情况会变成这个订单对应一行记录其中商品相关列全是 NULL。从语义上说一个缺少items键的对象和一个items为空数组的对象处理行为是不一样的。前者要留一行给你填NULL后者直接不出现。实际业务里如果这两种情况混在一起统计时就会发现多出一些幽灵行。处理方式通常是加过滤条件WHERE jt.product_name IS NOT NULL或者干脆在路径上使用 MySQL 8.0.20 之后提供的empty_field_behavior也可以用JSON_EXISTS做前置判断。如果你的库版本支持最推荐的做法是写一段预查询把 JSON 结构不规范的数据单独揪出来修掉。5.3 性能问题JSON 解析很贵别把表和索引当摆设JSON_TABLE的性能是使用中争论最多的点。它毕竟是要把整个 JSON 字符串读出来在内存里解析成文档结构然后再展开成行。如果 JSON 字段体积巨大、行数又多查询速度会非常感人。它不是魔法的免费午餐是有解析成本的。有几个可以落地的优化思路第一别在 WHERE 条件里对 JSON_TABLE 拆出来的列做无谓的二次处理。例如不要对jt.price做函数包裹后过滤保持列本身可应用索引策略。虽然虚拟行不像普通表列那样直接有索引但条件写得简单有助于优化器的行数估算更准确。第二尽量减少 JSON 字符串本身的体积。很多业务表因为历史原因存了整份接口响应快照里面大量字段是分析根本用不到的。可以在查询前先用JSON_REMOVE或者生成列裁掉无用键再喂给JSON_TABLE。这个做法对超大 JSON 字段的效果非常明显。第三给固定路径查询建生成列或多值索引。如果业务长期需要按 JSON 里的某个键过滤例如按product_id查询可以直接在表上增加生成列ALTER TABLE orders ADD COLUMN first_product_id INT GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(items_json, $[0].product_id))) STORED; CREATE INDEX idx_first_product ON orders(first_product_id);MySQL 8.0.17 之后还支持多值索引能直接索引 JSON 数组里的所有元素值CREATE INDEX idx_items_product ON orders ((CAST(JSON_EXTRACT(items_json, $[*].product_id) AS UNSIGNED ARRAY)));不过这类索引对 JSON 路径的写法有严格要求投入产出比需要自己评估。我个人的经验是索引确实能解决问题但前提是查询模式高度固定如果每次 JSON 结构都不一样那还是回到 ETL 层面把数据抽成明细宽表更靠谱。6. JSON_TABLE 的适用边界什么场景该用什么场景别硬用6.1 适合用 JSON_TABLE 的四类场景第一数据量中等、结构相对稳定的 JSON 字段需要拆行跟其他表做分析。这是最顺手的场景比如日志快照、订单明细、配置明细类的 JSON 数据。第二你只需要从大量 JSON 行里查一小部分但查询条件偏偏落在内层字段上。JSON_TABLE配合WHERE能在数据库侧完成裁剪省去把全表数据拖到应用层再过滤的笨办法。第三数据仓库 ODS 层到 DWD 层的清洗过程。ETL 任务里从 JSON 源表里拆出结构化明细再把结果写进宽表或者维度表JSON_TABLE可以直接嵌在 SQL 里省去中间脚本。第四需要把 JSON 字段里的多维数组同时展开拼成一张扁平宽表。嵌套路径天然就是为了这种扁平化场景设计的。6.2 不建议硬用 JSON_TABLE 的场景JSON 结构过于深、数组层级复杂且长度极大时JSON_TABLE会面临两个问题一是解析整个文档的 CPU 和内存开销居高不下二是多路径展开产生的笛卡尔积会让结果行数迅速膨胀甚至比原始业务行数大几个量级。这种情况我会建议还是让应用层解析或者用文档型数据库来处理关系型数据库硬拆不值得。另外如果是高频的关键业务路径每次请求都要实时拆 JSON那它本质上是把解析压力放到了数据库上。社区里对这类实践有句话总结得很好——JSON_TABLE 是给分析场景用的不是给面向用户的接口做实时处理用的。对这种高频路径更实际的做法是把常用的字段转成普通列或者提前在应用写入时同时落一份结构化版本。最后还有一种情况JSON 里键名随业务动态变化今天有price明天冒出一个sale_price。这类结构不稳定、元数据频繁演进的字段并不适合抽出固定的COLUMNS映射硬写JSON_TABLE只会变成一场没完没了的维护战。我在实际工作中发现JSON_TABLE 真正值钱的不是它能把 JSON 拆成行这个动作而是它把JSON 数据能不能参与关系查询这个能力边界往前推了一大步。以前碰到 JSON 字段第一反应是写脚本绕开现在第一反应是翻一下字段结构判断用 JSON_TABLE 能不能更直接地解决问题。不过也正因为这样使用之前有必要先花两分钟评估 JSON 的结构复杂度和数据量级。拆得明白、拆得有必要才是这个函数正确的打开方式。