目录

一、JSON_EXTRACT 提取指定数据

二、JSON_UNQUOTE 取消双引号

三、JSON_KEYS 取成员的数组

四、JSON_ARRAY 将参数转为数组

五、JSON_OBJECT 参数转为对象

六、JSON_DEPTH 取JSON深度

七、JSON_LENGTH 取节点长度

八、JSON_CONTAINS 判断是否包含

九、JSON_ARRAY_APPEND 追加值

十、JSON_ARRAY_INSERT 指定位置插入值

十一、JSON_CONTAINS_PATH 判断是否存在

十二、JSON_INSERT 插入数据

十三、JSON_MERGE 多个JSON合并

十四、JSON_MERGE_PATCH 多个JSON替换合并

十五、JSON_MERGE_PRESERVE 多个JSON合并

十六、JSON_OVERLAPS 判断有无相同键或值

十七、JSON_PRETTY 格式化输出

十八、JSON_REMOVE 删除指定数据

十九、JSON_REPLACE 替换数据

二十、JSON_SCHEMA_VALID 验证JSON文档

二十一、JSON_SCHEMA_VALIDATION_REPORT 验证JSON文档

二十二、JSON_SEARCH 查找

二十三、JSON_SET 插入或更新数据

二十四、JSON_STORAGE_FREE 释放空间

二十五、JSON_STORAGE_SIZE 占用字节数

二十六、JSON_TABLE 提取数据

二十七、JSON_TYPE 取类型

二十八、JSON_VALID 验证有校性

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

三十、MEMBER OF 是否为数组的元素

三十一、JSON_QUOTE 包装成JSON串


一、JSON_EXTRACT 提取指定数据

MySQL JSON_EXTRACT() 函数在 JSON 文档提取路径表达式指定的数据并返回。

语法:

JSON_EXTRACT(json, path, ...)

参数说明:

  • json:必需的。一个 JSON 文档。
  • path:必需的。至少指定一个路径表达式。

返回值:

  • 返回 JSON 文档中由路径表达式匹配的所有的值。
  • 返回NULL情况:
    •  不存在指定的路径。
    • 任意一个参数为 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() 函数取消双引号引用 JSON 值,并将结果作为字符串返回。

语法:

JSON_UNQUOTE(json_val)

参数说明:

  • json_val:必需的。一个字符串。

返回值:

  •  取消双引号引用 JSON 值
  • 返回NULL情况:参数为 NULL。
  • 报错情况:不是有效的 JSON 字符串文本。能够识别下表中的转义字符:
    • \":双引号 "
    • \b退格字符
    • \f换页符
    • \n换行符
    • \r回车符
    • \t制表符
    • \\反斜杠 \
    • \uXXXXUnicode 值 XXXX 的 UTF-8 字节

示例:

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

CASE函数将字符串转为JSON类型。关于CASE函数详见:https://cxian.blog.csdn.net/article/details/134231729

三、JSON_KEYS 取成员的数组

MySQL JSON_KEYS() 函数返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。

语法:

JSON_KEYS(json)
JSON_KEYS(json, path)

参数说明:

  • json:必需的。一个 JSON 对象文档。

  • path:可选的。路径表达式。

返回值:

  •  返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。
  • 若指定了路径表达式,则返回路径表达式匹配的 JSON 对象中的最上层的成员组成的数组。
  • 返回 NULL情况:
    • 无路径,JSON 文档不是一个 JSON 对象。
    • 有路径,路径匹配的 JSON 值不是 JSON 对象。
    • 任意参数为 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 将参数转为数组

MySQL JSON_ARRAY() 函数返回一个包含了所有参数的 JSON 数组。

语法:

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

参数说明:

  • value1[, value2[, ...]]:可选的。一些值,他们将被放在 JSON 数组中。

返回值:

  • 包含了所有参数的 JSON 数组。
  • 转换情况:
    • TRUE 被转换为 true
    • FALSE 被转换为 false
    • NULL 被转换为 null
    • 日期,时间,日期时间 被转换为 字符串 

示例:

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 参数转为对象

MySQL JSON_OBJECT() 函数返回一个包含了由参数指定的所有键值对的 JSON 对象。

语法:

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

参数说明:

  • key:必需的。对象中的键。
  • value:必需的。对象中的 key 的值。

返回值:

  •  一个包含了所有键值对的 JSON 对象。
  • 报错情况:
    • key 是 NULL。
    • 奇数个参数。

