欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Mysql中Json相關(guān)的函數(shù)使用

 更新時間：2023年06月08日 14:03:44   作者：怪?咖@  
本文主要介紹了Mysql當中Json相關(guān)的函數(shù)使用，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

一、前言

從5.7開始，MySQL開始支持json類型，用于存儲JSON數(shù)據(jù)。提供數(shù)據(jù)類型的同時也提供了很多關(guān)于json的函數(shù)供我們使用，本篇文章基本上都來源于官網(wǎng);https://dev.mysql.com/doc/refman/8.0/ja/json-function-reference.html

二、創(chuàng)建JSON文本的函數(shù)

2.1.JSON_ARRAY（轉(zhuǎn)換json數(shù)組）

  • 描述：JSON_ARRAY()接受一個值列表(可能為NULL)，并返回一個包含這些值的JSON數(shù)組
  • 語法：JSON_ARRAY([val[, val] ...])

參數(shù) val 表示輸入的值。

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "10:48:25.000000"]   |
+---------------------------------------------+
1 row in set (0.05 sec)
mysql> SELECT JSON_ARRAY(null);
+------------------+
| JSON_ARRAY(null) |
+------------------+
| [null]           |
+------------------+
1 row in set (0.00 sec)

2.2.JSON_OBJECT（轉(zhuǎn)換json對象）

  • 描述：JSON_OBJECT()接受一個鍵值對(可以為空)列表，并返回一個包含這些鍵值對的JSON對象
  • 語法：JSON_OBJECT([key, val[, key, val] ...])

key, val 表示一個鍵值對。如果參數(shù)數(shù)量為奇數(shù)，則會發(fā)生報錯。

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+

2.3.JSON_QUOTE（轉(zhuǎn)義字符串）

  • 描述：JSON_QUOTE()通過用雙引號字符包裹 字符串 并轉(zhuǎn)義內(nèi)部引號和其他字符，將字符串引用為 JSON 值，然后將結(jié)果作為 utf8mb4 字符串返回。如果參數(shù)為NULL則返回NULL。
  • 語法：JSON_QUOTE(string)

string 表示要引用的字符串，參數(shù)要用單引號括住，如果參數(shù)為 NULL，則返回 NULL。

mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
| "null"             | "\"null\""           |
+--------------------+----------------------+
mysql> SELECT JSON_QUOTE('[1, 2, 3]');
+-------------------------+
| JSON_QUOTE('[1, 2, 3]') |
+-------------------------+
| "[1, 2, 3]"             |
+-------------------------+

轉(zhuǎn)義字符一般以反斜杠符號\開頭，用來說明后面的字符不是字符本身的含義，而是表示其它的含義。MySQL 中常見的轉(zhuǎn)義字符如下表所示：

三、搜索JSON文本的函數(shù)

3.1.JSON_CONTAINS（json當中是否包含指定value）

  • 描述：該函數(shù)用于檢驗指定 JSON 文檔是否包含在目標 JSON 文檔中，或者是否在目標文檔的指定路徑上找到指定元素（如果提供了 path參數(shù)）。如果指定 JSON 文檔包含在目標 JSON 文檔中，該函數(shù)返回 1，否則返回 0。
  • 語法：JSON_CONTAINS(target, candidate[, path])

參數(shù) target 表示目標 JSON 文檔，參數(shù) candidate 用于指定 JSON 文檔。
如果任意參數(shù)為 NULL，或者 path 參數(shù)未被識別為目標文檔的一部分，則返回 NULL。

注意：target和candidate參數(shù)要使用單引號括住

以下情況都會發(fā)生報錯：

  • candidate 或 target 參數(shù)不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • path 參數(shù)包含 * 或 ** 通配符。

比較的對象：

  • 如果兩個json都是數(shù)值類型，可以直接進行比較。
  • 對于數(shù)組，當 candidate 數(shù)組的所有元素都包含在 target 數(shù)組中時，才會返回1
  • 對于對象，當 candidate 對象的所有 key 都需要包含在 target 對象中，而且每個 key 對應(yīng)的 value 也要和目標對象對應(yīng)的 key 相同時，candidate 對象才算包含在 target 對象中。
mysql> SELECT JSON_CONTAINS( '{ "a" : 1, "b" : "2", "c" : { "d" : 4 }}', '1', '$.a' ) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS( '{ "a" : 1, "b" : "2", "c" : { "d" : 4 }}', '"2"', '$.b' ) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS( '{ "a" : 1, "b" : 2, "c" : { "d" : 4 }}', '{ "d" : 4 }', '$.c' ) as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

判斷json數(shù)據(jù)類型當中的數(shù)組，是否包含某個元素：

