一、JSON_EXTRACT 提取指定數(shù)據(jù)

MySQL JSON_EXTRACT() 函數(shù)在 JSON 文檔提取路徑表達式指定的數(shù)據(jù)并返回。

語法：

JSON_EXTRACT(json, path, ...)

參數(shù)說明：

• json：必需的。一個 JSON 文檔。
• path：必需的。至少指定一個路徑表達式。

返回值：

• 返回 JSON 文檔中由路徑表達式匹配的所有的值。
• 返回NULL情況：
  •  不存在指定的路徑。
  • 任意一個參數(shù)為 NULL。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式。

示例：

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[1]'); // 2
SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]'); // {"x": 3}

二、JSON_UNQUOTE 取消雙引號

MySQL JSON_UNQUOTE() 函數(shù)取消雙引號引用 JSON 值，并將結(jié)果作為字符串返回。

語法：

JSON_UNQUOTE(json_val)

參數(shù)說明：

• json_val：必需的。一個字符串。

返回值：

•  取消雙引號引用 JSON 值
• 返回NULL情況：參數(shù)為 NULL。
• 報錯情況：不是有效的 JSON 字符串文本。能夠識別下表中的轉(zhuǎn)義字符：
  • \"：雙引號 "
  • \b：退格字符
  • \f：換頁符
  • \n：換行符
  • \r：回車符
  • \t：制表符
  • \\：反斜杠 \
  • \uXXXX：Unicode 值 XXXX 的 UTF-8 字節(jié)

示例：

SELECT JSON_UNQUOTE('"123456"');    // 123456
SELECT JSON_UNQUOTE(CAST('"cxian"' AS JSON));    // cxian

三、JSON_KEYS 取成員的數(shù)組

MySQL JSON_KEYS() 函數(shù)返回一個包含了指定的 JSON 對象中最上層的成員 (key) 的數(shù)組。

語法：

JSON_KEYS(json)
JSON_KEYS(json, path)

參數(shù)說明：

• json：必需的。一個 JSON 對象文檔。

• path：可選的。路徑表達式。

返回值：

•  返回一個包含了指定的 JSON 對象中最上層的成員 (key) 的數(shù)組。
• 若指定了路徑表達式，則返回路徑表達式匹配的 JSON 對象中的最上層的成員組成的數(shù)組。
• 返回 NULL情況：
  • 無路徑，JSON 文檔不是一個 JSON 對象。
  • 有路徑，路徑匹配的 JSON 值不是 JSON 對象。
  • 任意參數(shù)為 NULL。
• 報錯情況：
  •  json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式。

示例：

SELECT JSON_KEYS('{"a": 1, "b": 2, "c": 3}');              // ["a", "b", "c"]
SELECT JSON_KEYS('[1, {"a": 1, "b": 2, "c": 3}]', '$[1]'); // ["a", "b", "c"]

SELECT
    JSON_KEYS('1'),         // null
    JSON_KEYS('"true"'),    // null
    JSON_KEYS('"abc"'),     // null
    JSON_KEYS('[0, 1]');    // null

四、JSON_ARRAY 將參數(shù)轉(zhuǎn)為數(shù)組

MySQL JSON_ARRAY() 函數(shù)返回一個包含了所有參數(shù)的 JSON 數(shù)組。

語法：

JSON_ARRAY(value1[, value2[, ...]])

參數(shù)說明：

• value1[, value2[, ...]]：可選的。一些值，他們將被放在 JSON 數(shù)組中。

返回值：

• 包含了所有參數(shù)的 JSON 數(shù)組。
• 值轉(zhuǎn)換情況：
  • TRUE 被轉(zhuǎn)換為 true
  • FALSE 被轉(zhuǎn)換為 false
  • NULL 被轉(zhuǎn)換為 null
  • 日期，時間，日期時間 被轉(zhuǎn)換為 字符串 

示例：

SELECT JSON_ARRAY(1, '1', NULL, TRUE, FALSE, NOW()); // [1, "1", null, true, false, "2023-11-05 16:58:34.000000"]
SELECT JSON_ARRAY(JSON_ARRAY(1, 2), JSON_ARRAY('a', 'b')); // [[1, 2], ["a", "b"]]

五、JSON_OBJECT 參數(shù)轉(zhuǎn)為對象

MySQL JSON_OBJECT() 函數(shù)返回一個包含了由參數(shù)指定的所有鍵值對的 JSON 對象。

語法：

JSON_OBJECT(key, value[, key2, value2, ...])

參數(shù)說明：

• key：必需的。對象中的鍵。
• value：必需的。對象中的 key 的值。

返回值：

•  一個包含了所有鍵值對的 JSON 對象。
• 報錯情況：
  • key 是 NULL。
  • 奇數(shù)個參數(shù)。

示例：

SELECT JSON_OBJECT('name', 'cxian', 'age', 22); // {"age": 22, "name": "cxian"}
SELECT JSON_OBJECT('name', 'cxian', 'age', 22, 'age', 33); // {"age": 33, "name": "cxian"}

六、JSON_DEPTH 取JSON深度

MySQL JSON_DEPTH() 函數(shù)返回一個 JSON 文檔的最大深度。

語法：

JSON_DEPTH(json)

參數(shù)說明：

• json：必需的。一個 JSON 文檔。

返回值：