示例:

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() 函数返回一个 JSON 文档的最大深度。

语法:

JSON_DEPTH(json)

参数说明:

  • json:必需的。一个 JSON 文档。

返回值:

  • 一个 JSON 文档的最大深度。规则如下:
    • 空的数组、空的对象或者纯值的深度是 1。
    • 仅包含深度为 1 的元素的数组的深度是 2。
    • 所有成员的值的深度为 1 的对象的深度是 2。
    • 其他 JSON 文档的深度都大于 2。
  • 返回 NULL情况:参数为 NULL。
  • 报错情况:参数不是有效的 JSON 文档。

示例:

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

七、JSON_LENGTH 取节点长度

MySQL JSON_LENGTH() 函数返回 JSON 文档或者 JSON 文档中通过路径指定的节点的长度。

语法:

JSON_LENGTH(json)
JSON_LENGTH(json, path)

参数说明:

  • json:必需的。一个 JSON 文档。

  • path:可选的。一个路径表达式。

返回值:

  • 有path: 返回 JSON 文档中由路径指定的值的长度。
  • 无path:返回 JSON 文档的长度。
  • 计算 JSON 文档的长度规则:
    • 纯值的长度是 1。
    • 数组的长度是数组元素的数量。
    • 对象的长度是对象成员的数量。
    • 内嵌的数组或对象不参与计算长度。
  • 返回 NULL情况:
    • 不存在指定的路径。
    • 任意一个参数为 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() 函数检查一个 JSON 文档中是否包含另一个 JSON 文档。

语法:

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

参数说明:

  • target_json必需的。一个 JSON 文档。

  • candidate_json:必需的。被包含的 JSON 文档。

  • path:可选的。一个路径表达式。

返回值:

  •  包含:返回1。
  • 不包含:返回0。
  • 返回 NULL情况:
    • JSON 文档中不存在指定的路径。
    • 任意一个参数为 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() 函数向 JSON 文档中的指定的数组中追加一个值并返回修改后的 JSON 文档。

语法:

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

参数说明:

  • json:必需的。被修改的 JSON 文档。

  • path:必需的。添加新元素的路径。一个有效的路径表达式,它不能包含 * 或 **

  • value:必需的。被添加到数组的新元素值。

返回值:

  • 追加后的 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() 函数向 JSON 文档中的指定的数组中的指定位置插入一个值并返回新的 JSON 文档。

语法:

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

参数说明:

  • json:必需的。被修改的 JSON 文档。

  • path:必需的。插入新元素的数组元素位置。一个有效的路径表达式,它不能包含 * 或 **。比如 $[0] 和 $.a[0] 表示在数组的开头插入新元素。

  • value:必需的。被插入到数组的新元素值。

返回值:

  • 插入后的JSON文档。
  • 返回 NULL情况:JSON 文档或者路径为 NULL。
  • 报错情况:
    • json 不是有效的 JSON 文档。
    • path 不是有效的路径表达式或者其中包含 * 或 **。
    • path 指示的不是数组元素的路径。

示例:

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() 函数检查一个 JSON 文档中在指定的路径上是否有值存在。

语法:

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

参数说明:

  • json:必需的。一个 JSON 文档。

  • one_or_all:必需的。可用值:'one''all'。它指示是否检查所有的路径。

  • path:必需的。您应该至少指定一个路径表达式。

返回值:

  •  有值返回 1, 否则返回 0。
  • one_or_all参数说明:
    • '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 插入数据

MySQL JSON_INSERT() 函数向一个 JSON 文档中插入数据并返回新的 JSON 文档。

语法:

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

参数说明:

  • json:必需的。被修改的 JSON 文档。

  • path:必需的。一个有效的路径表达式,它不能包含 * 或 **

  • value:必需的。被插入的数据。

返回值:

  • 插入后的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() 函数合并两个或多个 JSON 文档并返回合并的结果。

语法:

JSON_MERGE(json1, json2, ...)

参数说明:

  • json1:必需的。一个 JSON 对象文档。

  • json2:必需的。一个 JSON 对象文档。

返回值:

  •  合并后的新JSON文档。
  • 返回 NULL情况:任意一个参数为 NULL。
  • 报错情况:参数不是有效的 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() 函数对两个或多个 JSON 文档执行替换合并并返回合并的结果。