實戰(zhàn)當中可使用：SELECT json_contains( CAST( declaration_info -> '$.intellectual.intellectualProperty' AS JSON ), json_array( 2 ) from 表;

3.2.JSON_CONTAINS_PATH（是否包含某個PATH）

  • 描述：該函數(shù)用于檢驗一個或多個指定的路徑（假如json是對象，那這里的路徑就是指的json當中的key值）是否包含于目標 JSON 文檔中。如果包含在目標 JSON 文檔中，該函數(shù)返回 1，否則返回 0。
  • 語法：JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

參數(shù) json_doc 用于指定 JSON 文檔，path 為路徑參數(shù)。如果任何參數(shù)為 NULL，則返回 NULL。
對于 one_or_all 參數(shù)，可選值如下：

  • ‘one’：如果文檔中至少存在一個路徑，則返回 1，否則返回 0。
  • ‘all’：如果文檔中存在所有路徑，則返回 1，否則返回 0。

以下情況都會發(fā)生報錯：

  • json_doc 參數(shù)不是有效的 JSON 文檔。
  • 任意 path 參數(shù)不是有效的路徑表達式。
  • one_or_all 參數(shù)不為 ‘one’ 或者 ‘all’
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

3.3.JSON_EXTRACT 和 -> （根據(jù)key取值）

  • 描述：該函數(shù)用于從 JSON 文檔中指定的路徑返回數(shù)據(jù)。如果任何參數(shù)為 NULL 或在文檔中沒有定位到路徑，則返回 NULL。
  • 語法：JSON_EXTRACT(json_doc, path[, path] ...)
  • -> 跟 JSON_EXTRACT 函數(shù) 都可以用來根據(jù)key值取value，他兩個其實就是等價的。
  • -> 語法：（5.7.9版本開始支持的）：json列名->"path" （這個好像是不能直接使用json的，只能使用存儲好json的列名）
  • path 參數(shù)，還可通過 .* 獲取對象中的所有元素。

參數(shù) json_doc 用于指定 JSON 文檔，path 為路徑參數(shù)。

如果參數(shù) json_doc 不是有效的 JSON 文檔或任意 path 參數(shù)不是有效的路徑表達式，則會發(fā)生錯誤。

返回值由 path 參數(shù)的所有匹配值組成。如果這些參數(shù)返回多個值，則匹配值將自動包裹為一個數(shù)組，順序與生成它們的路徑相對應(yīng)。否則，返回單個匹配值。

這個是實際開發(fā)當中重點要使用的，所以我著重記錄一下這塊，下面我會頻繁使用該json數(shù)據(jù)作為測試：

{"testFee": "3.00", "developList": [{"indicators": "的地方北方熱", "annualTarget": "發(fā)熱的隨訪人", "annualCompletion": "發(fā)VG熱娃娃"}, {"indicators": "1111", "annualTarget": "2222", "annualCompletion": "3333"}], "fundBalance": {"totalFunds": "3.00", "totalProject": "3.00", "totalExpenses": "3.00"}}

然后新增的一個test表，有兩個字段，json1一個是json類型的，另一個是longtext類型的，但是存儲的數(shù)據(jù)都是上面那個json，主要也是想看一下，兩個字段都存json是否都可以使用json函數(shù)：

取普通的value（事實證明longtext類型存儲的json數(shù)據(jù)也是可以使用json函數(shù)的）

取對象嵌套的value

根據(jù)數(shù)組下標取值，如果不帶下標[0]就是取的整個數(shù)組，下標是從0開始的,[0]就是取的數(shù)組第一條數(shù)據(jù)

取指定數(shù)組當中的value值

多維數(shù)組（實際開發(fā)當中應(yīng)該很少會用到）

obclient> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][1]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][1]') |
+-----------------------------------------------+
| 40                                            |
+-----------------------------------------------+
1 row in set

以上都是對查詢做的測試，實際上不僅僅可以用于查詢，還可以作為條件，這里就不測試了哈

注意：這種寫法是錯誤的

mysql> SELECT '{"testFee": "3.00"}' -> '$.testFee';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-> '$.testFee'' at line 1

3.4.JSON_UNQUOTE(JSON_EXTRACT()) 和 ->> （無引號提?。?/h3>

通過上面的取值會發(fā)現(xiàn)我們?nèi)〕鰜淼闹刀紟в须p引號：