• 一個 JSON 文檔的最大深度。規(guī)則如下：
  • 空的數(shù)組、空的對象或者純值的深度是 1。
  • 僅包含深度為 1 的元素的數(shù)組的深度是 2。
  • 所有成員的值的深度為 1 的對象的深度是 2。
  • 其他 JSON 文檔的深度都大于 2。
• 返回 NULL情況：參數(shù)為 NULL。
• 報錯情況：參數(shù)不是有效的 JSON 文檔。

示例：

SELECT 
    JSON_DEPTH('[]'),             // 1
    JSON_DEPTH('[1, 2]'),         // 2 
    JSON_DEPTH('[1, [2, 3]]');    // 3

七、JSON_LENGTH 取節(jié)點長度

MySQL JSON_LENGTH() 函數(shù)返回 JSON 文檔或者 JSON 文檔中通過路徑指定的節(jié)點的長度。

語法：

JSON_LENGTH(json)
JSON_LENGTH(json, path)

參數(shù)說明：

• json：必需的。一個 JSON 文檔。

• path：可選的。一個路徑表達式。

返回值：

• 有path： 返回 JSON 文檔中由路徑指定的值的長度。
• 無path：返回 JSON 文檔的長度。
• 計算 JSON 文檔的長度規(guī)則：
  • 純值的長度是 1。
  • 數(shù)組的長度是數(shù)組元素的數(shù)量。
  • 對象的長度是對象成員的數(shù)量。
  • 內(nèi)嵌的數(shù)組或?qū)ο蟛粎⑴c計算長度。
• 返回 NULL情況：
  • 不存在指定的路徑。
  • 任意一個參數(shù)為 NULL。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式。
  • path 中包含 * 或 **。

示例：

SELECT
    JSON_LENGTH('1'),        // 1
    JSON_LENGTH('true'),     // 1
    JSON_LENGTH('false'),    // 1
    JSON_LENGTH('null'),     // 1
    JSON_LENGTH('"a"');      // 1
    JSON_LENGTH('[]'),       // 0
    JSON_LENGTH('[1, 2]'),   // 2
    JSON_LENGTH('[1, {"x": 2}]');    // 2

八、JSON_CONTAINS 判斷是否包含

MySQL JSON_CONTAINS() 函數(shù)檢查一個 JSON 文檔中是否包含另一個 JSON 文檔。

語法：

JSON_CONTAINS(target_json, candidate_json)
JSON_CONTAINS(target_json, candidate_json, path)

參數(shù)說明：

• target_json必需的。一個 JSON 文檔。

• candidate_json：必需的。被包含的 JSON 文檔。

• path：可選的。一個路徑表達式。

返回值：

•  包含：返回1。
• 不包含：返回0。
• 返回 NULL情況：
  • JSON 文檔中不存在指定的路徑。
  • 任意一個參數(shù)為 NULL。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式。

示例：

SELECT
    JSON_CONTAINS('[1, 2, {"x": 3}]', '1'),        // 1
    JSON_CONTAINS('[1, 2, {"x": 3}]', '{"x": 3}'), // 1
    JSON_CONTAINS('[1, 2, {"x": 3}]', '3'),        // 0
    JSON_CONTAINS('[1, 2, [3, 4]]', '2'),          // 1
    JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]');  // 0

九、JSON_ARRAY_APPEND 追加值

MySQL JSON_ARRAY_APPEND() 函數(shù)向 JSON 文檔中的指定的數(shù)組中追加一個值并返回修改后的 JSON 文檔。

語法：

JSON_ARRAY_APPEND(json, path, value[, path2, value2] ...)

參數(shù)說明：

• json：必需的。被修改的 JSON 文檔。

• path：必需的。添加新元素的路徑。一個有效的路徑表達式，它不能包含 * 或 **。

• value：必需的。被添加到數(shù)組的新元素值。

返回值：

• 追加后的 JSON 文檔。 
• 返回 NULL情況：JSON 文檔或者路徑為 NULL。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式或者其中包含 * 或 **。

示例：

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4);      // [1, 2, 3, 4]
SELECT JSON_ARRAY_APPEND('[1, [2, 3]]', '$[0]', 4); // [[1, 4], [2, 3]]
SELECT JSON_ARRAY_APPEND('[1, [2, 3]]', '$[1]', 4); // [1, [2, 3, 4]]
SELECT JSON_ARRAY_APPEND('{"name": "Tim", "hobby": ["car"]}', '$.hobby', "food"); // {"name": "Tim", "hobby": ["car", "food"]}
SELECT JSON_ARRAY_APPEND('1', '$', 2); // [1, 2]

十、JSON_ARRAY_INSERT 指定位置插入值

MySQL JSON_ARRAY_INSERT() 函數(shù)向 JSON 文檔中的指定的數(shù)組中的指定位置插入一個值并返回新的 JSON 文檔。

語法：

JSON_ARRAY_INSERT(json, path, value[, path2, value2] ...)

參數(shù)說明：

• json：必需的。被修改的 JSON 文檔。

• path：必需的。插入新元素的數(shù)組元素位置。一個有效的路徑表達式，它不能包含 * 或 **。比如 $[0] 和 $.a[0] 表示在數(shù)組的開頭插入新元素。

• value：必需的。被插入到數(shù)組的新元素值。

返回值：