替换合并:在相同键值时,只保留后面的值。

语法:

JSON_MERGE_PATCH(json1, json2, ...)

参数说明:

  • json1:必需的。一个 JSON 对象文档。

  • json2:必需的。一个 JSON 对象文档。

返回值:

  • 合并后的JSON文档。
  • 合并规则如下:
    • 若第一个参数不是对象,则合并的结果与第二个参数合并空对象的结果相同。
    • 若第二个参数不是对象,则合并的结果为第二个参数。
    • 若两个参数都是对象,则合并的对象具有以下成员:
      • 只存在于第一个对象中的成员。
      • 只存在于第二个对象中且值不是 null 的成员。
      • 存在于第二个对象且值不是 null ,并且在第一个对象中有对应的相同键的成员。
  • 返回 NULL情况:任意一个参数为NULL。
  • 报错情况:任意一个参数不是有效的 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() 函数合并两个或多个 JSON 文档并返回合并的结果。

此函数和 JSON_MERGE_PATCH() 用法相同,但是合并逻辑有所不同。

不同规则合并如下:

  • 两个数组合并为一个数组,保留所有数组中的元素。
  • 两个对象合并为一个对象,保留所有的键和值。
  • 一个纯值会被包装成一个数组并作为数组进行合并。
  • 对象和数组合并时,会将对象包装到一个数组中并作为数组进行合并。

示例:

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() 函数检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素。

语法:

JSON_OVERLAPS(json1, json2)

参数说明:

  • json1:必需的。一个 JSON 文档。

  • json2:必需的。另一个 JSON 文档。

返回值:

  • 两个JSON文档重叠的内容返回 1,否则返回 0。
  • 函数判断规则如下:
    • 比较两个数组时,如果两个数组至少有一个相同的元素返回 1,否则返回 0
    • 比较两个对象时,如果两个对象至少有一个相同的键值对返回 1,否则返回 0
    • 比较两个纯值时,如果两个值相同返回 1,否则返回 0
    • 比较纯值和数组时,如果值是这个数组中的直接元素返回 1,否则返回 0
    • 比较纯值和对象的结果为 0
    • 比较数组和对象的结果为 0
  • 返回 NULL情况:参数为 NULL。
  • 报错情况:任意一个参数不是有效的 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() 函数格式化输出一个 JSON 文档,以便更易于阅读。

语法:

JSON_PRETTY(json)

参数说明:

  • 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 删除指定数据

MySQL JSON_REMOVE() 函数从一个 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档。

语法:

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

参数说明:

  • 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 替换数据

MySQL JSON_REPLACE() 函数在一个 JSON 文档中替换已存在的数据并返回新的 JSON 文档。

语法:

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

参数说明:

  • json:必需的。被修改的 JSON 文档。

  • path:必需的。一个有效的路径表达式,它不能包含 * 或 **

  • value:必需的。新的数据。

返回值:

  • 替换后的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() 函数根据指定的 JSON 模式验证一个 JSON 文档,并返回 1 表是验证通过或者返回 0 表示验证不通过。

语法:

JSON_SCHEMA_VALID(schema, json_doc)

参数说明:

  • schema:必需的。一个 JSON 模式。它必须是一个有效的 JSON 对象。

  • json_doc:必需的。被验证的 JSON 文档。

返回值:

  •  1 :JSON 文档通过了验证。
  •  0 :JSON 文档没有通过验证。
  • 返回 NULL情况:任何一个参数为 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() 函数根据指定的 JSON 模式验证一个 JSON 文档,并返回一个验证报告。

语法:

JSON_SCHEMA_VALIDATION_REPORT(schema, json_doc)

参数说明:

  • schema:必需的。一个 JSON 模式。它必须是一个有效的 JSON 对象。

  • json_doc:必需的。被验证的 JSON 文档。

