干后端这几年几乎每个月都能在技术群里看到同一类问题我表里有个字段是 JSON 字符串想把里面的某个值提取出来怎么写 SQL这个问题看着基础真上手却有一堆细节——路径写错返回 NULL、-和-类型对不上、数组展开不会做。今天就把 MySQL 里从 JSON 字符串提取数据的这套东西完整捋一遍。文章会覆盖 5.7/8.0 两个版本里最常用的 JSON 函数配合接口日志、商品 SKU、订单信息这类真实场景适合正在做数据清洗、接口分析、扩展属性查询的开发者。1. 什么场景会让你在 MySQL 里直面 JSON 字段1.1 三种最常见的 JSON 落库场景JSON 字段出现在业务表里绝大多数是这三种情况。第一种是扩展属性。商品要挂规格参数、订单要带附加信息、用户要存偏好设置这些字段的特点是“不稳定”——今天多一个颜色明天多一个尺寸后天可能加一个赠品标记。如果每加一个属性就 ALTER TABLE 加列表结构会越改越碎。用 JSON 存一份灵活的键值对是成本最低的方案。第二种是第三方 API 报文。支付回调、开放平台 webhook、短剧/影视资源接口的返回结果拿到的原始数据就是 JSON。业务方往往要求“先落库再说”于是整包 JSON 直接塞进一个字段后续再按需解析。这种数据的特点是结构不可控、嵌套层数深、偶尔还会整包变格式。第三种是日志和埋点。行为日志、访问记录、接口调用明细日志服务通常以 JSON 行格式输出。查问题时想按某个字段过滤、统计最直接的办法就是把日志灌进 MySQL 的 JSON 列再用 SQL 提取目标字段。这三种场景的共同点是数据已经以 JSON 形式存在我们不能要求上游改格式只能在取数时适配。1.2 直接上解析引擎还是用 MySQL 函数很多人的第一反应是“JSON 就该用 MongoDB、Elasticsearch 解析”这话对但不全对。如果你的数据量在百万级以内JSON 嵌套不超过三四层过滤条件就那么几个固定字段用 MySQL 内置函数解析是完全可以的。它胜在链路短——不需要引入新的数据管道、不需要维护同步任务、一条 SQL 就能出结果很适合写报表、做运营取数、排查线上问题。反过来如果数据已经上亿、JSON 里藏的是超长数组、需要全文检索或者复杂的关联分析那就别死磕 MySQL。我自己见过不少团队把 JSON 函数当 ETL 引擎用结果一条查询跑几十秒还拖垮主库。这种量级的活交给 Spark、Flink 这类计算引擎更合适MySQL 只负责把数据规规矩矩吐出来就行。2. 提取数据前必须弄懂的 JSON 路径语法2.1 路径表达式从 $ 开始JSON 路径是提取数据的“地图”。MySQL 的路径语法很直观$代表整个 JSON 文档往后一级一级往下指。想取对象里的某个键用$.key想取数组里的某个元素用$[下标]想取数组里的每一个元素用$[*]。举个例子。下面这段 JSON{ user: { name: 张三, age: 28, tags: [vip, seller], address: { city: 上海, district: 浦东 } } }对应的路径分别是$.user.name取到张三$.user.age取到28$.user.tags[0]取到vip$.user.tags[*]匹配tags数组里的每一个元素$.user.address.city取到上海如果 JSON 的顶层本身就是一个数组比如[{id:1}, {id:2}]那路径要从$[0]开始写$[0].id取到第一个元素的id。这里有个容易忽略的细节$[*]是通配符匹配“所有数组元素”如果你不确定 key 在哪一层也可以用$.***做多层通配。不过在实际生产里我会尽量避免这种写法——它能用但语义不够明确排查问题的时候很费劲。2.2 三种取值写法JSON_EXTRACT、-、-MySQL 提取 JSON 值有三套等价写法很多人混着用但没有意识到它们返回值的细节差异。第一种是函数写法SELECT JSON_EXTRACT({name:张三}, $.name); -- 结果 张三 注意是带双引号的 JSON 字符串第二种是-运算符它是JSON_EXTRACT的简写SELECT {name:张三}-$.name; -- 结果 张三第三种是-运算符它是JSON_UNQUOTE(JSON_EXTRACT(...))的简写会把值变成纯字符串SELECT {name:张三}-$.name; -- 结果 张三 没有引号三者的差别在比较和计算时特别明显。-返回的是 JSON 类型客户端显示出来可能不带引号数字、布尔但它本质上不是普通 SQL 字符串-返回的永远是文本数字也会变成28这种字符。所以写 WHERE 条件时-和-的写法不同匹配结果也可能出乎意料。2.3 路径写错和类型不对的坑先说路径写错的坑。路径不存在时JSON 函数不会报错而是返回NULL。于是出现一种很迷惑的情况SELECT JSON_EXTRACT({name:张三}, $.nmae); -- 返回 NULL它不是说“这个字段的值是 NULL”而是说“这个路径根本不存在”。如果你拿这个结果去 JOIN 或者做条件判断整条数据的逻辑就歪了。后面第 7 节我会详细讲怎么排查这类问题。再说类型。JSON 里明明有值提取出来用不了的情况也常见。比如 JSON 里存的是1024这个数字用-提取出来是字符串1024你要拿它和数值 1000 比较MySQL 可能会做隐式转换也可能不走索引结果不可控。最稳妥的做法是提取时顺手 CAST 成目标类型。还有一个坑是-对字符串里的特殊字符。JSON 字符串里如果带了转义符比如a\nb-去引号之后换行符是真实换行还是\n两个字符取决于字段存储格式这个在比对数据时常踩。3. 核心实操从单条 JSON 字符串里提取字段3.1 取对象属性嵌套层级怎么写假设有一张用户表extra列存了用户的扩展信息{level: 3, vip_until: 2026-12-31, referrer: {uid: 10086, name: 老王}}把level提取出来的标准写法SELECT id, extra-$.level AS level_json, extra-$.level AS level_text, extra-$.referrer.uid AS referrer_uid FROM user;这里extra-$.referrer.uid是嵌套路径的写法MySQL 允许用点号一路往下点不用刻意写成$.referrer[uid]。但要注意如果 key 本身带有特殊字符比如user-name、商品规格就必须用引号包住 keyextra-$.user-name extra-$.商品规格路径里带引号包 key这是很多人栽过的坑后面专门说。3.2 取数组元素和遍历数组JSON 数组的提取和对象略有区别。假设items列存的是[{sku: A-1, price: 19.9}, {sku: B-2, price: 29.9}]取第一个 SKUSELECT items-$[0].sku FROM t; -- 结果 A-1取最后一个 SKUMySQL 支持last关键字SELECT items-$[last].sku FROM t; -- 结果 B-2取所有 SKU 的编号这个典型场景要在下一节的 JSON_TABLE 里解决。但如果只是单纯地需要“检查数组里有没有某个值”不需要展开成多行那么JSON_CONTAINS就够了这一节先不展开。3.3 提取时顺手完成类型转换提取 JSON 值最规范的习惯是在 SQL 里就把它转成目标 SQL 类型而不是靠客户端程序二次处理。统计类字段转成数值SELECT id, CAST(extra-$.level AS UNSIGNED) AS level_num, CAST(extra-$.referrer.uid AS BIGINT) AS referrer_id FROM user;时间类字段转成日期SELECT id, CAST(extra-$.vip_until AS DATE) AS vip_date FROM user;布尔值要小心。JSON 里存的是true/false小写-出来是true/false直接 CAST 成 UNSIGNED 会得到 0。建议在提取时就写条件判断CAST(extra-$.is_vip true AS UNSIGNED) AS is_vip_int这个写法纯靠字符串比对简洁也好懂。如果 JSON 里存的是1/0那直接CAST(... AS UNSIGNED)就行。4. 实战案例接口日志表 JSON 提取与统计分析4.1 建表与初始数据光讲函数不够我拿一个真实场景串一遍。假设有一张接口日志表api_logreq_info列存的是接口请求的原始 JSON{ uid: 10086, action: submit_order, os: Android, duration_ms: 1234, params: { sku: A-1, count: 2, coupon: true } }建表语句CREATE TABLE api_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, req_info JSON, created_at DATETIME );插几条样例数据方便验证后面的 SQL。4.2 按 action 分组统计请求量和平均耗时需求很简单统计每个 action 的请求次数和平均耗时。写法SELECT req_info-$.action AS action, COUNT(*) AS cnt, ROUND(AVG(CAST(req_info-$.duration_ms AS UNSIGNED)), 2) AS avg_ms FROM api_log GROUP BY req_info-$.action;注意 GROUP BY 里我直接用了-表达式而不是起别名引用——MySQL 的 GROUP BY 能用别名但这里我刻意保留表达式避免某些版本的兼容性问题。这里有个细节duration_ms在 JSON 里是数字-提取后是字符串不 CAST 直接 AVGMySQL 会做隐式转换多数情况结果对但类型不明确。我习惯在聚合前就 CAST 干净后面加条件也清爽。4.3 从嵌套 params 里提取指定字段再往深一层想统计使用了优惠券coupon为 true的订单占多少比例。SELECT req_info-$.params.coupon AS coupon_flag, COUNT(*) AS cnt FROM api_log GROUP BY req_info-$.params.coupon;嵌套路径$.params.coupon一次到位。但要注意coupon是布尔值-出来是true/false分组结果自然是这两串字符串业务方要读明白得先理解这个约定。更直观的写法是直接转换成 0/1SELECT CASE WHEN req_info-$.params.coupon true THEN 1 ELSE 0 END AS coupon_int, COUNT(*) AS cnt FROM api_log GROUP BY coupon_int;4.4 按 JSON 里的值过滤日志筛选出os Android且耗时超过 1000ms 的日志SELECT id, req_info-$.uid AS uid, req_info-$.action AS action, req_info-$.os AS os, req_info-$.duration_ms AS duration FROM api_log WHERE req_info-$.os Android AND CAST(req_info-$.duration_ms AS UNSIGNED) 1000;这里必须强调WHERE req_info-$.os Android这类条件在字段是普通 JSON 列、没建生成列索引的情况下是没办法走 B 树索引的只能全表扫。数据量小无所谓超过几百万行就得考虑第 7 节讲的索引方案。5. JSON_TABLE 高级玩法把数组拆成多行5.1 JSON_TABLE 到底解决了什么痛点前面讲的都是“一行 JSON 里取某个值”但实际需求更狠——一个 JSON 数组里有 10 个 SKU我要拆成 10 行明细每行一个 SKU。MySQL 5.7 时代这活挺痛苦要么写死 N 个 UNION要么搞存储过程循环。到了 MySQL 8.0JSON_TABLE函数把这事变成了标准操作。JSON_TABLE的核心思想是把 JSON 文档里的数组“投影”成一张虚拟表然后像查普通表一样 JOIN、过滤、聚合。它只能出现在FROM子句里需要和原表做关联。5.2 基础用法拆 SKU 数组商品表productCREATE TABLE product ( id BIGINT, name VARCHAR(100), sku_json JSON );sku_json长这样[ {spec: 256G 黑色, price: 6999, stock: 12}, {spec: 512G 黑色, price: 7999, stock: 5}, {spec: 256G 白色, price: 6999, stock: 0} ]把每个 SKU 展开成明细行SELECT p.id, p.name, s.spec, s.price, s.stock FROM product p JOIN JSON_TABLE( p.sku_json, $[*] COLUMNS ( spec VARCHAR(50) PATH $.spec, price DECIMAL(10,2) PATH $.price, stock INT PATH $.stock ) ) AS s ON TRUE;关键点拆开看$[*]表示遍历sku_json数组里的每一个元素。COLUMNS (...)定义虚拟表的每一列PATH指定这一列从 JSON 元素的哪个键取值。ON TRUE是因为 JSON_TABLE 本质上是一个表函数与原表之间没有天然关联条件写ON TRUE等价于笛卡尔积——但对于每一行原表JSON_TABLE 只会产出对应其 JSON 数组长度的行数。如果原表的sku_json是 NULL 或者空数组JOIN ON TRUE会导致该行直接被丢弃。想保留原表行用LEFT JOINSELECT p.id, p.name, s.spec FROM product p LEFT JOIN JSON_TABLE( p.sku_json, $[*] COLUMNS ( spec VARCHAR(50) PATH $.spec ) ) AS s ON TRUE;5.3 嵌套数组的递归展开NESTED PATH实战里的 JSON 往往不止一层。订单表order_info里存了多个包裹每个包裹里有多个商品{ order_id: A001, packages: [ { package_no: P1, items: [ {sku: A-1, qty: 2}, {sku: B-2, qty: 1} ] }, { package_no: P2, items: [ {sku: C-3, qty: 5} ] } ] }想展开成“包裹 商品”的明细行需要两层COLUMNS内层用NESTED PATHSELECT o.id, pk.package_no, item.sku, item.qty FROM order_info o JOIN JSON_TABLE( o.package_json, $.packages[*] COLUMNS ( package_no VARCHAR(20) PATH $.package_no, NESTED PATH $.items[*] COLUMNS ( sku VARCHAR(20) PATH $.sku, qty INT PATH $.qty ) ) ) AS pk ON TRUE;NESTED PATH的含义是“在每一个外层元素的基础上再对内层数组做一次遍历”效果类似嵌套循环。这个功能是 MySQL 8.0.4 加进来的8.0 之前想实现只能拆多次相当痛苦。5.4 给 5.7 用户的替代方案如果你还在 5.7没有JSON_TABLE又实在要拆数组可以用JSON_LENGTH 硬编码序号表的方式SELECT p.id, JSON_UNQUOTE(JSON_EXTRACT(p.sku_json, CONCAT($[, n.idx, ].spec))) AS spec FROM product p JOIN ( SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) n ON n.idx JSON_LENGTH(p.sku_json);前提是数组元素数量有个上限。如果你能预估 JSON 数组最多不会超过 4 个元素这个办法能顶一阵子但超过上限就丢数据一旦业务膨胀必须升级 8.0。我的建议很直接还在 5.7 又频繁要处理 JSON 的赶紧规划升级。6. 按 JSON 值做搜索过滤四个函数怎么选6.1 JSON_CONTAINS判断包含关系JSON_CONTAINS判断“目标 JSON 里是否包含指定的值”。它适合的场景是数组里有没有某个元素、对象里某个键是否等于某个值。判断技术标签数组里有没有mysqlSELECT id, tags_json FROM user_tags WHERE JSON_CONTAINS(tags_json, mysql);注意第二个参数必须传合法的 JSON 值——字符串要带双引号写成mysql数字写18布尔写true。写成mysql会被当成非法 JSON 报错这是最常踩的坑。6.2 JSON_SEARCH不知道在哪层按值找路径JSON_SEARCH是“按内容反查路径”的工具。比如你知道 JSON 里有上海这个值但不确定它在哪个 key 下可以SELECT JSON_SEARCH({city:上海,district:浦东}, one, 上海); -- 返回 $.city第二个参数one表示只返回第一个匹配路径all返回所有匹配路径。它还支持%和_通配符可以实现模糊搜索SELECT JSON_SEARCH({city:上海,district:浦东}, all, %海%); -- 返回 [$.city, $.district]这个函数在调试结构不明确的 JSON 时特别有用但说实话生产业务里用得不多——因为路径不确定意味着数据模型不稳定这种表往往需要重构。6.3 8.0 新宠MEMBER OF 与 JSON_OVERLAPSMySQL 8.0.17 开始对 JSON 数组的过滤多了两个更顺手的运算符。MEMBER OF判断单值是否属于数组SELECT vip MEMBER OF([vip,seller]); -- 返回 1实际查询里这样用SELECT id, tags_json FROM user_tags WHERE vip MEMBER OF(tags_json);JSON_OVERLAPS判断两个数组是否有交集适合“用户标签数组是否命中任意一个目标标签”这类需求SELECT JSON_OVERLAPS([vip,seller], [vip,buyer]); -- 返回 1以上两个函数相比JSON_CONTAINS的写法更直观而且语义明确能配合多值索引走加速后面性能部分再说。6.4 四大过滤函数怎么选函数适用场景典型写法注意事项JSON_CONTAINS判断目标是否包含指定值JSON_CONTAINS(col, vip)第二个参数必须是合法 JSON 值字符串要带引号JSON_SEARCH按值找路径 / 模糊查找JSON_SEARCH(col, one, %vip%)性能一般适合调试MEMBER OF单值是否在数组中vip MEMBER OF(col)8.0.17语义清晰JSON_OVERLAPS两个数组是否有交集JSON_OVERLAPS(col, [vip])8.0.17做标签匹配很实用一句话总结判断“有没有”优先MEMBER OF和JSON_CONTAINS要拿位置、做模糊匹配用JSON_SEARCH两个数组互相匹配用JSON_OVERLAPS。7. 高频坑点与排查技巧7.1 为什么返回 NULL路径、合法性、空值JSON 函数返回 NULL不外乎三种原因。路径写错。key 名大小写、拼写、层级对应不上函数不报错只返回 NULL。排查方法很简单先看整体结构SELECT JSON_KEYS(req_info) AS keys, JSON_TYPE(req_info) AS type FROM api_log LIMIT 1;JSON_KEYS列出第一层的所有 keyJSON_TYPE返回这段 JSON 到底是 OBJECT 还是 ARRAY一眼就能看出路径从$开始还是从$[0]开始。字段本身是非法 JSON。比如存的是普通字符串hello提取函数直接返回 NULL。用JSON_VALID验证SELECT JSON_VALID(hello), JSON_VALID({name:张三}); -- 0, 1路径存在但值是 JSON null。JSON 里{a: null}和{b: 1}都提取出 NULL但前者路径存在、后者路径不存在。要区分用JSON_CONTAINS_PATHSELECT JSON_CONTAINS_PATH({a: null}, one, $.a) AS has_a, JSON_CONTAINS_PATH({b: 1}, one, $.a) AS has_a2; -- 1, 07.2 - 和 - 的比较陷阱把 JSON 里的数字拿来和 SQL 数字比较写法的坑很隐蔽。-- 错误示范- 提取出字符串 1234字符串和数字比较类型不一致 WHERE req_info-$.duration_ms 1000 -- 正确先 CAST 成数值 WHERE CAST(req_info-$.duration_ms AS UNSIGNED) 1000 -- 另一种正确用 - 保持 JSON 数值语义 WHERE req_info-$.duration_ms 1000-在大多数场景会触发隐式类型转换一时间看不出问题但一旦表里出现几条脏数据比如duration_ms存成了1234msCAST 会直接报错或者返回 0而隐式转换可能把整条查询带歪。我建议数值比较一律显式 CAST不要赌 MySQL 的隐式转换。7.3 带空格、带中文、带连字符的 keyJSON 的 key 不一定都像name这么规整。第三方接口经常返回user-name、商品规格、res-type这类 key。路径写法必须用双引号包住-- 错误JS 风格路径MySQL 不认 req_info-$.user-name -- 正确 req_info-$.user-name req_info-$.商品规格嵌套层里带特殊字符也一样req_info-$.data.res-type这个语法面试里容易考实际开发里也容易踩。规则只有一条路径里遇到非字母数字下划线的 key一律.key包起来。7.4 JSON 字段的索引问题普通 JSON 列上的WHERE req_info-$.uid 10086无法走索引因为-是函数函数包裹列名时无法直接利用列上的 B 树索引除非索引建立在表达式的生成列上。线上数据一多这类查询就是慢查询重灾区。标准解法生成列 二级索引。ALTER TABLE api_log ADD COLUMN uid BIGINT GENERATED ALWAYS AS (CAST(req_info-$.uid AS UNSIGNED)) VIRTUAL, ADD INDEX idx_uid (uid);加完之后WHERE uid 10086的写法直接命中新索引MySQL 会从 JSON 里提取值并自动维护生成列。注意生成列表达式必须是确定性的路径要写字符串字面量不能引用其他表的字段。MySQL 8.0.17 之后还有多值索引专门加速 JSON 数组过滤。比如user_tags.tags_json存的是[vip,seller]ALTER TABLE user_tags ADD INDEX idx_tags ( (CAST(tags_json-$[*] AS CHAR(20) ARRAY)) );多值索引能让MEMBER OF、JSON_CONTAINS、JSON_OVERLAPS这类判断加速。它能命中类似“这个数组包含某个值”的查询代价是写入时维护成本比普通索引高——写多读少的表慎用。7.5 别在一条 SQL 里频繁解析同一段 JSONJSON 解析本身有 CPU 成本。如果一条 SQL 里对同一个 JSON 字段做了 5 次-MySQL 就要解析 5 次。虽然 MySQL 有自己的缓存机制但不一定每次都命中。最直观的优化是用子查询或者 CTE先提取一次再用别名继续算WITH parsed AS ( SELECT id, CAST(req_info-$.uid AS UNSIGNED) AS uid, req_info-$.action AS action, CAST(req_info-$.duration_ms AS UNSIGNED) AS duration_ms, req_info-$.os AS os FROM api_log ) SELECT action, COUNT(*), AVG(duration_ms) FROM parsed GROUP BY action;这样每个字段只解析一次语义也更集中后续改字段名只改一处。这个习惯对复杂报表 SQL 帮助很大。8. 性能与架构边界什么时候不该在 MySQL 里玩 JSON8.1 过滤先行展开在后JSON_TABLE 这类展开操作最怕一上来就把全部数据展开再过滤。比如 1000 万行日志每行params数组里有 20 个元素直接JOIN JSON_TABLE会产生 2 亿行中间结果再过滤就晚了。正确姿势先用 WHERE 把范围缩到几千行再做展开。MySQL 8.0 优化器对 JSON_TABLE 的内联过滤能力有限别指望它的执行计划像普通表 JOIN 那么聪明。我见过一个案例加了外层过滤之后查询从 40 秒降到 0.2 秒差距就是这么夸张。8.2 避免无脑 SELECT * 大 JSON 字段JSON 字段的存储是二进制紧凑格式但 SELECT 出来传到客户端要序列化成文本。一个 200KB 的 JSON 如果不在结果集里需要就别把它带出去。只需要uid就写req_info-$.uid不要SELECT req_info全量拖走。这个建议听起来像废话实际开发里我见过太多人为了省事直接SELECT *把几 MB 的 JSON 全灌到报表里数据库网络 IO 直接爆掉。8.3 把 MySQL 当 ETL 引擎是最后的方案如果 JSON 解析的需求已经到了“每天跑一次全量报表、数据量上亿、中间结果要落临时表”的程度坦白讲MySQL 已经不该继续承担这个角色了。我自己的判断标准有这么几条单次扫描超过千万行、JSON 嵌套超过 5 层、需要多表 JSON 字段互相关联、查询响应要求秒级。满足任何一条都应该把 JSON 原始数据先同步到 Spark、Flink、ClickHouse 这类专门干脏活累活的引擎里在那边解析和建模MySQL 只存原始数据或者存解析后的宽表结果。这不是说 MySQL 的 JSON 功能不行而是架构上各有分工。MySQL 适合在线事务场景里做轻量提取重活交给数据管道处理两边都轻松。最后分享几点心得用了这么多年 MySQL JSON 函数我最大的体会是JSON 列是一把双刃剑。用得好的团队能把扩展属性管理得服服帖帖用得差的团队会在上线半年后面对一堆无法索引的慢查询欲哭无泪。我的习惯是能拆成独立列的字段坚决拆出来实在要保留 JSON 的字段第一时间把生成列索引建上别等慢查询报警了才想起来补。最后分享一个小技巧调试 JSON 路径的时候别靠猜。直接写一条SELECT JSON_KEYS(字段), JSON_TYPE(字段) FROM 表 LIMIT 1先把结构看清楚再写提取路径。这一步能帮你省掉至少一半的排查时间。