文章目录
一、概述
1、什么是JSON
略。自行百度。
2、MySQL的JSON
JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 或 TEXT ）来保存 JSON 文档。
MySQL 8.0版本中增加了对JSON类型的索引支持。可以使用CREATE INDEX语句创建JSON类型的索引，提高JSON类型数据的查询效率。
存储JSON文档所需的空间与存储LONGBLOB或LONGTEXT所需的空间大致相同。
在MySQL 8.0.13之前，JSON列不能有非空的默认值。
JSON 类型比较适合存储一些列不固定、修改较少、相对静态的数据。MySQL支持JSON格式的数据之后，可以减少对非关系型数据库的依赖。
3、varchar、text、json类型字段的区别
这三种类型的字段，都可以存储json格式，查询起来似乎正常的json函数也能用，这三者存储json类型的数据有什么区别吗？
我们接下来测试一下。
二、JSON类型的创建
1、建表指定
CREATETABLE`users`(`id`intNOTNULLAUTO_INCREMENTCOMMENT'id',`name`varchar(50)DEFAULTNULLCOMMENT'名字',`json_data` json DEFAULTNULLCOMMENT'json数据',`info`varchar(2000)DEFAULTNULLCOMMENT'普通数据',`text`textCOMMENT'text数据',PRIMARYKEY(`id`))ENGINE=InnoDB;
2、修改字段
-- 添加json字段ALTERTABLE users ADDCOLUMN`test_json` JSON DEFAULTNULLCOMMENT'测试';-- 修改字段类型为jsonALTERTABLE users modify test_json JSON DEFAULTNULLCOMMENT'测试';-- 删除json字段ALTERTABLE users DROPCOLUMN test_json;
三、JSON类型的插入
1、字符串直接插入
varchar、text、json格式都支持，也可以插入更复杂的嵌套json：
-- 插入数组insertinto users(json_data)values('[1, "abc", null, true, "08:45:06.000000"]');insertinto users(info)values('[1, "abc", null, true, "08:45:06.000000"]');insertinto users(text)values('[1, "abc", null, true, "08:45:06.000000"]');-- 插入对象insertinto users(json_data)values('{"id": 87, "name": "carrot"}');insertinto users(info)values('{"id": 87, "name": "carrot"}');insertinto users(text)values('{"id": 87, "name": "carrot"}');-- 插入嵌套jsoninsertinto users(json_data)values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');insertinto users(info)values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');insertinto users(text)values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
但是json格式的字段，插入时会自动校验格式，如果格式不是json的，会报错：
insertinto users(json_data)values('{"id", "name": "carrot"}');>3140- Invalid JSON text: "Missing a colon after a name of object member." at position 5invalueforcolumn'users.json_data'.
2、JSON_ARRAY()函数插入数组
-- 格式：
JSON_ARRAY([val[, val]...])-- 使用JSON_ARRAY()函数创建数组 ： [1, "abc", null, true, "08:09:38.000000"]insertinto users(json_data)values(JSON_ARRAY(1,"abc",null,true,curtime()));insertinto users(info)values(JSON_ARRAY(1,"abc",null,true,curtime()));insertinto users(text)values(JSON_ARRAY(1,"abc",null,true,curtime()));
3、JSON_OBJECT()函数插入对象
对于 JSON 文档，KEY 名不能重复。
如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。
从 MySQL 8.0.3 开始，遵循的是 last duplicate key wins 原则，只会保留最后一个 KEY。
-- 格式：
JSON_OBJECT([key, val[,key, val]...])-- 创建对象，一个key对应一个value ： {"id": 87, "name": "carrot"}insertinto users(json_data)values(json_object('id',87,'name','carrot'));insertinto users(info)values(json_object('id',87,'name','carrot'));insertinto users(text)values(json_object('id',87,'name','carrot'));
4、JSON_ARRAYAGG()和JSON_OBJECTAGG()将查询结果封装成json
mysql>SELECT o_id, attribute,valueFROM t3;+------+-----------+-------+| o_id | attribute |value|+------+-----------+-------+|2| color | red ||2| fabric | silk ||3| color | green ||3| shape | square|+------+-----------+-------+4rowsinset(0.00 sec)
mysql>SELECT o_id, JSON_ARRAYAGG(attribute)AS attributes
->FROM t3 GROUPBY o_id;+------+---------------------+| o_id | attributes |+------+---------------------+|2|["color","fabric"]||3|["color","shape"]|+------+---------------------+2rowsinset(0.00 sec)
mysql>SELECT o_id, attribute,valueFROM t3;+------+-----------+-------+| o_id | attribute |value|+------+-----------+-------+|2| color | red ||2| fabric | silk ||3| color | green ||3| shape | square|+------+-----------+-------+4rowsinset(0.00 sec)
mysql>SELECT o_id, JSON_OBJECTAGG(attribute,value)->FROM t3 GROUPBY o_id;+------+---------------------------------------+| o_id | JSON_OBJECTAGG(attribute,value)|+------+---------------------------------------+|2| {"color": "red","fabric": "silk"} ||3| {"color": "green","shape": "square"} |+------+---------------------------------------+2rowsinset(0.00 sec)
四、JSON类型的解析
1、JSON_EXTRACT()解析json
格式：JSON_EXTRACT(json_doc, path[, path] …)
其中，json_doc 是 JSON 文档，path 是路径。该函数会从 JSON 文档提取指定路径（path）的元素。如果指定 path 不存在，会返回 NULL。可指定多个 path，匹配到的多个值会以数组形式返回。
-- 解析数组-- 取下标为1的数组值（数组下标从0开始），结果：20SELECT JSON_EXTRACT('[10, 20, [30, 40]]','$[1]');-- 取多个，结果返回是一个数组，结果：[20, 10]SELECT JSON_EXTRACT('[10, 20, [30, 40]]','$[1]','$[0]');-- 可以使用*获取全部，结果：[30, 40]SELECT JSON_EXTRACT('[10, 20, [30, 40]]','$[2][*]');-- 还可通过 [M to N] 获取数组的子集-- 结果：[10, 20]select json_extract('[10, 20, [30, 40]]','$[0 to 1]');-- 这里的 last 代表最后一个元素的下标，结果：[20, [30, 40]]select json_extract('[10, 20, [30, 40]]','$[last-1 to last]');
-- 解析对象：对象的路径是通过 KEY 来表示的。set@j='{"a": 1, "b": [2, 3], "a c": 4}';-- 如果 KEY 在路径表达式中不合法（譬如存在空格），则在引用这个 KEY 时，需用双引号括起来。-- 结果： 1 4 3select json_extract(@j,'$.a'), json_extract(@j,'$."a c"'), json_extract(@j,'$.b[1]');-- 使用*获取所有元素，结果：[1, [2, 3], 4]select json_extract('{"a": 1, "b": [2, 3], "a c": 4}','$.*');-- 这里的 $**.b 匹配 $.a.b 和 $.c.b，结果：[1, 2]select json_extract('{"a": {"b": 1}, "c": {"b": 2}}','$**.b');
json_extract解析出来的数据，可以灵活用于where、order by等等所有地方。
2、-> 箭头函数解析json
column->path，包括后面讲到的 column->>path，都是语法糖，在实际使用的时候都会在底层自动转化为 JSON_EXTRACT。
column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一个path。
-- 同JSON_EXTRACTinsertinto users(json_data)values('{"empno": 1001, "ename": "jack"}');-- 结果："jack"select json_data, json_data ->'$.ename'from users;
3、JSON_QUOTE()引用与JSON_UNQUOTE()取消引用
JSON_QUOTE(string)，生成有效的 JSON 字符串，主要是对一些特殊字符（如双引号）进行转义。
-- 结果："null" "\"null\"" "[1, 2, 3]"select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');
JSON_UNQUOTE(json_val)，将 JSON 转义成字符串输出。常用于使用JSON_EXTRACT()和->函数解析完之后，去除引号。
JSON_UNQUOTE()特殊字符转义表：
转义序列由序列表示的字符"双引号\b退格字符\f换页字符\n换行符\r回车符\t制表符\反斜杠()字符\uXXXXUnicode XXXX 转UTF-8
insertinto users(json_data)values('{"empno": 1001, "ename": "jack"}');-- 字符串类型转换后会去掉引号，结果："jack" jack 1 0select json_data->'$.ename',json_unquote(json_data->'$.ename'),json_valid(json_data->'$.ename'),json_valid(json_unquote(json_data->'$.ename'))from users;-- 数字类型转换并没有额外效果，结果：1001 1001 1 1select json_data->'$.empno',json_unquote(json_data->'$.empno'),json_valid(json_data->'$.empno'),json_valid(json_unquote(json_data->'$.empno'))from users;
直观地看，没加 JSON_UNQUOTE 字符串会用双引号引起来，加了 JSON_UNQUOTE 就没有。但本质上，前者是 JSON 中的 STRING 类型，后者是 MySQL 中的字符类型，这一点可通过 JSON_VALID 来判断。
4、->>箭头解析json
同 column->path 类似，只不过其返回的是字符串，相当于将字符串的双引号去掉了，是一个语法糖，本质上是执行了JSON_UNQUOTE( JSON_EXTRACT(column, path) )。
以下三者是等价的：
JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path
insertinto users(json_data)values('{"empno": 1001, "ename": "jack"}');-- 结果："jack" jack jack jackselect json_data->'$.ename',json_unquote(json_data->'$.ename'),json_data->>'$.ename', JSON_UNQUOTE( JSON_EXTRACT(json_data,'$.ename'))from users;
五、JSON类型的查询
1、JSON_CONTAINS()判断是否包含
格式：JSON_CONTAINS(target, candidate[, path])
判断 target 文档是否包含 candidate 文档，包含的话返回1，不包含的话返回0
如果带了path，就判断path中的数据是否等于candidate，等于的话返回1，不等于的话返回0
函数前加not可取反
SET@j='{"a": 1, "b": 2, "c": {"d": 4}}';SET@j2='{"a":1}';-- 判断@j中是否包含@j2，结果：1SELECT JSON_CONTAINS(@j,@j2);SET@j2='1';-- 判断@j字段中的a是否等于1，结果：1SELECT JSON_CONTAINS(@j,@j2,'$.a');-- 结果：0SELECT JSON_CONTAINS(@j,@j2,'$.b');SET@j2='{"d": 4}';-- 结果：0SELECT JSON_CONTAINS(@j,@j2,'$.a');-- 结果：1SELECT JSON_CONTAINS(@j,@j2,'$.c');SET@j='[1, "a", 1.02]';SET@j2='"a"';-- 判断@j数组中是否包含@j2，结果：1SELECT JSON_CONTAINS(@j,@j2);
2、JSON_CONTAINS_PATH()判断
格式：JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
判断指定的 path 是否存在，存在，则返回 1，否则是 0。
函数中的 one_or_all 可指定 one 或 all，one 是任意一个路径存在就返回 1，all 是所有路径都存在才返回 1。
函数前加not可取反
SET@j='{"a": 1, "b": 2, "c": {"d": 4}}';-- a或者e 存在一个就返回1，结果：1SELECT JSON_CONTAINS_PATH(@j,'one','$.a','$.e');-- a和e都存在返回1，结果：0SELECT JSON_CONTAINS_PATH(@j,'all','$.a','$.e');-- c中的d存在返回1，结果：1SELECT JSON_CONTAINS_PATH(@j,'one','$.c.d');SET@j='[1, 4, "a", "c"]';-- @j是一个数组，$[1]判断第二个数据是否存在，结果为1select JSON_CONTAINS_PATH(@j,'one','$[1]');-- $[11]判断第11个数据不存在，结果为0select JSON_CONTAINS_PATH(@j,'one','$[11]');
3、JSON_KEYS()获取keys
返回 JSON 文档最外层的 key，如果指定了 path，则返回该 path 对应元素最外层的 key。
-- 结果：["a", "b"]SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');-- 结果：["c"]SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}','$.b');
4、JSON_OVERLAPS()比较两个json
MySQL 8.0.17 引入的，用来比较两个 JSON 文档是否有相同的键值对或数组元素，如果有，则返回 1，否则是 0。 如果两个参数都是标量，则判断这两个标量是否相等。
函数前加not可取反
-- 结果： 1 0select json_overlaps('[1,3,5,7]','[2,5,7]'),json_overlaps('[1,3,5,7]','[2,6,8]');-- 部分匹配被视为不匹配，结果：0SELECT JSON_OVERLAPS('[[1,2],[3,4],5]','[1,[2,3],[4,5]]');-- 比较对象时，如果它们至少有一个共同的键值对，则结果为真。-- 结果：1SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}','{"c":1,"e":10,"f":1,"d":10}');-- 结果：0SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}','{"a":5,"e":10,"f":1,"d":20}');-- 如果两个标量用作函数的参数，JSON_OVERLAPS()会执行一个简单的相等测试:-- 结果：1SELECT JSON_OVERLAPS('5','5');-- 结果：0SELECT JSON_OVERLAPS('5','6');-- 当比较标量和数组时，JSON_OVERLAPS()试图将标量视为数组元素。在此示例中，第二个参数6被解释为[6]，如下所示:结果：1SELECT JSON_OVERLAPS('[4,5,6,7]','6');-- 该函数不执行类型转换:-- 结果：0SELECT JSON_OVERLAPS('[4,5,"6",7]','6');-- 结果：0SELECT JSON_OVERLAPS('[4,5,6,7]','"6"');
5、JSON_SEARCH()返回字符串的位置
格式：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])
返回某个字符串（search_str）在 JSON 文档中的位置，其中，
one_or_all：匹配的次数，one 是只匹配一次，all 是匹配所有。如果匹配到多个，结果会以数组的形式返回。
search_str：子串，支持模糊匹配：% 和 _ 。
escape_char：转义符，如果该参数不填或为 NULL，则取默认转义符\。
path：查找路径。
SET@j='["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';-- 结果："$[0]"SELECT JSON_SEARCH(@j,'one','abc');-- 结果：["$[0]", "$[2].x"]SELECT JSON_SEARCH(@j,'all','abc');-- 结果：nullSELECT JSON_SEARCH(@j,'all','ghi');-- 结果："$[1][0].k"SELECT JSON_SEARCH(@j,'all','10');-- 结果："$[1][0].k"SELECT JSON_SEARCH(@j,'all','10',NULL,'$');-- 结果："$[1][0].k"SELECT JSON_SEARCH(@j,'all','10',NULL,'$[*]');-- 结果："$[1][0].k"SELECT JSON_SEARCH(@j,'all','10',NULL,'$**.k');-- 结果："$[1][0].k"SELECT JSON_SEARCH(@j,'all','10',NULL,'$[*][0].k');-- 结果："$[1][0].k"SELECT JSON_SEARCH(@j,'all','10',NULL,'$[1]');-- 结果："$[1][0].k"SELECT JSON_SEARCH(@j,'all','10',NULL,'$[1][0]');-- 结果："$[2].x"SELECT JSON_SEARCH(@j,'all','abc',NULL,'$[2]');-- 结果：["$[0]", "$[2].x"]SELECT JSON_SEARCH(@j,'all','%a%');-- 结果：["$[0]", "$[2].x", "$[3].y"]SELECT JSON_SEARCH(@j,'all','%b%');-- 结果："$[0]"SELECT JSON_SEARCH(@j,'all','%b%',NULL,'$[0]');-- 结果："$[2].x"SELECT JSON_SEARCH(@j,'all','%b%',NULL,'$[2]');-- 结果：nullSELECT JSON_SEARCH(@j,'all','%b%',NULL,'$[1]');-- 结果：nullSELECT JSON_SEARCH(@j,'all','%b%','','$[1]');-- 结果："$[3].y"SELECT JSON_SEARCH(@j,'all','%b%','','$[3]');
6、JSON_VALUE()提取指定路径的元素
格式：JSON_VALUE(json_doc, path)
8.0.21 引入的，从 JSON 文档提取指定路径（path）的元素。
完整的语法如下所示：
JSON_VALUE(json_doc, path [RETURNINGtype][on_empty][on_error])
on_empty:
{NULL| ERROR |DEFAULTvalue} ON EMPTY
on_error:
{NULL| ERROR |DEFAULTvalue} ON ERROR
其中：
RETURNING type：返回值的类型，不指定，则默认是 VARCHAR(512)。不指定字符集，则默认是 utf8mb4，且区分大小写。
on_empty：如果指定路径没有值，会触发 on_empty 子句， 默认是返回 NULL，也可指定 ERROR 抛出错误，或者通过 DEFAULT value 返回默认值。
on_error：三种情况下会触发 on_error 子句：从数组或对象中提取元素时，会解析到多个值；类型转换错误，譬如将 “abc” 转换为 unsigned 类型；值被 truncate 了。默认是返回 NULL。
-- 查找fname的值，结果为：JoeSELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}','$.fname');-- 结果：49.95SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}','$.price'RETURNINGDECIMAL(4,2))AS price;-- 结果：50.0SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}','$.price'RETURNINGDECIMAL(4,1))AS price;-- 使用RETURNING定义返回数据类型，等效于以下sql：SELECT CAST(
JSON_UNQUOTE( JSON_EXTRACT(json_doc, path))AStype);
mysql>select json_value('{"item": "shoes", "price": "49.95"}','$.price1' error on empty);
ERROR 3966(22035): Novalue 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'returningunsigned error on error)as price;
ERROR 1690(22003): UNSIGNEDvalueisoutof range in'json_value'
7、MEMBER OF()判断是否是json数组中的元素
格式：value MEMBER OF(json_array)
在 MySQL 8.0.17引入了MEMBER OF()函数。判断 value 是否是 JSON 数组的一个元素，如果是，则返回 1，否则是 0。
函数前加not可取反
-- 结果：1SELECT17 MEMBER OF('[23, "abc", 17, "ab", 10]');-- 结果：1SELECT'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');-- 部分匹配不代表匹配-- 结果：0SELECT7 MEMBER OF('[23, "abc", 17, "ab", 10]');-- 结果：0SELECT'a' MEMBER OF('[23, "abc", 17, "ab", 10]');-- 不执行字符串类型之间的相互转换:结果：0·0SELECT17 MEMBER OF('[23, "abc", "17", "ab", 10]'),"17" MEMBER OF('[23, "abc", 17, "ab", 10]')-- 要将该操作符与本身是数组的值一起使用，必须将其显式转换为JSON数组。结果：1SELECT CAST('[4,5]'AS JSON) MEMBER OF('[[3,4],[4,5]]');-- 还可以使用JSON_ARRAY()函数执行必要的强制转换，如下所示: 结果：1SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');--转换，结果：1 1SET@a= CAST('{"a":1}'AS JSON);SET@b= JSON_OBJECT("b",2);SET@c= JSON_ARRAY(17,@b,"abc",@a,23);SELECT@a MEMBER OF(@c),@b MEMBER OF(@c);
8、JSON_DEPTH()获取JSON最大深度
语法：JSON_DEPTH(json_doc)
返回JSON文档的最大深度。如果参数为NULL，则返回NULL。如果参数不是有效的JSON文档，则会出现错误。
对于空数组，空对象，标量值，其深度为 1。
-- 结果：1 1 1SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');-- 结果：2 2SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');-- 结果：3SELECT JSON_DEPTH('[10, {"a": 20}]');
9、JSON_LENGTH()获取文档长度
语法：JSON_LENGTH(json_doc[, path])
返回 JSON 文档的长度，其计算规则如下：
1、如果是标量值，其长度为 1。
2、如果是数组，其长度为数组元素的个数。
3、如果是对象，其长度为对象元素的个数。
4、不包括嵌套数据和嵌套对象的长度。
-- 结果：3SELECT JSON_LENGTH('[1, 2, {"a": 3}]');-- 结果：2SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');-- 结果：1SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}','$.b');
10、JSON_TYPE()获取JSON类型
语法：JSON_TYPE(json_val)
返回 JSON 值的类型。
如果参数不是有效的JSON值，则会出现错误。
SET@j='{"a": [10, true]}';-- 结果：OBJECTSELECT JSON_TYPE(@j);-- 结果：ARRAYSELECT JSON_TYPE(JSON_EXTRACT(@j,'$.a'));-- 结果：INTEGERSELECT JSON_TYPE(JSON_EXTRACT(@j,'$.a[0]'));-- 结果：BOOLEANSELECT JSON_TYPE(JSON_EXTRACT(@j,'$.a[1]'));-- 结果：NULLSELECT JSON_TYPE(NULL);-- 结果：STRINGselect json_type('"abc"');-- 结果：DATETIMEselect json_type(cast(now()as json));
JSON类型：OBJECT（对象）、ARRAY（数组）、BOOLEAN（布尔类型）、NULL
数字类型：INTEGER（TINYINT、SMALLINT、MEDIUMINT以及INT和BIGINT标量）、DOUBLE（DOUBLE、FLOAT）、DECIMAL（MySQL、DECIMAL）
时间类型：DATETIME（DATETIME、TIMESTAMP）、DATE、TIME
字符串类型：STRING（CHAR, VARCHAR, TEXT, ENUM, SET）
二进制类型：BLOB（ BINARY, VARBINARY, BLOB, BIT）
其他类型：OPAQUE
11、JSON_VALID()校验JSON格式
语法：JSON_VALID(val)
判断给定值是否是有效的 JSON 文档。
函数前加not可取反
-- 结果：1SELECT JSON_VALID('{"a": 1}');-- 结果：0 1SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
六、JSON类型的修改
1、全量修改
直接使用update语句，将json数据字段全部替换。
update users set json_data ='{"a":1}';
2、JSON_ARRAY_APPEND()向数组追加元素
格式：JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
向数组指定位置追加元素。如果指定 path 不存在，则不添加。
在MySQL 5.7中，这个函数被命名为JSON_APPEND()。MySQL 8.0不再支持该名称。
SET@j='["a", ["b", "c"], "d"]';-- 在数组第二个元素的数组中追加1，结果：["a", ["b", "c", 1], "d"]SELECT JSON_ARRAY_APPEND(@j,'$[1]',1);-- 结果：[["a", 2], ["b", "c"], "d"]SELECT JSON_ARRAY_APPEND(@j,'$[0]',2);-- 结果：["a", [["b", 3], "c"], "d"]SELECT JSON_ARRAY_APPEND(@j,'$[1][0]',3);-- 多个参数，结果：[["a", 1], [["b", 2], "c"], "d"]select json_array_append(@j,'$[0]',1,'$[1][0]',2,'$[3]',3);SET@j='{"a": 1, "b": [2, 3], "c": 4}';-- 往b中追加，结果：{"a": 1, "b": [2, 3, "x"], "c": 4}SELECT JSON_ARRAY_APPEND(@j,'$.b','x');-- 结果：{"a": 1, "b": [2, 3], "c": [4, "y"]}SELECT JSON_ARRAY_APPEND(@j,'$.c','y');SET@j='{"a": 1}';-- 结果：[{"a": 1}, "z"]SELECT JSON_ARRAY_APPEND(@j,'$','z');
3、JSON_ARRAY_INSERT()向数组指定位置插入元素
格式：JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
向数组指定位置插入元素。
SET@j='["a", {"b": [1, 2]}, [3, 4]]';-- 在下标1处添加元素x，结果：["a", "x", {"b": [1, 2]}, [3, 4]]SELECT JSON_ARRAY_INSERT(@j,'$[1]','x');-- 没有100个元素，在最后插入，结果： ["a", {"b": [1, 2]}, [3, 4], "x"]SELECT JSON_ARRAY_INSERT(@j,'$[100]','x');-- 结果：["a", {"b": ["x", 1, 2]}, [3, 4]]SELECT JSON_ARRAY_INSERT(@j,'$[1].b[0]','x');-- 结果：["a", {"b": [1, 2]}, [3, "y", 4]]SELECT JSON_ARRAY_INSERT(@j,'$[2][1]','y');-- 早期的修改会影响数组中后续元素的位置，因此同一个JSON_ARRAY_INSERT()调用中的后续路径应该考虑这一点。在最后一个示例中，第二个路径没有插入任何内容，因为在第一次插入之后，该路径不再匹配任何内容。-- 结果：["x", "a", {"b": [1, 2]}, [3, 4]]SELECT JSON_ARRAY_INSERT(@j,'$[0]','x','$[2][1]','y');
4、JSON_INSERT()插入新值
格式：JSON_INSERT(json_doc, path, val[, path, val] …)
插入不存在的key的值，已经存在的不修改。
仅当指定位置或指定 KEY 的值不存在时，才执行插入操作。另外，如果指定的 path 是数组下标，且 json_doc 不是数组，该函数首先会将 json_doc 转化为数组，然后再插入新值。
SET@j='{ "a": 1, "b": [2, 3]}';-- a已经存在则忽略，c不存在则添加，结果：{"a": 1, "b": [2, 3], "c": "[true, false]"}SELECT JSON_INSERT(@j,'$.a',10,'$.c','[true, false]');-- 上面插入的c是一个带引号的字符串，想要插入一个数组，必须进行转换，结果：{"a": 1, "b": [2, 3], "c": [true, false]}SELECT JSON_INSERT(@j,'$.a',10,'$.c', CAST('[true, false]'AS JSON));-- 下标0位置已经有值了，不会插入，结果：1select json_insert('1','$[0]',"10");-- 结果：[1, "10"]select json_insert('1','$[1]',"10");-- 结果：["1", "2", "10"]select json_insert('["1","2"]','$[2]',"10");
5、JSON_MERGE()合并json
格式：JSON_MERGE(json_doc, json_doc[, json_doc] …)
合并两个或多个JSON文档。JSON_MERGE_PRESERVE()的同义词；在MySQL 8.0.3中已弃用，在未来版本中可能会被删除。
推荐使用JSON_MERGE_PRESERVE()
-- 结果：[1, 2, true, false]SELECT JSON_MERGE('[1, 2]','[true, false]');
6、JSON_MERGE_PATCH()合并json
MySQL 8.0.3 引入的，用来合并多个 JSON 文档。其合并规则如下：
1、如果两个文档不全是 JSON 对象，则合并后的结果是第二个文档。
2、如果两个文档都是 JSON 对象，且不存在着同名 KEY，则合并后的文档包括两个文档的所有元素，如果存在着同名 KEY，则第二个文档的值会覆盖第一个。
-- 不是对象，结果：[true, false]SELECT JSON_MERGE_PATCH('[1, 2]','[true, false]');-- 都是对象，结果：{"id": 47, "name": "x"}SELECT JSON_MERGE_PATCH('{"name": "x"}','{"id": 47}');-- 都不是对象，取第二个，结果：trueSELECT JSON_MERGE_PATCH('1','true');-- 第一个不是对象，取第二个 ，结果：{"id": 47}SELECT JSON_MERGE_PATCH('[1, 2]','{"id": 47}');-- 第二个覆盖第一个，结果：{"a": 3, "b": 2, "c": 4}SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');-- 结果：{"a": 5, "b": 2, "c": 4, "d": 6}SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }');-- 第二个有null，会删除该key，结果：{"a": 1}SELECT JSON_MERGE_PATCH('{"a":1, "b":2}','{"b":null}');-- 嵌套json也可以合并，结果：{"a": {"x": 1, "y": 2}}SELECT JSON_MERGE_PATCH('{"a":{"x":1}}','{"a":{"y":2}}');
注意区别于JSON_MERGE_PRESERVE
7、JSON_MERGE_PRESERVE()合并json
MySQL 8.0.3 引入的，用来代替 JSON_MERGE。也是用来合并文档，但合并规则与 JSON_MERGE_PATCH 有所不同。
1、两个文档中，只要有一个文档是数组，则另外一个文档会合并到该数组中。
2、两个文档都是 JSON 对象，若存在着同名 KEY ，第二个文档并不会覆盖第一个，而是会将值 append 到第一个文档中。
-- 数组合并，结果：[1, 2, true, false]SELECT JSON_MERGE_PRESERVE('[1, 2]','[true, false]');-- 对象合并，结果：{"id": 47, "name": "x"}SELECT JSON_MERGE_PRESERVE('{"name": "x"}','{"id": 47}');-- 两个常量，合并为一个数组，结果：[1, true]SELECT JSON_MERGE_PRESERVE('1','true');-- 对象合并到数组中，结果：[1, 2, {"id": 47}]SELECT JSON_MERGE_PRESERVE('[1, 2]','{"id": 47}');-- 相同的key合并到一个数组，结果：{"a": [1, 3], "b": 2, "c": 4}SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }');-- 结果：{"a": [1, 3, 5], "b": 2, "c": 4, "d": 6} SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }','{ "a": 5, "d": 6 }');
注意区别于JSON_MERGE_PATCH()
8、JSON_REMOVE()删除元素
格式：JSON_REMOVE(json_doc, path[, path] …)
删除 JSON 文档指定位置的元素。
SET@j='["a", ["b", "c"], "d"]';-- 删除下标为1的元素，结果：["a", "d"]SELECT JSON_REMOVE(@j,'$[1]');set@j='{ "a": 1, "b": [2, 3]}';-- 删除a元素，结果：{"b": [2, 3]}select json_remove(@j,'$.a');set@j='["a", ["b", "c"], "d", "e"]';-- 删除多个元素，删除1下标之后，下标移动结果之后再删除下标2位置，结果：["a", "d"]select json_remove(@j,'$[1]','$[2]');-- 结果：["a", "e"]select json_remove(@j,'$[1]','$[1]');
9、JSON_REPLACE()替换元素
语法：JSON_REPLACE(json_doc, path, val[, path, val] …)
替换已经存在的值。不存在的值不做影响。
SET@j='{ "a": 1, "b": [2, 3]}';-- 对象替换，结果：{"a": 10, "b": [2, 3]}SELECT JSON_REPLACE(@j,'$.a',10,'$.c','[true, false]');-- 数组替换，结果：[1, "a", 4, "b"]select json_replace('[1, "a", 3, "b"]','$[2]',4,'$[8]',8);
10、JSON_SET()插入并替换
格式：JSON_SET(json_doc, path, val[, path, val] …)
插入新值，并替换已经存在的值。
换言之，如果指定位置或指定 KEY 的值不存在，会执行插入操作，如果存在，则执行更新操作。
注意JSON_SET、JSON_INSERT、JSON_REPLACE的区别。
SET@j='{ "a": 1, "b": [2, 3]}';-- 结果：{"a": 10, "b": [2, 3], "c": "[true, false]"}SELECT JSON_SET(@j,'$.a',10,'$.c','[true, false]');-- 结果：{"a": 1, "b": [2, 3], "c": "[true, false]"}SELECT JSON_INSERT(@j,'$.a',10,'$.c','[true, false]');-- 结果：{"a": 10, "b": [2, 3]}SELECT JSON_REPLACE(@j,'$.a',10,'$.c','[true, false]');
七、其他JSON函数
1、JSON_TABLE()列转行
语法：JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
MySQL 8.0支持这样一个函数，JSON_TABLE()，从 JSON 文档中提取数据并以表格的形式返回。
完整语法如下：
JSON_TABLE(
expr,
path COLUMNS(column_list))[AS] alias
column_list:
column[,column][,...]column:
name FOR ORDINALITY
| name type PATH string path [on_empty][on_error]| name typeEXISTS PATH string path
| NESTED [PATH] path COLUMNS(column_list)
on_empty:
{NULL|DEFAULT json_string | ERROR} ON EMPTY
on_error:
{NULL|DEFAULT json_string | ERROR} ON ERROR
mysql>SELECT*->FROM-> JSON_TABLE(->'[ {"c1": null} ]',->'$[*]'COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )->)as jt;+------+| c1 |+------+|NULL|+------+1rowinset(0.00 sec)
select*from
json_table('[{"x":2, "y":"8", "z":9, "b":[1,2,3]}, {"x":"3", "y":"7"}, {"x":"4", "y":6, "z":10}]',"$[*]"columns(
id for ordinality,
xval varchar(100) path "$.x",
yval varchar(100) path "$.y",
z_exist intexists path "$.z",
nested path '$.b[*]'columns(b INT PATH '$')))as t;+------+------+------+---------+------+| id | xval | yval | z_exist | b |+------+------+------+---------+------+|1|2|8|1|1||1|2|8|1|2||1|2|8|1|3||2|3|7|0|NULL||3|4|6|1|NULL|+------+------+------+---------+------+5rowsinset(0.00 sec)
mysql>SELECT*->FROM-> JSON_TABLE(->'[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',->"$[*]"->COLUMNS(-> rowid FOR ORDINALITY,-> ac VARCHAR(100) PATH "$.a"DEFAULT'111'ON EMPTY DEFAULT'999'ON ERROR,-> aj JSON PATH "$.a"DEFAULT'{"x": 333}'ON EMPTY,-> bx INTEXISTS PATH "$.b"->)->)AS tt;+-------+------+------------+------+| rowid | ac | aj | bx |+-------+------+------------+------+|1|3|"3"|0||2|2|2|0||3|111| {"x": 333} |1||4|0|0|0||5|999|[1,2]|0|+-------+------+------------+------+5rowsinset(0.00 sec)
mysql>SELECT*->FROM-> JSON_TABLE(->'[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',->"$[*]"COLUMNS(-> xval VARCHAR(100) PATH "$.x",-> yval VARCHAR(100) PATH "$.y"->)->)AS jt1;+------+------+| xval | yval |+------+------+|2|8||3|7||4|6|+------+------+
-- 指定path
mysql>SELECT*->FROM-> JSON_TABLE(->'[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',->"$[1]"COLUMNS(-> xval VARCHAR(100) PATH "$.x",-> yval VARCHAR(100) PATH "$.y"->)->)AS jt1;+------+------+| xval | yval |+------+------+|3|7|+------+------+
mysql>SELECT*->FROM-> JSON_TABLE(->'[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',->'$[*]'COLUMNS(-> a INT PATH '$.a',-> NESTED PATH '$.b[*]'COLUMNS(b INT PATH '$')->)->)AS jt
->WHERE b ISNOTNULL;+------+------+| a | b |+------+------+|1|11||1|111||2|22||2|222|+------+------+
mysql>SELECT*->FROM-> JSON_TABLE(->'[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',->'$[*]'COLUMNS(-> a INT PATH '$.a',-> NESTED PATH '$.b[*]'COLUMNS(b1 INT PATH '$'),-> NESTED PATH '$.b[*]'COLUMNS(b2 INT PATH '$')->)->)AS jt;+------+------+------+| a | b1 | b2 |+------+------+------+|1|11|NULL||1|111|NULL||1|NULL|11||1|NULL|111||2|22|NULL||2|222|NULL||2|NULL|22||2|NULL|222|+------+------+------+
mysql>SELECT*->FROM-> JSON_TABLE(->'[{"a": "a_val",
'>"b": [{"c": "c_val","l": [1,2]}]},'> {"a": "a_val",
'>"b": [{"c": "c_val","l": [11]}, {"c": "c_val","l": [22]}]}]',
-> '$[*]' COLUMNS(
-> top_ord FOR ORDINALITY,
-> apath VARCHAR(10) PATH '$.a',
-> NESTED PATH '$.b[*]' COLUMNS (
-> bpath VARCHAR(10) PATH '$.c',
-> ord FOR ORDINALITY,
-> NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')->)->)->)as jt;+---------+---------+---------+------+-------+| top_ord | apath | bpath | ord | lpath |+---------+---------+---------+------+-------+|1| a_val | c_val |1|1||1| a_val | c_val |1|2||2| a_val | c_val |1|11||2| a_val | c_val |2|22|+---------+---------+---------+------+-------+
与表关联查询：
CREATETABLE t1 (c1 INT, c2 CHAR(1), c3 JSON);INSERTINTO t1 ()VALUESROW(1,'z', JSON_OBJECT('a',23,'b',27,'c',1)),ROW(1,'y', JSON_OBJECT('a',44,'b',22,'c',11)),ROW(2,'x', JSON_OBJECT('b',1,'c',15)),ROW(3,'w', JSON_OBJECT('a',5,'b',6,'c',7)),ROW(5,'v', JSON_OBJECT('a',123,'c',1111));SELECT c1, c2, JSON_EXTRACT(c3,'$.*')FROM t1 AS m
JOIN
JSON_TABLE(
m.c3,'$.*'COLUMNS(
at VARCHAR(10) PATH '$.a'DEFAULT'1'ON EMPTY,
bt VARCHAR(10) PATH '$.b'DEFAULT'2'ON EMPTY,
ct VARCHAR(10) PATH '$.c'DEFAULT'3'ON EMPTY
))AS tt
ON m.c1 > tt.at;
结果：
与表关联查询：
CREATETABLE employees (
id INT,
details JSON
);INSERTINTO employees VALUES(1,'{"name": "John Doe", "position": "Manager"}');INSERTINTO employees VALUES(2,'{"name": "Jane Smith", "position": "Developer"}');SELECT name, position
FROM employees,
JSON_TABLE(details,'$'COLUMNS(
name VARCHAR(255) PATH '$.name',
position VARCHAR(255) PATH '$.position'))AS emp;
2、JSON_SCHEMA_VALID()验证json
语法：JSON_SCHEMA_VALID(schema,document)
判断 document （ JSON 文档 ）是否满足 schema （ JSON 对象）定义的规范要求。完整的规范要求可参考 Draft 4 of the JSON Schema specification （https://json-schema.org/specification-links.html#draft-4）。如果不满足，可通过 JSON_SCHEMA_VALIDATION_REPORT() 获取具体的原因。
它的要求如下：
1、document 必须是 JSON 对象。
2、JSON 对象必需的两个属性是 latitude 和 longitude。
3、latitude 和 longitude 必须是数值类型，且两者的大小分别在 -90 ～ 90，-180 ～ 180 之间。
mysql>SET@schema='{
'>"id": "http://json-schema.org/geo",'> "$schema": "http://json-schema.org/draft-04/schema#",
'>"description": "A geographical coordinate",'> "type": "object",
'>"properties": {
'> "latitude": {
'>"type": "number",'> "minimum": -90,
'>"maximum": 90'> },
'>"longitude": {
'> "type": "number",
'>"minimum": -180,'> "maximum": 180
'> }
'> },
'>"required": ["latitude","longitude"]'>}';
Query OK,0rows affected (0.01 sec)
mysql>SET@document='{
'>"latitude": 63.444697,'> "longitude": 10.445118
'>}';
Query OK,0rows affected (0.00 sec)
mysql>SELECT JSON_SCHEMA_VALID(@schema,@document);+---------------------------------------+| JSON_SCHEMA_VALID(@schema,@document)|+---------------------------------------+|1|+---------------------------------------+1rowinset(0.00 sec)
mysql>SET@document='{}';
mysql>SET@schema='{
'>"id": "http://json-schema.org/geo",'> "$schema": "http://json-schema.org/draft-04/schema#",
'>"description": "A geographical coordinate",'> "type": "object",
'>"properties": {
'> "latitude": {
'>"type": "number",'> "minimum": -90,
'>"maximum": 90'> },
'>"longitude": {
'> "type": "number",
'>"minimum": -180,'> "maximum": 180
'> }
'> }
'>}';
Query OK,0rows affected (0.00 sec)
mysql>SELECT JSON_SCHEMA_VALID(@schema,@document);+---------------------------------------+| JSON_SCHEMA_VALID(@schema,@document)|+---------------------------------------+|1|+---------------------------------------+1rowinset(0.00 sec)
-- 建表指定check
mysql>CREATETABLE geo (-> coordinate JSON,->CHECK(-> JSON_SCHEMA_VALID(->'{
'>"type":"object",'> "properties":{
'>"latitude":{"type":"number","minimum":-90,"maximum":90},'> "longitude":{"type":"number", "minimum":-180, "maximum":180}
'> },'> "required": ["latitude", "longitude"]
'> }',
-> coordinate
-> )
-> )
-> );
Query OK, 0 rows affected (0.45 sec)
mysql> SET @point1 = '{"latitude":59,"longitude":18}';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @point2 = '{"latitude":91,"longitude":0}';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @point3 = '{"longitude":120}';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO geo VALUES(@point1);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO geo VALUES(@point2);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
-- 查看原因
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Error
Code: 3934
Message: The JSON document location '#/latitude' failed requirement 'maximum' at
JSON Schema location '#/properties/latitude'.***************************2.row***************************Level: Error
Code: 3819
Message: Checkconstraint'geo_chk_1'is violated.2rowsinset(0.00 sec)
mysql>INSERTINTO geo VALUES(@point3);
ERROR 3819(HY000): Checkconstraint'geo_chk_1'is violated.
mysql>SHOWWARNINGS\G
***************************1.row***************************Level: Error
Code: 3934
Message: The JSON document location '#' failed requirement 'required' at JSON
Schema location '#'.***************************2.row***************************Level: Error
Code: 3819
Message: Checkconstraint'geo_chk_1'is violated.2rowsinset(0.00 sec)
3、JSON_SCHEMA_VALIDATION_REPORT()查看验证报告
语法：JSON_SCHEMA_VALIDATION_REPORT(schema,document)
该函数会以JSON文档的形式返回一个关于验证结果的报告。如果验证成功，返回
{"valid": true}
。如果JSON文档验证失败，该函数将返回一个JSON对象，该对象包含下面列出的属性:
valid：false
reason：失败原因
schema-location：校验失败的位置
document-location：失败位置
schema-failed-keyword：关键字或属性名
mysql>SET@schema='{
'>"id": "http://json-schema.org/geo",'> "$schema": "http://json-schema.org/draft-04/schema#",
'>"description": "A geographical coordinate",'> "type": "object",
'>"properties": {
'> "latitude": {
'>"type": "number",'> "minimum": -90,
'>"maximum": 90'> },
'>"longitude": {
'> "type": "number",
'>"minimum": -180,'> "maximum": 180
'> }
'> },
'>"required": ["latitude","longitude"]'>}';
Query OK,0rows affected (0.01 sec)
mysql>SET@document='{
'>"latitude": 63.444697,'> "longitude": 10.445118
'>}';
Query OK,0rows affected (0.00 sec)
mysql>SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema,@document);+---------------------------------------------------+| JSON_SCHEMA_VALIDATION_REPORT(@schema,@document)|+---------------------------------------------------+| {"valid": true} |+---------------------------------------------------+1rowinset(0.00 sec)
mysql>SET@document='{
'>"latitude": 63.444697,'> "longitude": 310.445118
'> }';
mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
"valid": false,
"reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'","schema-location": "#/properties/longitude","document-location": "#/longitude","schema-failed-keyword": "maximum"
}
1rowinset(0.00 sec)
mysql>SET@document='{}';
Query OK,0rows affected (0.00 sec)
mysql>SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema,@document))\G
***************************1.row***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema,@document)): {
"valid": false,"reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'","schema-location": "#","document-location": "#","schema-failed-keyword": "required"
}
1rowinset(0.00 sec)
mysql>SET@schema='{
'>"id": "http://json-schema.org/geo",'> "$schema": "http://json-schema.org/draft-04/schema#",
'>"description": "A geographical coordinate",'> "type": "object",
'>"properties": {
'> "latitude": {
'>"type": "number",'> "minimum": -90,
'>"maximum": 90'> },
'>"longitude": {
'> "type": "number",
'>"minimum": -180,'> "maximum": 180
'> }
'> }
'>}';
Query OK,0rows affected (0.00 sec)
mysql>SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema,@document);+---------------------------------------------------+| JSON_SCHEMA_VALIDATION_REPORT(@schema,@document)|+---------------------------------------------------+| {"valid": true} |+---------------------------------------------------+1rowinset(0.00 sec)
4、JSON_PRETTY()格式化输出
语法：JSON_PRETTY(json_val)
将 JSON 格式化输出。
SELECT JSON_PRETTY('123');# scalar+--------------------+| JSON_PRETTY('123')|+--------------------+|123|+--------------------+SELECT JSON_PRETTY("[1,3,5]");# array+------------------------+| JSON_PRETTY("[1,3,5]")|+------------------------+|[1,3,5]|+------------------------+SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}');# object+---------------------------------------------+| JSON_PRETTY('{"a":"10","b":"15","x":"25"}')|+---------------------------------------------+| {
"a": "10","b": "15","x": "25"
} |+---------------------------------------------+SELECT JSON_PRETTY('["a",1,{"key1":
"value1"},"5", "77" ,
{"key2":["value3","valueX",
"valueY"]},"j", "2" ]')\G # nested arrays and objects***************************1.row***************************
JSON_PRETTY('["a",1,{"key1":
"value1"},"5", "77" ,
{"key2":["value3","valuex",
"valuey"]},"j", "2" ]'): ["a",1,
{
"key1": "value1"
},"5","77",
{
"key2": ["value3","valuex","valuey"]
},"j","2"]
5、JSON_STORAGE_FREE()计算空间
MySQL 8.0 新增的，与 Partial Updates 有关，用于计算 JSON 文档在进行部分更新后的剩余空间。
CREATETABLE jtable (jcol JSON);INSERTINTO jtable VALUES('{"a": 10, "b": "wxyz", "c": "[true, false]"}');-- 更新，结果：{"a": 10, "b": "wxyz", "c": 1}UPDATE jtable SET jcol = JSON_SET(jcol,"$.a",10,"$.b","wxyz","$.c",1);-- 结果：14SELECT JSON_STORAGE_FREE(jcol)FROM jtable;-- 连续的部分更新对这个空闲空间的影响是累积的，如下例所示，使用JSON_SET()来减少具有键b的值所占用的空间(并且不做任何其他更改):UPDATE jtable SET jcol = JSON_SET(jcol,"$.a",10,"$.b","wx","$.c",1);-- 结果：16SELECT JSON_STORAGE_FREE(jcol)FROM jtable;-- 不使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()更新列意味着优化器不能就地执行更新；在这种情况下，JSON_STORAGE_FREE()返回0，如下所示:UPDATE jtable SET jcol ='{"a": 10, "b": 1}';-- 结果：0SELECT JSON_STORAGE_FREE(jcol)FROM jtable;-- JSON文档的部分更新只能在列值上执行。对于存储JSON值的用户变量，该值总是被完全替换，即使使用JSON_SET()执行更新也是如此:SET@j='{"a": 10, "b": "wxyz", "c": "[true, false]"}';SET@j= JSON_SET(@j,'$.a',10,'$.b','wxyz','$.c','1');SELECT@j, JSON_STORAGE_FREE(@j)AS Free;-- 结果：0-- 对于JSON文本，该函数总是返回0:SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}')AS Free;-- 结果：0
6、JSON_STORAGE_SIZE()计算空间
语法：JSON_STORAGE_SIZE(json_val)
MySQL 5.7.22 引入的，用于计算 JSON 文档的空间使用情况。
CREATETABLE jtable (jcol JSON);INSERTINTO jtable VALUES('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');SELECT jcol, JSON_STORAGE_SIZE(jcol)AS Size, JSON_STORAGE_FREE(jcol)AS Free FROM jtable;+-----------------------------------------------+------+------+| jcol | Size | Free |+-----------------------------------------------+------+------+| {"a": 1000,"b": "wxyz","c": "[1, 3, 5, 7]"} |47|0|+-----------------------------------------------+------+------+1rowinset(0.00 sec)UPDATE jtable SET jcol ='{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';SELECT jcol, JSON_STORAGE_SIZE(jcol)AS Size, JSON_STORAGE_FREE(jcol)AS Free FROM jtable;+------------------------------------------------+------+------+| jcol | Size | Free |+------------------------------------------------+------+------+| {"a": 4.55,"b": "wxyz","c": "[true, false]"} |56|0|+------------------------------------------------+------+------+1rowinset(0.00 sec)-- json文本显示占用存储空间SELECT JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]')AS A,
JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}')AS B,
JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}')AS C,
JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]')AS D;+----+----+----+----+| A | B | C | D |+----+----+----+----+|45|44|47|56|+----+----+----+----+1rowinset(0.00 sec)
八、JSON字段创建索引
同 TEXT，BLOB 字段一样，JSON 字段不允许直接创建索引。
即使支持，实际意义也不大，因为我们一般是基于文档中的元素进行查询，很少会基于整个 JSON 文档。
对文档中的元素进行查询，就需要用到 MySQL 5.7 引入的
虚拟列及函数索引
。
# C2 即虚拟列# index (c2) 对虚拟列添加索引。createtable t ( c1 json, c2 varchar(10)as(JSON_UNQUOTE(c1 ->"$.name")),index(c2));insertinto t (c1)values('{"id": 1, "name": "a"}'),('{"id": 2, "name": "b"}'),('{"id": 3, "name": "c"}'),('{"id": 4, "name": "d"}');
mysql>explainselect*from t where c2 ='a';+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type |table| partitions |type| possible_keys |key| key_len | ref |rows| filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+|1|SIMPLE| t |NULL| ref | c2 | c2 |43| const |1|100.00|NULL|+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+1rowinset,1 warning (0.00 sec)
mysql>explainselect*from t where c1->'$.name'='a';+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type |table| partitions |type| possible_keys |key| key_len | ref |rows| filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+|1|SIMPLE| t |NULL| ref | c2 | c2 |43| const |1|100.00|NULL|+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+1rowinset,1 warning (0.00 sec)
可以看到，无论是使用虚拟列，还是文档中的元素来查询，都可以利用上索引。
注意，在创建虚拟列时需指定 JSON_UNQUOTE，将 c1 -> “$.name” 的返回值转换为字符串。
参考文档
https://dev.mysql.com/doc/refman/8.0/en/json.html
https://blog.csdn.net/java_faep/article/details/125206014
https://zhuanlan.zhihu.com/p/514819634?utm_id=0
https://blog.csdn.net/sinat_20938225/article/details/129471550
GeoJSON：https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html
json方法：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
json索引：https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index
json多值索引：https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued
本文转载自: https://blog.csdn.net/A_art_xiang/article/details/132472381
版权归原作者 秃了也弱了。 所有， 如有侵权，请联系我们删除。
版权归原作者 秃了也弱了。 所有， 如有侵权，请联系我们删除。