1. 为什么需要 JSON_TABLE从一个“难以拆解”的存储现场说起把 JSON 字符串当成普通文本塞进数据库表是我见过最简单也最麻烦的设计。简单在写入时完全不用建模不管数据结构怎么变一个字段就能兜住麻烦在你想按某个字段筛选、统计、再做多张表关联的时候整条 JSON 就像一块整砖怎么拆都别扭。最近在梳理某项目 X 的历史订单数据我碰到一种存储结构主表只保留了订单号、会员标识剩下的扩展信息全部堆在一个 JSON 字符串里。要输出一张“会员等级 商品 SKU 数量金额”的报表靠传统 SQL 根本拿不动。这种时候JSON_TABLE 就是数据库底层提供的、专门把 JSON 拆成关系表的标准化工具。它的思路不像普通函数那样“取一个值给你”而是告诉你这段 JSON 里有若干行数据行里还有若干列我按你指定的路径和类型把它们抽出来组合成一张可以在 FROM 子句里继续参与查询的临时表。换句话说你可以在一条 SQL 里完成“拆 JSON → 过滤 → join → 聚合”的全流程不用先捞回应用层手工遍历也不用写一堆临时脚本来清洗数据。这篇文章适合谁看两类人最对口一类是手上有存量表、里面存着大量 JSON 字符串现在想直接拿来查数的数据开发另一类是刚接触 JSON 函数听说过 JSON_EXTRACT、- 但从来没系统用过 JSON_TABLE 的 SQL 使用者。我会把语法、实操、排查过程一起写尽量不保留所谓的“知识点盲区”。先给一个直观印象。假设主表里有一行记录order_json 字段的内容大概是{ orderNo: A10001, customer: { name: 小明, level: VIP }, items: [ { sku: P001, qty: 2, price: 39.9 }, { sku: P002, qty: 1, price: 19.9 } ] }我们希望最后得到的结果是三行数据A10001、小明、VIP、P001、2、39.9A10001、小明、VIP、P002、1、19.9。嵌套数组里的每个商品都展开成一行同时外层对象里的 orderNo、customer 信息也跟着重复带到每一行。这正是 JSON_TABLE 的主场。1.1 JSON_EXTRACT 系列函数的短板在哪在 MySQL 8.0 之前处理 JSON 最常用的写法是 JSON_EXTRACT以及后来更顺手的 - 运算符。它们解决的是“取某个路径下的值”这个问题。比如想拿订单号写SELECT id, JSON_UNQUOTE(JSON_EXTRACT(order_json, $.orderNo)) AS order_no, order_json - $.customer.name AS customer_name FROM member_orders;这写法本身不复杂但它的局限非常明显一次只能取一个标量字段。如果要拆嵌套数组麻烦劲就上来了。你没法在一条普通的 SELECT 里对一个数组里的 N 个对象做“行展开”除了用 JSON_TABLE只能先在外面写脚本或者用自己拼的派生数字表去关联代码又长又难维护。再加上 JSON 里的层级一旦深了JSON_EXTRACT 的路径会变得又长又绕每一个字段都要写一遍SQL 的可读性基本归零。JSON_TABLE 把问题拆成了两个层次第一层是“行匹配”用路径表达式指定 JSON 里的哪一段会被映射成结果表的行第二层是“列映射”用 COLUMNS 子句声明每一列叫什么名字、什么类型、从哪个路径取。这两个层次一分开复杂嵌套的解析就变成了一套结构化的声明而不是一串函数套函数。2. JSON_TABLE 语法核心拆解先用一句话概括完整语法JSON_TABLE(表达式, 路径 COLUMNS (列定义列表))整段会作为表出现在 FROM 子句里。最基础的一段 SQL 长这样SELECT jt.* FROM member_orders AS mo, JSON_TABLE(mo.order_json, $ COLUMNS ( order_no VARCHAR(20) PATH $.orderNo, customer_name VARCHAR(50) PATH $.customer.name ) ) AS jt;这里 JSON_TABLE 的第一个参数是数据来源它接收一个 JSON 文档或能够转成 JSON 的字符串。第二个参数里$ 指整个 JSON 文档的根路径也就是说我们从根上开始取行。COLUMNS 里每一行定义一个输出列顺序很重要后面的 NESTED 也必须放在普通列之后。如果只是取一个对象里的几个字段上面这种写法已经够了。实际业务里更常见的是“外层对象一行 内层数组多行”的结构这就需要用到 NESTED PATH。看下面这个例子SELECT jt.order_no, jt.customer_name, jt.sku, jt.qty FROM member_orders AS mo, JSON_TABLE(mo.order_json, $ COLUMNS ( order_no VARCHAR(20) PATH $.orderNo, customer_name VARCHAR(50) PATH $.customer.name, NESTED PATH $.items[*] COLUMNS ( sku VARCHAR(20) PATH $.sku, qty INT PATH $.qty, price DECIMAL(10, 2) PATH $.price ) ) ) AS jt;执行后外层 order_no 和 customer_name 是重复值因为数组里每个元素都会和它组合生成一行。这就是 JSON_TABLE 最核心的“行展开”能力。2.1 COLUMNS 子句里的四个关键角色PATH 是列定义的主心骨。它负责告诉数据库“从当前行上下文里的哪个位置取值”。这里有个容易踩的细节在 NESTED 内部写的 PATH是相对 NESTED PATH 匹配到的那一层而言的。比如 NESTED PATH $.items[] 已经把行定位到数组里的某一个元素内部的 $.sku 就是当前元素下的 sku不需要再写 $.items[].sku。这个相对路径思维一定要转过来不然很容易写出完全查不到数据的错路径。FOR ORDINALITY 是给输出行编号用的类似窗口函数里的 row_number但作用位置不一样。它在 COLUMNS 子句里声明不需要指定数据类型例子如下JSON_TABLE(mo.order_json, $ COLUMNS ( seq FOR ORDINALITY, order_no VARCHAR(20) PATH $.orderNo ) ) AS jt它的价值主要体现在排错和排序上。如果你怀疑同一行 JSON 被拆出了多行seq 是递增的配合普通列一对比能很快确认展开的先后顺序。遇到某些集合聚合函数也能用它辅助去重或者保留“第几次出现”的语义。EXISTS PATH 用于判断某个路径是否存在返回值是 1 或 0。它适合做标记位。比如你想知道 JSON 里有没有会员等级字段可以这么写COLUMNS ( has_level TINYINT EXISTS PATH $.customer.level )这在写报表时很好用。很多业务会把可选项字段直接缺省用 EXISTS PATH 就能区分“值为 null”和“字段不存在”这两种情况避免统计时误判。最后是 NESTED它是处理数组展开的主力。一个 JSON_TABLE 里可以出现多个 NESTED PATH 子句多个嵌套路径会形成多组行展开组合后的行数会出现乘积效应。举个例子如果外层数组有 2 个元素内层数组也有 2 个元素那么结果行数可能是 4 行。这是 SQL 的笛卡尔积逻辑分析需求时心里要有数否则报表汇总金额时容易成倍膨胀。2.2 空值、缺省和类型转换的声明方式实际存储的 JSON 很少规规矩矩每个字段都有。有的订单缺备注有的商品没有促销价。JSON_TABLE 默认情况下如果路径匹配不到对应列会得到 NULL。想让它返回默认值就用 ON EMPTY想让它在类型转换出错时报错就用 ON ERROR。MySQL 8.0 中比较完整的列定义长这样COLUMNS ( note VARCHAR(100) PATH $.note DEFAULT 无备注 ON EMPTY, qty INT PATH $.qty ERROR ON ERROR )这里有一层逻辑要分清ON EMPTY 管“路径不存在或者匹配结果是空”ON ERROR 管“路径存在但类型转换失败”。比如字符串 abc 想转成 INT这就是 ERROR 场景得用 ON ERROR 来决定是报错还是给默认值。生产环境里我建议显式写清楚别让数据库做隐式决定否则排查空数据和报错时很难定位。另外类型转换在 JSON 里比在普通表里更值得较真。JSON 的数值可能是 39.9也可能是 39.9。前者转 DECIMAL 没问题后者转 DECIMAL 不同数据库处理方式不同。在 MySQL 里字符串形式的数字多数情况下也能隐式转换但如果数据里混着39.9元这种带单位的文本INT 和 DECIMAL 转换就会直接触发 ERROR。这时候要么在源头清洗数据要么在列定义里改用 VARCHAR 类型取值后续再用 CAST 转。3. 实操从 JSON 字符串到可聚合的关系表纸上谈兵再多不如直接跑一遍。下面这个例子我特意模拟了存量系统中的常见形态一个字段是 JSON 类型但里面有嵌套对象和嵌套数组。我们最后要做的事情有三个把外层信息拆出来、把内层数组展开、按商品维度汇总金额。3.1 建表与样例数据先建一张订单表字段故意设计得简单一点模拟项目里“主表少量字段 扩展 JSON”的典型结构CREATE TABLE member_orders ( id INT PRIMARY KEY, member_no VARCHAR(30), order_json JSON ); INSERT INTO member_orders VALUES (1, M001, {orderNo: A10001, customer: {name: 小明, level: VIP}, items: [ {sku: P001, qty: 2, price: 39.9}, {sku: P002, qty: 1, price: 19.9} ]}), (2, M002, {orderNo: A10002, customer: {name: 小红, level: 普通}, items: [ {sku: P003, qty: 1, price: 99} ]});注意我把 order_json 字段定义为 JSON而不是 VARCHAR 或 TEXT。这是有意的选择。如果你手里的表是历史遗留的 VARCHAR 字段也没关系可以在 JSON_TABLE 里先做 CAST比如JSON_TABLE(CAST(order_json AS JSON), $ ...)。但你要知道JSON 类型字段在写入时就会做合法性校验存储上也会做二进制优化解析效率更高VARCHAR 里的 JSON 文本是每次都现解析性能和稳定性都会差一截。3.2 完成拆行和聚合的完整 SQL接下来写一条完整的查询把外层的 customer 信息、内层的 items 数组全部拆成明细行再按 SKU 汇总金额WITH parsed AS ( SELECT t.order_no, t.customer_name, t.customer_level, t.sku, t.qty, t.price FROM member_orders AS mo, JSON_TABLE(mo.order_json, $ COLUMNS ( order_no VARCHAR(20) PATH $.orderNo, customer_name VARCHAR(50) PATH $.customer.name, customer_level VARCHAR(20) PATH $.customer.level, NESTED PATH $.items[*] COLUMNS ( sku VARCHAR(20) PATH $.sku, qty INT PATH $.qty, price DECIMAL(10, 2) PATH $.price ) ) ) AS t ) SELECT order_no, customer_name, customer_level, sku, SUM(qty) AS total_qty, SUM(qty * price) AS total_amount FROM parsed GROUP BY order_no, customer_name, customer_level, sku ORDER BY total_amount DESC;这段 SQL 的执行逻辑分成四层。第一层从 member_orders 每一行里取出 order_json交给 JSON_TABLE第二层按照根路径 $ 匹配出一条记录提取 orderNo、customer.name、customer.level 三列第三层进入 items 数组的每个元素展开 sku、qty、price 三列第四层CTE 产出的明细行进入 GROUP BY按订单和商品粒度完成加总。我建议你在实际项目里保持这种“先拆成明细再聚合”的思路。很多人喜欢在 JSON_TABLE 的外层直接包 GROUP BY语法虽然没错但一旦嵌套路径多、字段多调试时你根本分不清某行 NULL 是拆出来的还是聚合产生的。先拆明细、后聚合逻辑清晰很多。3.3 常见过滤和关联写法拆出来的临时表能做的事和普通表没有区别。要做条件过滤直接在 CTE 外层加 WHERE 即可WITH parsed AS (...) SELECT order_no, sku, qty FROM parsed WHERE customer_level VIP AND qty 1;想和其它表关联也完全没问题比如补一个商品表把 SKU 对应的分类名带出来。这里需要注意一个容易翻车的点JSON_TABLE 在 FROM 子句里用逗号连接的时候语义接近内连接。如果主表某一行 JSON 里目标路径根本没匹配到数据这一行会被过滤掉。想保留主表所有数据、匹配不到就留空值就要用 LEFT JOIN并有一个恒真的 ON 条件SELECT mo.id, jt.sku, jt.qty FROM member_orders AS mo LEFT JOIN JSON_TABLE(mo.order_json, $ COLUMNS ( NESTED PATH $.items[*] COLUMNS ( sku VARCHAR(20) PATH $.sku, qty INT PATH $.qty ) ) ) AS jt ON TRUE;这个 ON TRUE 写法对不少初学者来说很反直觉但 JSON_TABLE 本质是一个表函数LEFT JOIN 总得有个关联条件既然 JSON_TABLE 和主表共享同一个 JSON 来源本身就是一对一展开用恒真条件让它保留主表全部行是最自然的做法。4. 选型对比与跨数据库差异JSON_TABLE 不是唯一能把 JSON 转成关系表的手段但在 MySQL 生态里它的标准度、表达力都比手工解析强。这里我从实际选型角度把几种做法放在一起比较一下。4.1 MySQL 内建解析方式对比先看一张对比表写法适合场景主要局限- 运算符取单个标量字段无法展开数组路径重复书写JSON_EXTRACT取值、判断路径是否存在返回 JSON 类型需要再 UNQUOTE 或 CASTJSON_TABLE多字段拆行、嵌套数组/对象展开语法略复杂需要理解路径和列定义应用层解析复杂业务逻辑、多次交互网络传输大代码量多难以做 SQL 级统计实际使用中我见过的错误倾向是“在 JSON_EXTRACT 一条路走到黑”。如果只是查一两个字段JSON_EXTRACT 没问题一旦涉及数组展开JSON_EXTRACT 几乎没法体面地完成任务。JSON_TABLE 的另一个优势是可以直接把拆出来的结果放进视图或物化视图里其他下游系统可以把它当普通表继续消费而不需要感知 JSON 结构。这对数据仓库、报表系统来说特别重要。4.2 其他数据库的 JSON_TABLE 差异先说 Oracle。Oracle 12.2 开始提供 JSON_TABLE语法上和 MySQL 很接近核心也是路径 COLUMNS。差异主要在错误处理和一些细节关键字上比如 Oracle 用 NULL ON EMPTY、ERROR ON ERROR 这种更显式的写法MySQL 的默认行为和关键字略有不同。如果你平时主要写 MySQL切到 Oracle 时不要想当然先看一眼官方语法再动手。再看 PostgreSQL。PostgreSQL 17 正式引入了 JSON_TABLE之前的版本更多使用 jsonb_to_recordset 配合 LATERAL 实现。如果你维护的是混合数据库环境建议把解析逻辑尽量收敛到同一种风格比如都通过 SQL 标准风格的 JSON_TABLE或者干脆都在应用层解析避免同样的需求在多个数据库里维护两套写法。SQL Server 也有对应的 OPENJSON 表值函数思路类似但函数名和路径语法又不一样。这种跨数据库不一致是客观存在的现实所以真正决定选型时我的建议是优先看当前存量系统用了什么数据库再去查对应版本的文档而不是把一个环境里跑通的 JSON_TABLE 原封不动搬到另一个环境。5. 常见故障排查与性能优化经验JSON_TABLE 的报错信息有时非常抽象尤其路径错误和类型错误混在一起的时候新手很容易被绕进去。我把自己排查过程中积累的问题整理成速查表你在现场可以直接对照。5.1 错误信息速查与处理建议现象 / 报错常见原因处理建议结果全是 NULL但 JSON 里明明有值PATH 路径写错或 NESTED 内用了绝对路径先单独用 JSON_EXTRACT 验证路径确认后再放进 JSON_TABLE报错 Invalid JSON text源字段存的是非法 JSON 字符串如果是 VARCHAR 字段先清洗再 CAST 为 JSON别直接解析报错 Data truncation / 转换失败类型声明与实际值不一致改用 VARCHAR 类型取值后续再转换出现意外多倍的行数多个 NESTED 路径存在笛卡尔积先单独拆一个 NESTED 验证行数再组合多个主表数据少了逗号连接 JSON_TABLE 起到了内连接效果改成 LEFT JOIN ... ON TRUE查询性能差全表扫无法利用普通索引考虑用生成列 索引或在应用层做好预解析其中“路径写错导致整列 NULL”是我遇到最多的。排查手段很简单先不带 NESTED只写普通列看外层字段能不能取到再加一个 SKU 字段如果 SKU 正常但价格 NULL那大概率是 NESTED 内部路径错了。用排除法一层层缩比盯着报错信息猜高效得多。还有一个容易被忽略的点存储过程或函数里使用 JSON_TABLE 时表别名是必须的。MySQL 对 JSON_TABLE 的别名要求比较严格不写别名或别名重复都会报错。很多看起来莫名其妙的语法错误其实都是别名问题。5.2 性能优化里的三个实操思路第一个思路是能用 JSON 类型列就不用 VARCHAR。同样是 JSON 字符串JSON 列在存储时有二进制格式优化解析效率明显更高。如果你的存量表是 VARCHAR而数据量又大我会建议抽时间一次性迁移到 JSON 列不要长期带病运行。第二个思路是给“高频过滤字段”建立生成列和索引。比如你经常按 JSON 里的 customer.level 筛选那么可以在表上增加一个生成列ALTER TABLE member_orders ADD COLUMN customer_level VARCHAR(20) GENERATED ALWAYS AS (order_json - $.customer.level) STORED; CREATE INDEX idx_member_orders_level ON member_orders(customer_level);这样查询 JSON_TABLE 拆出的结果时可以先从索引里筛掉大量无关行再把命中的行送去解析性能收益非常明显。生成列在写入和更新时有额外成本但相比全表 JSON 解析这点成本通常值得。第三个思路是控制解析结果量。不要在 SQL 里把整个大 JSON 无脑展开到几十万行再从里面取一点点数据。把 JSON_TABLE 的结果先限定在一个小范围里再和其它表 join能省下很多资源。比如先通过主表条件过滤出最近一周订单再展开明细就比全量展开然后汇总好得多。最后分享一个个人习惯我每次写 JSON_TABLE 前都会先用一条最简单的查询验证 JSON 的实际结构比如单独执行SELECT order_json FROM member_orders WHERE id 1;把原始 JSON 拿出来人肉确认路径。这个动作看着浪费时间实际上能节省后面很多排错时间。常用的解析模板我会保存成内部工具每次做新的数据迁移或报表任务时直接复用路径替换一下就能落地。数据结构复杂不可怕可怕的是没有一套稳定的解析套路让每次拆 JSON 都变成赌运气。