MySQL 使用特定的 JSON 路径表达式语法来导航和提取 JSON 文档中的数据
基本结构
MySQL 中的 JSON 路径遵循以下通用格式
$[路径组件] |
路径组件详解
| 操作符 | 描述 | 示例 | |
|
| ----------- | --------- | --------------------- | |
|
| $ \| 根对象 \| $ | |
|
| . 或 [] | 成员访问 | $.name 或 $['name'] | |
|
| [*] | 数组通配符 | $.items[*] | |
|
| [n] | 数组索引 | $[0] | |
|
| [m to n] | 数组范围 | $[1 to 3] | |
|
| ** | 递归通配符 | $**.price | |
1. 根对象 ($)
$表示整个 JSON 文档
2. 成员访问 (. 或 [])
- 点号表示法：
$.store.book - 括号表示法：
$['store']['book'] - 当键名包含特殊字符或空格时使用括号表示法
3. 数组访问
- 所有元素：
$[*]或$.array[*] - 指定索引：
$[0]计数是从0开始 - 范围：
$[1 to 3]（MySQL 8.0.26+）
4. 通配符
*匹配当前层级所有成员/元素**递归搜索所有路径（MySQL 8.0.26+）
特殊语法元素
1. 过滤表达式 (MySQL 8.0.4+)
$.items[?(@.price > 10)] |
?引入过滤表达式@表示当前元素
2. 路径范围 (MySQL 8.0.26+)
$[1 to 3] // 第1到第3个元素 |
|
$[last-1] // 倒数第二个元素 |
|
$[last-2 to last] // 最后三个元素 |
实际示例
简单路径
-- 提取标量值 |
|
SELECT JSON_EXTRACT('{"name": "张三", "age": 30}', '$.name'); |
|
-- 数组元素, 输出 "b", 注意是带双引号的 |
|
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]'); |
复杂路径
-- 嵌套对象 |
|
SELECT JSON_EXTRACT('{"store": {"book": {"title": "MySQL指南"}}}', '$.store.book.title'); |
|
-- 对象数组 |
|
SELECT JSON_EXTRACT('{"items": [{"id": 1}, {"id": 2}]}', '$.items[*].id'); |
简写操作符
MySQL 提供常用操作的简写形式
->: 等同于JSON_EXTRACT()->>: 等同于JSON_UNQUOTE(JSON_EXTRACT())
-- 以下两种写法等价： |
|
SELECT json_column->'$.name'; |
|
SELECT JSON_EXTRACT(json_column, '$.name'); |
|
-- 以下两种写法等价（返回去除引号的字符串）： |
|
SELECT json_column->>'$.name'; |
|
SELECT JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.name')); |
注意
- 路径表达式区分大小写
- 不存在的路径返回 NULL（不会报错）
**递归操作符可能影响性能- 过滤表达式支持比较运算符：
=、!=、<、>等
MySQL 的 JSON_TABLE 函数
使用过 JSON_EXTRACT 函数都知道, 这样获取的结果还不是真正的行列结构, MySQL 8.0 引入的 JSON_TABLE 函数可以将 JSON 数据转换为关系型表格格式, 将数组中的每个元素转换成表格中的一行数据.
JSON_TABLE 的功能
- 将 JSON 数组展开为多行记录
- 提取嵌套的 JSON 对象属性
- 将半结构化数据转为结构化数据
JSON_TABLE 用法
JSON_TABLE( |
|
json_doc, -- JSON 类型的字段或值 |
|
path_expression -- JSON 路径表达式 |
|
COLUMNS( -- 新表的列定义 |
|
column_name column_type PATH json_path [on_empty] [on_error], |
|
... |
|
) |
|
) [AS] alias |
参数说明
- json_doc：可以是 JSON 字符串字面量, 或者表中的 JSON 类型列
- path_expression：指向要展开的 JSON 数组的路径
- COLUMNS：定义输出列的结构
column_name：生成的列名column_type：数据类型（如 VARCHAR, INT, JSON 等）PATH：指定数据提取路径
- alias：必须提供的表别名
实际案例
将整数数组展开为一列多行
SELECT * |
|
FROM JSON_TABLE( |
|
'[1, 2, 3]', |
|
'$[*]' COLUMNS( |
|
rowid FOR ORDINALITY, |
|
value INT PATH '$' |
|
) |
|
) AS t; |
输出
rowid | value |
|
------+------- |
|
1 | 1 |
|
2 | 2 |
|
3 | 3 |
将对象数组展开为多列多行
SELECT * |
|
FROM JSON_TABLE( |
|
'[{"name":"张三","age":25},{"name":"李四","age":30}]', |
|
'$[*]' COLUMNS( |
|
name VARCHAR(20) PATH '$.name', |
|
age INT PATH '$.age', |
|
adult VARCHAR(3) PATH '$.age' DEFAULT '否' ON EMPTY |
|
) |
|
) AS t; |
输出
name | age | adult |
|
-----+-----+------ |
|
张三 | 25 | 否 |
|
李四 | 30 | 否 |
在数据表中展开
如果JSON是表中的一个字段, 可以使用 table_1 CROSS JOIN JSON_TABLE(...) 展开, 例如一个表 v_video 的字段 result 为 JSON 字段, 需要展开 result 中的一个成员 sequences, 写成SQL如下
SELECT |
|
e.id, |
|
e.match_id, |
|
e.result->>'$.id' AS json_id, |
|
j.tag->>'$.sf' AS sf_value, |
|
j.tag->>'$.ef' AS ef_value, |
|
j.tag->>'$.ef' - j.tag->>'$.sf'AS duration |
|
FROM |
|
v_video e |
|
CROSS JOIN JSON_TABLE( |
|
e.result->'$.sequences', |
|
'$[*]' COLUMNS ( |
|
tag JSON PATH '$' |
|
) |
|
) AS j ON e.match_id = 294 |
上面的SQL, 通过 CROSS JOIN JSON_TABLE 将每一行 e.result 字段下的 sequences 数组展开, 每个数组元素成为新字段 tag, 这时候还是一个 JSON, 然后在SELECT 中通过->>抽取其中的值, 得到完全展开的一个新表.
高级用法
FOR ORDINALITY 子句
生成自增的行号列
COLUMNS( |
|
id FOR ORDINALITY, |
|
... |
|
) |
嵌套路径处理
COLUMNS( |
|
NESTED PATH '$.nested_obj' COLUMNS( |
|
sub_col1 INT PATH '$.prop1', |
|
sub_col2 VARCHAR(10) PATH '$.prop2' |
|
) |
|
) |
上面的例子用嵌套可以改写为
SELECT |
|
j.id, |
|
j.sf, |
|
j.ef, |
|
j.ef - j.sf AS duration |
|
FROM |
|
v_video e |
|
CROSS JOIN |
|
JSON_TABLE( |
|
e.result->'$.sequences', |
|
'$[*]' COLUMNS ( |
|
id FOR ORDINALITY, |
|
NESTED PATH '$' COLUMNS( |
|
ef INT PATH '$.ef', |
|
sf INT PATH '$.sf' |
|
) |
|
) |
|
) AS j ON e.match_id = 294 |
上面的SQL, 通过 NESTED PATH ... COLUMNS(...) 将展开后数组中的一个JSON元素进一步展开为多个字段.
错误处理
COLUMNS( |
|
ef INT PATH '$.ef' NULL ON EMPTY NULL ON ERROR, |
|
sf INT PATH '$.sf' DEFAULT '0' ON EMPTY NULL ON ERROR |
|
) |
格式是
on_empty: |
|
{NULL | DEFAULT json_string | ERROR} ON EMPTY |
|
on_error: |
|
{NULL | DEFAULT json_string | ERROR} ON ERROR |
注意事项
- MySQL 版本要高于8.0
- 路径表达式必须指向 JSON 数组, 注意是数组
- 必须为结果集指定别名
- 在 FROM 子句和 JOIN 子句中都可以使用
- 在性能上, 对大数据集使用 JSON_TABLE 可能较慢, 可以为 JSON 列创建函数索引提高查询性能
行业拓展
分享一个面向研发人群使用的前后端分离的低代码软件——JNPF。
基于 Java Boot/.Net Core双引擎，它适配国产化，支持主流数据库和操作系统，提供五十几种高频预制组件，内置了常用的后台管理系统使用场景和实用模版，通过简单的拖拉拽操作，开发者能够高效完成软件开发，提高开发效率，减少代码编写工作。
JNPF基于SpringBoot+Vue.js，提供了一个适合所有水平用户的低代码学习平台，无论是有经验的开发者还是编程新手，都可以在这里找到适合自己的学习路径。
此外，JNPF支持全源码交付，完全支持根据公司、项目需求、业务需求进行二次改造开发或内网部署，具备多角色门户、登录认证、组织管理、角色授权、表单设计、流程设计、页面配置、报表设计、门户配置、代码生成工具等开箱即用的在线服务。