• 插入后的JSON文檔。
• 返回 NULL情況：JSON 文檔或者路徑為 NULL。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式或者其中包含 * 或 **。
  • path 指示的不是數(shù)組元素的路徑。

示例：

set @str = '[1, [2, 3], {"a": [4, 5]}]'
SELECT 
  JSON_ARRAY_INSERT(@str, '$[0]', 0), // [0, 1, [2, 3], {"a": [4, 5]}]
  JSON_ARRAY_INSERT(@str, '$[1]', 0), // [1, 0, [2, 3], {"a": [4, 5]}]
  JSON_ARRAY_INSERT(@str, '$[2]', 0), // [1, [2, 3], 0, {"a": [4, 5]}]
  JSON_ARRAY_INSERT(@str, '$[1][0]', 0), // [1, [0, 2, 3], {"a": [4, 5]}]
  JSON_ARRAY_INSERT(@str, '$[2].a[0]', 0); // [1, [2, 3], {"a": [0, 4, 5]}]

十一、JSON_CONTAINS_PATH 判斷是否存在

MySQL JSON_CONTAINS_PATH() 函數(shù)檢查一個 JSON 文檔中在指定的路徑上是否有值存在。

語法：

JSON_CONTAINS_PATH(json, one_or_all, path[, path])

參數(shù)說明：

• json：必需的。一個 JSON 文檔。

• one_or_all：必需的?？捎弥担?code>'one', 'all'。它指示是否檢查所有的路徑。

• path：必需的。您應(yīng)該至少指定一個路徑表達式。

返回值：

•  有值返回 1， 否則返回 0。
• one_or_all參數(shù)說明：
  • 'one'：任意一個路徑上有值，返回1，否則返回 0。
  • 'all'：所有路徑都有值，返回1，否則返回 0。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式。

示例：

SELECT
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]'), // 1
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[3]'), // 0
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[2].x'), // 1
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'one', '$[0]', '$[3]'), // 1
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]', '$[3]'); // 0

十二、JSON_INSERT 插入數(shù)據(jù)

MySQL JSON_INSERT() 函數(shù)向一個 JSON 文檔中插入數(shù)據(jù)并返回新的 JSON 文檔。

語法：

JSON_INSERT(json, path, value[, path2, value2] ...)

參數(shù)說明：

• json：必需的。被修改的 JSON 文檔。

• path：必需的。一個有效的路徑表達式，它不能包含 * 或 **。

• value：必需的。被插入的數(shù)據(jù)。

返回值：

• 插入后的JSON文檔。（若已存在， 則不插入）
• path 為 $：返回原JSON文檔。
• 返回 NULL情況：JSON 文檔或者路徑為 NULL。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式或者其中包含 * 或 **。 

示例：

SET @arr = '[1, [2, 3], {"a": [4, 5]}]';
SELECT JSON_INSERT(@arr, '$[0]', 0, '$[3]', 6); // [1, [2, 3], {"a": [4, 5]}, 6]
SET @obj = '{"x": 1}';
SELECT JSON_INSERT(@obj, '$.y', '2');           // {"x": 1, "y": "2"}

十三、JSON_MERGE 多個JSON合并

MySQL JSON_MERGE() 函數(shù)合并兩個或多個 JSON 文檔并返回合并的結(jié)果。

語法：

JSON_MERGE(json1, json2, ...)

參數(shù)說明：

• json1：必需的。一個 JSON 對象文檔。

• json2：必需的。一個 JSON 對象文檔。

返回值：

•  合并后的新JSON文檔。
• 返回 NULL情況：任意一個參數(shù)為 NULL。
• 報錯情況：參數(shù)不是有效的 JSON 文檔。

示例：

SELECT JSON_MERGE('1', 'true', '"hello"', 'null'); // [1, true, "hello", null]
SELECT JSON_MERGE('[1, 2]', '[2, 3]');             // [1, 2, 2, 3]
SELECT JSON_MERGE('{"x": 1}', '{"x": 2, "y": 3}'); // {"x": [1, 2], "y": 3}
SELECT JSON_MERGE('{"x": 1}', '[1, 2]');           // [{"x": 1}, 1, 2]

十四、JSON_MERGE_PATCH 多個JSON替換合并

MySQL JSON_MERGE_PATCH() 函數(shù)對兩個或多個 JSON 文檔執(zhí)行替換合并并返回合并的結(jié)果。

替換合并：在相同鍵值時，只保留后面的值。

語法：

JSON_MERGE_PATCH(json1, json2, ...)

參數(shù)說明：

• json1：必需的。一個 JSON 對象文檔。

• json2：必需的。一個 JSON 對象文檔。

返回值：

• 合并后的JSON文檔。
• 合并規(guī)則如下：
  • 若第一個參數(shù)不是對象，則合并的結(jié)果與第二個參數(shù)合并空對象的結(jié)果相同。
  • 若第二個參數(shù)不是對象，則合并的結(jié)果為第二個參數(shù)。
  • 若兩個參數(shù)都是對象，則合并的對象具有以下成員：
    • 只存在于第一個對象中的成員。
    • 只存在于第二個對象中且值不是 null 的成員。
    • 存在于第二個對象且值不是 null ，并且在第一個對象中有對應(yīng)的相同鍵的成員。