mysql也給我們提供了專門的函數(shù)用于去除引號：

  • ->> （5.7.13版本開始支持的）語法：json列名->>"path"
  • 語法：JSON_UNQUOTE(JSON_EXTRACT(json_doc, path[, path] ...)
  • ->>等同于JSON_UNQUOTE(JSON_EXTRACT())
  • JSON_UNQUOTE不僅僅是配合JSON_EXTRACT使用，他的作用主要是去除雙引號

那會剛用的時候不知道有這個函數(shù)，于是便是用了trim()函數(shù)來去除的引號：trim函數(shù)用于刪除字符串str頭或尾的指定字符串remstr，通常用來移除字符串首尾兩端的空格

– 完整格式: TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
– testxx (刪除指定的首字符 x)
SELECT TRIM(LEADING ‘x’ FROM ‘xxtestxx’);
– test (刪除指定的首尾字符 x)
SELECT TRIM(BOTH ‘x’ FROM ‘xxtestxx’);
– xxtest (刪除指定的尾字符 x)
SELECT TRIM(TRAILING ‘x’ FROM ‘xxtestxx’);
– testx (刪除指定的尾字符 xyz)
SELECT TRIM(TRAILING ‘xyz’ FROM ‘testxxyz’);

3.5.JSON_KEYS（獲取json當中key數(shù)組）

  • 描述：該函數(shù)從 JSON 對象的頂層值開始搜索，返回對應(yīng)路徑的 Key 的數(shù)組。
  • 語法：JSON_KEYS(json_doc[, path])

參數(shù) json_doc 用于指定 JSON 文檔，path 為路徑參數(shù)。如果任何參數(shù)為 NULL，則返回 NULL。
如果 json_doc 參數(shù)指定的不是對象（指的是json數(shù)據(jù)類型當中的 對象），或者 path（如果給出）不對應(yīng)任何路徑，返回 NULL。

以下情況都會發(fā)生報錯：

  • json_doc 參數(shù) 不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • path 參數(shù)包含 * 或 ** 通配符。

如果選定的對象為空，則結(jié)果為空。如果頂層值嵌套了子對象，則返回值不包括來自這些子對象的 Key。

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"]                                        |
+----------------------------------------------+

3.6.JSON_OVERLAPS（判斷兩個json是否存在同樣的的key value）

  • 描述：該函數(shù)用于比較兩個 JSON 文檔。如果兩個文檔具有共同的鍵值對（key-value）或數(shù)組元素（不要求全部一樣，只要一個鍵值對一樣就可以），則返回 1，否則返回 0。
  • 語法：JSON_OVERLAPS(json_doc1, json_doc2)

參數(shù) json_doc1 和 json_doc2 分別指定兩個用于比較的 JSON 文檔。如果兩個參數(shù)都是標量，則函數(shù)執(zhí)行簡單的相等性測試。

該函數(shù)與 JSON_CONTAINS() 對應(yīng)，它要求搜索的數(shù)組的所有元素都存在于被搜索的數(shù)組中。因此，JSON_CONTAINS() 對搜索鍵執(zhí)行 AND 運算，而 JSON_OVERLAPS() 執(zhí)行 OR 運算。

在比較兩個數(shù)組時，如果它們共享一個或多個數(shù)組元素，則 JSON_OVERLAPS() 返回 1，否則返回 0。

在 WHERE 子句中使用 JSON_OVERLAPS() 對 InnoDB 表的 JSON 列的查詢，可以使用多值索引進行優(yōu)化。

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
+-----------------------------------------------------------------------+
| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') |
+-----------------------------------------------------------------------+
|                                                                     1 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

3.7.JSON_SEARCH（通過內(nèi)容找path，支持模糊查）

  • 描述：該函數(shù)返回 JSON 文檔中指定字符串的路徑。
  • 語法：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

參數(shù) json_doc 用于指定 JSON 文檔，search_str 為搜索字符串參數(shù)（支持%字符串%模糊查詢），escape_char 參數(shù)指定常量表達式，path 為路徑參數(shù)。

如果任何參數(shù)為 NULL，則返回 NULL；如果文檔中不存在路徑或未找到指定字符串，也會返回 NULL。

對于 one_or_all 參數(shù)，可選值如下：

  • ‘one’：在第一次匹配后搜索終止并返回一個路徑字符串。
  • ‘all’：搜索返回所有匹配的路徑字符串，不會包含重復的路徑。如果有多個字符串，它們會自動包裹為一個數(shù)組。數(shù)組元素的順序不保證是有序的。

對于 search_str 參數(shù)，% 和 _ 字符與 LIKE 運算符的作用相同，含義如下：

  • % 表示匹配任意數(shù)量的字符（包括零個字符）。
  • _ 表示正好匹配一個字符。

如果要在搜索字符串中指定 % 或 _ 字符，請在其前面加上轉(zhuǎn)義字符。如果缺少 escape_char 參數(shù)或者為NULL，則轉(zhuǎn)義字符默認值為 \。否則，escape_char 必須是一個空的常量或字符。

無論數(shù)據(jù)庫的編碼值為多少，search_str 和 path 總是作為 utf8mb4 字符串使用。

以下情況會發(fā)生報錯：

  • json_doc 參數(shù)指定的不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • one_or_all 參數(shù)不為 ‘one’ 或者 ‘all’。
  • escape_char 不是常量表達式。
mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+-------------------------------+
| NULL                          |
+-------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10');
+------------------------------+
| JSON_SEARCH(@j, 'all', '10') |
+------------------------------+
| "$[1][0].k"                  |
+------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
+-----------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
+-----------------------------------------+
| "$[1][0].k"                             |
+-----------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
+---------------------------------------------+
| "$[1][0].k"                                 |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
+-------------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
+-------------------------------------------------+
| "$[1][0].k"                                     |
+-------------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
+--------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
+--------------------------------------------+
| "$[1][0].k"                                |
+--------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
+-----------------------------------------------+
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
+-----------------------------------------------+
| "$[1][0].k"                                   |
+-----------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%b%') |
+-------------------------------+
| ["$[0]", "$[2].x", "$[3].y"]  |
+-------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
+---------------------------------------------+
| "$[0]"                                      |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
+---------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y"                                  |
+-------------------------------------------+

3.8.JSON_VALUE（根據(jù)key取值，如果沒找到可以給默認值，如果找到了可以轉(zhuǎn)換想要的數(shù)據(jù)類型）

  • 描述：該函數(shù)從指定路徑中提取 JSON 文檔中的值，并返回提取的值，還可以選擇將其轉(zhuǎn)換為所需數(shù)據(jù)類型。
  • 語法：
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

相關(guān)參數(shù)解釋如下：

  • json_doc 參數(shù)指定有效的 JSON 文檔。
  • path 參數(shù)是指向文檔中某個位置的 JSON 路徑，必須是字符串。
  • type 參數(shù)是支持如下數(shù)據(jù)類型：
    • FLOAT
    • DOUBLE
    • DECIMAL
    • SIGNED
    • UNSIGNED
    • DATE
    • TIME
    • DATETIME
    • YEAR (不支持 YEAR(1) 和 YEAR(2))
    • CHAR
    • JSON

如果沒有使用 RETURNING 子句，則該函數(shù)的返回類型為 VARCHAR(512)。
當沒有為返回類型指定字符集時，JSON_VALUE() 使用 utf8mb4 和二進制排序規(guī)則，并區(qū)分大小寫； 如果將 utf8mb4指定為結(jié)果的字符集，則服務(wù)器使用此字符集的默認排序規(guī)則，不用區(qū)分大小寫。

當在指定路徑中未找到數(shù)據(jù)時，on_empty 子句用于指定 JSON_VALUE() 的行為。該子句取值如下：

  • NULL ON EMPTY：JSON_VALUE() 返回 NULL。這是 ON EMPTY 的默認行為。
  • DEFAULT value ON EMPTY’：返回所提供的 value 值，該值的類型必須與返回類型相匹配。
  • ERROR ON EMPTY：函數(shù)拋出錯誤。

當發(fā)生錯誤時，on_error 取值如下：

  • NULL ON ERROR：JSON_VALUE() 返回 NULL；如果未使用 ON ERROR 子句，則這是默認行為。
  • DEFAULT value ON ERROR：返回所提供的 value 值，該值的類型必須與返回類型的值相匹配。
  • ERROR ON ERROR：拋出錯誤。
  • ON EMPTY 的位置（如果使用）必須在所有 ON ERROR 子句之前，錯誤的順序會導致語法錯誤。

通常，JSON_VALUE() 會檢查所有 JSON 輸入（文檔和路徑）的有效性。如果其中任何一個為無效，則會拋出 SQL 錯誤，而不觸發(fā) ON ERROR 子句。

如果發(fā)生以下某一事件，就會觸發(fā) ON ERROR：

  • 嘗試提取對象或數(shù)組時，輸入的路徑在 JSON 文檔中被解析為多個路徑。
  • 轉(zhuǎn)換錯誤。例如，嘗試將 ‘asdf’ 轉(zhuǎn)換為 UNSIGNED 值。
  • 數(shù)據(jù)截斷
mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.item');
+-------------------------------------------------------------+
| json_value('{"item": "shoes", "price": "49.95"}', '$.item') |
+-------------------------------------------------------------+
| shoes                                                       |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.price' returning decimal(4,2)) as price;
+-------+
| price |
+-------+
| 49.95 |
+-------+
1 row in set (0.00 sec)
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'

3.9.MEMBER OF（查看數(shù)組是否有某個元素）

  • 描述：如果被檢索的元素和 JSON 數(shù)組中的任意一個元素相同，則該函數(shù)返回 1，否則返回 0。
  • 語法：元素 value MEMBER OF(json_array)

如果 value 是 json_array 的元素，則返回 1，否則返回 0。
value 必須是標量或 JSON 文檔；如果它是標量，則運算符會將其視為 JSON 數(shù)組的元素。

mysql> SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');
+---------------------------------------------+
| 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT
    -> 17 MEMBER OF('[23, "abc", "17", "ab", 10]'),
    -> "17" MEMBER OF('[23, "abc", 17, "ab", 10]')\G
*************************** 1. row ***************************
17 MEMBER OF('[23, "abc", "17", "ab", 10]'): 0
"17" MEMBER OF('[23, "abc", 17, "ab", 10]'): 0
1 row in set (0.00 sec)
mysql> SELECT cast( '{ "indicators" : "的地方北方熱", "annualTarget" : "發(fā)熱的隨訪人", "annualCompletion" : "發(fā)VG熱娃娃" }' AS json ) MEMBER OF ('[{
    '> "indicators": "的地方北方熱",
    '> "annualTarget": "發(fā)熱的隨訪人",
    '> "annualCompletion": "發(fā)VG熱娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]') as result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

要對本身為數(shù)組的值使用此操作符，必須將其顯式轉(zhuǎn)換為JSON數(shù)組。你可以用CAST (…)為JsoN):

