一文搞懂mysql如何處理json格式的字段(解析json數(shù)據(jù))
更新時間：2023年12月04日 09:32:42 作者：禿了也弱了。
這篇文章主要給大家介紹了關(guān)于mysql如何處理json格式的字段的相關(guān)資料,MySQL中的JSON類型是一種數(shù)據(jù)類型,用于存儲和處理JSON(JavaScript Object Notation)格式的數(shù)據(jù),需要的朋友可以參考下
一、概述
1、什么是JSON
略。自行百度。
2、MySQL的JSON
JSON 數(shù)據(jù)類型是 MySQL 5.7.8 開始支持的。在此之前，只能通過字符類型（CHAR，VARCHAR 或 TEXT ）來保存 JSON 文檔。
MySQL 8.0版本中增加了對JSON類型的索引支持?？梢允褂肅REATE INDEX語句創(chuàng)建JSON類型的索引，提高JSON類型數(shù)據(jù)的查詢效率。
存儲JSON文檔所需的空間與存儲LONGBLOB或LONGTEXT所需的空間大致相同。
在MySQL 8.0.13之前，JSON列不能有非空的默認值。
JSON 類型比較適合存儲一些列不固定、修改較少、相對靜態(tài)的數(shù)據(jù)。MySQL支持JSON格式的數(shù)據(jù)之后，可以減少對非關(guān)系型數(shù)據(jù)庫的依賴。
3、varchar、text、json類型字段的區(qū)別
這三種類型的字段，都可以存儲json格式，查詢起來似乎正常的json函數(shù)也能用，這三者存儲json類型的數(shù)據(jù)有什么區(qū)別嗎？
我們接下來測試一下。
二、JSON類型的創(chuàng)建
1、建表指定
CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT COMMENT 'id', `name` varchar(50) DEFAULT NULL COMMENT '名字', `json_data` json DEFAULT NULL COMMENT 'json數(shù)據(jù)', `info` varchar(2000) DEFAULT NULL COMMENT '普通數(shù)據(jù)', `text` text COMMENT 'text數(shù)據(jù)', PRIMARY KEY (`id`) ) ENGINE=InnoDB;
2、修改字段
-- 添加json字段 ALTER TABLE users ADD COLUMN `test_json` JSON DEFAULT NULL COMMENT '測試'; -- 修改字段類型為json ALTER TABLE users modify test_json JSON DEFAULT NULL COMMENT '測試'; -- 刪除json字段 ALTER TABLE users DROP COLUMN test_json;
三、JSON類型的插入
1、字符串直接插入
varchar、text、json格式都支持，也可以插入更復雜的嵌套json：
-- 插入數(shù)組
insert into users(json_data) values('[1, "abc", null, true, "08:45:06.000000"]');
insert into users(info) values('[1, "abc", null, true, "08:45:06.000000"]');
insert into users(text) values('[1, "abc", null, true, "08:45:06.000000"]');
-- 插入對象
insert into users(json_data) values('{"id": 87, "name": "carrot"}');
insert into users(info) values('{"id": 87, "name": "carrot"}');
insert into users(text) values('{"id": 87, "name": "carrot"}');
-- 插入嵌套json
insert into users(json_data) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
insert into users(info) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
insert into users(text) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
但是json格式的字段，插入時會自動校驗格式，如果格式不是json的，會報錯：
insert into users(json_data) values('{"id", "name": "carrot"}');
> 3140 - Invalid JSON text: "Missing a colon after a name of object member." at position 5 in value for column 'users.json_data'.
2、JSON_ARRAY()函數(shù)插入數(shù)組
-- 格式： JSON_ARRAY([val[, val] ...]) -- 使用JSON_ARRAY()函數(shù)創(chuàng)建數(shù)組 ： [1, "abc", null, true, "08:09:38.000000"] insert into users(json_data) values(JSON_ARRAY(1, "abc", null, true,curtime())); insert into users(info) values(JSON_ARRAY(1, "abc", null, true,curtime())); insert into users(text) values(JSON_ARRAY(1, "abc", null, true,curtime()));
3、JSON_OBJECT()函數(shù)插入對象
對于 JSON 文檔，KEY 名不能重復。
如果插入的值中存在重復 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原則，會保留第一個 KEY，后面的將被丟棄掉。
從 MySQL 8.0.3 開始，遵循的是 last duplicate key wins 原則，只會保留最后一個 KEY。
-- 格式：
JSON_OBJECT([key, val[, key, val] ...])
-- 創(chuàng)建對象，一個key對應一個value ： {"id": 87, "name": "carrot"}
insert into users(json_data) values(json_object('id', 87, 'name', 'carrot'));
insert into users(info) values(json_object('id', 87, 'name', 'carrot'));
insert into users(text) values(json_object('id', 87, 'name', 'carrot'));
4、JSON_ARRAYAGG()和JSON_OBJECTAGG()將查詢結(jié)果封裝成json
mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square|
+------+-----------+-------+
4 rows in set (0.00 sec)
mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
-> FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes |
+------+---------------------+
| 2 | ["color", "fabric"] |
| 3 | ["color", "shape"] |
+------+---------------------+
2 rows in set (0.00 sec)
mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square|
+------+-----------+-------+
4 rows in set (0.00 sec)
mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
-> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value) |
+------+---------------------------------------+
| 2 | {"color": "red", "fabric": "silk"} |
| 3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)
四、JSON類型的解析
1、JSON_EXTRACT()解析json
格式：JSON_EXTRACT(json_doc, path[, path] …)
其中，json_doc 是 JSON 文檔，path 是路徑。該函數(shù)會從 JSON 文檔提取指定路徑（path）的元素。如果指定 path 不存在，會返回 NULL。可指定多個 path，匹配到的多個值會以數(shù)組形式返回。
-- 解析數(shù)組
-- 取下標為1的數(shù)組值（數(shù)組下標從0開始），結(jié)果：20
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
-- 取多個，結(jié)果返回是一個數(shù)組，結(jié)果：[20, 10]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
-- 可以使用*獲取全部，結(jié)果：[30, 40]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
-- 還可通過 [M to N] 獲取數(shù)組的子集
-- 結(jié)果：[10, 20]
select json_extract('[10, 20, [30, 40]]', '$[0 to 1]');
-- 這里的 last 代表最后一個元素的下標，結(jié)果：[20, [30, 40]]
select json_extract('[10, 20, [30, 40]]', '$[last-1 to last]');
-- 解析對象：對象的路徑是通過 KEY 來表示的。
set @j='{"a": 1, "b": [2, 3], "a c": 4}';
-- 如果 KEY 在路徑表達式中不合法（譬如存在空格），則在引用這個 KEY 時，需用雙引號括起來。
-- 結(jié)果： 1 4 3
select json_extract(@j, '$.a'), json_extract(@j, '$."a c"'), json_extract(@j, '$.b[1]');
-- 使用*獲取所有元素，結(jié)果：[1, [2, 3], 4]
select json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*');
-- 這里的 $**.b 匹配 $.a.b 和 $.c.b，結(jié)果：[1, 2]
select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
json_extract解析出來的數(shù)據(jù)，可以靈活用于where、order by等等所有地方。
2、-> 箭頭函數(shù)解析json
column->path，包括后面講到的 column->>path，都是語法糖，在實際使用的時候都會在底層自動轉(zhuǎn)化為 JSON_EXTRACT。
column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一個path。
-- 同JSON_EXTRACT
insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
-- 結(jié)果："jack"
select json_data, json_data -> '$.ename' from users;
3、JSON_QUOTE()引用與JSON_UNQUOTE()取消引用
JSON_QUOTE(string)，生成有效的 JSON 字符串，主要是對一些特殊字符（如雙引號）進行轉(zhuǎn)義。
-- 結(jié)果："null" "\"null\"" "[1, 2, 3]"
select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');
JSON_UNQUOTE(json_val)，將 JSON 轉(zhuǎn)義成字符串輸出。常用于使用JSON_EXTRACT()和->函數(shù)解析完之后，去除引號。
JSON_UNQUOTE()特殊字符轉(zhuǎn)義表：
| 轉(zhuǎn)義序列 | 由序列表示的字符 |
|---|---|
| \" | 雙引號 |
| \b | 退格字符 |
| \f | 換頁字符 |
| \n | 換行符 |
| \r | 回車符 |
| \t | 制表符 |
| \\ | 反斜杠(\)字符 |
| \uXXXX | Unicode XXXX 轉(zhuǎn)UTF-8 |
insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
-- 字符串類型轉(zhuǎn)換后會去掉引號，結(jié)果："jack" jack 1 0
select json_data->'$.ename',json_unquote(json_data->'$.ename'),json_valid(json_data->'$.ename'),json_valid(json_unquote(json_data->'$.ename')) from users;
-- 數(shù)字類型轉(zhuǎn)換并沒有額外效果，結(jié)果：1001 1001 1 1
select json_data->'$.empno',json_unquote(json_data->'$.empno'),json_valid(json_data->'$.empno'),json_valid(json_unquote(json_data->'$.empno')) from users;
直觀地看，沒加 JSON_UNQUOTE 字符串會用雙引號引起來，加了 JSON_UNQUOTE 就沒有。但本質(zhì)上，前者是 JSON 中的 STRING 類型，后者是 MySQL 中的字符類型，這一點可通過 JSON_VALID 來判斷。
4、->>箭頭解析json
同 column->path 類似，只不過其返回的是字符串，相當于將字符串的雙引號去掉了，是一個語法糖，本質(zhì)上是執(zhí)行了JSON_UNQUOTE( JSON_EXTRACT(column, path) )。
以下三者是等價的：
JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path
insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
-- 結(jié)果："jack" jack jack jack
select json_data->'$.ename',json_unquote(json_data->'$.ename'),json_data->>'$.ename', JSON_UNQUOTE( JSON_EXTRACT(json_data, '$.ename') ) from users;
五、JSON類型的查詢
1、JSON_CONTAINS()判斷是否包含
格式：JSON_CONTAINS(target, candidate[, path])
判斷 target 文檔是否包含 candidate 文檔，包含的話返回1，不包含的話返回0
如果帶了path，就判斷path中的數(shù)據(jù)是否等于candidate，等于的話返回1，不等于的話返回0
函數(shù)前加not可取反
SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SET @j2 = '{"a":1}';
-- 判斷@j中是否包含@j2，結(jié)果：1
SELECT JSON_CONTAINS(@j, @j2);
SET @j2 = '1';
-- 判斷@j字段中的a是否等于1，結(jié)果：1
SELECT JSON_CONTAINS(@j, @j2, '$.a');
-- 結(jié)果：0
SELECT JSON_CONTAINS(@j, @j2, '$.b');
SET @j2 = '{"d": 4}';
-- 結(jié)果：0
SELECT JSON_CONTAINS(@j, @j2, '$.a');
-- 結(jié)果：1
SELECT JSON_CONTAINS(@j, @j2, '$.c');
SET @j = '[1, "a", 1.02]';
SET @j2 = '"a"';
-- 判斷@j數(shù)組中是否包含@j2，結(jié)果：1
SELECT JSON_CONTAINS(@j, @j2);
2、JSON_CONTAINS_PATH()判斷
格式：JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
判斷指定的 path 是否存在，存在，則返回 1，否則是 0。
函數(shù)中的 one_or_all 可指定 one 或 all，one 是任意一個路徑存在就返回 1，all 是所有路徑都存在才返回 1。
函數(shù)前加not可取反
SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
-- a或者e 存在一個就返回1，結(jié)果：1
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
-- a和e都存在返回1，結(jié)果：0
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
-- c中的d存在返回1，結(jié)果：1
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
SET @j = '[1, 4, "a", "c"]';
-- @j是一個數(shù)組，$[1]判斷第二個數(shù)據(jù)是否存在，結(jié)果為1
select JSON_CONTAINS_PATH(@j, 'one', '$[1]');
-- $[11]判斷第11個數(shù)據(jù)不存在，結(jié)果為0
select JSON_CONTAINS_PATH(@j, 'one', '$[11]');
3、JSON_KEYS()獲取keys
返回 JSON 文檔最外層的 key，如果指定了 path，則返回該 path 對應元素最外層的 key。
-- 結(jié)果：["a", "b"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
-- 結(jié)果：["c"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
4、JSON_OVERLAPS()比較兩個json
MySQL 8.0.17 引入的，用來比較兩個 JSON 文檔是否有相同的鍵值對或數(shù)組元素，如果有，則返回 1，否則是 0。 如果兩個參數(shù)都是標量，則判斷這兩個標量是否相等。
函數(shù)前加not可取反
-- 結(jié)果： 1 0
select json_overlaps('[1,3,5,7]', '[2,5,7]'),json_overlaps('[1,3,5,7]', '[2,6,8]');
-- 部分匹配被視為不匹配，結(jié)果：0
SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
-- 比較對象時，如果它們至少有一個共同的鍵值對，則結(jié)果為真。
-- 結(jié)果：1
SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
-- 結(jié)果：0
SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}');
-- 如果兩個標量用作函數(shù)的參數(shù)，JSON_OVERLAPS()會執(zhí)行一個簡單的相等測試:
-- 結(jié)果：1
SELECT JSON_OVERLAPS('5', '5');
-- 結(jié)果：0
SELECT JSON_OVERLAPS('5', '6');
-- 當比較標量和數(shù)組時，JSON_OVERLAPS()試圖將標量視為數(shù)組元素。在此示例中，第二個參數(shù)6被解釋為[6]，如下所示:結(jié)果：1
SELECT JSON_OVERLAPS('[4,5,6,7]', '6');
-- 該函數(shù)不執(zhí)行類型轉(zhuǎn)換:
-- 結(jié)果：0
SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');
-- 結(jié)果：0
SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');
5、JSON_SEARCH()返回字符串的位置
格式：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])
返回某個字符串（search_str）在 JSON 文檔中的位置，其中，
- one_or_all：匹配的次數(shù)，one 是只匹配一次，all 是匹配所有。如果匹配到多個，結(jié)果會以數(shù)組的形式返回。
- search_str：子串，支持模糊匹配：% 和 _ 。
- escape_char：轉(zhuǎn)義符，如果該參數(shù)不填或為 NULL，則取默認轉(zhuǎn)義符\。
- path：查找路徑。
SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
-- 結(jié)果："$[0]"
SELECT JSON_SEARCH(@j, 'one', 'abc');
-- 結(jié)果：["$[0]", "$[2].x"]
SELECT JSON_SEARCH(@j, 'all', 'abc');
-- 結(jié)果：null
SELECT JSON_SEARCH(@j, 'all', 'ghi');
-- 結(jié)果："$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10');
-- 結(jié)果："$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
-- 結(jié)果："$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
-- 結(jié)果："$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
-- 結(jié)果："$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
-- 結(jié)果："$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
-- 結(jié)果："$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
-- 結(jié)果："$[2].x"
SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
-- 結(jié)果：["$[0]", "$[2].x"]
SELECT JSON_SEARCH(@j, 'all', '%a%');
-- 結(jié)果：["$[0]", "$[2].x", "$[3].y"]
SELECT JSON_SEARCH(@j, 'all', '%b%');
-- 結(jié)果："$[0]"
SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
-- 結(jié)果："$[2].x"
SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
-- 結(jié)果：null
SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
-- 結(jié)果：null
SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
-- 結(jié)果："$[3].y"
SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
6、JSON_VALUE()提取指定路徑的元素
格式：JSON_VALUE(json_doc, path)
8.0.21 引入的，從 JSON 文檔提取指定路徑（path）的元素。
完整的語法如下所示：
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
on_empty:
{NULL | ERROR | DEFAULT value} ON EMPTY
on_error:
{NULL | ERROR | DEFAULT value} ON ERROR
其中：
- RETURNING type：返回值的類型，不指定，則默認是 VARCHAR(512)。不指定字符集，則默認是 utf8mb4，且區(qū)分大小寫。
- on_empty：如果指定路徑?jīng)]有值，會觸發(fā) on_empty 子句， 默認是返回 NULL，也可指定 ERROR 拋出錯誤，或者通過 DEFAULT value 返回默認值。
- on_error：三種情況下會觸發(fā) on_error 子句：從數(shù)組或?qū)ο笾刑崛≡貢r，會解析到多個值；類型轉(zhuǎn)換錯誤，譬如將 “abc” 轉(zhuǎn)換為 unsigned 類型；值被 truncate 了。默認是返回 NULL。
-- 查找fname的值，結(jié)果為：Joe
SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
-- 結(jié)果：49.95
SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' RETURNING DECIMAL(4,2)) AS price;
-- 結(jié)果：50.0
SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' RETURNING DECIMAL(4,1)) AS price;
-- 使用RETURNING定義返回數(shù)據(jù)類型，等效于以下sql：
SELECT CAST(
JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
AS type
);
mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.price1' error on empty);
ERROR 3966 (22035): No value was found by 'json_value' on the specified path.
mysql> select json_value('[1, 2, 3]', '$[1 to 2]' error on error);
ERROR 3967 (22034): More than one value was found by 'json_value' on the specified path.
mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.item' returning unsigned error on error) as price;
ERROR 1690 (22003): UNSIGNED value is out of range in 'json_value'
7、MEMBER OF()判斷是否是json數(shù)組中的元素
格式：value MEMBER OF(json_array)
在 MySQL 8.0.17引入了MEMBER OF()函數(shù)。判斷 value 是否是 JSON 數(shù)組的一個元素，如果是，則返回 1，否則是 0。
函數(shù)前加not可取反
-- 結(jié)果：1
SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
-- 結(jié)果：1
SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');
-- 部分匹配不代表匹配
-- 結(jié)果：0
SELECT 7 MEMBER OF('[23, "abc", 17, "ab", 10]');
-- 結(jié)果：0
SELECT 'a' MEMBER OF('[23, "abc", 17, "ab", 10]');
-- 不執(zhí)行字符串類型之間的相互轉(zhuǎn)換:結(jié)果：0·0
SELECT 17 MEMBER OF('[23, "abc", "17", "ab", 10]'), "17" MEMBER OF('[23, "abc", 17, "ab", 10]')
-- 要將該操作符與本身是數(shù)組的值一起使用，必須將其顯式轉(zhuǎn)換為JSON數(shù)組。結(jié)果：1
SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]');
-- 還可以使用JSON_ARRAY()函數(shù)執(zhí)行必要的強制轉(zhuǎn)換，如下所示: 結(jié)果：1
SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');
--轉(zhuǎn)換，結(jié)果：1 1
SET @a = CAST('{"a":1}' AS JSON);
SET @b = JSON_OBJECT("b", 2);
SET @c = JSON_ARRAY(17, @b, "abc", @a, 23);
SELECT @a MEMBER OF(@c), @b MEMBER OF(@c);
8、JSON_DEPTH()獲取JSON最大深度
語法：JSON_DEPTH(json_doc)
返回JSON文檔的最大深度。如果參數(shù)為NULL，則返回NULL。如果參數(shù)不是有效的JSON文檔，則會出現(xiàn)錯誤。
對于空數(shù)組，空對象，標量值，其深度為 1。
-- 結(jié)果：1 1 1
SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
-- 結(jié)果：2 2
SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
-- 結(jié)果：3
SELECT JSON_DEPTH('[10, {"a": 20}]');
9、JSON_LENGTH()獲取文檔長度
語法：JSON_LENGTH(json_doc[, path])
返回 JSON 文檔的長度，其計算規(guī)則如下：
- 1、如果是標量值，其長度為 1。
- 2、如果是數(shù)組，其長度為數(shù)組元素的個數(shù)。
- 3、如果是對象，其長度為對象元素的個數(shù)。
- 4、不包括嵌套數(shù)據(jù)和嵌套對象的長度。
-- 結(jié)果：3
SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
-- 結(jié)果：2
SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
-- 結(jié)果：1
SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
10、JSON_TYPE()獲取JSON類型
語法：JSON_TYPE(json_val)
返回 JSON 值的類型。
如果參數(shù)不是有效的JSON值，則會出現(xiàn)錯誤。
SET @j = '{"a": [10, true]}';
-- 結(jié)果：OBJECT
SELECT JSON_TYPE(@j);
-- 結(jié)果：ARRAY
SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
-- 結(jié)果：INTEGER
SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
-- 結(jié)果：BOOLEAN
SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
-- 結(jié)果：NULL
SELECT JSON_TYPE(NULL);
-- 結(jié)果：STRING
select json_type('"abc"');
-- 結(jié)果：DATETIME
select json_type(cast(now() as json));
- JSON類型：OBJECT（對象）、ARRAY（數(shù)組）、BOOLEAN（布爾類型）、NULL
- 數(shù)字類型：INTEGER（TINYINT、SMALLINT、MEDIUMINT以及INT和BIGINT標量）、DOUBLE（DOUBLE、FLOAT）、DECIMAL（MySQL、DECIMAL）
- 時間類型：DATETIME（DATETIME、TIMESTAMP）、DATE、TIME
- 字符串類型：STRING（CHAR, VARCHAR, TEXT, ENUM, SET）
- 二進制類型：BLOB（ BINARY, VARBINARY, BLOB, BIT）
- 其他類型：OPAQUE
11、JSON_VALID()校驗JSON格式
語法：JSON_VALID(val)
判斷給定值是否是有效的 JSON 文檔。
函數(shù)前加not可取反
-- 結(jié)果：1
SELECT JSON_VALID('{"a": 1}');
-- 結(jié)果：0 1
SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
六、JSON類型的修改
1、全量修改
直接使用update語句，將json數(shù)據(jù)字段全部替換。
update users set json_data = '{"a":1}';
2、JSON_ARRAY_APPEND()向數(shù)組追加元素
格式：JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
向數(shù)組指定位置追加元素。如果指定 path 不存在，則不添加。
在MySQL 5.7中，這個函數(shù)被命名為JSON_APPEND()。MySQL 8.0不再支持該名稱。
SET @j = '["a", ["b", "c"], "d"]';
-- 在數(shù)組第二個元素的數(shù)組中追加1，結(jié)果：["a", ["b", "c", 1], "d"]
SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
-- 結(jié)果：[["a", 2], ["b", "c"], "d"]
SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
-- 結(jié)果：["a", [["b", 3], "c"], "d"]
SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
-- 多個參數(shù)，結(jié)果：[["a", 1], [["b", 2], "c"], "d"]
select json_array_append(@j, '$[0]', 1, '$[1][0]', 2, '$[3]', 3);
SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
-- 往b中追加，結(jié)果：{"a": 1, "b": [2, 3, "x"], "c": 4}
SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
-- 結(jié)果：{"a": 1, "b": [2, 3], "c": [4, "y"]}
SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
SET @j = '{"a": 1}';
-- 結(jié)果：[{"a": 1}, "z"]
SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
3、JSON_ARRAY_INSERT()向數(shù)組指定位置插入元素
格式：JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
向數(shù)組指定位置插入元素。
SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
-- 在下標1處添加元素x，結(jié)果：["a", "x", {"b": [1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
-- 沒有100個元素，在最后插入，結(jié)果： ["a", {"b": [1, 2]}, [3, 4], "x"]
SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
-- 結(jié)果：["a", {"b": ["x", 1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
-- 結(jié)果：["a", {"b": [1, 2]}, [3, "y", 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
-- 早期的修改會影響數(shù)組中后續(xù)元素的位置，因此同一個JSON_ARRAY_INSERT()調(diào)用中的后續(xù)路徑應該考慮這一點。在最后一個示例中，第二個路徑?jīng)]有插入任何內(nèi)容，因為在第一次插入之后，該路徑不再匹配任何內(nèi)容。
-- 結(jié)果：["x", "a", {"b": [1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
4、JSON_INSERT()插入新值
格式：JSON_INSERT(json_doc, path, val[, path, val] …)
插入不存在的key的值，已經(jīng)存在的不修改。
僅當指定位置或指定 KEY 的值不存在時，才執(zhí)行插入操作。另外，如果指定的 path 是數(shù)組下標，且 json_doc 不是數(shù)組，該函數(shù)首先會將 json_doc 轉(zhuǎn)化為數(shù)組，然后再插入新值。
SET @j = '{ "a": 1, "b": [2, 3]}';
-- a已經(jīng)存在則忽略，c不存在則添加，結(jié)果：{"a": 1, "b": [2, 3], "c": "[true, false]"}
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
-- 上面插入的c是一個帶引號的字符串，想要插入一個數(shù)組，必須進行轉(zhuǎn)換，結(jié)果：{"a": 1, "b": [2, 3], "c": [true, false]}
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON));
-- 下標0位置已經(jīng)有值了，不會插入，結(jié)果：1
select json_insert('1','$[0]',"10");
-- 結(jié)果：[1, "10"]
select json_insert('1','$[1]',"10");
-- 結(jié)果：["1", "2", "10"]
select json_insert('["1","2"]','$[2]',"10");
5、JSON_MERGE()合并json
格式：JSON_MERGE(json_doc, json_doc[, json_doc] …)
合并兩個或多個JSON文檔。JSON_MERGE_PRESERVE()的同義詞；在MySQL 8.0.3中已棄用，在未來版本中可能會被刪除。
推薦使用JSON_MERGE_PRESERVE()
-- 結(jié)果：[1, 2, true, false]
SELECT JSON_MERGE('[1, 2]', '[true, false]');
6、JSON_MERGE_PATCH()合并json
MySQL 8.0.3 引入的，用來合并多個 JSON 文檔。其合并規(guī)則如下：
1、如果兩個文檔不全是 JSON 對象，則合并后的結(jié)果是第二個文檔。
2、如果兩個文檔都是 JSON 對象，且不存在著同名 KEY，則合并后的文檔包括兩個文檔的所有元素，如果存在著同名 KEY，則第二個文檔的值會覆蓋第一個。
-- 不是對象，結(jié)果：[true, false]
SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
-- 都是對象，結(jié)果：{"id": 47, "name": "x"}
SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
-- 都不是對象，取第二個，結(jié)果：true
SELECT JSON_MERGE_PATCH('1', 'true');
-- 第一個不是對象，取第二個 ，結(jié)果：{"id": 47}
SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
-- 第二個覆蓋第一個，結(jié)果：{"a": 3, "b": 2, "c": 4}
SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
-- 結(jié)果：{"a": 5, "b": 2, "c": 4, "d": 6}
SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }');
-- 第二個有null，會刪除該key，結(jié)果：{"a": 1}
SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
-- 嵌套json也可以合并，結(jié)果：{"a": {"x": 1, "y": 2}}
SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
注意區(qū)別于JSON_MERGE_PRESERVE
7、JSON_MERGE_PRESERVE()合并json
MySQL 8.0.3 引入的，用來代替 JSON_MERGE。也是用來合并文檔，但合并規(guī)則與 JSON_MERGE_PATCH 有所不同。
1、兩個文檔中，只要有一個文檔是數(shù)組，則另外一個文檔會合并到該數(shù)組中。
2、兩個文檔都是 JSON 對象，若存在著同名 KEY ，第二個文檔并不會覆蓋第一個，而是會將值 append 到第一個文檔中。
-- 數(shù)組合并，結(jié)果：[1, 2, true, false]
SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
-- 對象合并，結(jié)果：{"id": 47, "name": "x"}
SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
-- 兩個常量，合并為一個數(shù)組，結(jié)果：[1, true]
SELECT JSON_MERGE_PRESERVE('1', 'true');
-- 對象合并到數(shù)組中，結(jié)果：[1, 2, {"id": 47}]
SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
-- 相同的key合并到一個數(shù)組，結(jié)果：{"a": [1, 3], "b": 2, "c": 4}
SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }', '{ "a": 3, "c": 4 }');
-- 結(jié)果：{"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}
SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }', '{ "a": 5, "d": 6 }');
注意區(qū)別于JSON_MERGE_PATCH()
8、JSON_REMOVE()刪除元素
格式：JSON_REMOVE(json_doc, path[, path] …)
刪除 JSON 文檔指定位置的元素。
SET @j = '["a", ["b", "c"], "d"]';
-- 刪除下標為1的元素，結(jié)果：["a", "d"]
SELECT JSON_REMOVE(@j, '$[1]');
set @j = '{ "a": 1, "b": [2, 3]}';
-- 刪除a元素，結(jié)果：{"b": [2, 3]}
select json_remove(@j, '$.a');
set @j = '["a", ["b", "c"], "d", "e"]';
-- 刪除多個元素，刪除1下標之后，下標移動結(jié)果之后再刪除下標2位置，結(jié)果：["a", "d"]
select json_remove(@j, '$[1]','$[2]');
-- 結(jié)果：["a", "e"]
select json_remove(@j, '$[1]','$[1]');
9、JSON_REPLACE()替換元素
語法：JSON_REPLACE(json_doc, path, val[, path, val] …)
替換已經(jīng)存在的值。不存在的值不做影響。
SET @j = '{ "a": 1, "b": [2, 3]}';
-- 對象替換，結(jié)果：{"a": 10, "b": [2, 3]}
SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
-- 數(shù)組替換，結(jié)果：[1, "a", 4, "b"]
select json_replace('[1, "a", 3, "b"]', '$[2]', 4, '$[8]', 8);
10、JSON_SET()插入并替換
格式：JSON_SET(json_doc, path, val[, path, val] …)
插入新值，并替換已經(jīng)存在的值。
換言之，如果指定位置或指定 KEY 的值不存在，會執(zhí)行插入操作，如果存在，則執(zhí)行更新操作。
注意JSON_SET、JSON_INSERT、JSON_REPLACE的區(qū)別。
SET @j = '{ "a": 1, "b": [2, 3]}';
-- 結(jié)果：{"a": 10, "b": [2, 3], "c": "[true, false]"}
SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
-- 結(jié)果：{"a": 1, "b": [2, 3], "c": "[true, false]"}
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
-- 結(jié)果：{"a": 10, "b": [2, 3]}
SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
七、其他JSON函數(shù)
1、JSON_TABLE()列轉(zhuǎn)行
語法：JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
MySQL 8.0支持這樣一個函數(shù)，JSON_TABLE()，從 JSON 文檔中提取數(shù)據(jù)并以表格的形式返回。
完整語法如下：
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
column_list:
column[, column][, ...]
column:
name FOR ORDINALITY
| name type PATH string path [on_empty] [on_error]
| name type EXISTS PATH string path
| NESTED [PATH] path COLUMNS (column_list)
on_empty:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
on_error:
{NULL | DEFAULT json_string | ERROR} ON ERROR
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[ {"c1": null} ]',
-> '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
-> ) as jt;
+------+
| c1 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
select *
from
json_table(
'[{"x":2, "y":"8", "z":9, "b":[1,2,3]}, {"x":"3", "y":"7"}, {"x":"4", "y":6, "z":10}]',
"$[*]" columns(
id for ordinality,
xval varchar(100) path "$.x",
yval varchar(100) path "$.y",
z_exist int exists path "$.z",
nested path '$.b[*]' columns (b INT PATH '$')
)
) as t;
+------+------+------+---------+------+
| id | xval | yval | z_exist | b |
+------+------+------+---------+------+
| 1 | 2 | 8 | 1 | 1 |
| 1 | 2 | 8 | 1 | 2 |
| 1 | 2 | 8 | 1 | 3 |
| 2 | 3 | 7 | 0 | NULL |
| 3 | 4 | 6 | 1 | NULL |
+------+------+------+---------+------+
5 rows in set (0.00 sec)
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
-> "$[*]"
-> COLUMNS(
-> rowid FOR ORDINALITY,
-> ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
-> aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
-> bx INT EXISTS PATH "$.b"
-> )
-> ) AS tt;
+-------+------+------------+------+
| rowid | ac | aj | bx |
+-------+------+------------+------+
| 1 | 3 | "3" | 0 |
| 2 | 2 | 2 | 0 |
| 3 | 111 | {"x": 333} | 1 |
| 4 | 0 | 0 | 0 |
| 5 | 999 | [1, 2] | 0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
-> "$[*]" COLUMNS(
-> xval VARCHAR(100) PATH "$.x",
-> yval VARCHAR(100) PATH "$.y"
-> )
-> ) AS jt1;
+------+------+
| xval | yval |
+------+------+
| 2 | 8 |
| 3 | 7 |
| 4 | 6 |
+------+------+
-- 指定path
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
-> "$[1]" COLUMNS(
-> xval VARCHAR(100) PATH "$.x",
-> yval VARCHAR(100) PATH "$.y"
-> )
-> ) AS jt1;
+------+------+
| xval | yval |
+------+------+
| 3 | 7 |
+------+------+
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
-> '$[*]' COLUMNS(
-> a INT PATH '$.a',
-> NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
-> )
-> ) AS jt
-> WHERE b IS NOT NULL;
+------+------+
| a | b |
+------+------+
| 1 | 11 |
| 1 | 111 |
| 2 | 22 |
| 2 | 222 |
+------+------+
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
-> '$[*]' COLUMNS(
-> a INT PATH '$.a',
-> NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
-> NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
-> )
-> ) AS jt;
+------+------+------+
| a | b1 | b2 |
+------+------+------+
| 1 | 11 | NULL |
| 1 | 111 | NULL |
| 1 | NULL | 11 |
| 1 | NULL | 111 |
| 2 | 22 | NULL |
| 2 | 222 | NULL |
| 2 | NULL | 22 |
| 2 | NULL | 222 |
+------+------+------+
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[{"a": "a_val",
'> "b": [{"c": "c_val", "l": [1,2]}]},
'> {"a": "a_val",
'> "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
-> '$[*]' COLUMNS(
-> top_ord FOR ORDINALITY,
-> apath VARCHAR(10) PATH '$.a',
-> NESTED PATH '$.b[*]' COLUMNS (
-> bpath VARCHAR(10) PATH '$.c',
-> ord FOR ORDINALITY,
-> NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
-> )
-> )
-> ) as jt;
+---------+---------+---------+------+-------+
| top_ord | apath | bpath | ord | lpath |
+---------+---------+---------+------+-------+
| 1 | a_val | c_val | 1 | 1 |
| 1 | a_val | c_val | 1 | 2 |
| 2 | a_val | c_val | 1 | 11 |
| 2 | a_val | c_val | 2 | 22 |
+---------+---------+---------+------+-------+
與表關(guān)聯(lián)查詢：
CREATE TABLE t1 (c1 INT, c2 CHAR(1), c3 JSON);
INSERT INTO t1 () VALUES
ROW(1, 'z', JSON_OBJECT('a', 23, 'b', 27, 'c', 1)),
ROW(1, 'y', JSON_OBJECT('a', 44, 'b', 22, 'c', 11)),
ROW(2, 'x', JSON_OBJECT('b', 1, 'c', 15)),
ROW(3, 'w', JSON_OBJECT('a', 5, 'b', 6, 'c', 7)),
ROW(5, 'v', JSON_OBJECT('a', 123, 'c', 1111))
;
SELECT c1, c2, JSON_EXTRACT(c3, '$.*')
FROM t1 AS m
JOIN
JSON_TABLE(
m.c3,
'$.*'
COLUMNS(
at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY,
bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY,
ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY
)
) AS tt
ON m.c1 > tt.at;
結(jié)果：
與表關(guān)聯(lián)查詢：
CREATE TABLE employees (
id INT,
details JSON
);
INSERT INTO employees VALUES (1, '{"name": "John Doe", "position": "Manager"}');
INSERT INTO employees VALUES (2, '{"name": "Jane Smith", "position": "Developer"}');
SELECT name, position
FROM employees,
JSON_TABLE(details, '$' COLUMNS(
name VARCHAR(255) PATH '$.name',
position VARCHAR(255) PATH '$.position'
)) AS emp;
2、JSON_SCHEMA_VALID()驗證json
語法：JSON_SCHEMA_VALID(schema,document)
判斷 document （ JSON 文檔 ）是否滿足 schema （ JSON 對象）定義的規(guī)范要求。完整的規(guī)范要求可參考 Draft 4 of the JSON Schema specification （https://json-schema.org/specification-links.html#draft-4）。如果不滿足，可通過 JSON_SCHEMA_VALIDATION_REPORT() 獲取具體的原因。
它的要求如下：
1、document 必須是 JSON 對象。
2、JSON 對象必需的兩個屬性是 latitude 和 longitude。
3、latitude 和 longitude 必須是數(shù)值類型，且兩者的大小分別在 -90 ～ 90，-180 ～ 180 之間。
mysql> SET @schema = '{
'> "id": "http://json-schema.org/geo",
'> "$schema": "http://json-schema.org/draft-04/schema#",
'> "description": "A geographical coordinate",
'> "type": "object",
'> "properties": {
'> "latitude": {
'> "type": "number",
'> "minimum": -90,
'> "maximum": 90
'> },
'> "longitude": {
'> "type": "number",
'> "minimum": -180,
'> "maximum": 180
'> }
'> },
'> "required": ["latitude", "longitude"]
'>}';
Query OK, 0 rows affected (0.01 sec)
mysql> SET @document = '{
'> "latitude": 63.444697,
'> "longitude": 10.445118
'>}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SET @document = '{}';
mysql> SET @schema = '{
'> "id": "http://json-schema.org/geo",
'> "$schema": "http://json-schema.org/draft-04/schema#",
'> "description": "A geographical coordinate",
'> "type": "object",
'> "properties": {
'> "latitude": {
'> "type": "number",
'> "minimum": -90,
'> "maximum": 90
'> },
'> "longitude": {
'> "type": "number",
'> "minimum": -180,
'> "maximum": 180
'> }
'> }
'>}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)
-- 建表指定check
mysql> CREATE TABLE geo (
-> coordinate JSON,
-> CHECK(
-> JSON_SCHEMA_VALID(
-> '{
'> "type":"object",
'> "properties":{
'> "latitude":{"type":"number", "minimum":-90, "maximum":90},
'> "longitude":{"type":"number", "minimum":-180, "maximum":180}
'> },
'> "required": ["latitude", "longitude"]
'> }',
-> coordinate
-> )
-> )
-> );
Query OK, 0 rows affected (0.45 sec)
mysql> SET @point1 = '{"latitude":59, "longitude":18}';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @point2 = '{"latitude":91, "longitude":0}';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @point3 = '{"longitude":120}';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO geo VALUES(@point1);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO geo VALUES(@point2);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
-- 查看原因
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Error
Code: 3934
Message: The JSON document location '#/latitude' failed requirement 'maximum' at
JSON Schema location '#/properties/latitude'.
*************************** 2. row ***************************
Level: Error
Code: 3819
Message: Check constraint 'geo_chk_1' is violated.
2 rows in set (0.00 sec)
mysql> INSERT INTO geo VALUES(@point3);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Error
Code: 3934
Message: The JSON document location '#' failed requirement 'required' at JSON
Schema location '#'.
*************************** 2. row ***************************
Level: Error
Code: 3819
Message: Check constraint 'geo_chk_1' is violated.
2 rows in set (0.00 sec)
3、JSON_SCHEMA_VALIDATION_REPORT()查看驗證報告
語法：JSON_SCHEMA_VALIDATION_REPORT(schema,document)
該函數(shù)會以JSON文檔的形式返回一個關(guān)于驗證結(jié)果的報告。如果驗證成功，返回{"valid": true}。如果JSON文檔驗證失敗，該函數(shù)將返回一個JSON對象，該對象包含下面列出的屬性:
- valid：false
- reason：失敗原因
- schema-location：校驗失敗的位置
- document-location：失敗位置
- schema-failed-keyword：關(guān)鍵字或?qū)傩悦?/li>
mysql> SET @schema = '{
'> "id": "http://json-schema.org/geo",
'> "$schema": "http://json-schema.org/draft-04/schema#",
'> "description": "A geographical coordinate",
'> "type": "object",
'> "properties": {
'> "latitude": {
'> "type": "number",
'> "minimum": -90,
'> "maximum": 90
'> },
'> "longitude": {
'> "type": "number",
'> "minimum": -180,
'> "maximum": 180
'> }
'> },
'> "required": ["latitude", "longitude"]
'>}';
Query OK, 0 rows affected (0.01 sec)
mysql> SET @document = '{
'> "latitude": 63.444697,
'> "longitude": 10.445118
'>}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true} |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET @document = '{
'> "latitude": 63.444697,
'> "longitude": 310.445118
'> }';
mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
"valid": false,
"reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'",
"schema-location": "#/properties/longitude",
"document-location": "#/longitude",
"schema-failed-keyword": "maximum"
}
1 row in set (0.00 sec)
mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
"valid": false,
"reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
"schema-location": "#",
"document-location": "#",
"schema-failed-keyword": "required"
}
1 row in set (0.00 sec)
mysql> SET @schema = '{
'> "id": "http://json-schema.org/geo",
'> "$schema": "http://json-schema.org/draft-04/schema#",
'> "description": "A geographical coordinate",
'> "type": "object",
'> "properties": {
'> "latitude": {
'> "type": "number",
'> "minimum": -90,
'> "maximum": 90
'> },
'> "longitude": {
'> "type": "number",
'> "minimum": -180,
'> "maximum": 180
'> }
'> }
'>}';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true} |
+---------------------------------------------------+
1 row in set (0.00 sec)
4、JSON_PRETTY()格式化輸出
語法：JSON_PRETTY(json_val)
將 JSON 格式化輸出。
SELECT JSON_PRETTY('123'); # scalar
+--------------------+
| JSON_PRETTY('123') |
+--------------------+
| 123 |
+--------------------+
SELECT JSON_PRETTY("[1,3,5]"); # array
+------------------------+
| JSON_PRETTY("[1,3,5]") |
+------------------------+
| [
1,
3,
5
] |
+------------------------+
SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
+---------------------------------------------+
| JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
+---------------------------------------------+
| {
"a": "10",
"b": "15",
"x": "25"
} |
+---------------------------------------------+
SELECT JSON_PRETTY('["a",1,{"key1":
"value1"},"5", "77" ,
{"key2":["value3","valueX",
"valueY"]},"j", "2" ]')\G # nested arrays and objects
*************************** 1. row ***************************
JSON_PRETTY('["a",1,{"key1":
"value1"},"5", "77" ,
{"key2":["value3","valuex",
"valuey"]},"j", "2" ]'): [
"a",
1,
{
"key1": "value1"
},
"5",
"77",
{
"key2": [
"value3",
"valuex",
"valuey"
]
},
"j",
"2"
]
5、JSON_STORAGE_FREE()計算空間
MySQL 8.0 新增的，與 Partial Updates 有關(guān)，用于計算 JSON 文檔在進行部分更新后的剩余空間。
CREATE TABLE jtable (jcol JSON);
INSERT INTO jtable VALUES ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
-- 更新，結(jié)果：{"a": 10, "b": "wxyz", "c": 1}
UPDATE jtable SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);
-- 結(jié)果：14
SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
-- 連續(xù)的部分更新對這個空閑空間的影響是累積的，如下例所示，使用JSON_SET()來減少具有鍵b的值所占用的空間(并且不做任何其他更改):
UPDATE jtable SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
-- 結(jié)果：16
SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
-- 不使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()更新列意味著優(yōu)化器不能就地執(zhí)行更新；在這種情況下，JSON_STORAGE_FREE()返回0，如下所示:
UPDATE jtable SET jcol = '{"a": 10, "b": 1}';
-- 結(jié)果：0
SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
-- JSON文檔的部分更新只能在列值上執(zhí)行。對于存儲JSON值的用戶變量，該值總是被完全替換，即使使用JSON_SET()執(zhí)行更新也是如此:
SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
SELECT @j, JSON_STORAGE_FREE(@j) AS Free; -- 結(jié)果：0
-- 對于JSON文本，該函數(shù)總是返回0:
SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free; -- 結(jié)果：0
6、JSON_STORAGE_SIZE()計算空間
語法：JSON_STORAGE_SIZE(json_val)
MySQL 5.7.22 引入的，用于計算 JSON 文檔的空間使用情況。
CREATE TABLE jtable (jcol JSON);
INSERT INTO jtable VALUES ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
SELECT jcol, JSON_STORAGE_SIZE(jcol) AS Size, JSON_STORAGE_FREE(jcol) AS Free FROM jtable;
+-----------------------------------------------+------+------+
| jcol | Size | Free |
+-----------------------------------------------+------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} | 47 | 0 |
+-----------------------------------------------+------+------+
1 row in set (0.00 sec)
UPDATE jtable SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
SELECT jcol, JSON_STORAGE_SIZE(jcol) AS Size, JSON_STORAGE_FREE(jcol) AS Free FROM jtable;
+------------------------------------------------+------+------+
| jcol | Size | Free |
+------------------------------------------------+------+------+
| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} | 56 | 0 |
+------------------------------------------------+------+------+
1 row in set (0.00 sec)
-- json文本顯示占用存儲空間
SELECT JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
+----+----+----+----+
| A | B | C | D |
+----+----+----+----+
| 45 | 44 | 47 | 56 |
+----+----+----+----+
1 row in set (0.00 sec)
八、JSON字段創(chuàng)建索引
同 TEXT，BLOB 字段一樣，JSON 字段不允許直接創(chuàng)建索引。
即使支持，實際意義也不大，因為我們一般是基于文檔中的元素進行查詢，很少會基于整個 JSON 文檔。
對文檔中的元素進行查詢，就需要用到 MySQL 5.7 引入的虛擬列及函數(shù)索引。
# C2 即虛擬列
# index (c2) 對虛擬列添加索引。
create table t ( c1 json, c2 varchar(10) as (JSON_UNQUOTE(c1 -> "$.name")), index (c2) );
insert into t (c1) values ('{"id": 1, "name": "a"}'), ('{"id": 2, "name": "b"}'), ('{"id": 3, "name": "c"}'), ('{"id": 4, "name": "d"}');
mysql> explain select * from t where c2 = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t | NULL | ref | c2 | c2 | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t where c1->'$.name' = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t | NULL | ref | c2 | c2 | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可以看到，無論是使用虛擬列，還是文檔中的元素來查詢，都可以利用上索引。
注意，在創(chuàng)建虛擬列時需指定 JSON_UNQUOTE，將 c1 -> “$.name” 的返回值轉(zhuǎn)換為字符串。
參考文檔
- https://dev.mysql.com/doc/refman/8.0/en/json.html
- https://blog.csdn.net/java_faep/article/details/125206014
- https://zhuanlan.zhihu.com/p/514819634?utm_id=0
- https://blog.csdn.net/sinat_20938225/article/details/129471550
- GeoJSON：https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html
- json方法：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
- json索引：https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index
- json多值索引：https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued
總結(jié)
到此這篇關(guān)于mysql如何處理json格式的字段的文章就介紹到這了,更多相關(guān)mysql解析json數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL?InnoDB?Cluster搭建安裝教程,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧2024-01-01
Mysql 5.7.18安裝方法及啟動MySQL服務的過程詳解
這篇文章主要介紹了Mysql 5.7.18安裝方法及啟動MySQL服務的過程,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2017-05-05
這篇文章主要給大家介紹了關(guān)于MySQL中索引失效的常見場景與規(guī)避的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-12-12
這篇文章主要介紹了查找MySQL中查詢慢的SQL語句方法,需要的朋友可以參考下2017-05-05
在使用MySQL的過程中,相比大家都踩過不少坑,下面這篇文章主要給大家介紹了關(guān)于Mysql常見bug及解決方案超詳細講解的相關(guān)資料,文中介紹的非常詳細,需要的朋友可以參考下2023-06-06
Mysql分組查詢每組最新一條數(shù)據(jù)的三種實現(xiàn)方法
我們在開發(fā)中經(jīng)常會遇到分組查詢最新數(shù)據(jù)的問題,下面這篇文章主要給大家介紹了關(guān)于Mysql分組查詢每組最新一條數(shù)據(jù)的三種實現(xiàn)方法,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-01-01
MySQL之DATE_ADD()和DATE_SUB()函數(shù)的使用方式
這篇文章主要介紹了MySQL之DATE_ADD()和DATE_SUB()函數(shù)的使用方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
這是由一則生產(chǎn)環(huán)境問題引出的MySQL對于datetime時間類型字段中毫秒的處理的深究,這篇文章主要給大家介紹了關(guān)于MySQL中datetime時間字段的四舍五入操作的相關(guān)資料,需要的朋友可以參考下2021-09-09
下面小編就為大家?guī)硪黄狹ysql inner join on的用法實例(必看)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
這篇文章主要介紹了MySQL性能突然下降的原因，幫助大家更好的了解和維護數(shù)據(jù)庫，感興趣的朋友可以了解下2020-10-10