• 返回 NULL情況：任意一個參數(shù)為NULL。
• 報錯情況：任意一個參數(shù)不是有效的 JSON 文檔。

示例：

SELECT
    JSON_MERGE_PATCH('2', 'true'),    // true
    JSON_MERGE_PATCH('[1, 2]', '[2, 3]'),    // [2, 3]
	JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "y": 3}'), // {"x": 2, "y": 3, "z": 7}
	JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "z": null}'); // {"x": 2}

十五、JSON_MERGE_PRESERVE 多個JSON合并

MySQL JSON_MERGE_PRESERVE() 函數(shù)合并兩個或多個 JSON 文檔并返回合并的結(jié)果。

此函數(shù)和 JSON_MERGE_PATCH() 用法相同，但是合并邏輯有所不同。

不同規(guī)則合并如下：

• 兩個數(shù)組合并為一個數(shù)組，保留所有數(shù)組中的元素。
• 兩個對象合并為一個對象，保留所有的鍵和值。
• 一個純值會被包裝成一個數(shù)組并作為數(shù)組進行合并。
• 對象和數(shù)組合并時，會將對象包裝到一個數(shù)組中并作為數(shù)組進行合并。

示例：

SELECT
    JSON_MERGE_PRESERVE('2', 'true'), // [2, true]
    JSON_MERGE_PRESERVE('[1, 2]', '[2, 3]'), // [1, 2, 2, 3]
	JSON_MERGE_PRESERVE('{"x": 1, "z": 7}', '{"x": 2, "y": 3}'), // {"x": [1, 2], "y": 3, "z": 7}
	JSON_MERGE_PRESERVE('{"x": 1, "z": 7}', '{"x": 2, "z": null}'); // {"x": [1, 2], "z": [7, null]}

十六、JSON_OVERLAPS 判斷有無相同鍵或值

MySQL JSON_OVERLAPS() 函數(shù)檢測兩個 JSON 文檔是否擁有任何一個相同鍵值對或數(shù)組元素。

語法：

JSON_OVERLAPS(json1, json2)

參數(shù)說明：

• json1：必需的。一個 JSON 文檔。

• json2：必需的。另一個 JSON 文檔。

返回值：

• 兩個JSON文檔重疊的內(nèi)容返回 1，否則返回 0。
• 函數(shù)判斷規(guī)則如下：
  • 比較兩個數(shù)組時，如果兩個數(shù)組至少有一個相同的元素返回 1，否則返回 0。
  • 比較兩個對象時，如果兩個對象至少有一個相同的鍵值對返回 1，否則返回 0。
  • 比較兩個純值時，如果兩個值相同返回 1，否則返回 0。
  • 比較純值和數(shù)組時，如果值是這個數(shù)組中的直接元素返回 1，否則返回 0。
  • 比較純值和對象的結(jié)果為 0。
  • 比較數(shù)組和對象的結(jié)果為 0。
• 返回 NULL情況：參數(shù)為 NULL。
• 報錯情況：任意一個參數(shù)不是有效的 JSON 文檔。

示例：

SELECT 
	JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]'),	    // 1
	JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]'),	    // 0
    JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}'),	// 1
    JSON_OVERLAPS('{"x": 1}', '{"y": 2}'),	        // 0
    JSON_OVERLAPS('[1, 2, 3]', '3'),	            // 1
    JSON_OVERLAPS('[1, 2, [3]]', '3');	            // 0

十七、JSON_PRETTY 格式化輸出

MySQL JSON_PRETTY() 函數(shù)格式化輸出一個 JSON 文檔，以便更易于閱讀。

語法：

JSON_PRETTY(json)

參數(shù)說明：

• json：必需的。一個 JSON 文檔或 JSON 類型的值。

返回值：

•  格式化輸出JSON文檔，易于閱讀。

示例：

SELECT JSON_PRETTY('[1, 2, 3]');
[
  1,
  2,
  3
]

SELECT JSON_PRETTY('{"x": 1, "y": 2}');
{
  "x": 1,
  "y": 2
}

十八、JSON_REMOVE 刪除指定數(shù)據(jù)

MySQL JSON_REMOVE() 函數(shù)從一個 JSON 文檔中刪除由路徑指定的數(shù)據(jù)并返回修改后的 JSON 文檔。

語法：

JSON_REMOVE(json, path[, path] ...)

參數(shù)說明：

• json：必需的。一個 JSON 文檔。

• path：必需的。一個有效的路徑表達式，它不能包含 * 或 **。

返回值：

•  刪除后的JSON文檔。
• 返回 NULL情況：JSON 文檔或者路徑為 NULL。
• 報錯情況；
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式或者等于 $ 或者其中包含 * 或 **

示例：

SELECT 
	JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]', '$[2]'),	// [1, 2]
	JSON_REMOVE('{"x": 1, "y": 2}', '$.x');				// {"y": 2}

十九、JSON_REPLACE 替換數(shù)據(jù)

MySQL JSON_REPLACE() 函數(shù)在一個 JSON 文檔中替換已存在的數(shù)據(jù)并返回新的 JSON 文檔。

語法：

JSON_REPLACE(json, path, value[, path2, value2] ...)

參數(shù)說明：

• json：必需的。被修改的 JSON 文檔。

• path：必需的。一個有效的路徑表達式，它不能包含 * 或 **。