mysql> SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]');
+--------------------------------------------------+
| CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]') |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+
1 row in set (0.00 sec)

也可以使用JSON ARRAY()函數(shù)執(zhí)行必要的強制轉(zhuǎn)換，如下所示:

mysql> SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');
+--------------------------------------------+
| JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]') |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

任何用作要測試的值或出現(xiàn)在目標數(shù)組中的JSON對象都必須使用CAST強制轉(zhuǎn)換或JSON_OBJECT()。
此外，包含JSON對象的目標數(shù)組本身必須使用JSON_ARRAY進行強制轉(zhuǎn)換，下面的語句序列演示了這一點:

mysql> SELECT'{
    '> "indicators": "的地方北方熱",
    '> "annualTarget": "發(fā)熱的隨訪人",
    '> "annualCompletion": "發(fā)VG熱娃娃"
    '> }' MEMBER OF ( '[{
    '> "indicators": "的地方北方熱",
    '> "annualTarget": "發(fā)熱的隨訪人",
    '> "annualCompletion": "發(fā)VG熱娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]' ) AS result;
+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OBJECT( 'indicators', "的地方北方熱", "annualTarget", "發(fā)熱的隨訪人", "annualCompletion", "發(fā)VG熱娃娃" ) MEMBER OF ( '[{
    '> "indicators": "的地方北方熱",
    '> "annualTarget": "發(fā)熱的隨訪人",
    '> "annualCompletion": "發(fā)VG熱娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]' ) AS result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

四、修改JSON文本的函數(shù)

4.1.JSON_ARRAY_APPEND（在指定的數(shù)組位置末尾追加元素，假如指定的位置不是數(shù)組追加完過后就變成了數(shù)組）

  • 描述：該函數(shù)用于將指定的值附加到 JSON 文檔中指定數(shù)組的末尾并返回結(jié)果。
  • 語法：JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

json_doc 參數(shù)用于指定 JSON 文檔，path 為路徑參數(shù)。如果任何參數(shù)為 NULL，則返回 NULL。

以下情況都會發(fā)生報錯：

  • json_doc 參數(shù)不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • path 參數(shù)包含 * 或 ** 通配符。

如果路徑對應(yīng)的值是一個標量或?qū)ο笾?，則該值將自動包裹在數(shù)組中，并將新值添加到該數(shù)組中。

數(shù)組：

mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"]        |
+----------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[0]', 2) |
+----------------------------------+
| [["a", 2], ["b", "c"], "d"]      |
+----------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
+-------------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
+-------------------------------------+
| ["a", [["b", 3], "c"], "d"]         |
+-------------------------------------+

