JSON_EXTRACT 大概是 MySQL 所有 JSON 函数里我用得最多的一个。凡是遇到 JSON 字段要取值、筛选、统计第一反应基本就是它。前两周帮同事排查一条慢 SQL问题就是一张表里存了 JSON 格式的业务配置where 条件里用了LIKE %xxx%去模糊匹配 JSON 文本数据量一到百万级直接把查询拖到好几秒。我说这写法从根上就错了应该用JSON_EXTRACT把字段拆出来再做条件过滤。同事反问了我一句JSON_EXTRACT返回的结果怎么有时候带引号、有时候不带这个问题其实特别典型是刚接触这个函数的人最容易踩的坑。这篇文章我把JSON_EXTRACT的用法、路径表达式规则、典型实战场景和常见问题完整梳理一遍。适合刚入门 MySQL JSON 功能的开发者也适合已经用了 JSON 字段但总在查询上出问题的老同学。看的过程中建议把代码贴到本地 MySQL 里实际跑一遍尤其是路径表达式那部分多试几次印象就深刻了。1. 先从一张查询慢的 SQL 说起JSON 字段到底该怎么取先还原一下那个让我同事头疼的场景。有一张用户扩展信息表里面有个extra字段类型是 JSON存的是用户注册渠道、偏好设置这类不固定的属性。结构类似{ channel: wechat, preference: { theme: dark }, tags: [vip, early_adopter] }同事想统计注册渠道是wechat的用户数第一版 SQL 是这么写的SELECT COUNT(*) FROM t_user_extra WHERE extra LIKE %wechat%;这种写法的问题不只是慢还错。extra LIKE %wechat%是在整个 JSON 文本里做子串匹配一旦某个用户的备注、标签、昵称里也出现wechat这个单词就会被误算进去。更麻烦的是JSON 里的字符串值本身带双引号文本匹配的模式很难写对数据量大一点就是全表扫描。正确的做法是把 JSON 当作结构化数据来查询直接提取channel字段的值再做条件过滤SELECT COUNT(*) FROM t_user_extra WHERE JSON_EXTRACT(extra, $.channel) wechat;等一下这里有个细节JSON_EXTRACT(extra, $.channel)返回的其实是一段 JSON 文档如果channel是字符串结果会带着双引号也就是返回wechat而不是wechat。所以上面这条 SQL 严格来说是有问题的正确的等值比较应该写成WHERE JSON_UNQUOTE(JSON_EXTRACT(extra, $.channel)) wechat;或者直接用 MySQL 提供的-运算符WHERE extra-$.channel wechat;这两个写法效果一样。-是JSON_UNQUOTE(JSON_EXTRACT(...))的简写生产中我一般直接用-写着省事也不容易漏掉去引号那一步。这个坑后面会有专门一节来讲先记住一个结论用JSON_EXTRACT取字符串值一定要配套JSON_UNQUOTE或者-。为什么会有人写出LIKE %wechat%这种 SQL因为 JSON 字段在 MySQL 5.7 之前根本没有对应的查询函数大家只能用 TEXT 类型硬存提取字段全靠程序代码或者正则。MySQL 5.7 引入原生 JSON 类型之后官方才补齐了JSON_EXTRACT这一整套 JSON 函数。所以从本质上说JSON_EXTRACT解决的问题非常明确以路径表达式的方式从 JSON 文档里精确取出某个或某几个值让 JSON 字段也能像普通字段一样参与筛选、统计、排序。2. JSON_EXTRACT 的语法与路径表达式这层皮最薄2.1 函数签名和最简单的用法先看函数签名JSON_EXTRACT(json_doc, path[, path] ...)第一个参数是 JSON 文档可以是 JSON 类型的列也可以是字符串字面量第二个参数是路径表达式至少要给一个。函数的作用就是沿着路径把对应的值取出来返回值依然是 JSON 格式。最简单的情况直接对一个 JSON 字符串取字段SELECT JSON_EXTRACT({name: 张三, age: 30}, $.name);执行结果张三注意输出的张三是带双引号的。如果换成数字SELECT JSON_EXTRACT({name: 张三, age: 30}, $.age);结果就是30不带引号。因为 JSON 里的数字本身不带引号数字在 JSON 里是一种原生类型提取出来之后也不会莫名其妙多一对引号。所以“返回结果到底带不带引号”取决于被提取的值在 JSON 里是什么类型而不是取决于函数本身。这个逻辑想清楚后面很多困惑都能解开。2.2 路径表达式的完整规则路径表达式是JSON_EXTRACT的核心也是出错率最高的地方。我整理了一份常用路径写法对照表建议直接收藏路径写法含义示例$整个 JSON 文档JSON_EXTRACT(doc, $)$.key取对象的某个字段$.channel取channel字段$.key1.key2嵌套取值$.preference.theme取嵌套主题$[0]取数组第 1 个元素$.tags[0]取第一个标签$[1].field数组元素再取字段$.items[1].name$[*]所有数组元素$.tags[*]返回所有标签组成的数组$.*对象所有字段的值$.*返回所有值组成的数组$**.key递归查找所有层级的key$**.name匹配任意层级的name字段$.key-name字段名含特殊字符时加双引号$.first-name递归查找$**.key这个写法很有用但很多人不知道。举个例子SELECT JSON_EXTRACT( {a: {name: 张三}, b: {c: {name: 李四}}}, $**.name );结果会把a和b.c里所有name字段的值都取出来拼成一个 JSON 数组[张三, 李四]不过实际业务里我很少用递归查找因为结果是一段 JSON 数组后面还要再做JSON_CONTAINS或拆解操作绕了一圈反而不如直接按已知路径取。知道有这个能力就行别一上来就无脑$**。路径表达式里大小写敏感吗敏感。$.Name和$.name是两个不同的路径如果 JSON 里字段叫name用$.Name去取结果就是 SQLNULL。这个细节在排查问题的时候特别容易忽略。2.3 一次性提取多个路径冷门但好用JSON_EXTRACT的签名里path是变长的意味着你可以在一次调用里提取多个值。比如SELECT JSON_EXTRACT( {channel: wechat, theme: dark}, $.channel, $.theme );返回结果是把多个值合并成一个 JSON 数组[wechat, dark]这个特性的应用场景是当你需要同时取出多个字段做拼接或对比时可以少写几个JSON_EXTRACT。不过要注意返回的是数组不是多个独立列所以实际用起来并不像你想的那么方便。我对这个特性的评价是“知道有这么回事偶尔能救命日常别硬用”真有拆多列需求时直接用后面的JSON_TABLE更合理。3. 三个典型场景订单、配置、用户属性光讲语法没意思JSON 字段在实际项目里翻来覆去就是那几种用法。我挑三个最常见也最典型的场景把完整 SQL 写出来照着自己表结构改一下就能用。3.1 场景一订单表里存收货信息很多订单系统的订单表都有一列存下单时的快照信息比如收货人、地址、备注因为用户后续可能修改地址订单里的地址必须保持不变。用 JSON 存这些字段非常合适。建表示意CREATE TABLE t_order ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, order_info JSON );插入一条样例数据INSERT INTO t_order (order_no, order_info) VALUES ( NO20250001, JSON_OBJECT( receiver, JSON_OBJECT( name, 张三, phone, 13800000000, address, 某市某区某路1号 ), items, JSON_ARRAY( JSON_OBJECT(name, 手机, price, 6999), JSON_OBJECT(name, 保护壳, price, 99) ) ) );现在要查询订单里的收货人姓名和电话SELECT order_no, order_info-$.receiver.name AS receiver_name, order_info-$.receiver.phone AS receiver_phone FROM t_order WHERE order_no NO20250001;这里用-直接去引号拿到的是普通字符串展示、导出、拼接都方便。如果要查订单里所有商品的名称可以借助JSON_TABLE或者先取出数组再用JSON_EXTRACT配合通配符不过直接对路径$.items[*].name取的结果是一个 JSON 数组展示起来不太直观这个放到第五节再展开。3.2 场景二业务配置按 JSON 存放配置项用 JSON 存是另一个高频用法。比如一个活动系统需要配置各种规则字段可能随时增加传统做法是加列但列是静态的加一次改一次表结构特别痛苦。JSON 字段天然适合这种“schema 不固定”的场景。假设一张配置表CREATE TABLE t_config ( config_key VARCHAR(64) PRIMARY KEY, config_value JSON );存一条营销活动配置INSERT INTO t_config (config_key, config_value) VALUES ( activity_spring_2025, { name: 春季促销, discount: 0.8, target_users: [vip, new_user], time_range: { start: 2025-03-01 00:00:00, end: 2025-03-31 23:59:59 } } );程序里要读这个配置直接查出来解析即可。但有时候 DBA 要手动查某个配置项是否合理比如想确认所有活动的折扣率分布SELECT config_key, config_value-$.name AS activity_name, CAST(config_value-$.discount AS DECIMAL(3, 2)) AS discount FROM t_config WHERE config_key activity_spring_2025;这里有个细节必须提醒-取出来的0.8实际上是字符串虽然是数字形态但类型是VARCHAR。如果直接拿去做小数比较或排序MySQL 会触发隐式类型转换结果经常让你怀疑人生。所以凡是涉及数值计算和比较一律显式CAST成DECIMAL或数值类型。这条经验在前面统计渠道的例子用不到但做配置聚合、折扣对比时一定会碰上。3.3 场景三扩展属性做统计与筛选用户表、商品表加一列 JSON 作为扩展属性是很多团队避免频繁加列的常规手段。但这类字段用不好就是慢查询重灾区。比如要筛选注册渠道为wechat且偏好主题为dark的用户SELECT user_id, extra-$.channel AS channel FROM t_user_extra WHERE extra-$.channel wechat AND extra-$.preference.theme dark;如果extra字段上的 JSON 结构是固定的建议直接加生成列虚拟列并建索引查询性能会有质的提升。这是 JSON 字段的核心优化手段下一节的索引专题会细讲。另外提醒一点筛选条件如果只出现一个 JSON 字段很多人会偷懒直接对整列做JSON_CONTAINS但JSON_CONTAINS(extra, wechat, $.channel)这种写法在可读性和性能上都不如拆成生成列再用普通 where能少用就少用。统计扩展属性里的枚举值分布也是常规操作。比如统计各注册渠道的用户数SELECT extra-$.channel AS channel, COUNT(*) AS cnt FROM t_user_extra GROUP BY extra-$.channel ORDER BY cnt DESC;这条 SQL 在生产环境跑了快一年逻辑没有任何问题。如果数据量特别大GROUP BY 的字段会走生成列索引响应时间能控制在毫秒级。4. 踩坑和性能优化JSON_EXTRACT 的五个“没想到”4.1 坑一返回结果带引号等值比较直接失败前面反复提到带引号的问题这里用一个完整的例子展示为什么直接比较会失败SELECT JSON_EXTRACT({name: 张三}, $.name) 张三 AS result_direct, JSON_UNQUOTE(JSON_EXTRACT({name: 张三}, $.name)) 张三 AS result_unquote;执行结果result_direct: 0 result_unquote: 1第一个比较返回 0因为左边是张三带双引号的 JSON 字符串右边是张三普通字符串两边长度和内容都不同。这种情况在初期特别容易踩排查了半天最后发现是引号问题。记住这个原则用JSON_EXTRACT提取字符串必须JSON_UNQUOTE提取数字也建议CAST成数值类型千万别以为函数返回的就是可以直接用的值。4.2 坑二JSON 字段没法直接建索引WHERE 条件里全表扫描这是被问得最多的问题“我给 JSON 字段加了索引为什么没用”答案是 MySQL 不支持直接给 JSON 列建索引。看一个报错ALTER TABLE t_user_extra ADD INDEX idx_channel (extra);执行后 MySQL 会直接报错。正确姿势是借助生成列GENERATED COLUMN把 JSON 里的某个字段“摊”成一个普通列再对这个列建索引。步骤很简单ALTER TABLE t_user_extra ADD COLUMN channel VARCHAR(32) GENERATED ALWAYS AS (extra-$.channel) VIRTUAL, ADD INDEX idx_channel (channel);生成列是虚拟的还是物理存储的都行这里我用的VIRTUAL它的值不占额外磁盘空间只在查询时计算索引依然可以生效。加完索引后原来的 where 写法直接改写SELECT user_id FROM t_user_extra WHERE channel wechat;性能差异非常明显。我在一个两百万行的表上做过对比全表扫描的查询是秒级用生成列索引后毫秒级返回。这是 JSON 字段性能优化的第一个必做动作。4.3 坑三NULL 语义的三种情况别全混在一起JSON_EXTRACT返回 SQLNULL有三种常见情况路径不存在、被提取的值在 JSON 里就是null、JSON 文档本身是无效的。前两种容易被混在一起。看这个例子SELECT JSON_EXTRACT({a: null}, $.a) AS value_is_null, JSON_EXTRACT({a: 1}, $.b) AS path_not_exist;第一个返回 JSON 的null第二个返回 SQL 的NULL。在结果集里显示都是NULL但含义完全不同。如果你在 where 里写JSON_EXTRACT(doc, $.a) IS NULL它会把“值是 JSON null”和“路径不存在”两种情况都筛选出来逻辑上就是错的。要区分它们可以用JSON_EXTRACT加JSON_TYPE判断SELECT JSON_TYPE(JSON_EXTRACT({a: null}, $.a)) AS type_of_null, -- NULL JSON_TYPE(JSON_EXTRACT({a: 1}, $.a)) AS type_of_number; -- INTEGERJSON_TYPE在值为 JSON 的null时返回NULL在路径不存在时也返回NULL单靠它也不能区分所有情况。所以在实际业务里尽量避免把“字段允许存 null”和“字段可能不存在”设计成两种需要区分的状态否则查询逻辑会非常绕。我的建议是JSON 结构里的字段要么始终存在要么就统一按“不存在”处理别把null和缺失混进业务判断。4.4 坑四隐式转换在 JSON 数值比较里非常隐蔽前面说过-出来的数字是字符串这里举个具体翻车例子有一张表存商品价格快照SELECT product_id FROM t_product_snapshot WHERE CAST(price_snapshot-$.price AS DECIMAL(10, 2)) 1000;如果我偷懒写成WHERE price_snapshot-$.price 1000;MySQL 会把两边都转成数值吗不一定。用VARCHAR和数字比较时MySQL 通常会把字符串转成数字但如果字符串里带了非数字字符行为就飘了。等价的规则本身有歧义对于price这种明确是小数的字段还是老老实实写CAST不要赌隐式转换。再举一个排序的例子。对 JSON 里的价格排序直接ORDER BY price_snapshot-$.price是按字典序排的结果就是10排在9前面完全不符合预期。正确的写法是ORDER BY CAST(price_snapshot-$.price AS DECIMAL(10, 2));这类错误不会报错只会在结果里悄悄给错误排序十分阴险。4.5 坑五通配符和多路径返回的是数组不是对象前面说过$**.name和多路径提取都会返回 JSON 数组。这个行为在需要拼接字符串的时候很坑。比如我想把订单里所有商品名拼成一句话SELECT JSON_EXTRACT( {items: [{name: 手机}, {name: 保护壳}]}, $.items[*].name );结果是[手机, 保护壳]如果程序端拿到的返回值是一段 JSON 字符串还要再 parse 一次。想要纯文本的逗号拼接可以用JSON_ARRAYAGG配合JSON_TABLE展开来做也可以直接在应用层处理。总之通配符取出来的结果跟你想象中的“一行文本”不一样要提前有预期。5. 进阶方向-、-、JSON_VALUE 和 JSON_TABLE到这里基础用法已经讲透了。如果你的 MySQL 版本在 8.0 以上还有几个东西值得学会能让 JSON 查询更顺手。5.1 两个语法糖运算符先看 MySQL 5.7 就有的-和-运算符写法等价于col-$.pathJSON_EXTRACT(col, $.path)col-$.pathJSON_UNQUOTE(JSON_EXTRACT(col, $.path))用运算符省去函数套函数的写法可读性提升非常明显。比如前面订单的例子用-写SELECT order_info-$.receiver.name FROM t_order;返回带引号用-则不带。我个人的习惯是展示、筛选、统计一律用-只有当你想保留 JSON 类型比如传给其他 JSON 函数时才用-或JSON_EXTRACT。这个习惯能少踩一半引号相关的坑。5.2 MySQL 8.0 的 JSON_VALUEMySQL 8.0 新增了JSON_VALUE函数可以从 JSON 文档里提取标量值更贴合“取普通列值”的直觉。语法多了一个RETURNING子句可以指定返回类型SELECT JSON_VALUE(order_info, $.receiver.phone RETURNING CHAR(20)) AS phone FROM t_order;相比-JSON_VALUE的优势是可以直接做类型转换在提取时就把字符串转成需要的类型省得后面再CAST。我一般在 MySQL 8.0 项目里会把JSON_VALUE加入备选特别是字段类型明确的场景。JSON_VALUE还有一个行为上的差异如果路径取出来的是一个 JSON 对象或数组JSON_VALUE会返回错误而不是像JSON_EXTRACT那样返回一段 JSON。所以在用JSON_VALUE之前必须确认目标字段是标量不能是嵌套结构。5.3 复杂展开交给 JSON_TABLE如果 JSON 里存了一个数组你要把数组展开成多行多列JSON_EXTRACT就力不从心了。这时候需要请出JSON_TABLE。回到订单表那个例子把商品明细展开成行SELECT t.order_no, item.item_name, item.price FROM t_order t, JSON_TABLE( t.order_info, $.items[*] COLUMNS ( item_name VARCHAR(50) PATH $.name, price DECIMAL(10, 2) PATH $.price ) ) AS item;这段 SQL 的逻辑是对t_order的每一行取order_info里的items数组数组里的每个元素都映射成一行行里包含item_name和price两列。相当于把 JSON 数组“拍平”成关系表再跟原表做隐式连接。JSON_TABLE是目前 MySQL 处理复杂 JSON 结构的最强工具也是我处理“JSON 数组拆多行”的首选。它的缺点是写法略显繁琐刚开始用容易在COLUMNS语法上报错多写几次习惯就好。整体来看我的建议是分层次使用简单取一个标量用JSON_EXTRACT或-、-选哪个取决于是否需要去引号需要类型转换且是 MySQL 8.0优先JSON_VALUE数组展开成多行用JSON_TABLE判断 JSON 里是否存在某个键或值用JSON_CONTAINS不要用LIKE最后再说一个我自己的使用习惯JSON 字段适合存“不参与复杂联表查询的扩展信息”如果你的核心业务字段将来一定要反复查询、排序、聚合那就老老实实拆成普通列别把所有东西都塞进 JSON。工具能解决查询问题但改变不了一个事实——关系模型擅长的东西JSON 替代不了。遇到 JSON 字段访问频率特别高的场景我会在表设计阶段就规划好生成列和索引让 JSON 成为一种“存储补充”而不是查询主路径。我实际用下来的体会是JSON_EXTRACT这个函数本身不难难的是围绕它的一整套使用习惯——路径写对、引号处理好、索引建到点子上、类型转换不偷懒。这套习惯建立之后JSON 字段就不再是查询卡顿的根源而是一个灵活又可靠的扩展手段。