• value：必需的。新的數(shù)據(jù)。

返回值：

• 替換后的JSON文檔。
• 回 NULL情況：JSON 文檔或者路徑為 NULL。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式或者其中包含 * 或 **。

示例：

SET @arr = '[1, [2, 3]]';
SELECT 
	JSON_REPLACE(@arr, '$[0]', 0, '$[2]', 6),    // [0, [2, 3]]
	JSON_REPLACE(@arr, '$[0]', 0, '$[1]', 6);    // [0, 6]

SET @obj = '{"x": 1}';
SELECT 
    JSON_REPLACE(@obj, '$.x', 'true', '$.y', 'true');   // {"x": "true"}
	JSON_REPLACE(@obj, '$.x', '[1, 2]'),                // {"x": "[1, 2]"}
	JSON_REPLACE(@obj, '$.x', JSON_ARRAY(1,2));         // {"x": [1, 2]}

二十、JSON_SCHEMA_VALID 驗證JSON文檔

MySQL JSON_SCHEMA_VALID() 函數(shù)根據(jù)指定的 JSON 模式驗證一個 JSON 文檔，并返回 1 表是驗證通過或者返回 0 表示驗證不通過。

語法：

JSON_SCHEMA_VALID(schema, json_doc)

參數(shù)說明：

• schema：必需的。一個 JSON 模式。它必須是一個有效的 JSON 對象。

• json_doc：必需的。被驗證的 JSON 文檔。

返回值：

•  1 ：JSON 文檔通過了驗證。
•  0 ：JSON 文檔沒有通過驗證。
• 返回 NULL情況：任何一個參數(shù)為 NULL。

示例：

SET @schema = '{
      "type": "object",
      "properties": {
        "x": {
          "type": "number",
          "minimum": -128,
          "maximum": 127
        },
        "y": {
          "type": "number",
          "minimum": -128,
          "maximum": 127
        }
      },
      "required": ["x", "y"]
    }';
	
SELECT 
	JSON_SCHEMA_VALID(@schema, '{"x": 1}'),        // 0
	JSON_SCHEMA_VALID(@schema, '{"x": 1, "y": 2}') // 1

二十一、JSON_SCHEMA_VALIDATION_REPORT 驗證JSON文檔

MySQL JSON_SCHEMA_VALIDATION_REPORT() 函數(shù)根據(jù)指定的 JSON 模式驗證一個 JSON 文檔，并返回一個驗證報告。

語法：

JSON_SCHEMA_VALIDATION_REPORT(schema, json_doc)

參數(shù)說明：

• schema：必需的。一個 JSON 模式。它必須是一個有效的 JSON 對象。

• json_doc：必需的。被驗證的 JSON 文檔。

返回值：

•  驗證結(jié)果的報告。
• {"valid": true}：驗證通過。
• {"valid": false, reason:'...'}：驗證不通過，reason為不通過原因。

示例：

SET @schema = '{
      "type": "object",
      "properties": {
        "x": {
          "type": "number",
          "minimum": -90,
          "maximum": 90
        },
        "y": {
          "type": "number",
          "minimum": -180,
          "maximum": 180
        }
      },
      "required": ["x", "y"]
    }';
	
SELECT 
	JSON_SCHEMA_VALIDATION_REPORT(@schema, '{"x": 1}'), // {"valid": false, "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "required"}
	JSON_SCHEMA_VALIDATION_REPORT(@schema, '{"x": 1, "y": 2}') // {"valid": true}

二十二、JSON_SEARCH 查找

MySQL JSON_SEARCH() 函數(shù)返回一個給定字符串在一個 JSON 文檔中的路徑。

語法：

JSON_SEARCH(json, one_or_all, search_str)
JSON_SEARCH(json, one_or_all, search_str, escape_char)
JSON_SEARCH(json, one_or_all, search_str, escape_char, path)

參數(shù)說明：

• json：必需的。一個 JSON 文檔。

• one_or_all：必需的。可用值：'one', 'all'。 規(guī)則如下：

  • 'one'：返回第一個匹配的路徑。
  • 'all'：返回所有匹配的路徑。所有的路徑會包裝在一個數(shù)組內(nèi)返回。

• search_str：必需的。被搜索的字符串。 您可以在 search_str 參數(shù)中使用 % 和 _ 通配符，就像 LIKE 一樣：

  • % 匹配任意數(shù)量的任意字符。
  • _ 匹配一個任意字符。

• escape_char：可選的。 如果 search_str 中包含 % 和 _，需要在他們之前添加轉(zhuǎn)移字符。默認是 \。

• path：可選的。只能在此路徑下進行搜索。

返回值：

• 一個路徑字符串或者由多個路徑組成的數(shù)組。
• 返回 NULL情況：
  • 未搜索到指定的字符串。
  • JSON 文檔中不存在指定的 path。
  • 任意一個參數(shù)為 NULL。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式。

示例：

SET @json = '{
      "type": "object",
      "properties": {
        "x": {
          "type": "number",
          "minimum": -90,
          "maximum": 90
        },
        "y": {
          "type": "number",
          "minimum": -180,
          "maximum": 180
        }
      },
      "required": ["x", "y"]
    }';
	
SELECT 
	JSON_SEARCH(@json, 'one','number'), // "$.properties.x.type"
	JSON_SEARCH(@json, 'all','number')  // ["$.properties.x.type", "$.properties.y.type"]

