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

將對象數組展開為多列多行

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 eCROSS 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 EMPTYon_error:{NULL | DEFAULT json_string | ERROR} ON ERROR

注意事項

1. MySQL 版本要高于8.0
2. 路徑表達式必須指向 JSON 數組, 注意是數組
3. 必須為結果集指定別名
4. 在 FROM 子句和 JOIN 子句中都可以使用
5. 在性能上, 對大數據集使用 JSON_TABLE 可能較慢, 可以為 JSON 列創建函數索引提高查詢性能