目录 一、前言二、创建JSON文本的函数2.1.JSON_ARRAY(转换json数组)2.2.JSON_OBJECT(转换json对象)2.3.JSON_QUOTE(转义字符串) 三、搜索JSON文本的函数3.1.JSON_
从5.7开始,Mysql开始支持JSON类型,用于存储jsON数据。提供数据类型的同时也提供了很多关于json的函数供我们使用,本篇文章基本上都来源于官网;https://dev.mysql.com/doc/refman/8.0/ja/json-function-reference.html
JSON_ARRAY()
接受一个值列表(可能为NULL),并返回一个包含这些值的JSON数组JSON_ARRAY([val[, val] ...])
参数 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)
JSON_OBJECT()
接受一个键值对(可以为空)列表,并返回一个包含这些键值对的JSON对象JSON_OBJECT([key, val[, key, val] ...])
key, val 表示一个键值对。如果参数数量为奇数,则会发生报错。
mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');+-----------------------------------------+| JSON_OBJECT('id', 87, 'name', 'carrot') |+-----------------------------------------+| {"id": 87, "name": "carrot"} |+-----------------------------------------+
JSON_QUOTE()
通过用双引号字符包裹 字符串 并转义内部引号和其他字符,将字符串引用为 JSON 值,然后将结果作为 utf8mb4 字符串返回。如果参数为NULL则返回NULL。JSON_QUOTE(string)
string 表示要引用的字符串,参数要用单引号括住,如果参数为 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]" |+-------------------------+
转义字符一般以反斜杠符号\
开头,用来说明后面的字符不是字符本身的含义,而是表示其它的含义。MySQL 中常见的转义字符如下表所示:
JSON 文档是否包含在目标 JSON 文档中
,或者是否在目标文档的指定路径上找到指定元素(如果提供了 path参数)。如果指定 JSON 文档包含在目标 JSON 文档中,该函数返回 1,否则返回 0。JSON_CONTAINS(target, candidate[, path])
参数 target 表示目标 JSON 文档,参数 candidate 用于指定 JSON 文档。
如果任意参数为 NULL,或者 path 参数未被识别为目标文档的一部分,则返回 NULL。
注意:target和candidate参数要使用单引号括住
以下情况都会发生报错:
比较的对象:
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数据类型当中的数组,是否包含某个元素:
实战当中可使用:SELECT json_contains( CAST( declaration_info -> '$.intellectual.intellectualProperty' AS JSON ), json_array( 2 ) from 表;
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
参数 json_doc 用于指定 JSON 文档,path 为路径参数。如果任何参数为 NULL,则返回 NULL。
对于 one_or_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 |+----------------------------------------+
JSON_EXTRACT(json_doc, path[, path] ...)
->
跟 JSON_EXTRACT
函数 都可以用来根据key值取value,他两个其实就是等价的。->
语法:(5.7.9版本开始支持的):json列名->"path"
(这个好像是不能直接使用json的,只能使用存储好json的列名).*
获取对象中的所有元素。参数 json_doc 用于指定 JSON 文档,path 为路径参数。
如果参数 json_doc 不是有效的 JSON 文档或任意 path 参数不是有效的路径表达式,则会发生错误。
返回值由 path 参数的所有匹配值组成。如果这些参数返回多个值,则匹配值将自动包裹为一个数组,顺序与生成它们的路径相对应。否则,返回单个匹配值。
这个是实际开发当中重点要使用的,所以我着重记录一下这块,下面我会频繁使用该json数据作为测试:
{"testFee": "3.00", "developList": [{"indicators": "的地方北方热", "annualTarget": "发热的随访人", "annualCompletion": "发VG热娃娃"}, {"indicators": "1111", "annualTarget": "2222", "annualCompletion": "3333"}], "fundBalance": {"totalFunds": "3.00", "totalProject": "3.00", "totalExpenses": "3.00"}}
然后新增的一个test表,有两个字段,json1一个是json类型的,另一个是longtext类型的,但是存储的数据都是上面那个json,主要也是想看一下,两个字段都存json是否都可以使用json函数:
[0]
就是取的整个数组,下标是从0开始的,[0]
就是取的数组第一条数据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
通过上面的取值会发现我们取出来的值都带有双引号:
mysql也给我们提供了专门的函数用于去除引号:
->>
(5.7.13版本开始支持的)语法:json列名->>"path"
JSON_UNQUOTE(JSON_EXTRACT(json_doc, path[, path] ...)
那会刚用的时候不知道有这个函数,于是便是用了trim()函数来去除的引号:trim函数用于删除字符串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’);
JSON_KEYS(json_doc[, path])
参数 json_doc 用于指定 JSON 文档,path 为路径参数。如果任何参数为 NULL,则返回 NULL。
如果 json_doc 参数指定的不是对象(指的是json数据类型当中的 对象),或者 path(如果给出)不对应任何路径,返回 NULL。
以下情况都会发生报错:
如果选定的对象为空,则结果为空。如果顶层值嵌套了子对象,则返回值不包括来自这些子对象的 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"] |+----------------------------------------------+
JSON_OVERLAPS(json_doc1, json_doc2)
参数 json_doc1 和 json_doc2 分别指定两个用于比较的 JSON 文档。如果两个参数都是标量,则函数执行简单的相等性测试。
该函数与 JSON_CONTAINS()
对应,它要求搜索的数组的所有元素都存在于被搜索的数组中。因此,JSON_CONTAINS()
对搜索键执行 AND
运算,而 JSON_OVERLAPS()
执行 OR
运算。
在比较两个数组时,如果它们共享一个或多个数组元素,则 JSON_OVERLAPS() 返回 1,否则返回 0。
在 WHERE 子句中使用 JSON_OVERLAPS() 对 InnoDB 表的 JSON 列的查询,可以使用多值索引进行优化。
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)
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
参数 json_doc 用于指定 JSON 文档,search_str 为搜索字符串参数(支持%字符串%模糊查询),escape_char 参数指定常量表达式,path 为路径参数。
如果任何参数为 NULL,则返回 NULL;如果文档中不存在路径或未找到指定字符串,也会返回 NULL。
对于 one_or_all 参数,可选值如下:
对于 search_str 参数,% 和 _ 字符与 LIKE 运算符的作用相同,含义如下:
如果要在搜索字符串中指定 %
或 _
字符,请在其前面加上转义字符。如果缺少 escape_char 参数或者为NULL,则转义字符默认值为 \。否则,escape_char 必须是一个空的常量或字符。
无论数据库的编码值为多少,search_str 和 path 总是作为 utf8mb4 字符串使用。
以下情况会发生报错:
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" |+-------------------------------------------+
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])on_empty: {NULL | ERROR | DEFAULT value} ON EMPTYon_error: {NULL | ERROR | DEFAULT value} ON ERROR
相关参数解释如下:
json_doc 参数指定有效的 JSON 文档。
path 参数是指向文档中某个位置的 JSON 路径,必须是字符串。
type 参数是支持如下数据类型:
如果没有使用 RETURNING 子句,则该函数的返回类型为 VARCHAR(512)。
当没有为返回类型指定字符集时,JSON_VALUE() 使用 utf8mb4 和二进制排序规则,并区分大小写; 如果将 utf8mb4指定为结果的字符集,则服务器使用此字符集的默认排序规则,不用区分大小写。
当在指定路径中未找到数据时,on_empty 子句用于指定 JSON_VALUE() 的行为。该子句取值如下:
当发生错误时,on_error 取值如下:
通常,JSON_VALUE() 会检查所有 JSON 输入(文档和路径)的有效性。如果其中任何一个为无效,则会抛出 SQL 错误,而不触发 ON ERROR 子句。
如果发生以下某一事件,就会触发 ON ERROR:
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'
元素 value MEMBER OF(json_array)
如果 value 是 json_array 的元素,则返回 1,否则返回 0。
value 必须是标量或 JSON 文档;如果它是标量,则运算符会将其视为 JSON 数组的元素。
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]'): 01 row in set (0.00 sec)mysql> SELECT cast( '{ "indicators" : "的地方北方热", "annualTarget" : "发热的随访人", "annualCompletion" : "发VG热娃娃" }' AS json ) MEMBER OF ('[{ '> "indicators": "的地方北方热", '> "annualTarget": "发热的随访人", '> "annualCompletion": "发VG热娃娃" '> }, { '> "indicators": "1111", '> "annualTarget": "2222", '> "annualCompletion": "3333" '> }]') as result;+--------+| result |+--------+| 1 |+--------+1 row in set (0.00 sec)
要对本身为数组的值使用此操作符,必须将其显式转换为JSON数组。你可以用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()函数执行必要的强制转换,如下所示:
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)
任何用作要测试的值或出现在目标数组中的JSON对象都必须使用CAST强制转换或JSON_OBJECT()。
此外,包含JSON对象的目标数组本身必须使用JSON_ARRAY进行强制转换,下面的语句序列演示了这一点:
mysql> SELECT'{ '> "indicators": "的地方北方热", '> "annualTarget": "发热的随访人", '> "annualCompletion": "发VG热娃娃" '> }' MEMBER OF ( '[{ '> "indicators": "的地方北方热", '> "annualTarget": "发热的随访人", '> "annualCompletion": "发VG热娃娃" '> }, { '> "indicators": "1111", '> "annualTarget": "2222", '> "annualCompletion": "3333" '> }]' ) AS result;+--------+| result |+--------+| 0 |+--------+1 row in set (0.00 sec)mysql> SELECT JSON_OBJECT( 'indicators', "的地方北方热", "annualTarget", "发热的随访人", "annualCompletion", "发VG热娃娃" ) MEMBER OF ( '[{ '> "indicators": "的地方北方热", '> "annualTarget": "发热的随访人", '> "annualCompletion": "发VG热娃娃" '> }, { '> "indicators": "1111", '> "annualTarget": "2222", '> "annualCompletion": "3333" '> }]' ) AS result;+--------+| result |+--------+| 1 |+--------+1 row in set (0.00 sec)
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
json_doc 参数用于指定 JSON 文档,path 为路径参数。如果任何参数为 NULL,则返回 NULL。
以下情况都会发生报错:
如果路径对应的值是一个标量或对象值,则该值将自动包裹在数组中,并将新值添加到该数组中。
数组:
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)
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
json_doc 参数用于指定 JSON 文档,path 为路径参数。如果任何参数为 NULL,则返回 NULL。
以下情况都会发生报错:
如果路径识别了某一数组元素,则在该元素位置插入相应的值,将所有后续值向右移动。如果路径识别的数组位置超出数组末尾的位置,则将值插入到数组末尾。
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]] |+----------------------------------------------------+
假如数组元素是对象,这时候要插入一定要使用cast进行转换一下,不然直接以字符串进行插入会出现问题,他会以为你让他追加字符串!
mysql> SELECT JSON_ARRAY_INSERT( '[{ '> "indicators": "的地方北方热", '> "annualTarget": "发热的随访人", '> "annualCompletion": "发VG热娃娃" '> }, { '> "indicators": "1111", '> "annualTarget": "2222", '> "annualCompletion": "3333" '> }]', '$[3]', cast( '{"indicators":"999","annualTarget":"999","annualCompletion":"999"}' AS json ) ) AS result FROM test;+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| result |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| [{"indicators": "的地方北方热", "annualTarget": "发热的随访人", "annualCompletion": "发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": "发热的随访人", '> "annualCompletion": "发VG热娃娃" '> }, { '> "indicators": "1111", '> "annualTarget": "2222", '> "annualCompletion": "3333" '> }]', '$[3]', '{"indicators":"999","annualTarget":"999","annualCompletion":"999"}' ) AS result FROM test;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| result |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| [{"indicators": "的地方北方热", "annualTarget": "发热的随访人", "annualCompletion": "发VG热娃娃"}, {"indicators": "1111", "annualTarget": "2222", "annualCompletion": "3333"},"{\"indicators\":\"999\",\"annualTarget\":\"999\",\"annualCompletion\":\"999\"}"] |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>
JSON_INSERT(json_doc, path, val[, path, val] ...)
json_doc 参数用于指定 JSON 文档,path 为路径参数。如果任何参数为 NULL,则返回 NULL。
以下情况都会发生报错:
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)
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 setobclient> 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 setobclient> SELECT JSON_MERGE('{"1": 2}', '{"a":100}');+--------------------------------------+| JSON_MERGE('{"1": 2}', '{"a":100}') |+--------------------------------------+| {"1": 2, "a": 100} |+--------------------------------------+1 row in set
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
SON_MERGE_PATCH() 执行合并的规则如下:
如果第一个参数不是对象,则将其视为空对象与第二个参数合并作为合并结果。
如果第二个参数不是对象,则合并结果是第二个参数。
如果两个参数都是对象,则合并结果是具有以下成员的对象:
obclient> SELECT JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]');+------------------------------------------------+| JSON_MERGE_PATCH('[1, 2, 3]', '[true, false]') |+------------------------------------------------+| [true, false] |+------------------------------------------------+1 row in setobclient> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}');+-------------------------------------------------+| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 23}') |+-------------------------------------------------+| {"id": 23, "name": "x"} |+-------------------------------------------------+1 row in setobclient> SELECT JSON_MERGE_PATCH('1', 'true');+-------------------------------+| JSON_MERGE_PATCH('1', 'true') |+-------------------------------+| true |+-------------------------------+1 row in setobclient> SELECT JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}');+---------------------------------------------+| JSON_MERGE_PATCH('[1, 2, 3]', '{"id": 45}') |+---------------------------------------------+| {"id": 45} |+---------------------------------------------+1 row in setobclient> 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 setobclient> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL);+--------------------------------------------+| JSON_MERGE_PATCH('{ "a": 1, "b":2 }',NULL) |+--------------------------------------------+| NULL |+--------------------------------------------+1 row in setobclient> 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
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
如果任何参数为 NULL,则返回 NULL。如果任何参数不是有效的 JSON 文档,则会报错。
JSON_MERGE_PRESERVE() 合并规则如下:
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 setobclient> SELECT JSON_MERGE_PRESERVE('{"name": "apple"}', '{"id": 56}');+--------------------------------------------------------+| JSON_MERGE_PRESERVE('{"name": "apple"}', '{"id": 56}') |+--------------------------------------------------------+| {"id": 56, "name": "apple"}|+--------------------------------------------------------+1 row in setobclient> SELECT JSON_MERGE_PRESERVE('1', 'true');+----------------------------------+| JSON_MERGE_PRESERVE('1', 'true') |+----------------------------------+| [1, true] |+----------------------------------+1 row in setobclient> 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 setobclient> 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 setobclient> 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
JSON_REMOVE(json_doc, path[, path] ...)
json_doc 参数用于指定 JSON 文档,path 为路径参数。如果任何参数为 NULL,则返回 NULL。
以下情况都会发生报错:
如果要移除的元素在文档中不存在,则不会产生错误。在这种情况下,路径不会影响文档。
obclient> SET @jn = '["a", ["b", "c"], "d"]';Query OK, 0 rows affectedobclient> SELECT JSON_REMOVE(@jn, '$[1]');+--------------------------+| JSON_REMOVE(@jn, '$[1]') |+--------------------------+| ["a", "d"] |+--------------------------+1 row in setobclient> SELECT JSON_REMOVE(@jn, '$[7]');+--------------------------+| JSON_REMOVE(@jn, '$[7]') |+--------------------------+| ["a", ["b", "c"], "d"] |+--------------------------+1 row in set
JSON_REPLACE(json_doc, path, val[, path, val] ...)
文档中现有路径的 path-value 对会用新值覆盖现有文档值。文档中不存在路径的 path-value 将被忽略且无效。
obclient> SET @jn = '{ "a": 1, "b": [2, 3, 4]}';Query OK, 0 rows affectedobclient> 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
JSON_SET(json_doc, path, val[, path, val] ...)
json_doc 参数用于指定 JSON 文档,path 为路径参数。如果任何参数为 NULL 或 path(如果给定)未定位到对象,则返回 NULL。
以下情况都会发生报错:
文档中现有路径的 path-value 对会用新值覆盖现有文档值。如果路径标识的对象不存在,则文档中不存在路径的路径值对会按照如下规则添加到文档中:
obclient> SET @jn = '{ "a": 1, "b": [2, 3]}';Query OK, 0 rows affectedobclient> 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_doc 参数用于指定 JSON 文档。如果参数为 NULL,则返回 NULL。
如果参数不是有效的 JSON 文档,则会发生错误。
空数组、空对象或标量值的深度为 1。仅包含深度为 1 的元素的非空数组深度为 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 |+-------------------------------+
JSON_LENGTH(json_doc[, path])
json_doc 参数用于指定 JSON 文档,path 为路径参数。如果任何参数为 NULL 或 path 参数未标识文档中的值,则返回 NULL。
以下情况都会发生报错:
JSON 文档的长度定义如下:
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 |+------------------------------------------------+
JSON_TYPE(json_val)
参数 json_val 可以是对象、数组或标量类型。
如果参数为 NULL,则返回 NULL。如果参数不是有效的 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 |+---------------------------------------+
obclient> SELECT JSON_VALID('{"a": 1}');+------------------------+| JSON_VALID('{"a": 1}') |+------------------------+| 1 |+------------------------+1 row in setobclient> SELECT JSON_VALID('oceanbase'), JSON_VALID('"oceanbase"');+-------------------------+---------------------------+| JSON_VALID('oceanbase') | JSON_VALID('"oceanbase"') |+-------------------------+---------------------------+| 0 | 1 |+-------------------------+---------------------------+1 row in set
JSON_PRETTY(json_val)
参数 json_val 必须是 JSON 值或 JSON 值的有效字符串表示形式。此值中存在的无关空格和换行符对输出没有影响。如果该值不是 JSON 文档,或者无法进行解析,则该函数将执行失败并显示错误。
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
JSON_STORAGE_SIZE(json_val)
json_val 参数必须是有效的 JSON 文档或可以解析为一个字符串。在 json_val 是字符串的情况下,该函数将字符串解析为 JSON 并将其转换为二进制后返回存储空间的二进制字节数。
obclient> CREATE TABLE jtbl (jcol JSON);Query OK, 0 rows affectedobclient> INSERT INTO jtbl VALUES ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');Query OK, 1 row affectedobclient> 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_ARRAYAGG(col_or_expr)[over_clause]
参数 col_or_expr 为列或表达式。如果结果不包含行,或者出现错误,则返回 NULL。
obclient> CREATE TABLE tbl1 (oid INT, attr VARCHAR(100), value VARCHAR(100));Query OK, 0 rows affectedobclient> 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: 0obclient> SELECT oid, JSON_ARRAYAGG(attr) AS attributes FROM tbl1 GROUP BY oid;+------+---------------------+| oid | attributes |+------+---------------------+| 2 | ["color", "fabric"] || 3 | ["color", "shape"] |+------+---------------------+2 rows in setobclient> INSERT INTO tbl1 SELECT * FROM tbl1;Query OK, 4 rows affected Records: 4 Duplicates: 0 Warnings: 0obclient> 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
JSON_OBJECTAGG(key, value) [over_clause]
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 setobclient> CREATE TABLE tbl2(c VARCHAR(10), i INT);Query OK, 0 rows affectedobclient> INSERT INTO tbl2 VALUES ('key', 3), ('key', 4), ('key', 5);Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0obclient> SELECT c, i FROM tbl2;+------+------+| c | i |+------+------+| key | 3 || key | 4 || key | 5 |+------+------+3 rows in setobclient> SELECT JSON_OBJECTAGG(c, i) FROM tbl2;+----------------------+| JSON_OBJECTAGG(c, i) |+----------------------+| {"key": 5} |+----------------------+1 row in set
来源地址:https://blog.csdn.net/weixin_43888891/article/details/130431272
--结束END--
本文标题: Mysql当中Json相关的函数详解
本文链接: https://www.lsjlt.com/news/374521.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0