二十三、JSON_SET 插入或更新數(shù)據(jù)

MySQL JSON_SET() 函數(shù)在一個 JSON 文檔中插入或更新數(shù)據(jù)并返回新的 JSON 文檔。它相當于是 JSON_INSERT() 和 JSON_REPLACE() 的組合。

語法：

JSON_SET(json, path, value[, path2, value2] ...)

參數(shù)說明：

• json：必需的。被修改的 JSON 文檔。

• path：必需的。一個有效的路徑表達式，它不能包含 * 或 **。

• value：必需的。要設(shè)置的數(shù)據(jù)。

返回值：

•  插入或更新數(shù)據(jù)并返回新的 JSON 文檔。規(guī)則如下：
  • 存在路徑：更新。
  • 不存在路徑：添加。
• 若value 為字符串：直接插入。
• 返回 NULL情況：JSON 文檔或者路徑為 NULL。
• 報錯情況：
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式或者其中包含 * 或 **。

示例：

SET @obj = '{"x": 1}';
SELECT 
	JSON_SET(@obj, '$.x', '10', '$.y', '[1, 2]'),    // {"x": "10", "y": "[1, 2]"}
	JSON_SET(@obj, '$.x', '10', '$.y', '{"z": 2}'),  // {"x": "10", "y": "{\"z\": 2}"}
	JSON_SET(@obj, '$.x', '10', '$.y', CAST('[1, 2]' AS JSON)); // {"x": "10", "y": [1, 2]}

二十四、JSON_STORAGE_FREE 釋放空間

MySQL JSON_STORAGE_FREE() 函數(shù)返回一個 JSON 列在被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 更新后所釋放的空間。

語法：

JSON_STORAGE_FREE(json)

參數(shù)說明：

• json：必需的。一個 JSON 文檔。它可以是一個 JSON 字符串，或者一個 JSON 列。

返回值：

• 一個 JSON 列在被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 更新后所釋放的空間。它可以接受一個 JSON 字符串，或者一個 JSON 列作為參數(shù)。
• 返回 0 情況：
  • 參數(shù)是一個 JSON 字符串。
  • 列沒有被更新過，或不是使用JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 部分更新。
  • 內(nèi)容在更新后變的更大了。
• 返回更新后釋放的空間：列（數(shù)據(jù)庫表的列）被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 部分更新。
• 返回 NULL情況：參數(shù)為 NULL。
• 報錯情況：json 不是有效的 JSON 文檔。

示例：

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    json_col JSON NOT NULL
);

INSERT INTO test
VALUES ('{"x": 1, "y": "99"}');

SELECT
    json_col,                    // {"x": 1, "y": "99"}
    JSON_STORAGE_SIZE(json_col), // 24
    JSON_STORAGE_FREE(json_col)  // 0
FROM
    test;
// {"x": 1, "y": "99"} ｜ 24  ｜  0


UPDATE test
SET json_col = JSON_REMOVE(json_col, '$.y');
SELECT
    json_col,                    // {"x": 1}
    JSON_STORAGE_SIZE(json_col), // 24
    JSON_STORAGE_FREE(json_col)  // 11
FROM
    test;

二十五、JSON_STORAGE_SIZE 占用字節(jié)數(shù)

MySQL JSON_STORAGE_SIZE() 函數(shù)返回存儲一個 JSON 文檔的二進制表示所占用的字節(jié)數(shù)。

語法：

JSON_STORAGE_SIZE(json)

參數(shù)說明：

• json：必需的。一個 JSON 文檔。它可以是一個 JSON 字符串，或者一個 JSON 列。

返回值：

• JSON 文檔的二進制表示所占用的字節(jié)數(shù)。
• 返回 NULL：參數(shù)為 NULL。
• 報錯情況：json 不是有效的 JSON 文檔。

示例：

SELECT
    JSON_STORAGE_SIZE('100'),  // 3
    JSON_STORAGE_SIZE('"a"'),  // 3
	JSON_STORAGE_SIZE('true'), // 2
	JSON_STORAGE_SIZE('null'); // 2

二十六、JSON_TABLE 提取數(shù)據(jù)

MySQL JSON_TABLE() 函數(shù)從一個指定的 JSON 文檔中提取數(shù)據(jù)并返回一個具有指定列的關(guān)系表。

語法：