對象：

mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
+------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.b', 'x')  |
+------------------------------------+
| {"a": 1, "b": [2, 3, "x"], "c": 4} |
+------------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
+--------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.c', 'y')    |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "y"]} |
+--------------------------------------+
mysql> SET @j = '{"a": 1}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
+---------------------------------+
| JSON_ARRAY_APPEND(@j, '$', 'z') |
+---------------------------------+
| [{"a": 1}, "z"]                 |
+---------------------------------+
mysql> SELECT JSON_ARRAY_APPEND('{"a": 1}','$',JSON_OBJECT( 'z', 2 ));
+---------------------------------------------------------+
| JSON_ARRAY_APPEND('{"a": 1}','$',JSON_OBJECT( 'z', 2 )) |
+---------------------------------------------------------+
| [{"a": 1}, {"z": 2}]                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

4.2.JSON_ARRAY_INSERT（在指定的數(shù)組位置后追加元素）

  • 描述：該函數(shù)用于將數(shù)組插入文檔中并返回修改后的文檔。
  • 語法：JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

json_doc 參數(shù)用于指定 JSON 文檔，path 為路徑參數(shù)。如果任何參數(shù)為 NULL，則返回 NULL。

以下情況都會發(fā)生報錯：

  • json_doc 參數(shù)不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • path 參數(shù)包含 * 或 ** 通配符。

如果路徑識別了某一數(shù)組元素，則在該元素位置插入相應(yīng)的值，將所有后續(xù)值向右移動。如果路徑識別的數(shù)組位置超出數(shù)組末尾的位置，則將值插入到數(shù)組末尾。

mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]]  |
+------------------------------------+
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
+--------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[100]', 'x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"]    |
+--------------------------------------+
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
+-----------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
+-----------------------------------------+
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
+---------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]]     |
+---------------------------------------+
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
+----------------------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') |
+----------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]]                  |
+----------------------------------------------------+

假如數(shù)組元素是對象，這時候要插入一定要使用cast進行轉(zhuǎn)換一下，不然直接以字符串進行插入會出現(xiàn)問題，他會以為你讓他追加字符串！

mysql> SELECT JSON_ARRAY_INSERT( '[{
    '> "indicators": "的地方北方熱",
    '> "annualTarget": "發(fā)熱的隨訪人",
    '> "annualCompletion": "發(fā)VG熱娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]', '$[3]', cast( '{"indicators":"999","annualTarget":"999","annualCompletion":"999"}' AS json ) ) AS result FROM test;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                                                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"indicators": "的地方北方熱", "annualTarget": "發(fā)熱的隨訪人", "annualCompletion": "發(fā)VG熱娃娃"}, 
{"indicators": "1111", "annualTarget": "2222", "annualCompletion": "3333"}, 
{"indicators": "999", "annualTarget": "999", "annualCompletion": "999"}] |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT( '[{
    '> "indicators": "的地方北方熱",
    '> "annualTarget": "發(fā)熱的隨訪人",
    '> "annualCompletion": "發(fā)VG熱娃娃"
    '> }, {
    '> "indicators": "1111",
    '> "annualTarget": "2222",
    '> "annualCompletion": "3333"
    '> }]', '$[3]',  '{"indicators":"999","annualTarget":"999","annualCompletion":"999"}' ) AS result FROM test;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"indicators": "的地方北方熱", "annualTarget": "發(fā)熱的隨訪人", "annualCompletion": "發(fā)VG熱娃娃"}, 
{"indicators": "1111", "annualTarget": "2222", "annualCompletion": "3333"},
"{\"indicators\":\"999\",\"annualTarget\":\"999\",\"annualCompletion\":\"999\"}"] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

4.3.JSON_INSERT（向指定path添加元素，path有值不覆蓋，沒值就添加）

  • 描述：該函數(shù)將數(shù)據(jù)插入 JSON 文檔并返回結(jié)果。
  • 語法：JSON_INSERT(json_doc, path, val[, path, val] ...)

json_doc 參數(shù)用于指定 JSON 文檔，path 為路徑參數(shù)。如果任何參數(shù)為 NULL，則返回 NULL。

以下情況都會發(fā)生報錯：

  • json_doc 參數(shù)不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • path 參數(shù)包含 * 或 ** 通配符。
mysql> SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+--------------------------------------------------------------------------+
| JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
+--------------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}                              |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT JSON_INSERT( '{ "a": 1, "b": [2, 3],"c":666}', '$.a', 10, '$.c', 999 );
+------------------------------------------------------------------------+
| JSON_INSERT( '{ "a": 1, "b": [2, 3],"c":666}', '$.a', 10, '$.c', 999 ) |
+------------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": 666}                                        |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.5.JSON_MERGE（多個json合并）

  • 描述：該函數(shù)用于合并多個 JSON 文檔。該函數(shù)是 JSON_MERGE_PRESERVE() 的同義詞。
  • 語法：JSON_MERGE(json_doc, json_doc[, json_doc] ...)
obclient> SELECT JSON_MERGE('[1, 2, 3]', '[true, false]', '{"a":100}');
+-------------------------------------------------------+
| JSON_MERGE('[1, 2, 3]', '[true, false]', '{"a":100}') |
+-------------------------------------------------------+
| [1, 2, 3, true, false, {"a": 100}]                    |
+-------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE('{"1": 2}', '[true, false]', '{"a":100}');
+------------------------------------------------------+
| JSON_MERGE('{"1": 2}', '[true, false]', '{"a":100}') |
+------------------------------------------------------+
| [{"1": 2}, true, false, {"a": 100}]                  |
+------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE('{"1": 2}',  '{"a":100}');
+--------------------------------------+
| JSON_MERGE('{"1": 2}',  '{"a":100}') |
+--------------------------------------+
| {"1": 2, "a": 100}                   |
+--------------------------------------+
1 row in set

