MySQL JSON函数详解：从JSON_EXTRACT到JSON_TABLE的SQL实践

做后端开发的这几年JSON 和 MySQL 基本是每天都要打交道的两样东西。以前的常规操作是把 JSON 字符串整段取出来丢给应用层的 Jackson 或 Gson 解析需要哪个字段再 get 哪个字段。可一旦遇到要在数据库里做筛选、统计、排序这种玩法就特别别扭——只能先全量捞回内存再在代码里做二次处理数据量一大性能和代码复杂度双双崩盘。MySQL 从 5.7 开始原生支持 JSON 类型并且提供了一套完整的 JSON 函数JSON_EXTRACT、JSON_UNQUOTE、JSON_CONTAINS、JSON_SEARCH8.0 又补上了 JSON_TABLE、JSON_VALUE 和多值索引。这套函数解决的就是上面那个问题直接在 SQL 里从 JSON 字符串提取字段、过滤、聚合把“数据在哪、计算就在哪”真正落到执行层面。这篇文章我打算从实际业务出发先从最基础的函数讲起再到 JSON_TABLE 展开数组这种高级用法最后把路径语法、索引优化、常见坑位全部过一遍。适合给那些数据库里已经躺着 JSON 列、正头疼怎么高效使用它的开发同学也适合准备把部分 JSON 解析逻辑从应用层下沉到数据库的团队参考。全文不涉及安装配置专门聊怎么把 JSON 用明白。1. 整体思路为什么要在数据库里直接解析 JSON1.1 业务场景哪些情况下你该用 SQL 提取 JSON我遇到的典型场景有这么几类。第一类是电商订单表。订单主表字段固定但支付渠道、设备来源、用户标签、收货地址详情这类扩展信息业务方经常变动传统做法就是不断加列或者干脆建一堆关联表。大部分团队最后都会选择加一个ext_infoJSON 列想存什么存什么。第二类是第三方回调数据。微信支付回调、开放平台推送、埋点上报这些数据格式由对方定义我们只能整包落库。入库之后经常要按里面的某个字段筛选比如“拉出所有 event_type pay_success 的记录”这显然得靠 SQL 里的 JSON 函数。第三类是配置类数据。CMS 文章的自定义字段、A/B 实验参数、商品规格书用 JSON 存储再合适不过。这些场景里如果你还在“全表拉回应用层再 parse”当数据量到几十万、上百万行网络传输和 GC 压力会先把你压垮。直接在 MySQL 里提取至少有三层收益传输量小只返回你需要的列而不是整条 JSON。过滤下推WHERE条件在存储引擎层就能砍掉大部分数据。逻辑收敛报表统计、导出脚本不再依赖应用代码一条 SQL 就能完成。当然也不是所有场景都适合在数据库里解析。JSON 嵌套层级太深、内部结构频繁变化、或者一次查询要展开几百个嵌套数组的时候数据库里写起来会非常痛苦这时候老老实实回到应用层解析反而更稳。我的判断标准很简单如果这个 JSON 字段需要参与筛选、聚合、排序就值得用 SQL 处理如果只是原样展示给前端那就不要动它。1.2 版本选型5.7 和 8.0 的 JSON 能力差异很多同学不清楚自己的 MySQL 版本到底支持哪些 JSON 功能这里先给一张对比表省得写出来的 SQL 上线才发现语法不支持。功能MySQL 5.7MySQL 8.0JSON 类型存储支持支持8.0 优化为二进制存储JSON_EXTRACT / - / -支持支持JSON_CONTAINS / JSON_SEARCH支持支持JSON_KEYS / JSON_LENGTH / JSON_VALID支持支持生成列 索引支持支持JSON_TABLE 表函数不支持8.0.4JSON_VALUE不支持8.0.21多值索引Multi-Valued Index不支持8.0.17JSON_OBJECTAGG / JSON_ARRAYAGG5.7.22支持另外有一点必须提前说清楚JSON 函数不仅能用在 JSON 类型列上也能用在VARCHAR、TEXT类型的字符串上只要字符串内容是合法的 JSON。区别在于JSON 类型列在写入时会校验合法性并以优化后的二进制格式存储提取速度更快VARCHAR 里存 JSON 字符串则每次函数调用都需要重新解析文本性能差一些。所以如果你还在用TEXT存 JSON且经常需要按内部字段查询建议直接改成 JSON 类型。ALTER TABLE ... MODIFY COLUMN col JSON一条语句就能完成但要注意先做数据校验别让脏数据卡住 DDL。我用这类语句前都会先跑一遍SELECT COUNT(*) FROM t WHERE NOT JSON_VALID(col)检查确保没有非法 JSON 再动表。1.3 JSON 路径语法写不对路径一切白搭JSON 函数的核心是路径表达式路径写不对后面的操作全白费。MySQL 的 JSON Path 规则很简单记住几条就够了$代表整个 JSON 文档.key访问对象成员[n]访问数组第 n 个元素下标从 0 开始[*]匹配数组所有元素如果 key 名包含点号、空格、特殊字符必须用双引号包起来比如$.user.name。看几个最常用的例子-- 对象嵌套 SELECT JSON_EXTRACT({a: [1, 2, {b: 3}]}, $.a[2].b); -- 结果3 -- 数组下标 SELECT JSON_EXTRACT([10, 20, 30], $[0]); -- 结果10 -- 含特殊字符的 key SELECT JSON_EXTRACT({user name: zhangsan}, $.user name); -- 结果zhangsan路径表达式在 SQL 里是一个普通字符串所以外层用单引号内部的双引号属于路径本身。最容易踩的坑是把路径写成$.a[0].name却在数组是对象数组时下标从 1 开始或者漏写$前缀。一旦路径不合法MySQL 会直接报错Invalid JSON path expression并且提示错误位置。我自己排查这种报错的经验是先把路径字符串单独拷到 SELECT 里试一遍SELECT JSON_EXTRACT({a:1}, $.a)这样最小化问题范围。2. 核心函数拆解从 JSON_EXTRACT 到 JSON_TABLE2.1 最常用的三件套JSON_EXTRACT、- 和 -JSON_EXTRACT(json_doc, path)是最底层的提取函数返回结果是 JSON 类型。换句话说如果提取的值是一个字符串结果会带双引号比如zhangsan直接拿去和 VARCHAR 字段比较、拼接都会得到意料之外的结果。配合JSON_UNQUOTE()可以把 JSON 字符串两侧的引号去掉转成 MySQL 普通字符串。这俩组合实在太常用所以 MySQL 直接提供了两个运算符-等价于JSON_EXTRACT-等价于JSON_UNQUOTE(JSON_EXTRACT(...))。实际 SQL 里长这样SELECT JSON_EXTRACT(doc, $.name) AS a, JSON_UNQUOTE(JSON_EXTRACT(doc, $.name)) AS b, doc - $.name AS c, doc - $.name AS d FROM t;结果四列分别是zhangsan、zhangsan、zhangsan、zhangsan。日常开发中只要不是想保留 JSON 类型继续做嵌套计算直接无脑用-就行。比如WHERE doc - $.status paid、SELECT doc - $.city清爽直观。MySQL 8.0.21 之后还提供了JSON_VALUE可以在提取时直接指定返回类型并且处理路径无匹配的情况SELECT JSON_VALUE(doc, $.age RETURNING UNSIGNED) AS age, JSON_VALUE(doc, $.name RETURNING CHAR(50) ON EMPTY DEFAULT 未知 ON ERROR DEFAULT 未知) AS name FROM t;ON EMPTY处理路径不存在ON ERROR处理类型转换失败这个函数在做数据清洗、接口入参标准化时特别好用。不过要注意它只返回标量值不能用来提取数组或对象。2.2 条件过滤在 WHERE 里查询 JSON 内部字段提取字段除了用于 SELECT 展示更常见的是放到 WHERE 里过滤。最基础的写法就是「路径提取 比较」SELECT order_no FROM orders WHERE ext_info - $.channel wechat;但如果 JSON 里的目标字段是数组比如tags: [vip, new_user]想查“包含 vip 标签”的订单-就比较麻烦了。这时候用JSON_CONTAINSSELECT order_no FROM orders WHERE JSON_CONTAINS(ext_info, vip, $.tags);JSON_CONTAINS(target, candidate, path)的candidate必须是一个合法 JSON 值所以字符串要写成带双引号的vip也可以配合JSON_QUOTE(vip)动态拼接。判断的是整个数组是否包含该元素语义很清晰。还有一种场景是你不知道目标值在 JSON 的哪个位置只想“全文搜索”。这时用JSON_SEARCHSELECT order_no FROM orders WHERE JSON_SEARCH(ext_info, one, vip) IS NOT NULL;第二个参数指定返回第一个匹配还是所有匹配one或all。这个函数在 JSON 结构不确定、只能确定值的情况下是救命稻草但性能一般数据量大要慎用后面讲索引时再展开。2.3 JSON_TABLE把 JSON 数组展开成关系表如果说前面几个函数只是“单点提取”那JSON_TABLE就是一个质变。它的作用是把 JSON 数组逐行展开和普通表做 JOIN生成一张“看起来就是关系表”的虚拟表。这是 MySQL 8.0.4 引入的功能也是我处理报表统计时最爱用的函数。基本语法如下SELECT jt.* FROM orders, JSON_TABLE( ext_info, $.items[*] COLUMNS ( sku VARCHAR(32) PATH $.sku, qty INT PATH $.qty, price DECIMAL(10,2) PATH $.price ) ) AS jt;这会把每条订单里items数组的每个元素展开成一行COLUMNS里定义了输出的字段和提取路径。注意FROM orders, JSON_TABLE(...)这种逗号连接本质是隐式内连接只返回items非空的订单如果希望数组为空的订单也保留用LEFT JOINSELECT o.order_no, jt.sku, jt.qty FROM orders o LEFT JOIN JSON_TABLE( o.ext_info, $.items[*] COLUMNS ( sku VARCHAR(32) PATH $.sku, qty INT PATH $.qty ) ) AS jt ON TRUE;JSON_TABLE还支持嵌套路径比如订单里有items数组每个 item 里又有specs数组可以一次展开两层SELECT o.order_no, jt.sku, spec.spec_name FROM orders o JOIN JSON_TABLE( o.ext_info, $.items[*] COLUMNS ( sku VARCHAR(32) PATH $.sku, NESTED PATH $.specs[*] COLUMNS ( spec_name VARCHAR(50) PATH $.name ) ) ) AS jt;这个功能让我在写 ETL、统计报表时几乎可以放弃应用层解析复杂的 JSON 结构可以平坦化然后直接复用 GROUP BY、ORDER BY、JOIN 等一套关系代数。唯一的代价是行数会成倍膨胀后面排查部分再细说。3. 实操过程三个真实业务场景的完整 SQL3.1 场景一订单扩展属性提取先造一个贴近现实的表结构和数据。订单主表字段固定扩展信息全部收敛到ext_infoJSON 列里CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, ext_info JSON NOT NULL ); INSERT INTO orders (order_no, ext_info) VALUES (SO20250101001, JSON_OBJECT( channel, app, device, ios, tags, JSON_ARRAY(vip, new_user), address, JSON_OBJECT(city, 上海市, district, 浦东新区), items, JSON_ARRAY( JSON_OBJECT(sku, SKU-1001, qty, 2, price, 199.00), JSON_OBJECT(sku, SKU-1002, qty, 1, price, 59.90) ) )), (SO20250101002, JSON_OBJECT( channel, h5, device, android, tags, JSON_ARRAY(normal), address, JSON_OBJECT(city, 杭州市, district, 西湖区), items, JSON_ARRAY( JSON_OBJECT(sku, SKU-1003, qty, 3, price, 29.90) ) ));注意我在 INSERT 里用了JSON_OBJECT和JSON_ARRAY这两个构造函数它们可以把关系型数据转成 JSON 值和直接手写 JSON 字符串的效果一致。日常查询里用得最多的就是这种“多字段提取 过滤”组合SELECT order_no, ext_info - $.channel AS channel, ext_info - $.device AS device, ext_info - $.address.city AS city, JSON_LENGTH(ext_info - $.items) AS item_cnt FROM orders WHERE ext_info - $.channel app;结果一眼能看出 app 渠道的订单、设备来源、所在城市、商品数量。这里JSON_LENGTH配合-保留了数组类型返回的是数组元素个数这个函数在统计嵌套数量时特别好用。类似的还有JSON_KEYS返回对象的所有 keyJSON_TYPE返回 JSON 值的类型。如果要统计“所有带有 vip 标签的订单有多少”可以这样SELECT COUNT(*) AS vip_order_cnt FROM orders WHERE JSON_CONTAINS(ext_info, vip, $.tags);JSON_CONTAINS会把$.tags对应的数组和vip比对语义直观也是我推荐判断数组包含关系的首选。3.2 场景二数组展开做行级统计很多运营报表需要的不是单条订单信息而是订单里每个商品的行级数据。以前这种需求只能用「应用层循环 多次 SQL」实现现在一条JSON_TABLE搞定SELECT o.order_no, item.sku, item.qty, item.price, ROUND(item.qty * item.price, 2) AS line_amount FROM orders o JOIN JSON_TABLE( o.ext_info, $.items[*] COLUMNS ( sku VARCHAR(32) PATH $.sku, qty INT PATH $.qty, price DECIMAL(10,2) PATH $.price ) ) AS item;执行之后每个 item 都变成了独立的一行SKU-1001、SKU-1002会分别出现在两行里。基于这张“展开后的虚拟表”你想怎么聚合都行SELECT item.sku, SUM(item.qty) AS total_qty, SUM(item.qty * item.price) AS total_amount FROM orders o JOIN JSON_TABLE( o.ext_info, $.items[*] COLUMNS ( sku VARCHAR(32) PATH $.sku, qty INT PATH $.qty, price DECIMAL(10,2) PATH $.price ) ) AS item GROUP BY item.sku ORDER BY total_amount DESC;这种写法把“数组内嵌 明细统计”从业务代码完全搬到了 SQL 里。我实际跑过百万级订单表的场景整条 SQL 在服务器内存充足的情况下几秒出结果比原来的 Python/Java 轮询方案快了一个量级。如果你要做按月、按渠道维度拆分只需在 GROUP BY 里追加o.created_at月份表达式或者o.ext_info - $.channel组合自由度极高。3.3 场景三动态 key 与未知 JSON 结构大多数业务 JSON 结构是固定的但偶尔会遇到一段“key 不固定”的数据比如第三方接口用动态字段传参。这种情况下直接写死路径不可行需要先枚举 key再动态提取。先用JSON_KEYS看有哪些 keySELECT order_no, JSON_KEYS(ext_info) AS keys FROM orders;返回结果是一个 JSON 数组比如[address, channel, device, items, tags]。如果你想把整条记录拆成“key-value 两列”的宽表转长表可以用JSON_TABLE处理JSON_KEYS的输出SELECT o.order_no, k.k, o.ext_info - CONCAT($., k.k) AS val FROM orders o JOIN JSON_TABLE( JSON_KEYS(o.ext_info), $[*] COLUMNS (k VARCHAR(64) PATH $) ) AS k;这里JSON_KEYS(o.ext_info)得到 key 数组JSON_TABLE把它展开成多行然后用CONCAT($., k.k)动态拼接路径再提取值。虽然性能和可读性不如静态路径但在处理“格式不可控”的第三方数据时非常实用。不过我要提醒一句动态 key 方案应该只用于临场排查和小数据量任务。如果业务长期需要按动态 key 筛选第一选择永远是推动上游统一结构实在统一不了就用应用层配合 JSON 解析库处理别在 SQL 里硬扛。3.4 索引优化生成列和多值索引JSON 函数能不能走索引是个绕不开的问题。先给结论普通 B 树索引无法直接建立在 JSON 列上也不能建立在 JSON 函数表达式上。所以WHERE ext_info - $.channel app这种写法在数据量大时必然是全表扫描这是 JSON 方案最大的性能短板。解决思路是引入生成列Generated Column。把 JSON 里需要频繁查询的字段提取成独立列再在生成列上建索引ALTER TABLE orders ADD COLUMN channel VARCHAR(20) GENERATED ALWAYS AS (ext_info - $.channel) STORED; CREATE INDEX idx_channel ON orders(channel);加了这列之后查询改成SELECT order_no FROM orders WHERE channel app;EXPLAIN里就能看到走了idx_channel索引性能从全表扫描变成索引查找量级差距非常明显。生成列可以是VIRTUAL或STOREDVIRTUAL不占实际存储空间InnoDB 也支持在上面建二级索引STORED会把值物化回表时省去计算。我个人的习惯是只要这个字段会频繁参与WHERE、JOIN、ORDER BY用STORED最省心。MySQL 8.0.17 之后还支持了多值索引专门用于 JSON 数组。比如想给ext_info - $.tags这个数组建索引可以这样CREATE INDEX idx_tags ON orders ( (CAST(ext_info - $.tags AS CHAR(20) ARRAY)) );多值索引建立后下面这些查询有机会走索引SELECT order_no FROM orders WHERE JSON_CONTAINS(ext_info, vip, $.tags); SELECT order_no FROM orders WHERE vip MEMBER OF (ext_info - $.tags);不过多值索引的使用条件比较苛刻JSON_CONTAINS的搜索值最好是非常量优化器才更容易匹配上数组元素类型要和索引定义一致否则会失效。实测中我见过很多“建了索引但 EXPLAIN 不走”的情况所以不要把它当成银弹关键查询写完后一定要EXPLAIN验证。4. 常见问题与排查技巧实录4.1 路径语法和转义问题JSON 函数报错里出现频率最高的就是路径语法错误。常见原因有几个数组下标写错比如$.[0]多了个点正确写法是$[0]key 名带点号或空格没有加双引号比如$.user.name实际想取一个叫user.name的 key必须写成$.user.name路径里出现了反引号或者单引号混用比如在 SQL 字符串里写了双引号包裹路径这在默认 SQL 模式下也可以但建议统一用单引号包裹整个路径字符串使用**通配符时匹配范围过大导致结果集不符合预期。**可以匹配任意多层路径比如JSON_EXTRACT(doc, $**.name)会返回所有层级的name值在复杂文档里往往不是你想要的结果。排查路径问题我一般直接拉一条数据出来用JSON_PRETTY格式化后肉眼观察结构再逐步缩短路径测试SELECT JSON_PRETTY(ext_info) FROM orders WHERE order_no SO20250101001;肉眼看清层级之后再一级一级写路径比一遍遍改 SQL 试错快得多。4.2 NULL 和类型隐式转换JSON 里有两个“空”非常容易混淆JSON 的 null 值和 SQL 的 NULL。举个例子SELECT JSON_EXTRACT({a: null}, $.a) IS NULL AS extract_is_null, JSON_UNQUOTE(JSON_EXTRACT({a: null}, $.a)) IS NULL AS unquoted_is_null;第一行返回 0因为JSON_EXTRACT返回的是 JSON 文档里的 null 值它是一个 JSON 字面量第二行返回 1因为JSON_UNQUOTE会把它转成 SQL NULL。如果你的代码里出现“查到数据却判断为空”的诡异问题多半就是这两种 null 混用导致的。判断字段是否存在正确做法是判断JSON_EXTRACT的结果是否为 SQL NULL或者路径不存在时返回 NULL而 JSON null 值本身就是“存在但为空”。另一个坑是类型隐式转换。-返回的是字符串比如199.00会被提取成字符串199.00和数字比较时 MySQL 会尝试隐式转换大部分情况下没问题但遇到小数精度、前导零、科学计数法时就容易翻车。稳妥的做法是显式CASTSELECT CAST(ext_info - $.price AS DECIMAL(10,2)) AS price, CAST(ext_info - $.qty AS UNSIGNED) AS qty FROM orders;另外JSON 里的日期时间一律是字符串MySQL 不会自动识别成 DATETIME 类型。你要做日期范围过滤必须自己CAST(... AS DATETIME)否则比较结果是字典序而不是日期序。4.3 性能陷阱全表扫描和行数膨胀性能问题集中在三个方面。第一函数调用导致索引失效。WHERE JSON_EXTRACT(ext_info, $.channel) app这种写法写得再漂亮优化器也没法用索引唯一出路就是前面说的生成列。这条规矩适用于所有 JSON 函数包括JSON_CONTAINS、JSON_SEARCH——它们天然扫全表。第二JSON_TABLE造成行数膨胀。一条订单有 100 个 item展开后就变 100 行嵌套展开还可能 100 × 50 5000 行。SQL 写起来很爽但内存和临时表压力瞬间上来了。建议在开发环境先用小数据量验证再对生产大表结合LIMIT和分区条件控制范围。第三JSON_SEARCH的全文查找代价高。它需要遍历每个 JSON 值复杂度近似 O(N×M)只适合小表或者离线任务。生产环境按值查 JSON 内部位置更靠谱的还是提前用生成列把目标值物化出来。调优时记得用EXPLAIN看执行计划。MySQL 8.0 的EXPLAIN会显示是否使用了索引、扫描行数甚至 JSON 格式的完整执行计划。如果看到type: ALL且rows巨大说明你的 JSON 查询没吃到索引红利该考虑加生成列了。4.4 调试技巧看不懂 JSON 就用这些函数最后分享几个排查 JSON 结构时的实用函数组合基本能解决 90% 的“不知道 JSON 里到底存了什么”问题。先用JSON_VALID确认数据合法性再看类型和结构SELECT JSON_VALID(ext_info) AS valid, JSON_TYPE(ext_info) AS json_type, JSON_LENGTH(ext_info) AS member_cnt, JSON_KEYS(ext_info) AS keys FROM orders;JSON_TYPE会告诉你整体是 OBJECT、ARRAY 还是字符串、数字JSON_LENGTH会告诉你对象有几个成员或数组有几个元素JSON_KEYS会列出对象的 key。这几列拼在一起大概就能拼出结构全貌。如果还不够直接用JSON_PRETTY格式化输出配合临时表或客户端工具看完整内容。写复杂JSON_TABLE之前我强烈建议先用字面量 JSON 做一次“干跑”确认路径和列定义都没问题再套到真实表上SELECT * FROM JSON_TABLE( [{sku:SKU-A,qty:2},{sku:SKU-B,qty:3}], $[*] COLUMNS ( sku VARCHAR(20) PATH $.sku, qty INT PATH $.qty ) ) AS jt;这种方式能快速定位是数据问题还是 SQL 写错避免在大表上反复试探。我个人用了几年 JSON 函数下来最大的体会是MySQL 里处理 JSON 最舒服的方式是“少量关键字段用生成列加索引展示类字段直接用-统计汇总交给JSON_TABLE”。不要试图把所有 JSON 解析都塞进 SQL也别一看到 JSON 就整段拉到应用层。还有个小技巧想分享如果你还在 MySQL 5.7 上没有JSON_TABLE可用临时可以用UNION ALL配合JSON_EXTRACT、JSON_LENGTH模拟数组展开虽然写起来啰嗦但胜在能应急——等升级到 8.0 之后再替换成标准写法就行。