Mysql提供了许多JSON函数,用于对jsON数据进行各种处理。以下是一些常用的JSON函数。 建议收藏以备后续用到查阅参考。 目录 一、JSON_EXTRACT 提取指定数据 二、JSON_UNQUOTE 取消双引号 三、JSO
Mysql提供了许多JSON函数,用于对jsON数据进行各种处理。以下是一些常用的JSON函数。
建议收藏以备后续用到查阅参考。
目录
十四、JSON_MERGE_PATCH 多个JSON替换合并
十五、JSON_MERGE_PRESERVE 多个JSON合并
二十一、JSON_SCHEMA_VALIDATION_REPORT 验证JSON文档
mysql JSON_EXTRACT()
函数在 JSON 文档提取路径表达式指定的数据并返回。
语法:
JSON_EXTRACT(json, path, ...)
参数说明:
返回值:
NULL
。json
不是有效的 JSON 文档。path
不是有效的路径表达式。示例:
SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[1]'); // 2SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]'); // {"x": 3}
Mysql JSON_UNQUOTE()
函数取消双引号引用 JSON 值,并将结果作为字符串返回。
语法:
JSON_UNQUOTE(json_val)
参数说明:
json_val:
必需的。一个字符串。返回值:
NULL。
报错情况:
不是有效的 JSON 字符串文本。能够识别下表中的转义字符: "
\b
:退格字符
\f
:换页符
\n
:换行符
\r
:回车符
\t
:制表符
\\
:反斜杠 \
\uXXXX
:Unicode 值 XXXX 的 UTF-8 字节
示例:
SELECT JSON_UNQUOTE('"123456"'); // 123456SELECT JSON_UNQUOTE(CAST('"cxian"' AS JSON)); // cxian
CASE函数将字符串转为JSON类型。关于CASE函数详见:https://cxian.blog.csdn.net/article/details/134231729
MySQL JSON_KEYS()
函数返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。
语法:
JSON_KEYS(json)JSON_KEYS(json, path)
参数说明:
json
:必需的。一个 JSON 对象文档。
path
:可选的。路径表达式。
返回值:
NULL情况:
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
MySQL JSON_ARRAY()
函数返回一个包含了所有参数的 JSON 数组。
语法:
JSON_ARRAY(value1[, value2[, ...]])
参数说明:
value1[, value2[, ...]]
:可选的。一些值,他们将被放在 JSON 数组中。
返回值:
示例:
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"]]
MySQL JSON_OBJECT()
函数返回一个包含了由参数指定的所有键值对的 JSON 对象。
语法:
JSON_OBJECT(key, value[, key2, value2, ...])
参数说明:
返回值:
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"}
MySQL JSON_DEPTH()
函数返回一个 JSON 文档的最大深度。
语法:
JSON_DEPTH(json)
参数说明:
json
:必需的。一个 JSON 文档。
返回值:
NULL情况:
参数为 NULL。
报错情况:
参数不是有效的 JSON 文档。示例:
SELECT JSON_DEPTH('[]'), // 1 JSON_DEPTH('[1, 2]'), // 2 JSON_DEPTH('[1, [2, 3]]'); // 3
MySQL JSON_LENGTH()
函数返回 JSON 文档或者 JSON 文档中通过路径指定的节点的长度。
语法:
JSON_LENGTH(json)JSON_LENGTH(json, path)
参数说明:
json
:必需的。一个 JSON 文档。
path
:可选的。一个路径表达式。
返回值:
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
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
:可选的。一个路径表达式。
返回值:
NULL情况:
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
MySQL JSON_ARRAY_APPEND()
函数向 JSON 文档中的指定的数组中追加一个值并返回修改后的 JSON 文档。
语法:
JSON_ARRAY_APPEND(json, path, value[, path2, value2] ...)
参数说明:
json
:必需的。被修改的 JSON 文档。
path
:必需的。添加新元素的路径。一个有效的路径表达式,它不能包含 *
或 **
。
value
:必需的。被添加到数组的新元素值。
返回值:
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]
MySQL JSON_ARRAY_INSERT()
函数向 JSON 文档中的指定的数组中的指定位置插入一个值并返回新的 JSON 文档。
语法:
JSON_ARRAY_INSERT(json, path, value[, path2, value2] ...)
参数说明:
json:
必需的。被修改的 JSON 文档。
path:
必需的。插入新元素的数组元素位置。一个有效的路径表达式,它不能包含 *
或 **
。比如 $[0]
和 $.a[0]
表示在数组的开头插入新元素。
value
:必需的。被插入到数组的新元素值。
返回值:
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]}]
MySQL JSON_CONTAINS_PATH()
函数检查一个 JSON 文档中在指定的路径上是否有值存在。
语法:
JSON_CONTAINS_PATH(json, one_or_all, path[, path])
参数说明:
json
:必需的。一个 JSON 文档。
one_or_all
:必需的。可用值:'one'
, 'all'
。它指示是否检查所有的路径。
path
:必需的。您应该至少指定一个路径表达式。
返回值:
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
MySQL JSON_INSERT()
函数向一个 JSON 文档中插入数据并返回新的 JSON 文档。
语法:
JSON_INSERT(json, path, value[, path2, value2] ...)
参数说明:
json
:必需的。被修改的 JSON 文档。
path
:必需的。一个有效的路径表达式,它不能包含 *
或 **
。
value
:必需的。被插入的数据。
返回值:
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"}
MySQL JSON_MERGE()
函数合并两个或多个 JSON 文档并返回合并的结果。
语法:
JSON_MERGE(json1, json2, ...)
参数说明:
json1
:必需的。一个 JSON 对象文档。
json2
:必需的。一个 JSON 对象文档。
返回值:
NULL情况:
任意一个参数为 NULL。
示例:
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]
MySQL JSON_MERGE_PATCH()
函数对两个或多个 JSON 文档执行替换合并并返回合并的结果。
替换合并:在相同键值时,只保留后面的值。
语法:
JSON_MERGE_PATCH(json1, json2, ...)
参数说明:
json1
:必需的。一个 JSON 对象文档。
json2
:必需的。一个 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}
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]}
MySQL JSON_OVERLAPS()
函数检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素。
语法:
JSON_OVERLAPS(json1, json2)
参数说明:
json1
:必需的。一个 JSON 文档。
json2
:必需的。另一个 JSON 文档。
返回值:
1
,否则返回 0
。1
,否则返回 0
。1
,否则返回 0
。1
,否则返回 0
。0
。0
。NULL情况:
参数为 NULL。
报错情况:
任意一个参数不是有效的 JSON 文档。示例:
SELECT JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]'), // 1JSON_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
MySQL JSON_PRETTY()
函数格式化输出一个 JSON 文档,以便更易于阅读。
语法:
JSON_PRETTY(json)
参数说明:
json
:必需的。一个 JSON 文档或 JSON 类型的值。
返回值:
示例:
SELECT JSON_PRETTY('[1, 2, 3]');[ 1, 2, 3]SELECT JSON_PRETTY('{"x": 1, "y": 2}');{ "x": 1, "y": 2}
MySQL JSON_REMOVE()
函数从一个 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档。
语法:
JSON_REMOVE(json, path[, path] ...)
参数说明:
json
:必需的。一个 JSON 文档。
path
:必需的。一个有效的路径表达式,它不能包含 *
或 **
。
返回值:
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}
MySQL JSON_REPLACE()
函数在一个 JSON 文档中替换已存在的数据并返回新的 JSON 文档。
语法:
JSON_REPLACE(json, path, value[, path2, value2] ...)
参数说明:
json
:必需的。被修改的 JSON 文档。
path
:必需的。一个有效的路径表达式,它不能包含 *
或 **
。
value
:必需的。新的数据。
返回值:
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]}
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}'), // 0JSON_SCHEMA_VALID(@schema, '{"x": 1, "y": 2}') // 1
MySQL JSON_SCHEMA_VALIDATION_REPORT()
函数根据指定的 JSON 模式验证一个 JSON 文档,并返回一个验证报告。
语法:
JSON_SCHEMA_VALIDATION_REPORT(schema, json_doc)
参数说明:
schema
:必需的。一个 JSON 模式。它必须是一个有效的 JSON 对象。
json_doc
:必需的。被验证的 JSON 文档。
返回值:
示例:
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}
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情况:
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"]
MySQL JSON_SET() 函数在一个 JSON 文档中插入或更新数据并返回新的 JSON 文档。它相当于是 JSON_INSERT() 和 JSON_REPLACE() 的组合。
语法:
JSON_SET(json, path, value[, path2, value2] ...)
参数说明:
json
:必需的。被修改的 JSON 文档。
path
:必需的。一个有效的路径表达式,它不能包含 *
或 **
。
value
:必需的。要设置的数据。
返回值:
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]}
MySQL JSON_STORAGE_FREE() 函数返回一个 JSON 列在被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 更新后所释放的空间。
语法:
JSON_STORAGE_FREE(json)
参数说明:
json
:必需的。一个 JSON 文档。它可以是一个 JSON 字符串,或者一个 JSON 列。
返回值:
。
NULL情况:
参数为 NULL。
报错情况:json
不是有效的 JSON 文档。示例:
DROP TABLE IF EXISTS test;CREATE TABLE test ( json_col JSON NOT NULL);INSERT INTO testVALUES ('{"x": 1, "y": "99"}');SELECT json_col, // {"x": 1, "y": "99"} JSON_STORAGE_SIZE(json_col), // 24 JSON_STORAGE_FREE(json_col) // 0FROM test;// {"x": 1, "y": "99"} | 24 | 0UPDATE testSET json_col = JSON_REMOVE(json_col, '$.y');SELECT json_col, // {"x": 1} JSON_STORAGE_SIZE(json_col), // 24 JSON_STORAGE_FREE(json_col) // 11FROM test;
MySQL JSON_STORAGE_SIZE()
函数返回存储一个 JSON 文档的二进制表示所占用的字节数。
语法:
JSON_STORAGE_SIZE(json)
参数说明:
json
:必需的。一个 JSON 文档。它可以是一个 JSON 字符串,或者一个 JSON 列。
返回值:
NULL:
参数为 NULL。
json
不是有效的 JSON 文档。示例:
SELECT JSON_STORAGE_SIZE('100'), // 3 JSON_STORAGE_SIZE('"a"'), // 3JSON_STORAGE_SIZE('true'), // 2JSON_STORAGE_SIZE('null'); // 2
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 EMPTYon_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[, ...]])
: 将内嵌的对象或者数组中的数据拉平放在一行中。返回值:
示例:
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|+------+-------+--------+
示例说明:
拉平内嵌的数组示例:
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 |+------+-------+
示例说明:
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。
MySQL JSON_TYPE()
函数返回一个给定的 JSON 值的类型。
语法:
JSON_TYPE(json_value)
参数说明:
json_value
:必需的。一个 JSON 值。
返回值:(一个 utf8mb4
字符串)
OBJECT
: JSON 对象。ARRAY
: JSON 数组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
MySQL JSON_VALID()
函数返回 0 和 1 来指示给定的参数是否是一个有效的 JSON 文档。
语法:
JSON_VALID(str)
参数说明:
str
:必需的。需要被验证的内容。
返回值:
NULL情况:
参数为 NULL。
示例:
SELECT JSON_VALID(1), // 0JSON_VALID('1'), // 1 JSON_VALID(true), // 0JSON_VALID('true'), // 1 JSON_VALID('abc'), // 0JSON_VALID('"abc"'), // 1JSON_VALID('{"a": 1}'), // 1JSON_VALID('{a: 1}'); // 0
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
MySQL MEMBER OF()
函数检查一个指定的值是否是一个 JSON 数组中的元素。
语法:
value MEMBER OF(value, json_array)
参数说明:
value
:必需的。一个值。它可以是任意类型。
json_array
:必需的。一个 JSON 数组。
返回值:
json_array
不是有效的 JSON 文档。示例:
SELECT 1 MEMBER OF('[1, 2, "a"]'), // 1 'a' MEMBER OF('"a"'), // 1 CAST('true' AS JSON) MEMBER OF('true') // 1
MySQL JSON_QUOTE()
函数使用双引号包装一个值,使其成为一个 JSON 字符串值。
语法:
JSON_QUOTE(str)
参数说明:
str
:必需的。一个字符串。
返回值:
NULL情况:
参数为 NULL。
示例:
SELECT JSON_QUOTE('1'), // "1" JSON_QUOTE('NULL'), // "NULL" JSON_QUOTE('"NULL"') // "\"NULL\""
--结束END--
本文标题: MYSQL JSON函数详解和实战(JSON函数大全,内含示例)
本文链接: https://www.lsjlt.com/news/556113.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-30
2024-05-30
2024-05-30
2024-05-30
2024-05-30
2024-05-30
2024-05-30
2024-05-30
2024-05-30
2024-05-30
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0