4.6.JSON_MERGE_PATCH

  • 描述：該函數(shù)依據(jù) RFC 7396 標準對兩個或多個 JSON文檔執(zhí)行合并，并返回合并結(jié)果，結(jié)果不保留具有重復鍵的成員。如果某一個參數(shù)所傳遞的文檔無效，則會引發(fā)錯誤。
  • 語法：JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

SON_MERGE_PATCH() 執(zhí)行合并的規(guī)則如下：

如果第一個參數(shù)不是對象，則將其視為空對象與第二個參數(shù)合并作為合并結(jié)果。

如果第二個參數(shù)不是對象，則合并結(jié)果是第二個參數(shù)。

如果兩個參數(shù)都是對象，則合并結(jié)果是具有以下成員的對象：

  • 第一個對象的所有成員在第二個對象中沒有具有相同鍵的對應(yīng)成員。
  • 第二個對象的所有成員在第一個對象中沒有對應(yīng)的鍵，并且其值不是 JSON 的 null 字面量。
  • 所有成員的鍵都存在于第一個和第二個對象中，并且在第二個對象中的值不是 JSON 的 null 字面量。 這些成員的值是遞歸合并第一個對象中的值與第二個對象中的值的結(jié)果。
obclient> SELECT JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]');
+------------------------------------------------+
| JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]') |
+------------------------------------------------+
| [true, false]                                  |
+------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}');
+-------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}') |
+-------------------------------------------------+
| {"id": 23, "name": "x"}                         |
+-------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('1', 'true');
+-------------------------------+
| JSON_MERGE_PATCH('1', 'true') |
+-------------------------------+
| true                          |
+-------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}');
+---------------------------------------------+
| JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}') |
+---------------------------------------------+
| {"id": 45}                                  |
+---------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
+-----------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
+-----------------------------------------------------------+
| {"a": 3, "b": 2, "c": 4}                                  |
+-----------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL);
+--------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL) |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }');
+--------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }') |
+--------------------------------------------------------------------------------+
| {"a": 5, "b": 2, "c": 4, "d": 6}                                               |
+--------------------------------------------------------------------------------+
1 row in set

4.7.JSON_MERGE_PRESERVE

  • 描述：合并兩個或多個 JSON 文檔并返回合并結(jié)果。
  • 語法：JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

如果任何參數(shù)為 NULL，則返回 NULL。如果任何參數(shù)不是有效的 JSON 文檔，則會報錯。

JSON_MERGE_PRESERVE() 合并規(guī)則如下：

  • 相鄰數(shù)組合并為一個數(shù)組。
  • 相鄰的對象合并為一個對象。
  • 標量值自動包裹為數(shù)組并且合并為數(shù)組。
  • 通過將對象自動包裹為數(shù)組來合并相鄰的數(shù)組和對象。
obclient> SELECT JSON_MERGE_PRESERVE('[1, 2, 3]', '[true, false]');
+---------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2, 3]', '[true, false]') |
+---------------------------------------------------+
| [1, 2, 3, true, false]                            |
+---------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('{"name": "apple"}', '{"id": 56}');
+--------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name": "apple"}', '{"id": 56}') |
+--------------------------------------------------------+
| {"id": 56, "name": "apple"}                            |
+--------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('1', 'true');
+----------------------------------+
| JSON_MERGE_PRESERVE('1', 'true') |
+----------------------------------+
| [1, true]                        |
+----------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('[1, 2, 3]', '{"id": 56}');
+------------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2, 3]', '{"id": 56}') |
+------------------------------------------------+
| [1, 2, 3, {"id": 56}]                          |
+------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2, "c": 5}',  '{ "a": 3, "c": 4 }');
+-------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{ "a": 1, "b": 2, "c": 5}',  '{ "a": 3, "c": 4 }') |
+-------------------------------------------------------------------------+
| {"a": [1, 3], "b": 2, "c": [5, 4]}                                      |
+-------------------------------------------------------------------------+
1 row in set
obclient> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }', '{ "a": 5, "d": 6 }');
+--------------------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }', '{ "a": 5, "d": 6 }') |
+--------------------------------------------------------------------------------------+
| {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}                                             |
+--------------------------------------------------------------------------------------+
1 row in set

4.8.JSON_REMOVE（根據(jù)path移除）

  • 描述：該函數(shù)用于從 JSON 文檔中刪除數(shù)據(jù)并返回結(jié)果。
  • 語法：JSON_REMOVE(json_doc, path[, path] ...)

json_doc 參數(shù)用于指定 JSON 文檔，path 為路徑參數(shù)。如果任何參數(shù)為 NULL，則返回 NULL。

以下情況都會發(fā)生報錯：

  • json_doc 參數(shù)不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • path 參數(shù)是 $。
  • path 參數(shù)包含 * 或 ** 通配符。

如果要移除的元素在文檔中不存在，則不會產(chǎn)生錯誤。在這種情況下，路徑不會影響文檔。

obclient> SET @jn = '["a", ["b", "c"], "d"]';
Query OK, 0 rows affected
obclient> SELECT JSON_REMOVE(@jn, '$[1]');
+--------------------------+
| JSON_REMOVE(@jn, '$[1]') |
+--------------------------+
| ["a", "d"]               |
+--------------------------+
1 row in set
obclient> SELECT JSON_REMOVE(@jn, '$[7]');
+--------------------------+
| JSON_REMOVE(@jn, '$[7]') |
+--------------------------+
| ["a", ["b", "c"], "d"]   |
+--------------------------+
1 row in set

