做数据支持的时候经常能遇到一类需求业务方在接口里塞了一个 JSON 字段库里存的也是一整串 JSON 字符串然后过了两天提数的人跑过来说“帮我把这个字段里的某某值取出来按它筛选一下数据”。以前我第一反应是拉出来用 Python 解析后来发现这个动作在 MySQL 里就能做而且做得还挺干净。这篇文章就把我实际用下来的一些函数、操作思路和踩过的坑整理一遍主要围绕 JSON_EXTRACT、-、- 和 JSON_TABLE 这几个东西展开顺便把隐式转换和索引这两个最磨人的点也说清楚。1. 非用 MySQL 来抽 JSON 不可吗先算清楚这笔账1.1 什么时候该用数据库抽什么时候该用程序抽很多人一听到“从 MySQL 提取 JSON 数据”第一反应是这种脏活不是应该在程序里做吗确实如果数据量小、一次性导出放到 Python、Java 或者 Kettle 里面解析反而更灵活。但实际工作中至少有三类场景在 MySQL 里直接处理会舒服得多。第一类是联表查询场景。你需要把 JSON 字段里的某个值作为关联条件去 JOIN 另外一张表。这时候把数据拉出去解析再关联等于要自己实现一遍 JOIN 逻辑纯属脱裤子放屁。第二类是持续性的查询报表。比如运营后台每五分钟刷一次大屏数据源直连 MySQL你不可能让大屏先去调一段 Python 脚本再拿结果。第三类是增量数据处理。上游业务表每天写入带 JSON 字段的记录下游要实时筛选这种链路用 SQL 函数处理最顺。反过来如果一次要解析几百万行数据、每个 JSON 特别大、路径特别深那 MySQL 的函数运算效率就不如程序端的内存解析了。所以我的判断标准很简单能在一个 SQL 里完成的事别拆成两个系统做但涉及大数据量复杂 JSON 解析时别硬扛。1.2 MySQL 版本要求与函数全景要在 MySQL 里操作 JSON版本至少是 5.7因为 JSON 类型和相关函数从 5.7 才开始正式支持。8.0 之后在此基础上加了 JSON_TABLE 这种把数组展开成行的函数实用性进一步提升。生产环境如果还是 5.6那就别折腾了老实导出到程序里处理吧。MySQL 提供的 JSON 处理函数不少但我实际高频使用的主要就这些函数/运算符作用适用版本JSON_EXTRACT(json_doc, path)按路径提取值返回 JSON 格式5.7-JSON_EXTRACT 的简写嵌套在 SQL 中使用5.7-相当于 JSON_UNQUOTE(JSON_EXTRACT(...))返回纯字符串5.7JSON_UNQUOTE()去掉提取结果外侧的引号5.7JSON_CONTAINS()判断目标 JSON 中是否包含指定值5.7JSON_KEYS()返回 JSON 对象的所有 key5.7JSON_TABLE()将 JSON 数组展开成一张虚拟表用于 JOIN 和查询8.0从这张表能看出围绕“提取”这个动作有两条线一条是直接取值用的是 JSON_EXTRACT 家族另一条是结构分析用的 JSON_KEYS、JSON_CONTAINS 这些。后面实战部分我会把两条线都串起来讲。2. 三个最常用的提取函数JSON_EXTRACT、- 和 - 的区别2.1 从一条最简单的提取开始先用一个最常见的例子“热身”。假设你有一张用户表user_log里面有个字段extra存的是 JSON 字符串内容类似这样{device: iPhone 14, os: iOS 16.4, location: {city: 上海, lat: 31.23}, tags: [vip, new_user]}现在要取出device的值SQL 可以这样写SELECT JSON_EXTRACT(extra, $.device) AS device_json FROM user_log;这里$.device是 JSON 路径语法$代表整个 JSON 文档.device表示取device这个 key 对应的值。执行结果是iPhone 14注意结果是带双引号的。因为 JSON_EXTRACT 的返回值仍然是 JSON 类型JSON 中的字符串在返回时就是带引号的。这在某些客户端里会显示成iPhone 14如果你拿这个值去和普通字符串比较是比不上的这一点最容易坑到人。2.2 - 和 - 的等价关系为了避免上面那个问题MySQL 给了两个运算符。-和JSON_EXTRACT完全等价也就是返回的值仍然带引号。而-是在提取之后自动做了一次JSON_UNQUOTE也就是把外侧引号剥掉。所以下面这三句话效果是一样的SELECT extra-$.device FROM user_log; SELECT JSON_EXTRACT(extra, $.device) FROM user_log; SELECT JSON_UNQUOTE(JSON_EXTRACT(extra, $.device)) FROM user_log;而这两句话效果一样返回的都是不带引号的纯文本SELECT extra-$.device FROM user_log; SELECT JSON_UNQUOTE(extra-$.device) FROM user_log;在实际使用时我基本不用-因为带引号的返回结果要么还得再包一层JSON_UNQUOTE要么就是在比较的时候踩坑。直接养成本能用-就用-的习惯能省掉很多麻烦。2.3 路径语法里容易被忽略的细节JSON 路径看着简单但有几个细节值得单独说一下。数组下标从 0 开始。要取tags数组的第一个元素路径写成$.tags[0]而不是$.tags[1]。如果下标越界MySQL 返回 NULL不会报错。这就意味着你拿$[.tags[10]]去取数时如果数组没那么多元素Sql 不会提示而是安静地返回 NULL这需要你在代码里对结果做空值兜底。key 名里带空格或特殊字符路径要用双引号包住。比如 JSON 里有{user name: 张三}取数路径就得写成$.user name。这个细节在接第三方数据时特别常见对方接口的 key 往往不规范。嵌套路径可以一层一层往下写。extra-$.location.city可以直接取到上海不需要先取location再取city。对于多层嵌套的对象路径可以一直往下点这就很像在文件系统里找某个深层目录下的文件。关于路径语法我建议先用JSON_KEYS()看一个 JSON 对象里有哪些 key再动手写路径比自己瞎猜靠谱很多SELECT JSON_KEYS(extra) FROM user_log;返回结果是一组 key 的 JSON 数组比如[device, os, location, tags]。虽然很多场景下你知道接口文档里定义了哪些字段但实际入库的数据偶尔会有出入用JSON_KEYS快速核验一下能避免路径写错导致整列数据全是 NULL。3. 实战案例把订单表的 JSON 字段拆成结构化查询3.1 准备一张带 JSON 字段的业务表为了把提取函数真正串起来用我构造一个更接近实际业务的例子。假设电商系统里有一张订单表order_info其中有几个核心字段CREATE TABLE order_info ( id INT PRIMARY KEY, order_no VARCHAR(32), user_id INT, order_time DATETIME, amount DECIMAL(10,2), extra JSON );这里的extra字段类型是 JSON里面存放了下单时的扩展信息。可能长这样{ source: app, promotion: {coupon_amount: 20, type: 满减}, items: [ {sku: 1001, name: 手机壳, price: 39.00, qty: 2}, {sku: 1002, name: 钢化膜, price: 19.00, qty: 1} ], address: {province: 浙江省, city: 杭州市, detail: xxx路xx号} }实际生产里有些团队会把extra字段设计为 VARCHAR/TEXT 来存 JSON 字符串由于 MySQL 本身有 JSON 类型直接用 JSON 字段类型的查询性能和合法性检查都优于 VARCHAR这一点后面的索引部分也会提到。3.2 提取用户信息与订单金额现在的需求是从订单表里取订单号、来源渠道、优惠券金额和收货城市。直接在SELECT语句里用-就能完成提取SELECT order_no, extra-$.source AS source, extra-$.promotion.coupon_amount AS coupon_amount, extra-$.address.city AS city FROM order_info WHERE user_id 1024;执行结果大概是这样的order_nosourcecoupon_amountcityD2024001app20杭州市这里的coupon_amount是从 JSON 里取出来的字符串如果后面要参与金额计算需要先确认数据类型。比如计算实际支付金额时直接写SELECT order_no, amount - CAST(extra-$.promotion.coupon_amount AS DECIMAL(10,2)) AS paid_amount FROM order_info WHERE user_id 1024;extra-$.promotion.coupon_amount返回的是20这种带引号的 JSON 字符串通过CAST(... AS DECIMAL)变回数值参与运算。这一步看起来简单但其实是很重要的习惯因为 JSON 提取出来的数据在 MySQL 内部按字符串路线走遇到数值运算必须显式转换。3.3 按 JSON 里的值做筛选和排序提取出的值不光要出现在 SELECT 列表里还经常要作为 WHERE 条件和 ORDER BY 条件。比如筛选所有来自 App 渠道且优惠券金额大于 10 元的订单SELECT order_no, extra-$.source AS source, extra-$.promotion.coupon_amount AS coupon_amount FROM order_info WHERE extra-$.source app AND CAST(extra-$.promotion.coupon_amount AS DECIMAL(10,2)) 10;这里有两个点值得注意。第一extra-$.source返回的是纯字符串所以和app比较没问题直接用不比较就不知道刚才说的去引号有多重要。第二coupon_amount在 JSON 里是一个数字但-取出来变成字符串之后MySQL 在做比较的时候会尝试隐式转换但为了避免不可控的转换规则最好还是显式CAST到数值类型。再比如按订单里商品数组的数量排序可以用JSON_LENGTH()这个函数SELECT order_no, JSON_LENGTH(extra-$.items) AS item_cnt FROM order_info ORDER BY item_cnt DESC;JSON_LENGTH返回 JSON 数组的元素个数是处理数组结构时一个相当顺手的工具。之前有同事一直不知道这个函数用 Python 把数据拉出去数了一遍再导回来属实绕了大远路。4. 数组和动态 key 的处理JSON_TABLE 与路径的边界4.1 JSON_TABLE 的完整展开流程前面的例子都是提取对象里的某个 key或者对数组计算长度。但很多需求本质上是把 JSON 数组展开成多行。比如上面那张订单表每个订单里有多个商品要统计每个 SKU 的销量最常见的做法是把数组展开每个商品变成一行。在 MySQL 8.0 里这个工作由JSON_TABLE完成。直接看用法SELECT o.order_no, t.sku, t.name, t.price, t.qty FROM order_info o, JSON_TABLE( o.extra, $.items[*] COLUMNS ( sku VARCHAR(20) PATH $.sku, name VARCHAR(50) PATH $.name, price DECIMAL(10,2) PATH $.price, qty INT PATH $.qty ) ) AS t;说一下这段 SQL 的逻辑。$.items[*]表示遍历items数组里的每一个元素相当于把数组“拍扁”。COLUMNS里面定义展开出的每一列数据从哪里取sku取当前数组元素的$.sku路径price取$.price等等。执行完之后一行订单如果有两个商品就会变成两行数据。这个输出就是标准的行列转换逻辑比在程序里循环拼 SQL 再 UNION ALL 要干净太多。4.2 数组过滤与存在性判断除了展开整个数组业务上经常还要判断数组里有没有某个元素。比如找出订单商品里包含 sku 为1001的所有订单可以用JSON_CONTAINSSELECT order_no FROM order_info WHERE JSON_CONTAINS(extra-$.items, JSON_OBJECT(sku, 1001));这里写法有一点点绕JSON_CONTAINS的第二个参数是要查找的目标值必须是 JSON 格式。所以不能直接写1001要写JSON_OBJECT(sku, 1001)这个 JSON 片段。如果你只是想判断数组里是否包含某个字符串同样可以简化成WHERE JSON_CONTAINS(extra-$.tags, vip)注意vip这里的双引号不能丢因为 JSON 字符串本身就带引号。判断包含关系时最容易犯的错就是忘了加内层引号。建议写下标时要带上注释过一个月自己回来看都能立刻明白这段 SQL 在查什么。4.3 动态 key 的应对思路还有一种需求更麻烦JSON 里的 key 不是固定的而是动态的。比如埋点日志里存了不同事件的属性{event: click, props: {button_id: btn_1}} {event: view, props: {page_name: home}}props下面的 key 完全取决于事件类型。这时候用extra-$.props.button_id只能适配点击事件对查看事件就返回 NULL。应对这种场景有两条路。一条是用JSON_KEYS先捞出动态 key 列表在程序层做二次处理。另一条是如果确实只需要某一类事件的某个固定字段就配合WHERE条件把事件类型限定住再去取那个 key。如果动态 key 的组合非常多且需要灵活查询老实说 MySQL 并不是最好的工具这时候把 JSON 交给搜索引擎或者专门的文档数据库更合理。在关系库里硬解动态 schema性能和灵活性两头都不讨好这个边界要拎清。5. 性能与踩坑隐式转换、空值和索引这三件事最磨人5.1 最常见的隐式转换问题从 JSON 里提取的值经常是字符串形态但你以为拿到的数字所以直接和数值比较。比如SELECT * FROM order_info WHERE extra-$.promotion.coupon_amount 10;MySQL 在这种情况下会尝试把extra-$.promotion.coupon_amount的字符串值转为数字再和 10 比较。大多数时候结果是对的但这属于隐式转换有两个风险一是字符串转数字时如果 JSON 里存的是20元这种带单位的字符串转换结果可能是 20也可能变成 0你根本想不到二是转换会放弃索引后面第五节的索引方案就完全失效。我的原则是凡是提取出来要参与计算和比较的字段一律显式 CAST。虽然代码看着啰嗦但能把行为锁死。比如WHERE CAST(extra-$.promotion.coupon_amount AS DECIMAL(10,2)) 10这样一旦数据格式不合预期MySQL 会直接报错而不是悄悄返回一个让你查半天的问题数字。数据解析宁可报错也别静默出错——这句话在做数据抽取时怎么强调都不过分。5.2 空值判断和解析失败JSON 提取还有一个高频问题取出的值是 NULL但到底是因为 key 不存在还是因为整个 JSON 字段就是 NULL还是因为 JSON 本身格式非法这三种情况得区分清楚。SELECT extra, extra-$.promotion.coupon_amount AS coupon FROM order_info;如果extra字段本身是 NULL那结果就是 NULL。如果 extra 字段有值但是 JSON 里没有promotion这个 key那个子路径解析结果也是 NULL。这两种情况在查询结果上几乎是不可区分的。那怎么办如果业务上必须区分可以改用显式的存在性判断SELECT extra, JSON_CONTAINS_PATH(extra, one, $.promotion.coupon_amount) AS has_coupon FROM order_info;JSON_CONTAINS_PATH返回 1 表示路径存在返回 0 表示不存在。用这个函数可以先判断 key 是否存在再做后续操作比直接解析一个 NULL 回来再猜原因要精准得多。另外如果 JSON 字段类型是 VARCHAR 或者 TEXT里面的字符串不是合法 JSON 格式MySQL 会在执行 JSON 函数时报出Invalid JSON text错误。这种错误在数据量大的时候就是灾难。我的习惯是在写入层就保证 JSON 合法但如果历史数据已经乱了筛选的时候可以先给 JSON 列加入格式校验条件比如在 MySQL 5.7 里直接把这个列改成 JSON 类型MySQL 会自动验证并拒绝非法值。5.3 生成列加索引的思路最后一个大坑是性能。JSON 字段本身不能被传统索引直接优化因为 B 树索引要求数据是有序可比较的值而 JSON 是一个整体结构。所以如果你在 WHERE 里直接写extra-$.source appMySQL 只能把整个 extra 字段读出来解析一遍这在百万行数据上就是全表扫描。解决办法是用生成列Generated Column把 JSON 里的值“提”成一个普通列然后在这个列上建索引。具体做法ALTER TABLE order_info ADD COLUMN source VARCHAR(20) GENERATED ALWAYS AS (extra-$.source) STORED; CREATE INDEX idx_source ON order_info(source);这里把extra-$.source定义为一个持久化的生成列。之后查询直接写WHERE source app就能走普通索引了。生成列有两种STORED和VIRTUAL。STORED 会在插入数据时把提取结果持久化到磁盘占空间但是索引效率高VIRTUAL 不占物理存储但建索引时 MySQL 支持在虚拟列上也建索引。以我个人的经验如果这个字段查询频率很高建议用 STORED查询性能更稳定。用上生成列之后不仅查询快了语义也更清晰。索引生效需要有意识地设计如果你在一个需要天天查的 JSON 字段上用了-做过滤而不建生成列数据库早晚会给你还以颜色。还有一点需要提醒生成列表达式中的函数必须是确定的。如果这个 extra 字段本身是 JSON 类型生成列写法会更稳定如果字段是 VARCHAR 存储的 JSON也可以建生成列但前提是原列里的所有值都能被解析成合法 JSON。最后补几个实际体会说句实在话MySQL 处理 JSON 的能力放在今天看来不是让你把数据库当成万能 JSON 解析器用而是解决那些“轻量级、偶发性”的提取需求。如果业务字段结构很固定字段量又少干脆拆成独立列存储别包在一层 JSON 里如果 JSON 结构深度超过三四层、数组嵌套复杂、查询频率又高那 MySQL 里的函数操作就开始显得别扭了这种场景下配合程序端解析或者干脆换文档数据库才是治本。我在实际运维中体会最深的还是那句老话查询之前先想清楚数据类型和路径是否存在。很多线上慢查询和数据异常不是 MySQL 做得不够好而是写 SQL 的人在提取 JSON 后没处理隐式转换没检查路径返回 NULL 的情况也没想着加生成列索引。把这三样东西从潜意识里变成条件反射再复杂的 JSON 提取需求处理起来也会踏实很多。万一哪天你接手一张全是 JSON 字符串的旧表别慌先从JSON_KEYS开始逐步把常用的几个 key 用生成列提出来配合-和JSON_TABLE做完你手里的提数任务你就会发现这个看上去没头没尾的 JSON 字段也就是一层窗户纸的事。