MySQL JSON函數詳解

一、MySQL JSON函數概述

JSON(JavaScript Object Notation) 是一種輕量級的數據交換格式，目前常用於前後段數據傳輸。MySQL 從 5.7.8 版本開始提供 JSON 數據類型及相關函數，其通過新增json對象類型和相應的函數來解析和生成 JSON 格式數據，使得 MySQL 在處理 JSON 數據方面更加高效便捷。

在 MySQL 中，JSON 對象可以用字符串類型表示，可以使用 JSON 函數對 JSON 對象進行解析和操作。下面將圍繞 MySQL JSON 函數展開介紹。

二、MySQL JSON函數大全

1. mysql_json_extract()

mysql_json_extract(json_object, json_path)函數按照指定的路徑從JSON對象中提取結果，並以字符串格式返回結果。

json_object參數可以是以下數據類型之一：

• JSON類型的列
• JSON類型的文本字面值

json_path參數可以指向以下數據：

• 整個JSON文檔
• 單個元素
• JSON數組

示例：

SELECT JSON_EXTRACT('{"id": 1, "info":{"name": "Mary", "age": 25}}', '$.info.name');
-- 返回 "Mary"
SELECT JSON_EXTRACT('{"id": 2, "info":{"name": "John", "age": 30}}', '$.info.age');
-- 返回 30

2. mysql_json_replace()

mysql_json_replace(json_object, json_path, replace_with)函數用於替換 JSON 對象中指定路徑的值。如果替換的結果與原來的 JSON 對象不同，則返回替換後的 JSON 對象；否則返回原 JSON 對象。

示例：

SELECT JSON_REPLACE('{"id": 1, "name": "Mary"}', '$.name', 'John');
-- 返回 '{"id":1,"name":"John"}'

3. mysql_json_merge()

mysql_json_merge(json_object1, json_object2) 函數用於合併兩個 JSON 對象，如果兩個 JSON 對象的鍵是相同的，則後者的值會覆蓋前者的值。

示例：

SELECT JSON_MERGE('{"id": 1, "name": "Mary"}', '{"name": "John", "age": "30"}');
-- 返回 '{"id": 1, "name": "John", "age": "30"}'

4. mysql_json_array()

mysql_json_array(value1, value2, ..., valueN)函數用於創建一個JSON數組。可以傳入1到多個參數作為數組元素。

示例：

SELECT JSON_ARRAY(1, 'Mary', 30);
-- 返回 '[1,"Mary",30]'

三、MySQL JSON函數詳解

1. mysql_json_path()

mysql_json_path(path, value) 函數用於在 JSON 對象中設置指定路徑下的值。

path參數是JSON路徑表達式。

value參數是要在JSON對象的路徑表達式中設置的值。

示例：

SELECT JSON_SET('{"id": 1, "name": "Mary"}', '$.age', 25);
-- 返回 '{"id":1,"name":"Mary","age":25}'

2. mysql_json_unquote()

mysql_json_unquote(json_string) 函數用於去掉雙引號並輸出字符串類型的JSON值。

示例：

SELECT JSON_UNQUOTE('{"id": 1, "name": "Mary"}');
-- 返回 '{id: 1, name: Mary}'

3. mysql_json_array_append()

mysql_json_array_append(json_array, ..., valueN)函數用於將一個或多個值附加到已有的 JSON數組中。

json_array是當前的JSON數組。

value1到valueN是要添加到JSON數組的值。

示例：

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4);
-- 返回 '[1,2,3,4]'

4. mysql_json_extract_path()

mysql_json_extract_path(json_object, ..., path, ..., pathN) 函數用於從JSON對象中提取多個路徑的結果，並以JSON數組的形式返回結果。

json_object參數可以是以下數據類型之一：

• JSON類型的列
• JSON類型的文本字面值

path1到pathN參數可以指向以下數據：

• 整個JSON文檔
• 單個元素
• JSON數組

示例：

SELECT JSON_EXTRACT_PATH('{"id": 1, "info":{"name": "Mary", "age": 25}}', '$.info.name', '$.id');
-- 返回 '["Mary", 1]'

四、MySQL JSON函數使用實例

1. 按年齡分組，求出每組中年齡最大的人的信息

SELECT MAX(JSON_EXTRACT(info, "$.age")) AS max_age, 
JSON_EXTRACT(info, "$.name") AS name, 
JSON_EXTRACT(info, "$.gender") AS gender 
FROM students 
GROUP BY JSON_EXTRACT(info, "$.gender")

2. 按城市信息統計學生數量

SELECT COUNT(*) AS num, 
JSON_EXTRACT(info, "$.city") AS city 
FROM students 
GROUP BY JSON_EXTRACT(info, "$.city")

3. 對學生成績進行排名

SELECT JSON_EXTRACT(info, "$.name") AS name, 
JSON_EXTRACT(info, "$.score") AS score, 
(
SELECT COUNT(*)+1 
FROM students b 
WHERE 
  JSON_EXTRACT(b.info, "$.score") > JSON_EXTRACT(a.info, "$.score")
) AS rank 
FROM students a

五、小結

MySQL提供的JSON函數使得MySQL在處理JSON數據方面更加高效便捷，能夠快速地實現JSON格式數據的解析和生成，對於前後端分離的應用開發非常方便。本文從10個關鍵字展開詳解，對於想要學習MySQL JSON語法的開發人員來說，可以提供一個基礎的學習參考。