返回值:

  •  验证结果的报告。
  • {"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() 函数返回一个给定字符串在一个 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)

参数说明:

  • json:必需的。一个 JSON 文档。

  • one_or_all:必需的。可用值:'one''all'规则如下

    • 'one':返回第一个匹配的路径。
    • 'all':返回所有匹配的路径。所有的路径会包装在一个数组内返回。
  • search_str:必需的。被搜索的字符串。 您可以在 search_str 参数中使用 % 和 _ 通配符,就像 LIKE 一样:

    • % 匹配任意数量的任意字符。
    • _ 匹配一个任意字符。
  • escape_char:可选的。 如果 search_str 中包含 % 和 _,需要在他们之前添加转移字符。默认是 \

  • path:可选的。只能在此路径下进行搜索。

返回值:

  • 一个路径字符串或者由多个路径组成的数组。
  • 返回 NULL情况:
    • 未搜索到指定的字符串。
    • JSON 文档中不存在指定的 path。
    • 任意一个参数为 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 插入或更新数据

MySQL JSON_SET() 函数在一个 JSON 文档中插入或更新数据并返回新的 JSON 文档。它相当于是 JSON_INSERT() 和 JSON_REPLACE() 的组合。

语法:

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

参数说明:

  • json:必需的。被修改的 JSON 文档。

  • path:必需的。一个有效的路径表达式,它不能包含 * 或 **

  • value:必需的。要设置的数据。

返回值:

  •  插入或更新数据并返回新的 JSON 文档。规则如下:
    • 存在路径:更新。
    • 不存在路径:添加。
  • 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() 函数返回一个 JSON 列在被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 更新后所释放的空间。

语法:

JSON_STORAGE_FREE(json)

参数说明:

  • json:必需的。一个 JSON 文档。它可以是一个 JSON 字符串,或者一个 JSON 列。

返回值:

  • 一个 JSON 列在被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 更新后所释放的空间。它可以接受一个 JSON 字符串,或者一个 JSON 列作为参数。
  • 返回 0 情况:
    • 参数是一个 JSON 字符串。
    • 列没有被更新过,或不是使用JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 部分更新。
    • 内容在更新后变的更大了
  • 返回更新后释放的空间:列(数据库表的列)被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 部分更新。
  • 返回 NULL情况:参数为 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 占用字节数

MySQL JSON_STORAGE_SIZE() 函数返回存储一个 JSON 文档的二进制表示所占用的字节数。

语法:

JSON_STORAGE_SIZE(json)

参数说明:

  • json:必需的。一个 JSON 文档。它可以是一个 JSON 字符串,或者一个 JSON 列。

返回值:

  • JSON 文档的二进制表示所占用的字节数。
  • 返回 NULL:参数为 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 提取数据

MySQL JSON_TABLE() 函数从一个指定的 JSON 文档中提取数据并返回一个具有指定列的关系表。

语法:

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

参数说明:

  • json:必需的。一个 JSON 文档。

  • path:必需的。一个路径表达式。

  • column:必需的。定义一个列。可以使用如下 4 中方式定义列:

    • name FOR ORDINALITY: 生成一个从 1 开始的计数器列,名字为 name
    • name type PATH string_path [on_empty] [on_error]: 将由路径表达式 string_path 指定的值放在名字为 name 的列中。
    • name type EXISTS PATH string_path:根据 string_path 指定的位置是否有值将 1 或 0 放在名字为 name 的列中。
    • NESTED [PATH] path COLUMNS (column[, column[, ...]]): 将内嵌的对象或者数组中的数据拉平放在一行中。
  • on_empty:可选的。如果指定了,它决定了指定路径下没有数据时的返回值:
    • NULL ON EMPTY: 如果指定路径下没有数据,JSON_TABLE() 函数将使用 NULL,这是默认的行为。
    • DEFAULT value ON EMPTY: 如果指定路径下没有数据,JSON_TABLE() 函数将使用 value。
    • ERROR ON EMPTY: 如果指定路径下没有数据,JSON_TABLE() 函数将抛出一个错误。
  • on_error:可选的。如果指定了,它决定了处理错误的逻辑:
    • NULL ON ERROR: 如果有错误,JSON_TABLE() 函数将使用 NULL,这是默认的行为。
    • DEFAULT value ON ERROR: 如果有错误,JSON_TABLE() 函数将使用 value。
    • ERROR ON ERROR: 如果有错误,JSON_TABLE() 函数将抛出一个错误。

返回值:

  •  具有指定列的关系表。

示例:

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|
+------+-------+--------+

示例说明:

  • 路径表达式$[*]: 数组中的每个元素,也就是数组中的那两个对象。
    • $[0]:只提取 JSON 数组中的第 1 元素
    • $[1]:只提取 JSON 数组中的第 2 元素
    • 以此类推
  • COLUMNS 子句定义了关系表中的 3 个列:
    • id FOR ORDINALITY: 列名为 id,列的内容为从 1 开始的自增序列。
    • x INT PATH '$.x' DEFAULT '100' ON EMPTY: 当对象中不存在成员 x 或者 x 的值为空时要使用默认值 100。
    • y INT PATH '$.y: 列名为 y,列的内容是对应了对象中的成员 y。
  • 其中 $.x 和 $.y 中的 $ 代表了当前的上下文对象,也就是数组中的每个对象。

拉平内嵌的数组示例:

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 数组中的每个元素放入名称为 y 的列中。

拉平内嵌的对象:

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() 函数返回一个给定的 JSON 值的类型。

语法:

JSON_TYPE(json_value)

参数说明:

  • json_value:必需的。一个 JSON 值。

返回值:(一个 utf8mb4 字符串)

  • OBJECT: JSON 对象。
  • ARRAY: JSON 数组
  • BOOLEAN: JSON 布尔值
  • NULL: JSON null 值
  • INTEGER: MySQL TINYINTSMALLINTMEDIUMINT,INT 和 BIGINT 类型的值
  • DOUBLE: MySQL DOUBLE和 FLOAT 类型的值
  • DECIMAL: MySQL DECIMAL 和 NUMERIC 类型的值
  • DATETIME: MySQL DATETIME 和 TIMESTAMP 类型的值
  • DATE: MySQL DATE 类型的值
  • TIME: MySQL TIME 类型的值
  • STRING: MySQL CHARVARCHARTEXTENUM 和 SET
  • BLOB: MySQL BINARYVARBINARYBLOB 和 BIT
  • OPAQUE: 以上之外的类型

示例:

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

二十八、JSON_VALID 验证有校性

MySQL JSON_VALID() 函数返回 0 和 1 来指示给定的参数是否是一个有效的 JSON 文档。

语法:

JSON_VALID(str)

参数说明:

  • str:必需的。需要被验证的内容。

返回值:

  • 1:是JSON文档。
  • 0:不是JSON文档。
  • 返回 NULL情况:参数为 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() 函数从一个指定的 JSON 文档中提取指定路径的值并返回。

语法:

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

参数说明:

  • 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:可选的。如果指定了,它决定了指定路径下没有数据的返回值:
    • ​​​​​​​NULL ON EMPTY: 如果指定路径下没有数据,JSON_VALUE() 函数将返回 NULL,这是默认的行为。
    • DEFAULT value ON EMPTY: 如果指定路径下没有数据,JSON_VALUE() 函数将返回 value
    • ERROR ON EMPTY: 如果指定路径下没有数据,JSON_VALUE() 函数将抛出一个错误。
  • ​​​​​​​{NULL | ERROR | DEFAULT value} ON ERROR:可选的。如果指定了,它决定了处理错误的逻辑:
    • NULL ON ERROR: 如果有错误,JSON_VALUE() 函数将返回 NULL,这是默认的行为。
    • DEFAULT value ON ERROR: 如果有错误,JSON_VALUE() 函数将返回 value
    • ERROR ON ERROR: 如果有错误,JSON_VALUE() 函数将抛出一个错误。

返回值:

  • 路径上的数据。
  • 报错情况: 
    • 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 是否为数组的元素

MySQL MEMBER OF() 函数检查一个指定的值是否是一个 JSON 数组中的元素。

语法:

value MEMBER OF(value, json_array)

参数说明:

  • value:必需的。一个值。它可以是任意类型。

  • json_array:必需的。一个 JSON 数组。

返回值:

  • 1:json_array中包含value,或 json_array为值且与value相等。
  • 0:数组中不包含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() 函数使用双引号包装一个值,使其成为一个 JSON 字符串值。

语法:

JSON_QUOTE(str)

参数说明:

  • str:必需的。一个字符串。

返回值:

  •  一个使用双引号包围的 JSON 字符串值。
  • 返回 NULL情况:参数为 NULL。
  • 特殊字符将使用反斜杠转义:
    • \":双引号 "
    • \b:退格字符
    • \f:换页符
    • \n:换行符
    • \r:回车符
    • \t:制表符
    • \\:反斜杠 \
    • \uXXXX:Unicode 值 XXXX 的 UTF-8 字节

示例:

SELECT
    JSON_QUOTE('1'),       // "1"
    JSON_QUOTE('NULL'),    // "NULL"
    JSON_QUOTE('"NULL"')   // "\"NULL\""
11-06 09:40