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_SEARCH，8.0 又补上了 JSON_TABLE、JSON_VALUE 和多值索引。这套函数解决的就是上面那个问题：直接在 SQL 里从 JSON 字符串提取字段、过滤、聚合，把“数据在哪、计算就在哪”真正落到执行层面。

这篇文章我打算从实际业务出发，先从最基础的函数讲起，再到 JSON_TABLE 展开数组这种高级用法，最后把路径语法、索引优化、常见坑位全部过一遍。适合给那些数据库里已经躺着 JSON 列、正头疼怎么高效使用它的开发同学，也适合准备把部分 JSON 解析逻辑从应用层下沉到数据库的团队参考。全文不涉及安装配置，专门聊怎么把 JSON 用明白。

1. 整体思路：为什么要在数据库里直接解析 JSON

1.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 上线才发现语法不支持。

另外有一点必须提前说清楚：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_TABLE

2.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_CONTAINS：

SELECT order_no FROM orders WHERE JSON_CONTAINS(ext_info, '"vip"', '$.tags');

JSON_CONTAINS(target, candidate, path)的candidate必须是一个合法 JSON 值，所以字符串要写成带双引号的'"vip"'，也可以配合JSON_QUOTE('vip')动态拼接。判断的是整个数组是否包含该元素，语义很清晰。

还有一种场景是你不知道目标值在 JSON 的哪个位置，只想“全文搜索”。这时用JSON_SEARCH：

SELECT 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 JOIN：

SELECT 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. 实操过程：三个真实业务场景的完整 SQL

3.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返回对象的所有 key，JSON_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看有哪些 key：

SELECT 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或STORED：VIRTUAL不占实际存储空间，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 会尝试隐式转换，大部分情况下没问题，但遇到小数精度、前导零、科学计数法时就容易翻车。稳妥的做法是显式CAST：

SELECT 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 之后再替换成标准写法就行。