4.9.JSON_REPLACE（替換指定path的值）

  • 描述：該函數(shù)用于替換 JSON 文檔中的現(xiàn)有值并返回結(jié)果。
  • 語法：JSON_REPLACE(json_doc, path, val[, path, val] ...)

以下情況都會發(fā)生報錯：

  • json_doc 參數(shù)不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • path 參數(shù)包含 * 或 ** 通配符。

文檔中現(xiàn)有路徑的 path-value 對會用新值覆蓋現(xiàn)有文檔值。文檔中不存在路徑的 path-value 將被忽略且無效。

obclient> SET @jn = '{ "a": 1, "b": [2, 3, 4]}';
Query OK, 0 rows affected
obclient> SELECT JSON_REPLACE(@jn, '$.a', 10, '$.c', '[true, false]');
+------------------------------------------------------+
| JSON_REPLACE(@jn, '$.a', 10, '$.c', '[true, false]') |
+------------------------------------------------------+
| {"a": 10, "b": [2, 3, 4]}                            |
+------------------------------------------------------+
1 row in set

5.0.JSON_SET（有則覆蓋，沒有則新增）

  • 描述：該函數(shù)用于在 JSON 文檔中插入或更新數(shù)據(jù)并返回結(jié)果。
  • 語法：JSON_SET(json_doc, path, val[, path, val] ...)

json_doc 參數(shù)用于指定 JSON 文檔，path 為路徑參數(shù)。如果任何參數(shù)為 NULL 或 path（如果給定）未定位到對象，則返回 NULL。

以下情況都會發(fā)生報錯：

  • json_doc 參數(shù)不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • path 參數(shù)包含 * 或 ** 通配符。

文檔中現(xiàn)有路徑的 path-value 對會用新值覆蓋現(xiàn)有文檔值。如果路徑標識的對象不存在，則文檔中不存在路徑的路徑值對會按照如下規(guī)則添加到文檔中：

  • 現(xiàn)有對象中不存在的成員。該成員被添加到對象并與新值相關(guān)聯(lián)。
  • 超出現(xiàn)有數(shù)組末尾的位置。此時使用新值擴展數(shù)組。如果現(xiàn)有值不是數(shù)組，則將其自動包裹為數(shù)組，然后使用新值進行擴展。
obclient> SET @jn = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected
obclient> SELECT JSON_SET(@jn, '$.a', 10, '$.c', '[true, false]');
+--------------------------------------------------+
| JSON_SET(@jn, '$.a', 10, '$.c', '[true, false]') |
+--------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"}     |
+--------------------------------------------------+
1 row in set

五、返回JSON文本屬性的函數(shù)

5.1.JSON_DEPTH（返回 JSON 文檔的最大深度）

  • 描述：返回 JSON 文檔的最大深度。
  • 語法：JSON_DEPTH(json_doc)

json_doc 參數(shù)用于指定 JSON 文檔。如果參數(shù)為 NULL，則返回 NULL。

如果參數(shù)不是有效的 JSON 文檔，則會發(fā)生錯誤。

空數(shù)組、空對象或標量值的深度為 1。僅包含深度為 1 的元素的非空數(shù)組深度為 2，僅包含深度為 1 的成員值的非空對象的深度為 2。否則，JSON 文檔的深度大于 2。

mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
+------------------+------------------+--------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
+------------------+------------------+--------------------+
|                1 |                1 |                  1 |
+------------------+------------------+--------------------+
mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
+------------------------+------------------------+
| JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
+------------------------+------------------------+
|                      2 |                      2 |
+------------------------+------------------------+
mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
+-------------------------------+
| JSON_DEPTH('[10, {"a": 20}]') |
+-------------------------------+
|                             3 |
+-------------------------------+

5.2.JSON_LENGTH（返回長度）

  • 描述：該函數(shù)返回 JSON 文檔的長度。如果給出了 path 參數(shù)，則返回由路徑標識的文檔中值的長度。
  • 語法：JSON_LENGTH(json_doc[, path])

json_doc 參數(shù)用于指定 JSON 文檔，path 為路徑參數(shù)。如果任何參數(shù)為 NULL 或 path 參數(shù)未標識文檔中的值，則返回 NULL。

以下情況都會發(fā)生報錯：

  • json_doc 參數(shù)不是有效的 JSON 文檔。
  • path 參數(shù)不是有效的路徑表達式。
  • path 參數(shù)包含 * 或 ** 通配符。

JSON 文檔的長度定義如下：

  • 標量的長度為 1。
  • 數(shù)組的長度是數(shù)組元素的數(shù)量。
  • 對象的長度是對象成員的數(shù)量。
  • 不計算嵌套數(shù)組或?qū)ο蟮拈L度。
mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
|                               3 |
+---------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

5.3.JSON_TYPE（獲取json的類型）

  • 描述：該函數(shù)返回代表 JSON 值類型的 utf8mb4 字符串。
  • 語法：JSON_TYPE(json_val)

參數(shù) json_val 可以是對象、數(shù)組或標量類型。

如果參數(shù)為 NULL，則返回 NULL。如果參數(shù)不是有效的 JSON 值，則會報錯。

mysql> SET @j = '{"a": [10, true]}';
mysql> SELECT JSON_TYPE(@j);
+---------------+
| JSON_TYPE(@j) |
+---------------+
| OBJECT        |
+---------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
+------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |
+------------------------------------+
| ARRAY                              |
+------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
+---------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |
+---------------------------------------+
| INTEGER                               |
+---------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
+---------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) |
+---------------------------------------+
| BOOLEAN                               |
+---------------------------------------+

