一文搞懂mysql如何處理json格式的字段(解析json數(shù)據(jù))
更新時(shí)間：2023年12月04日 09:32:42 作者：禿了也弱了。
這篇文章主要給大家介紹了關(guān)于mysql如何處理json格式的字段的相關(guān)資料,MySQL中的JSON類型是一種數(shù)據(jù)類型,用于存儲(chǔ)和處理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版本中增加了對(duì)JSON類型的索引支持?？梢允褂肅REATE INDEX語句創(chuàng)建JSON類型的索引，提高JSON類型數(shù)據(jù)的查詢效率。
存儲(chǔ)JSON文檔所需的空間與存儲(chǔ)LONGBLOB或LONGTEXT所需的空間大致相同。
在MySQL 8.0.13之前，JSON列不能有非空的默認(rèn)值。
JSON 類型比較適合存儲(chǔ)一些列不固定、修改較少、相對(duì)靜態(tài)的數(shù)據(jù)。MySQL支持JSON格式的數(shù)據(jù)之后，可以減少對(duì)非關(guān)系型數(shù)據(jù)庫的依賴。
3、varchar、text、json類型字段的區(qū)別
這三種類型的字段，都可以存儲(chǔ)json格式，查詢起來似乎正常的json函數(shù)也能用，這三者存儲(chǔ)json類型的數(shù)據(jù)有什么區(qū)別嗎？
我們接下來測(cè)試一下。
二、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 '測(cè)試'; -- 修改字段類型為json ALTER TABLE users modify test_json JSON DEFAULT NULL COMMENT '測(cè)試'; -- 刪除json字段 ALTER TABLE users DROP COLUMN test_json;
三、JSON類型的插入
1、字符串直接插入
varchar、text、json格式都支持，也可以插入更復(fù)雜的嵌套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"]');
-- 插入對(duì)象
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格式的字段，插入時(shí)會(huì)自動(dòng)校驗(yàn)格式，如果格式不是json的，會(huì)報(bào)錯(cuò)：
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ù)插入對(duì)象
對(duì)于 JSON 文檔，KEY 名不能重復(fù)。
如果插入的值中存在重復(fù) KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原則，會(huì)保留第一個(gè) KEY，后面的將被丟棄掉。
從 MySQL 8.0.3 開始，遵循的是 last duplicate key wins 原則，只會(huì)保留最后一個(gè) KEY。
-- 格式：
JSON_OBJECT([key, val[, key, val] ...])
-- 創(chuàng)建對(duì)象，一個(gè)key對(duì)應(yīng)一個(gè)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ù)會(huì)從 JSON 文檔提取指定路徑（path）的元素。如果指定 path 不存在，會(huì)返回 NULL?？芍付ǘ鄠€(gè) path，匹配到的多個(gè)值會(huì)以數(shù)組形式返回。
-- 解析數(shù)組
-- 取下標(biāo)為1的數(shù)組值（數(shù)組下標(biāo)從0開始），結(jié)果：20
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
-- 取多個(gè)，結(jié)果返回是一個(gè)數(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 代表最后一個(gè)元素的下標(biāo)，結(jié)果：[20, [30, 40]]
select json_extract('[10, 20, [30, 40]]', '$[last-1 to last]');
-- 解析對(duì)象：對(duì)象的路徑是通過 KEY 來表示的。
set @j='{"a": 1, "b": [2, 3], "a c": 4}';
-- 如果 KEY 在路徑表達(dá)式中不合法（譬如存在空格），則在引用這個(gè) KEY 時(shí)，需用雙引號(hào)括起來。
-- 結(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，都是語法糖，在實(shí)際使用的時(shí)候都會(huì)在底層自動(dòng)轉(zhuǎn)化為 JSON_EXTRACT。
column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一個(gè)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 字符串，主要是對(duì)一些特殊字符（如雙引號(hào)）進(jìn)行轉(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ù)解析完之后，去除引號(hào)。
JSON_UNQUOTE()特殊字符轉(zhuǎn)義表：
| 轉(zhuǎn)義序列 | 由序列表示的字符 |
|---|---|
| \" | 雙引號(hào) |
| \b | 退格字符 |
| \f | 換頁字符 |
| \n | 換行符 |
| \r | 回車符 |
| \t | 制表符 |
| \\ | 反斜杠(\)字符 |
| \uXXXX | Unicode XXXX 轉(zhuǎn)UTF-8 |
insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
-- 字符串類型轉(zhuǎn)換后會(huì)去掉引號(hào)，結(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 字符串會(huì)用雙引號(hào)引起來，加了 JSON_UNQUOTE 就沒有。但本質(zhì)上，前者是 JSON 中的 STRING 類型，后者是 MySQL 中的字符類型，這一點(diǎn)可通過 JSON_VALID 來判斷。
4、->>箭頭解析json
同 column->path 類似，只不過其返回的是字符串，相當(dāng)于將字符串的雙引號(hào)去掉了，是一個(gè)語法糖，本質(zhì)上是執(zhí)行了JSON_UNQUOTE( JSON_EXTRACT(column, path) )。
以下三者是等價(jià)的：
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 是任意一個(gè)路徑存在就返回 1，all 是所有路徑都存在才返回 1。
函數(shù)前加not可取反
SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
-- a或者e 存在一個(gè)就返回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是一個(gè)數(shù)組，$[1]判斷第二個(gè)數(shù)據(jù)是否存在，結(jié)果為1
select JSON_CONTAINS_PATH(@j, 'one', '$[1]');
-- $[11]判斷第11個(gè)數(shù)據(jù)不存在，結(jié)果為0
select JSON_CONTAINS_PATH(@j, 'one', '$[11]');
3、JSON_KEYS()獲取keys
返回 JSON 文檔最外層的 key，如果指定了 path，則返回該 path 對(duì)應(yīng)元素最外層的 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()比較兩個(gè)json
MySQL 8.0.17 引入的，用來比較兩個(gè) JSON 文檔是否有相同的鍵值對(duì)或數(shù)組元素，如果有，則返回 1，否則是 0。 如果兩個(gè)參數(shù)都是標(biāo)量，則判斷這兩個(gè)標(biāo)量是否相等。
函數(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]]');
-- 比較對(duì)象時(shí)，如果它們至少有一個(gè)共同的鍵值對(duì)，則結(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}');
-- 如果兩個(gè)標(biāo)量用作函數(shù)的參數(shù)，JSON_OVERLAPS()會(huì)執(zhí)行一個(gè)簡(jiǎn)單的相等測(cè)試:
-- 結(jié)果：1
SELECT JSON_OVERLAPS('5', '5');
-- 結(jié)果：0
SELECT JSON_OVERLAPS('5', '6');
-- 當(dāng)比較標(biāo)量和數(shù)組時(shí)，JSON_OVERLAPS()試圖將標(biāo)量視為數(shù)組元素。在此示例中，第二個(gè)參數(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] …])
返回某個(gè)字符串（search_str）在 JSON 文檔中的位置，其中，
- one_or_all：匹配的次數(shù)，one 是只匹配一次，all 是匹配所有。如果匹配到多個(gè)，結(jié)果會(huì)以數(shù)組的形式返回。
- search_str：子串，支持模糊匹配：% 和 _ 。
- escape_char：轉(zhuǎn)義符，如果該參數(shù)不填或?yàn)?NULL，則取默認(rèn)轉(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：返回值的類型，不指定，則默認(rèn)是 VARCHAR(512)。不指定字符集，則默認(rèn)是 utf8mb4，且區(qū)分大小寫。
- on_empty：如果指定路徑?jīng)]有值，會(huì)觸發(fā) on_empty 子句， 默認(rèn)是返回 NULL，也可指定 ERROR 拋出錯(cuò)誤，或者通過 DEFAULT value 返回默認(rèn)值。
- on_error：三種情況下會(huì)觸發(fā) on_error 子句：從數(shù)組或?qū)ο笾刑崛≡貢r(shí)，會(huì)解析到多個(gè)值；類型轉(zhuǎn)換錯(cuò)誤，譬如將 “abc” 轉(zhuǎn)換為 unsigned 類型；值被 truncate 了。默認(rèn)是返回 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定義返回?cái)?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ù)組的一個(gè)元素，如果是，則返回 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í)行必要的強(qiáng)制轉(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文檔，則會(huì)出現(xiàn)錯(cuò)誤。
對(duì)于空數(shù)組，空對(duì)象，標(biāo)量值，其深度為 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()獲取文檔長(zhǎng)度
語法：JSON_LENGTH(json_doc[, path])
返回 JSON 文檔的長(zhǎng)度，其計(jì)算規(guī)則如下：
- 1、如果是標(biāo)量值，其長(zhǎng)度為 1。
- 2、如果是數(shù)組，其長(zhǎng)度為數(shù)組元素的個(gè)數(shù)。
- 3、如果是對(duì)象，其長(zhǎng)度為對(duì)象元素的個(gè)數(shù)。
- 4、不包括嵌套數(shù)據(jù)和嵌套對(duì)象的長(zhǎng)度。
-- 結(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值，則會(huì)出現(xiàn)錯(cuò)誤。
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（對(duì)象）、ARRAY（數(shù)組）、BOOLEAN（布爾類型）、NULL
- 數(shù)字類型：INTEGER（TINYINT、SMALLINT、MEDIUMINT以及INT和BIGINT標(biāo)量）、DOUBLE（DOUBLE、FLOAT）、DECIMAL（MySQL、DECIMAL）
- 時(shí)間類型：DATETIME（DATETIME、TIMESTAMP）、DATE、TIME
- 字符串類型：STRING（CHAR, VARCHAR, TEXT, ENUM, SET）
- 二進(jìn)制類型：BLOB（ BINARY, VARBINARY, BLOB, BIT）
- 其他類型：OPAQUE
11、JSON_VALID()校驗(yàn)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中，這個(gè)函數(shù)被命名為JSON_APPEND()。MySQL 8.0不再支持該名稱。
SET @j = '["a", ["b", "c"], "d"]';
-- 在數(shù)組第二個(gè)元素的數(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);
-- 多個(gè)參數(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]]';
-- 在下標(biāo)1處添加元素x，結(jié)果：["a", "x", {"b": [1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
-- 沒有100個(gè)元素，在最后插入，結(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');
-- 早期的修改會(huì)影響數(shù)組中后續(xù)元素的位置，因此同一個(gè)JSON_ARRAY_INSERT()調(diào)用中的后續(xù)路徑應(yīng)該考慮這一點(diǎn)。在最后一個(gè)示例中，第二個(gè)路徑?jīng)]有插入任何內(nèi)容，因?yàn)樵诘谝淮尾迦胫螅撀窂讲辉倨ヅ淙魏蝺?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)存在的不修改。
僅當(dāng)指定位置或指定 KEY 的值不存在時(shí)，才執(zhí)行插入操作。另外，如果指定的 path 是數(shù)組下標(biāo)，且 json_doc 不是數(shù)組，該函數(shù)首先會(huì)將 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是一個(gè)帶引號(hào)的字符串，想要插入一個(gè)數(shù)組，必須進(jìn)行轉(zhuǎn)換，結(jié)果：{"a": 1, "b": [2, 3], "c": [true, false]}
SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON));
-- 下標(biāo)0位置已經(jīng)有值了，不會(huì)插入，結(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] …)
合并兩個(gè)或多個(gè)JSON文檔。JSON_MERGE_PRESERVE()的同義詞；在MySQL 8.0.3中已棄用，在未來版本中可能會(huì)被刪除。
推薦使用JSON_MERGE_PRESERVE()
-- 結(jié)果：[1, 2, true, false]
SELECT JSON_MERGE('[1, 2]', '[true, false]');
6、JSON_MERGE_PATCH()合并json
MySQL 8.0.3 引入的，用來合并多個(gè) JSON 文檔。其合并規(guī)則如下：
1、如果兩個(gè)文檔不全是 JSON 對(duì)象，則合并后的結(jié)果是第二個(gè)文檔。
2、如果兩個(gè)文檔都是 JSON 對(duì)象，且不存在著同名 KEY，則合并后的文檔包括兩個(gè)文檔的所有元素，如果存在著同名 KEY，則第二個(gè)文檔的值會(huì)覆蓋第一個(gè)。
-- 不是對(duì)象，結(jié)果：[true, false]
SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
-- 都是對(duì)象，結(jié)果：{"id": 47, "name": "x"}
SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
-- 都不是對(duì)象，取第二個(gè)，結(jié)果：true
SELECT JSON_MERGE_PATCH('1', 'true');
-- 第一個(gè)不是對(duì)象，取第二個(gè) ，結(jié)果：{"id": 47}
SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
-- 第二個(gè)覆蓋第一個(gè)，結(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 }');
-- 第二個(gè)有null，會(huì)刪除該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、兩個(gè)文檔中，只要有一個(gè)文檔是數(shù)組，則另外一個(gè)文檔會(huì)合并到該數(shù)組中。
2、兩個(gè)文檔都是 JSON 對(duì)象，若存在著同名 KEY ，第二個(gè)文檔并不會(huì)覆蓋第一個(gè)，而是會(huì)將值 append 到第一個(gè)文檔中。
-- 數(shù)組合并，結(jié)果：[1, 2, true, false]
SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
-- 對(duì)象合并，結(jié)果：{"id": 47, "name": "x"}
SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
-- 兩個(gè)常量，合并為一個(gè)數(shù)組，結(jié)果：[1, true]
SELECT JSON_MERGE_PRESERVE('1', 'true');
-- 對(duì)象合并到數(shù)組中，結(jié)果：[1, 2, {"id": 47}]
SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
-- 相同的key合并到一個(gè)數(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"]';
-- 刪除下標(biāo)為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"]';
-- 刪除多個(gè)元素，刪除1下標(biāo)之后，下標(biāo)移動(dòng)結(jié)果之后再刪除下標(biāo)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]}';
-- 對(duì)象替換，結(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 的值不存在，會(huì)執(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支持這樣一個(gè)函數(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()驗(yàn)證json
語法：JSON_SCHEMA_VALID(schema,document)
判斷 document （ JSON 文檔 ）是否滿足 schema （ JSON 對(duì)象）定義的規(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 對(duì)象。
2、JSON 對(duì)象必需的兩個(gè)屬性是 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()查看驗(yàn)證報(bào)告
語法：JSON_SCHEMA_VALIDATION_REPORT(schema,document)
該函數(shù)會(huì)以JSON文檔的形式返回一個(gè)關(guān)于驗(yàn)證結(jié)果的報(bào)告。如果驗(yàn)證成功，返回{"valid": true}。如果JSON文檔驗(yàn)證失敗，該函數(shù)將返回一個(gè)JSON對(duì)象，該對(duì)象包含下面列出的屬性:
- valid：false
- reason：失敗原因
- schema-location：校驗(yàn)失敗的位置
- 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()計(jì)算空間
MySQL 8.0 新增的，與 Partial Updates 有關(guān)，用于計(jì)算 JSON 文檔在進(jìn)行部分更新后的剩余空間。
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ù)的部分更新對(duì)這個(gè)空閑空間的影響是累積的，如下例所示，使用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í)行。對(duì)于存儲(chǔ)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
-- 對(duì)于JSON文本，該函數(shù)總是返回0:
SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free; -- 結(jié)果：0
6、JSON_STORAGE_SIZE()計(jì)算空間
語法：JSON_STORAGE_SIZE(json_val)
MySQL 5.7.22 引入的，用于計(jì)算 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文本顯示占用存儲(chǔ)空間
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)建索引。
即使支持，實(shí)際意義也不大，因?yàn)槲覀円话闶腔谖臋n中的元素進(jìn)行查詢，很少會(huì)基于整個(gè) JSON 文檔。
對(duì)文檔中的元素進(jìn)行查詢，就需要用到 MySQL 5.7 引入的虛擬列及函數(shù)索引。
# C2 即虛擬列
# index (c2) 對(duì)虛擬列添加索引。
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)建虛擬列時(shí)需指定 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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL?InnoDB?Cluster搭建安裝教程,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧2024-01-01
Mysql 5.7.18安裝方法及啟動(dòng)MySQL服務(wù)的過程詳解
這篇文章主要介紹了Mysql 5.7.18安裝方法及啟動(dòng)MySQL服務(wù)的過程,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2017-05-05
MySQL中索引失效的常見場(chǎng)景與規(guī)避方法
這篇文章主要給大家介紹了關(guān)于MySQL中索引失效的常見場(chǎng)景與規(guī)避的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
這篇文章主要介紹了查找MySQL中查詢慢的SQL語句方法,需要的朋友可以參考下2017-05-05
在使用MySQL的過程中,相比大家都踩過不少坑,下面這篇文章主要給大家介紹了關(guān)于Mysql常見bug及解決方案超詳細(xì)講解的相關(guān)資料,文中介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06
Mysql分組查詢每組最新一條數(shù)據(jù)的三種實(shí)現(xiàn)方法
我們?cè)陂_發(fā)中經(jīng)常會(huì)遇到分組查詢最新數(shù)據(jù)的問題,下面這篇文章主要給大家介紹了關(guān)于Mysql分組查詢每組最新一條數(shù)據(jù)的三種實(shí)現(xiàn)方法,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-01-01
MySQL之DATE_ADD()和DATE_SUB()函數(shù)的使用方式
這篇文章主要介紹了MySQL之DATE_ADD()和DATE_SUB()函數(shù)的使用方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-04-04
MySQL中datetime時(shí)間字段的四舍五入操作
這是由一則生產(chǎn)環(huán)境問題引出的MySQL對(duì)于datetime時(shí)間類型字段中毫秒的處理的深究,這篇文章主要給大家介紹了關(guān)于MySQL中datetime時(shí)間字段的四舍五入操作的相關(guān)資料,需要的朋友可以參考下2021-09-09
Mysql inner join on的用法實(shí)例(必看)
下面小編就為大家?guī)硪黄狹ysql inner join on的用法實(shí)例(必看)。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-03-03
這篇文章主要介紹了MySQL性能突然下降的原因，幫助大家更好的了解和維護(hù)數(shù)據(jù)庫，感興趣的朋友可以了解下2020-10-10