JSON_TABLE(
    json,
    path COLUMNS (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[, column[, ...]])

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

參數(shù)說明：

• json：必需的。一個 JSON 文檔。

• path：必需的。一個路徑表達式。

• column：必需的。定義一個列?？梢允褂萌缦?4 中方式定義列：

  • name FOR ORDINALITY: 生成一個從 1 開始的計數(shù)器列，名字為 name。
  • name type PATH string_path [on_empty] [on_error]: 將由路徑表達式 string_path 指定的值放在名字為 name 的列中。
  • name type EXISTS PATH string_path：根據(jù) string_path 指定的位置是否有值將 1 或 0 放在名字為 name 的列中。
  • NESTED [PATH] path COLUMNS (column[, column[, ...]]): 將內(nèi)嵌的對象或者數(shù)組中的數(shù)據(jù)拉平放在一行中。
• on_empty：可選的。如果指定了，它決定了指定路徑下沒有數(shù)據(jù)時的返回值：
  • NULL ON EMPTY: 如果指定路徑下沒有數(shù)據(jù)，JSON_TABLE() 函數(shù)將使用 NULL，這是默認的行為。
  • DEFAULT value ON EMPTY: 如果指定路徑下沒有數(shù)據(jù)，JSON_TABLE() 函數(shù)將使用 value。
  • ERROR ON EMPTY: 如果指定路徑下沒有數(shù)據(jù)，JSON_TABLE() 函數(shù)將拋出一個錯誤。
• on_error：可選的。如果指定了，它決定了處理錯誤的邏輯：
  • NULL ON ERROR: 如果有錯誤，JSON_TABLE() 函數(shù)將使用 NULL，這是默認的行為。
  • DEFAULT value ON ERROR: 如果有錯誤，JSON_TABLE() 函數(shù)將使用 value。
  • ERROR ON ERROR: 如果有錯誤，JSON_TABLE() 函數(shù)將拋出一個錯誤。

返回值：

•  具有指定列的關(guān)系表。

示例：

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":11}, {"y": 21}, {"x": 30}]',
        '$[*]'
        COLUMNS (
            id FOR ORDINALITY,
            x INT PATH '$.x' DEFAULT '100' ON EMPTY,
            y INT PATH '$.y'
        )
    ) AS t;


   id      x       y
+------+-------+--------+
|     1|     10|      11|
+-----------------------+
|     2|    100|      21|
+------+-------+--------+
|     2|     30|    NULL|
+------+-------+--------+

示例說明：

• 路徑表達式$[*]: 數(shù)組中的每個元素，也就是數(shù)組中的那兩個對象。
  • $[0]：只提取 JSON 數(shù)組中的第 1 元素
  • $[1]：只提取 JSON 數(shù)組中的第 2 元素
  • 以此類推
• COLUMNS 子句定義了關(guān)系表中的 3 個列：
  • id FOR ORDINALITY: 列名為 id，列的內(nèi)容為從 1 開始的自增序列。
  • x INT PATH '$.x' DEFAULT '100' ON EMPTY: 當對象中不存在成員 x 或者 x 的值為空時要使用默認值 100。
  • y INT PATH '$.y: 列名為 y，列的內(nèi)容是對應(yīng)了對象中的成員 y。
• 其中 $.x 和 $.y 中的 $ 代表了當前的上下文對象，也就是數(shù)組中的每個對象。

拉平內(nèi)嵌的數(shù)組示例：

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":[11, 12]}, {"x":20,"y":[21, 22]}]',
        '$[*]'
        COLUMNS (
            x INT PATH '$.x',
            NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$')
        )
    ) AS t;

    x     y    
+------+-------+
|   10 |   11  |
|   10 |   12  |
|   20 |   21  |
|   20 |   22  |
+------+-------+

示例說明：

• NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$')：展開 y 對應(yīng)的數(shù)組，并將 y 數(shù)組中的每個元素放入名稱為 y 的列中。

拉平內(nèi)嵌的對象：

SELECT
    *
FROM
    JSON_TABLE(
        '[{"x":10,"y":{"a":11,"b":12}},{"x":20,"y":{"a":21,"b":22}}]',
        '$[*]'
        COLUMNS (
            x INT PATH '$.x',
            NESTED PATH '$.y' COLUMNS (
                ya INT PATH '$.a',
                yb INT PATH '$.b'
            )
        )
    ) AS t;

    x       ya      yb   
+------+-------+-------+
|   10 |    11 |    12 |
|   20 |    21 |    22 |
+------+-------+-------+

示例說明：

• NESTED PATH '$.y' 子句將對象 y 中成員提取到 2 列：
  • 成員 a 被提取到列 ya。
  • 成員 b 被提取到列 yb。

二十七、JSON_TYPE 取類型

MySQL JSON_TYPE() 函數(shù)返回一個給定的 JSON 值的類型。

語法：

JSON_TYPE(json_value)

參數(shù)說明：

• json_value：必需的。一個 JSON 值。

返回值：（一個 utf8mb4 字符串）

• OBJECT: JSON 對象。
• ARRAY: JSON 數(shù)組
• BOOLEAN: JSON 布爾值
• NULL: JSON null 值
• INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT,INT 和 BIGINT 類型的值
• DOUBLE: MySQL DOUBLE和 FLOAT 類型的值
• DECIMAL: MySQL DECIMAL 和 NUMERIC 類型的值
• DATETIME: MySQL DATETIME 和 TIMESTAMP 類型的值
• DATE: MySQL DATE 類型的值
• TIME: MySQL TIME 類型的值
• STRING: MySQL CHAR, VARCHAR, TEXT, ENUM 和 SET
• BLOB: MySQL BINARY, VARBINARY, BLOB 和 BIT
• OPAQUE: 以上之外的類型

示例：

SELECT 
    JSON_TYPE('true'),	// BOOLEAN
    JSON_TYPE('null'),	// NULL
    JSON_TYPE('"abc"');	// STRING

二十八、JSON_VALID 驗證有校性

MySQL JSON_VALID() 函數(shù)返回 0 和 1 來指示給定的參數(shù)是否是一個有效的 JSON 文檔。

語法：

JSON_VALID(str)

參數(shù)說明：

• str：必需的。需要被驗證的內(nèi)容。

