MySQL 的 JSON 路径格式

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'));

注意

1. 路径表达式区分大小写
2. 不存在的路径返回 NULL（不会报错）
3. ** 递归操作符可能影响性能
4. 过滤表达式支持比较运算符：=、!=、<、> 等

MySQL 的 JSON_TABLE 函数

使用过 JSON_EXTRACT 函数都知道， 这样获取的结果还不是真正的行列结构， MySQL 8.0 引入的 JSON_TABLE 函数可以将 JSON 数据转换为关系型表格格式， 将数组中的每个元素转换成表格中的一行数据.

JSON_TABLE 的功能

1. 将 JSON 数组展开为多行记录
2. 提取嵌套的 JSON 对象属性
3. 将半结构化数据转为结构化数据

JSON_TABLE 用法

JSON_TABLE(
    json_doc，       -- JSON 类型的字段或值
    path_Expression -- JSON 路径表达式
    COLUMNS(        -- 新表的列定义
        column_name column_type PATH json_path [on_empty] [on_error]，
        ...
    )
) [AS] alias

参数说明

1. json_doC++：可以是 JSON 字符串字面量， 或者表中的 JSON 类型列
2. path_Expression：指向要展开的 JSON 数组的路径
3. COLUMNS：定义输出列的结构
   • column_name：生成的列名
   • column_type：数据类型（如 VARCHAR， INT， JSON 等）
   • PATH：指定数据提取路径
4. 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

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

| 操作符       | 描述      | 示例                  |
| ----------- | --------- | --------------------- |
| $           \| 根对象     \| $                    |
| . 或 []     | 成员访问   | $.name 或 $['name']   |
| [*]         | 数组通配符 | $.items[*]            |
| [n]         | 数组索引   | $[0]                 |
| [m to n]    | 数组范围   | $[1 to 3]            |
| **          | 递归通配符 | $**.price             |
0

输出

| 操作符       | 描述      | 示例                  |
| ----------- | --------- | --------------------- |
| $           \| 根对象     \| $                    |
| . 或 []     | 成员访问   | $.name 或 $['name']   |
| [*]         | 数组通配符 | $.items[*]            |
| [n]         | 数组索引   | $[0]                 |
| [m to n]    | 数组范围   | $[1 to 3]            |
| **          | 递归通配符 | $**.price             |
1

在数据表中展开

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

| 操作符       | 描述      | 示例                  |
| ----------- | --------- | --------------------- |
| $           \| 根对象     \| $                    |
| . 或 []     | 成员访问   | $.name 或 $['name']   |
| [*]         | 数组通配符 | $.items[*]            |
| [n]         | 数组索引   | $[0]                 |
| [m to n]    | 数组范围   | $[1 to 3]            |
| **          | 递归通配符 | $**.price             |
2

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

高级用法

FOR ORDINALITY 子句

生成自增的行号列

| 操作符       | 描述      | 示例                  |
| ----------- | --------- | --------------------- |
| $           \| 根对象     \| $                    |
| . 或 []     | 成员访问   | $.name 或 $['name']   |
| [*]         | 数组通配符 | $.items[*]            |
| [n]         | 数组索引   | $[0]                 |
| [m to n]    | 数组范围   | $[1 to 3]            |
| **          | 递归通配符 | $**.price             |
3

嵌套路径处理

| 操作符       | 描述      | 示例                  |
| ----------- | --------- | --------------------- |
| $           \| 根对象     \| $                    |
| . 或 []     | 成员访问   | $.name 或 $['name']   |
| [*]         | 数组通配符 | $.items[*]            |
| [n]         | 数组索引   | $[0]                 |
| [m to n]    | 数组范围   | $[1 to 3]            |
| **          | 递归通配符 | $**.price             |
4

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

| 操作符       | 描述      | 示例                  |
| ----------- | --------- | --------------------- |
| $           \| 根对象     \| $                    |
| . 或 []     | 成员访问   | $.name 或 $['name']   |
| [*]         | 数组通配符 | $.items[*]            |
| [n]         | 数组索引   | $[0]                 |
| [m to n]    | 数组范围   | $[1 to 3]            |
| **          | 递归通配符 | $**.price             |
5

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

错误处理

| 操作符       | 描述      | 示例                  |
| ----------- | --------- | --------------------- |
| $           \| 根对象     \| $                    |
| . 或 []     | 成员访问   | $.name 或 $['name']   |
| [*]         | 数组通配符 | $.items[*]            |
| [n]         | 数组索引   | $[0]                 |
| [m to n]    | 数组范围   | $[1 to 3]            |
| **          | 递归通配符 | $**.price             |
6

格式是

| 操作符       | 描述      | 示例                  |
| ----------- | --------- | --------------------- |
| $           \| 根对象     \| $                    |
| . 或 []     | 成员访问   | $.name 或 $['name']   |
| [*]         | 数组通配符 | $.items[*]            |
| [n]         | 数组索引   | $[0]                 |
| [m to n]    | 数组范围   | $[1 to 3]            |
| **          | 递归通配符 | $**.price             |
7

注意事项

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