MySQL 的 JSON 查询

MySQL 的 JSON 路径格式

MySQL 使用特定的 JSON 路径表达式语法来导航和提取 JSON 文档中的数据
基本结构

MySQL 中的 JSON 路径遵循以下通用格式

1. $[路径组件]

复制代码

路径组件详解

1. | 操作符       | 描述      | 示例                  |
2. | ----------- | --------- | --------------------- |
3. | $           \| 根对象     \| $                    |
4. | . 或 []     | 成员访问   | $.name 或 $['name']   |
5. | [*]         | 数组通配符 | $.items[*]            |
6. | [n]         | 数组索引   | $[0]                 |
7. | [m to n]    | 数组范围   | $[1 to 3]            |
8. | **          | 递归通配符 | $**.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+)

1. $.items[?(@.price > 10)]

复制代码

• ? 引入过滤表达式
  
• @ 表示当前元素
  

2. 路径范围 (MySQL 8.0.26+)

1. $[1 to 3]       // 第1到第3个元素
2. $[last-1]       // 倒数第二个元素
3. $[last-2 to last] // 最后三个元素

复制代码

实际示例

简单路径

1. -- 提取标量值
2. SELECT JSON_EXTRACT('{"name": "张三", "age": 30}', '$.name');
4. -- 数组元素, 输出 "b", 注意是带双引号的
5. SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');

复制代码

复杂路径

1. -- 嵌套对象
2. SELECT JSON_EXTRACT('{"store": {"book": {"title": "MySQL指南"}}}', '$.store.book.title');
4. -- 对象数组
5. SELECT JSON_EXTRACT('{"items": [{"id": 1}, {"id": 2}]}', '$.items[*].id');

复制代码

简写操作符

MySQL 提供常用操作的简写形式

• -> : 等同于 JSON_EXTRACT()
  
• ->> : 等同于 JSON_UNQUOTE(JSON_EXTRACT())
  

1. -- 以下两种写法等价：
2. SELECT json_column->'$.name';
3. SELECT JSON_EXTRACT(json_column, '$.name');
5. -- 以下两种写法等价（返回去除引号的字符串）：
6. SELECT json_column->>'$.name';
7. 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 用法

1. JSON_TABLE(
2.     json_doc,       -- JSON 类型的字段或值
3.     path_expression -- JSON 路径表达式
4.     COLUMNS(        -- 新表的列定义
5.         column_name column_type PATH json_path [on_empty] [on_error],
6.         ...
7.     )
8. ) [AS] alias

复制代码

参数说明

• json_doc：可以是 JSON 字符串字面量, 或者表中的 JSON 类型列
  
• path_expression：指向要展开的 JSON 数组的路径
  
• COLUMNS：定义输出列的结构
  
  
  
  • column_name：生成的列名
    
  • column_type：数据类型（如 VARCHAR, INT, JSON 等）
    
  • PATH：指定数据提取路径
    
  
  
• alias：必须提供的表别名
  

实际案例

将整数数组展开为一列多行

1. SELECT *
2. FROM JSON_TABLE(
3.     '[1, 2, 3]',
4.     '$[*]' COLUMNS(
5.         rowid FOR ORDINALITY,
6.         value INT PATH '$'
7.     )
8. ) AS t;

复制代码

输出

1. rowid | value
2. ------+-------
3. 1     | 1
4. 2     | 2
5. 3     | 3

复制代码

将对象数组展开为多列多行

1. SELECT *
2. FROM JSON_TABLE(
3.     '[{"name":"张三","age":25},{"name":"李四","age":30}]',
4.     '$[*]' COLUMNS(
5.         name VARCHAR(20) PATH '$.name',
6.         age INT PATH '$.age',
7.         adult VARCHAR(3) PATH '$.age' DEFAULT '否' ON EMPTY
8.     )
9. ) AS t;

复制代码

输出

1. name | age | adult
2. -----+-----+------
3. 张三 | 25  | 否
4. 李四 | 30  | 否

复制代码

在数据表中展开

如果JSON是表中的一个字段, 可以使用 table_1 CROSS JOIN JSON_TABLE(...) 展开, 例如一个表 v_video 的字段 result 为 JSON 字段, 需要展开 result 中的一个成员 sequences, 写成SQL如下

1. SELECT
2.     e.id,
3.     e.match_id,
4.     e.result->>'$.id' AS json_id,
5.     j.tag->>'$.sf' AS sf_value,
6.     j.tag->>'$.ef' AS ef_value,
7.     j.tag->>'$.ef' - j.tag->>'$.sf'AS duration
8. FROM
9.     v_video e
10.         CROSS JOIN JSON_TABLE(
11.             e.result->'$.sequences',
12.             '$[*]' COLUMNS (
13.                 tag JSON PATH '$'
14.             )
15.         ) AS j ON e.match_id = 294

复制代码

上面的SQL, 通过 CROSS JOIN JSON_TABLE 将每一行 e.result 字段下的 sequences 数组展开, 每个数组元素成为新字段 tag, 这时候还是一个 JSON, 然后在SELECT 中通过->>抽取其中的值, 得到完全展开的一个新表.
高级用法

FOR ORDINALITY 子句

生成自增的行号列

1. COLUMNS(
2.     id FOR ORDINALITY,
3.     ...
4. )

复制代码

嵌套路径处理

1. COLUMNS(
2.     NESTED PATH '$.nested_obj' COLUMNS(
3.         sub_col1 INT PATH '$.prop1',
4.         sub_col2 VARCHAR(10) PATH '$.prop2'
5.     )
6. )

复制代码

上面的例子用嵌套可以改写为

1. SELECT
2.     j.id,
3.     j.sf,
4.     j.ef,
5.     j.ef - j.sf AS duration
6. FROM
7.     v_video e
8. CROSS JOIN
9.     JSON_TABLE(
10.         e.result->'$.sequences',
11.         '$[*]' COLUMNS (
12.             id FOR ORDINALITY,
13.             NESTED PATH '$' COLUMNS(
14.                 ef INT PATH '$.ef',
15.                 sf INT PATH '$.sf'
16.             )
17.         )
18.     ) AS j ON e.match_id = 294

复制代码

上面的SQL, 通过 NESTED PATH ... COLUMNS(...) 将展开后数组中的一个JSON元素进一步展开为多个字段.
错误处理

1. COLUMNS(
2.     ef INT PATH '$.ef' NULL ON EMPTY NULL ON ERROR,
3.     sf INT PATH '$.sf' DEFAULT '0' ON EMPTY NULL ON ERROR
4. )

复制代码

格式是

1. on_empty:
2.     {NULL | DEFAULT json_string | ERROR} ON EMPTY
4. on_error:
5.     {NULL | DEFAULT json_string | ERROR} ON ERROR

复制代码

注意事项

• MySQL 版本要高于8.0
  
• 路径表达式必须指向 JSON 数组, 注意是数组
  
• 必须为结果集指定别名
  
• 在 FROM 子句和 JOIN 子句中都可以使用
  
• 在性能上, 对大数据集使用 JSON_TABLE 可能较慢, 可以为 JSON 列创建函数索引提高查询性能
  

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！