返回值：

• 1：是JSON文檔。
• 0：不是JSON文檔。
• 返回 NULL情況：參數(shù)為 NULL。

示例：

SELECT 
    JSON_VALID(1), 			  // 0
	JSON_VALID('1'),          // 1   
    JSON_VALID(true),         // 0
	JSON_VALID('true'),       // 1
    JSON_VALID('abc'),        // 0
	JSON_VALID('"abc"'),      // 1
	JSON_VALID('{"a": 1}'),   // 1
	JSON_VALID('{a: 1}');     // 0

二十九、JSON_VALUE 提取指定路徑的值

MySQL JSON_VALUE() 函數(shù)從一個指定的 JSON 文檔中提取指定路徑的值并返回。

語法：

JSON_VALUE(json, path [RETURNING type] [{NULL | ERROR | DEFAULT value} ON EMPTY] [{NULL | ERROR | DEFAULT value} ON ERROR])

參數(shù)說明：

• json：必需的。一個 JSON 文檔。

• path：必需的。一個路徑表達式。

• RETURNING type

  可選的。他決定了返回值的類型。您可以使用下面值中的一個：

  • FLOAT
  • DOUBLE
  • DECIMAL
  • SIGNED
  • UNSIGNED
  • DATE
  • TIME
  • DATETIME
  • YEAR (MySQL 8.0.22 and later)
  • CHAR
  • JSON
• {NULL | ERROR | DEFAULT value} ON EMPTY：可選的。如果指定了，它決定了指定路徑下沒有數(shù)據(jù)的返回值：
  • NULL ON EMPTY: 如果指定路徑下沒有數(shù)據(jù)，JSON_VALUE() 函數(shù)將返回 NULL，這是默認的行為。
  • DEFAULT value ON EMPTY: 如果指定路徑下沒有數(shù)據(jù)，JSON_VALUE() 函數(shù)將返回 value。
  • ERROR ON EMPTY: 如果指定路徑下沒有數(shù)據(jù)，JSON_VALUE() 函數(shù)將拋出一個錯誤。
• {NULL | ERROR | DEFAULT value} ON ERROR：可選的。如果指定了，它決定了處理錯誤的邏輯：
  • NULL ON ERROR: 如果有錯誤，JSON_VALUE() 函數(shù)將返回 NULL，這是默認的行為。
  • DEFAULT value ON ERROR: 如果有錯誤，JSON_VALUE() 函數(shù)將返回 value。
  • ERROR ON ERROR: 如果有錯誤，JSON_VALUE() 函數(shù)將拋出一個錯誤。

返回值：

• 路徑上的數(shù)據(jù)。
• 報錯情況： 
  • json 不是有效的 JSON 文檔。
  • path 不是有效的路徑表達式。

示例：

SET @json = '[
  {
    "name": "cxian",
    "age": 22,
  },
  {
    "name": "jie",
    "age": 23  }
]';

SELECT 
    JSON_VALUE(@json, '$[0].age'), // 22
    JSON_VALUE(@json, '$[1].age'); // 23
    JSON_VALUE(@json, '$[1].age' RETURNING DECIMAL(9,2)), // 23.00
    JSON_VALUE(@json, '$[0].note' DEFAULT 'Nothing' ON EMPTY) // Nothing

三十、MEMBER OF 是否為數(shù)組的元素

MySQL MEMBER OF() 函數(shù)檢查一個指定的值是否是一個 JSON 數(shù)組中的元素。

語法：

value MEMBER OF(value, json_array)

參數(shù)說明：

• value：必需的。一個值。它可以是任意類型。

• json_array：必需的。一個 JSON 數(shù)組。

返回值：

• 1：json_array中包含value，或 json_array為值且與value相等。
• 0：數(shù)組中不包含value。
• 報錯情況：json_array 不是有效的 JSON 文檔。

示例：

SELECT
    1 MEMBER OF('[1, 2, "a"]'),               // 1
    'a' MEMBER OF('"a"'),                     // 1
    CAST('true' AS JSON) MEMBER OF('true')    // 1

三十一、JSON_QUOTE 包裝成JSON串

MySQL JSON_QUOTE() 函數(shù)使用雙引號包裝一個值，使其成為一個 JSON 字符串值。

語法：

JSON_QUOTE(str)

參數(shù)說明：

• str：必需的。一個字符串。

返回值：

•  一個使用雙引號包圍的 JSON 字符串值。
• 返回 NULL情況：參數(shù)為 NULL。
• 特殊字符將使用反斜杠轉(zhuǎn)義：
  • \"：雙引號 "
  • \b：退格字符
  • \f：換頁符
  • \n：換行符
  • \r：回車符
  • \t：制表符
  • \\：反斜杠 \
  • \uXXXX：Unicode 值 XXXX 的 UTF-8 字節(jié)

示例：

SELECT
    JSON_QUOTE('1'),       // "1"
    JSON_QUOTE('NULL'),    // "NULL"
    JSON_QUOTE('"NULL"')   // "\"NULL\""

總結(jié) 

到此這篇關(guān)于輕松上手MYSQL之JSON函數(shù)實現(xiàn)高效數(shù)據(jù)查詢與操作的文章就介紹到這了,更多相關(guān)MYSQL JSON函數(shù)數(shù)據(jù)查詢與操作內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論