MySQL進行JSON查詢的詳細教程
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'));
注意
- 路徑表達式區(qū)分大小寫
- 不存在的路徑返回 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 列創(chuàng)建函數索引提高查詢性能
到此這篇關于MySQL進行JSON查詢的詳細教程的文章就介紹到這了,更多相關MySQL JSON查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要給大家介紹了關于MySQL中int最大值的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-02-02
這篇文章詳細介紹了Windows下Mysql5.6.11的下載、安裝與配置教程，需要的朋友可以參考下2013-06-06
這篇文章主要介紹了MySQL慢日志實踐小結，小編覺得挺不錯的，現在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2019-01-01
MySQL提示Accessdeniedforuser‘‘@‘localhost‘”的解決方案
在使用MySQL數據庫的過程中,有時會遇到錯誤提示：“Access denied for user ''@'localhost'”,這個錯誤通常意味著MySQL服務器拒絕了當前用戶的連接請求,本文將詳細探討該問題的原因及解決方法,需要的朋友可以參考下2025-01-01
為了裝mysql環(huán)境測試，裝上后發(fā)現啟動后mysql占用了很大的虛擬內存，達8百多兆,需要的朋友可以參考下2012-12-12
如果正確合理設計并且使用索引的MySQL是一輛蘭博基尼的話，那么沒有索引的MySQL就是一個人力三輪車。通常大型網站單日就可能會產生幾十萬甚至幾百萬的數據，沒有索引查詢會變的非常緩慢，下面小編來帶大家了解下索引2019-05-05
這篇文章主要介紹了MySQL索引概念及七種索引類型分享介紹，索引是存儲引擎用于快速找到記錄的一種數據結構，這也是索引最基本的功能2022-08-08
這篇文章主要介紹了mysql 使用use命令選擇數據庫的相關資料,需要的朋友可以參考下2016-09-09
這篇文章主要介紹了MySql如何按照日期進行分組統計問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-09-09
索引的設計可以遵循一些已有的原則，創(chuàng)建索引的時候請盡量符合這些原則，便于提升索引地使用效率，更高效地使用索引。今天給大家介紹跳槽必備之你設計索引的原則是什么?怎么避免索引失效?感興趣的朋友一起看看吧2021-05-05