5.4.JSON_VALID（判斷是否是有效json）

  • 描述：該函數(shù)用于判斷輸入值是否為有效的 JSON，如果是有效的 JSON 返回 1，否則返回 0。
  • 語法：JSON_VALID(val)
obclient> SELECT JSON_VALID('{"a": 1}');
+------------------------+
| JSON_VALID('{"a": 1}') |
+------------------------+
|                      1 |
+------------------------+
1 row in set
obclient> SELECT JSON_VALID('oceanbase'), JSON_VALID('"oceanbase"');
+-------------------------+---------------------------+
| JSON_VALID('oceanbase') | JSON_VALID('"oceanbase"') |
+-------------------------+---------------------------+
|                       0 |                         1 |
+-------------------------+---------------------------+
1 row in set

六、JSON工具函數(shù)

6.1.JSON_PRETTY（json格式化輸出）

  • 描述：該函數(shù)對 JSON 值按照格式打印。類似于 PHP 等。
  • 語法：JSON_PRETTY(json_val)

參數(shù) json_val 必須是 JSON 值或 JSON 值的有效字符串表示形式。此值中存在的無關(guān)空格和換行符對輸出沒有影響。如果該值不是 JSON 文檔，或者無法進行解析，則該函數(shù)將執(zhí)行失敗并顯示錯誤。

obclient> SELECT JSON_PRETTY('{"a":"10","b":"20","c":"30"}');
+---------------------------------------------+
| JSON_PRETTY('{"a":"10","b":"20","c":"30"}') |
+---------------------------------------------+
| {
  "a": "10",
  "b": "20",
  "c": "30"
}   |
+---------------------------------------------+
1 row in set

6.2.JSON_STORAGE_SIZE（計算占用的存儲空間，單位字節(jié)）

  • 描述：此函數(shù)返回用于存儲 JSON 文檔的二進制字節(jié)數(shù)。當參數(shù)是一個 JSON 列時，會用于存儲 JSON 文檔。JSON的部分更新不會改變該函數(shù)的返回值。
  • 語法：JSON_STORAGE_SIZE(json_val)

json_val 參數(shù)必須是有效的 JSON 文檔或可以解析為一個字符串。在 json_val 是字符串的情況下，該函數(shù)將字符串解析為 JSON 并將其轉(zhuǎn)換為二進制后返回存儲空間的二進制字節(jié)數(shù)。

obclient> CREATE TABLE jtbl (jcol JSON);
Query OK, 0 rows affected
obclient> INSERT INTO jtbl VALUES  ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected
obclient> SELECT jcol,JSON_STORAGE_SIZE(jcol) AS Size FROM jtbl;
+-----------------------------------------------+------+
| jcol                                          | Size |
+-----------------------------------------------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   41 |
+-----------------------------------------------+------+
1 row in set

七、JSON聚合函數(shù)

7.1.JSON_ARRAYAGG（配合GROUP BY進行分組，得到的是數(shù)組）

描述：該函數(shù)將結(jié)果集聚合為單個 JSON 數(shù)組，其元素由行組成。此數(shù)組中元素的順序未定義。
該函數(shù)作用于計算為單個值的列或表達式。

語法：JSON_ARRAYAGG(col_or_expr)[over_clause]

參數(shù) col_or_expr 為列或表達式。如果結(jié)果不包含行，或者出現(xiàn)錯誤，則返回 NULL。

obclient> CREATE TABLE tbl1 (oid INT, attr VARCHAR(100), value VARCHAR(100));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES (2, 'color', 'red'),(2, 'fabric', 'silk'),(3,'color','green'),(3,'shape','square');
Query OK, 4 rows affected 
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT oid, JSON_ARRAYAGG(attr) AS attributes FROM tbl1 GROUP BY oid;
+------+---------------------+
| oid  | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set
obclient> INSERT INTO tbl1 SELECT * FROM tbl1;
Query OK, 4 rows affected 
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT oid, JSON_ARRAYAGG(attr) AS attributes FROM tbl1 GROUP BY oid;
+------+----------------------------------------+
| oid  | attributes                             |
+------+----------------------------------------+
|    2 | ["color", "fabric", "color", "fabric"] |
|    3 | ["color", "shape", "color", "shape"]   |
+------+----------------------------------------+
2 rows in set

7.2.JSON_OBJECTAGG（配合GROUP BY進行分組，得到的是對象）

  • 描述：該函數(shù)將兩個列名或表達式作為參數(shù)，第一個用作鍵，第二個用作值，并返回包含鍵值對的 JSON 對象。
  • 語法：JSON_OBJECTAGG(key, value) [over_clause]

key, value 表示鍵值對。如果結(jié)果不包含行，或者出現(xiàn)錯誤，則返回 NULL。如果任何鍵名稱為 NULL 或參數(shù)數(shù)量不等于 2，則會發(fā)生錯誤。

obclient> SELECT oid, JSON_OBJECTAGG(attr, value) AS attributes FROM tbl1 GROUP BY oid;
+------+---------------------------------------+
| oid  | attributes                            |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set
obclient> CREATE TABLE tbl2(c VARCHAR(10), i INT);
Query OK, 0 rows affected
obclient>  INSERT INTO tbl2 VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected 
Records: 3  Duplicates: 0  Warnings: 0
obclient> SELECT c, i FROM tbl2;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    4 |
| key  |    5 |
+------+------+
3 rows in set
obclient> SELECT JSON_OBJECTAGG(c, i) FROM tbl2;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5}           |
+----------------------+
1 row in set

到此這篇關(guān)于Mysql中Json相關(guān)的函數(shù)使用的文章就介紹到這了,更多相關(guān)Mysql Json函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論