mysql的json类型的json函数

2020-08-14 本文已影响0人老柿子

示例表

后面的所有的表demo_json结构都是这个

create table demo_json(
    `id` bigint unsigned not null auto_increment,
    `json` json not null,
    primary key (`id`)
)engine=innodb;

json_set

用于将对应的json已有的字段进行修改

语法

json_set(json_doc, path, value, [path, value, ...])
说明：
其中json_doc就是表中对应的json列，path就是json中对应的字段key，value就是对应的值，后面的都是这样。返回值就是修改后的值

-- 插入数据
insert into demo_json(`json`) values ('{"ok":12}');
-- 更新数据
update demo_json set `json`=json_set(`json`, '$.f1', 2333, '$.f2', "v1");
-- 选择数据
select `json` from demo_json;

-- 返回
{"f1": 2333, "f2": "v1", "ok": 12}

json_keys

返回对应文档中的最上层的keys，如果内部还有更多嵌套的key，则是不会嵌套返回的

json_keys(json_doc[, path])

-- 无path参数，返回的是json_doc中的顶级key
-- 返回[a,b]
select json_kesy('{"a":12, "b":32}');
-- 返回[a, b, c]
select json_keys('{"a":12, "b":32, "c":{"ok":11, "kk":43}}');

-- 有path参数，则返回的是path对应文档中的顶级key
-- 返回[ok, kk]
select json_keys('{"a":12, "b":32, "c":{"ok":11, "kk":43}}', '$.c');

json_type

返回对应文档中的字段值的类型

json_type(value)

普通情况下直接返回，但是如果文档的话，可以用函数json_extract(json_doc, path[, path...]) 进行提取即可

-- 返回integer
select json_type('12');

-- 返回double
select json_type('12.0');

-- 返回string
select json_type('"abc"');

-- 返回object
select json_type('{"a":12,"b":"v1"}');

-- 返回array
select json_type('["a", 1]');

-- 返回 {"a": 12, "b": "vv"}
select `json` from demo_json where id = 3;
-- 返回integer
select json_type(json_extract(`json`, '$.a')) from demo_json where id = 3;
-- 返回string
select json_type(json_extract(`json`, '$.b')) from demo_json where id = 3;

json_array

该函数用于将数据进行拼接，其实就有点像java中的new ArrayList() 这种

json_array(value[,value...])

-- 返回数组：["a", "1", 34]
select json_array('a', '1', 34);

json_depth

返回文档的深度

json_depth(json_doc)

-- 返回1
select json_depth('[]');
-- 返回1
select json_depth('{}');
-- 返回1
select json_depth('12');
-- 返回1
select json_depth('"a"');

-- 返回2
select json_depth('[1]');
-- 返回2
select json_depth('[1, 2, 3, "a"]');
-- 返回2
select json_depth('{"a":12}');
-- 返回2
select json_depth('{"a":12, "b":"v"}');

-- 返回3
select json_depth('["a", {"b":12}]');
-- 返回3
select json_depth('[{"a":10}, {"b":12}]');
-- 返回3
select json_depth('{"a":12, "b":{"b1":12}}');

其中普通的空以及普通字段，深度是1级，二级的话，就是普通的数组和对象

json_quote

将非json_doc文档格式的数据，转换为文档格式

json_quote(string)

-- 返回 ""
select json_quote('a');
-- 返回 "\"a\""
select json_quote('"a"');

-- 返回 ""
select json_quote('');
-- 返回 "[a, b]"
select json_quote('[a, b]');
-- 返回 "[\"a\", \"b\"]"
select json_quote('["a", "b"]');

json_valid

判断值是否是json类型

json_valid(val)

-- 返回 null
select json_valid(null);
-- 返回 0
select json_valid('');
-- 返回 0
select json_valid('a');
-- 返回 1
select json_valid('[1,2]');
-- 返回 0
select json_valid('{a,1}');
-- 返回 1
select json_valid('{"a":12, "b":2}');

json_insert

给对应的文档添加数据，这个给update的时候，这样设置，更方便

select json_insert(json_doc, path, val[, path, val] ...)

-- 插入数据
insert into demo_json(`json`) values ('{"a":1, "b":2}');
-- {"a": 1, "b": 2}
select `json` from demo_json;
update demo_json set `json`=json_insert(`json`, '$.c', '3');
-- {"a": 1, "b": 2, "c": "3"}
select `json` from demo_json;

json_length

返回对应文档的长度，我们知道文档有这么几种类型：标量、对象、数组
文件长度确定如下：

标量的长度为1。
数组的长度是数组元素的数量。
对象的长度是对象成员的数量。
该长度不计算嵌套数组或对象的长度。

-- 返回错误
select json_length('a');
select json_length(1);
select json_length('');

-- 0
select json_length('{}');
select json_length('[]');
select json_length('null');

-- 1
select json_length('"2"');
select json_length('[1]');
select json_length('{"a":1}');

-- 2
select json_length('[1, "a"]');
select json_length('{"a":1, "b":2}');

--------- 文档类型 ---------
truncate demo_json;
insert into demo_json(`json`) values ('{"a": 1, "b": 2, "c": {"c1":1, "c2": 2}}');

-- {"a": 1, "b": 2, "c": {"c1":1, "c2": 2}}
select `json` from demo_json;

-- 1
select json_length(`json`, '$.a') from demo_json where id = 1;
-- 2
select json_length(`json`, '$.c') from demo_json where id = 1;
-- 3
select json_length(`json`) from demo_json where id = 1;

json_object

其实就是把一些值转换为object格式，跟函数json_array有点相同

json_object([key, val[, key, val] ...])

json_pretty

该函数就是把文档给打印出来，按照json格式进行打印

-- 1
select json_pretty('1');
-- "a"
select json_pretty('"a"');

-- 返回
-- [
--   1,
--   2,
--   "a"
-- ]
select json_pretty('[1, 2, "a"]');
-- 返回
-- {
--   "a": 1,
--   "b": 12,
--   "c": 39
-- }
select json_pretty('{"a":1, "b":12, "c":39}');

json_remove

从文档中删除指定的元素，然后返回

select json_remove(json_doc, path[, path] ...)

-- {"b": 2, "c": 3}
select json_remove('{"a":1, "b":2, "c":3}', '$.a');

-- {"c": 3}
select json_remove('{"a":1, "b":2, "c":3}', '$.a', '$.b');


-- [3, 2]
select json_remove('[12, 3, 2]', '$[0]');
-- [12, 2]
select json_remove('[12, 3, 2]', '$[1]');

------- 使用在字段上 ------
truncate demo_json;
insert into demo_json(`json`) values ('{"a":12, "b":2}');
update demo_json set `json`=json_remove(`json`, '$.a') where id = 1;
-- {"b": 2}
select `json` from demo_json;

json_search

该函数返回的是指定字符串的路径，就是doc中的字段

json_search(json_doc, one_or_all, search_str[, escape_char[, path] ...])

说明：

one_or_all：

'one'：搜索到一个就直接返回
'all'：搜索到所有的才返回，所有的字段会包装成一个数组

search_str：这个是搜索字段，默认是全部匹配，可以模糊匹配，采用%和，%表示匹配多个，表示匹配一个字符，这个跟like使用方式是一样的
escape_char：这个值转义符，如果搜索的字符中有需要转义的，则请在该字符这了添加，默认是\，通常情况下请填写为空或者null，必须要有一个值
path：更多的指定的字段

注意：该命令只是用于搜索字符使用

-- $.a
select json_search('{"a":"v"}', 'all', "v");

-- $.b
select json_search('{"a":"v", "b":"women is ok"}', 'all', "%is%");

-- $.a
select json_search('{"a":"v", "b":"women is ok", "c":{"c1":"v", "c2":"v2"}}', 'one', "v");

-- ["$.a", "$.c.c1"]
select json_search('{"a":"v", "b":"women is ok", "c":{"c1":"v", "c2":"v2"}}', 'all', "v");
-- ["$.a", "$.c.c1", "$.c.c2"]
select json_search('{"a":"v", "b":"women is ok", "c":{"c1":"v", "c2":"v2"}}', 'all', "%v%");

-- ["$.c.c1", "$.c.c2"]
select json_search('{"a":"v", "b":"women is ok", "c":{"c1":"v", "c2":"v2"}}', 'all', "%v%", null, '$.c');

-- 注意：数字搜索全部返回null，这个只是搜索字符的
select json_search('{"a":2}', 'all', 2);
select json_search('{"a":2}', 'all', '2');
select json_search('{"a":2}', 'all', '"2"');
select json_search('{"a":2}', 'all', '%2');
select json_search('{"a":2}', 'all', '%2%');

json_extract

该命令是从doc文件中提取对应的值

select json_extract(json_doc, path[, path] ...)

-- 1
select json_extract('{"a":1, "b":2}', '$.a');
-- [1, 2]
select json_extract('{"a":1, "b":2}', '$.a', '$.b');
-- [1, 2, {"c1": "v1", "c2": "v2"}]
select json_extract('{"a":1, "b":2, "c":{"c1":"v1", "c2":"v2"}}', '$.a', '$.b', '$.c');

json_unquote

该函数用于去除转义符，和函数json_quote是作用相反

json_quote(string)

-- "\"123\""
select json_quote('"123"');
-- "123"
select json_quote('123');
-- 123
select json_unquote('123');
-- 123
select json_unquote('"123"');

json_contains

判断一个文档内容是否包含另外一个内容

json_contains(target, candidate[, path])

-- --- 对象包含：只有全部包含才返回1
-- 1
select json_contains('{"a":12}', '{"a":12}');
-- 0
select json_contains('{"a":1}', '{"a":12}');
-- 1 
select json_contains('{"a":12, "b":2}', '{"a":12}');

-- --- 数组包含：只有全部包含才返回1
-- 1
select json_contains('[1, 2, "a"]', '1');
-- 1
select json_contains('[1, 2, "a"]', '"a"');
-- 1
select json_contains('[1, 2, "a"]', '[1, 2]');
-- 0
select json_contains('[1, 2, "a"]', '[1, 2, "b"]');

-- 嵌套包含，需要指定字段，其中字段是target的字段
-- 0
select json_contains('{"a":1, "b":{"b1":"v1", "b2":"v2"}}', '{"b2":"v2"}');
-- 1
select json_contains('{"a":1, "b":{"b1":"v1", "b2":"v2"}}', '{"b2":"v2"}', '$.b');

json_arrayagg

将结果集聚合为json数组，该函数一般用在group by的命令上面，这样根据某个key聚合，其他的key就可以为数组形式了，这里用json_arrayagg就可以把那些值聚合为json数组

json_arrayagg(col_or_expr)

-- 建表
create table demo_json1(
    `id` bigint unsigned not null auto_increment,
    `num` int not null,
    `json` json not null,
    primary key(`id`)
)engine=innodb;

-- 添加数据
insert into demo_json1(`num`, `json`) values(1, '1');
insert into demo_json1(`num`, `json`) values(1, '{"a":1}');
insert into demo_json1(`num`, `json`) values(2, '{"a":1}');
insert into demo_json1(`num`, `json`) values(2, '{"a":2}');
insert into demo_json1(`num`, `json`) values(2, '{"a":3}');

-- 分组聚合
select `num`, json_arrayagg(`json`) as js from demo_json1 group by `num`;

json_objectagg

该函数用于将多个值聚集为一个json对象

json_objectagg(key, value)

说明：
其中key和value都是当前的数据，最后作为一个对象使用

CREATE TABLE `demo_json1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `num` int NOT NULL,
  `json` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 数据
mysql> select * from demo_json1;
+----+-----+------+
| id | num | json |
+----+-----+------+
|  1 |   1 | "a"  |
|  2 |   2 | "b"  |
|  3 |   3 | "c"  |
+----+-----+------+

-- 聚合查询
{
  "1": "a",
  "2": "b",
  "3": "c"
}
select json_objectagg(`num`,`json`) from demo_json1;

注意：
如果key对应的值相同，但是value对应的值不同，则这个时候怎么办，默认是随机的，按照查询出来的顺序指定，最后查到的就覆盖前面的，如下

mysql> select * from demo_json1;
+----+-----+------+
| id | num | json |
+----+-----+------+
|  1 |   1 | "a"  |
|  2 |   2 | "b"  |
|  3 |   3 | "c"  |
|  4 |   3 | "d"  |
+----+-----+------+

-- 聚合查询：
{
  "1": "a",
  "2": "b",
  "3": "d"
}
select json_objectagg(`num`,`json`) from demo_json1;

如果我们要指定呢，则可以通过函数over子句，这个子句是怎么用，如下

-- over内部为空，则会返回所有的聚合
mysql> select json_objectagg(`num`,`json`) over() as js from demo_json1;
+--------------------------------+
| js                             |
+--------------------------------+
| {"1": "a", "2": "b", "3": "d"} |
| {"1": "a", "2": "b", "3": "d"} |
| {"1": "a", "2": "b", "3": "d"} |
| {"1": "a", "2": "b", "3": "d"} |
+--------------------------------+

-- 返回四条数据，因为数据内部有四条数据
mysql> select * from demo_json1;
+----+-----+------+
| id | num | json |
+----+-----+------+
|  1 |   1 | "a"  |
|  2 |   2 | "b"  |
|  3 |   3 | "c"  |
|  4 |   3 | "d"  |
+----+-----+------+

我们可以给over子句内部添加order by 进行排序，这样就有了顺序了，其中order by 官网中说是按照如下进行排序的

range between unbounded preceding and current row

mysql> select json_objectagg(`num`,`json`) over(order by `num`) as js from demo_json1;
+--------------------------------+
| js                             |
+--------------------------------+
| {"1": "a"}                     |
| {"1": "a", "2": "b"}           |
| {"1": "a", "2": "b", "3": "d"} |
| {"1": "a", "2": "b", "3": "d"} |
+--------------------------------+

？？为啥显示是这个？官网上好像不是这样显示的，未知，暂时遗留吧

json_merge_patch

该函数用于对多个文档进行合并

json_merge_patch(json_doc, json_doc[, json_doc] ...)


-- 在多个数据中有不是json对象的时候（包括json数组，json数组也认为不是对象），则返回最后一个
-- 2
select json_merge_patch('1', '2');
-- {"a": 1}
select json_merge_patch('1', '{"a":1}');
-- 2
select json_merge_patch('{"a":1}', '2');
-- 3
select json_merge_patch('{"a":1}', '2', '3');
-- {"c": 2}
select json_merge_patch('{"a":1}', '2', '3', '{"c":2}');

-- 若包含json数组，则也不是合并，而是为最后一个
-- {"a": 1}
select json_merge_patch('[1, 2]', '{"a":1}');
-- [1, 2]
select json_merge_patch('{"a":1}', '[1, 2]');
-- [1, 3, 5]
select json_merge_patch('{"a":1}', '[1, 2]', '[1,3, 5]');
-- {"b": 2}
select json_merge_patch('{"a":1}', '[1, 2]', '[1,3, 5]', '{"b":2}');
-- [3, 4]
select json_merge_patch('[1, 2]', '[3, 4]');

-- 所有的都为对象，则进行合并
-- {"a": 1, "c": 2}
select json_merge_patch('{"a":1}','{"c":2}');
-- 如果有key相同，则为最后一个，不同的则合并
-- {"a": 2}
select json_merge_patch('{"a":1}','{"a":2}');
-- {"a": 3}
select json_merge_patch('{"a":1}','{"a":2}', '{"a":3}');
-- {"a": 2, "b": 3}
select json_merge_patch('{"a":1, "b":3}','{"a":2}');

json_array_append

给某些元素的值添加对应的值

json_array_append(json_doc, path, val[, path, val] ...)

-- 对数组添加元素
-- [1]
select json_array_append('[]', '$', 1);
-- [1, 2, 3]
select json_array_append('[1]', '$', 2, '$', 3);
-- [[1, 1], 2, 3]
select json_array_append('[1, 2, 3]', '$[0]', 1);

-- 对对象添加数组元素
-- {"a": ["v", 1], "b": 1}
select json_array_append('{"a": "v", "b":1}', '$.a', 1);
-- [{"a": "v", "b": 1}, 1]
select json_array_append('{"a": "v", "b":1}', '$[0]', 1);

json_array_insert

该函数用于向已有的数组中添加对应的值，这个值的下标是函数的path指定的，指定后，其他的值向后退

json_array_insert(json_doc, path, value[, path, value] ...)

-- [10, 0, 1, 2]
select json_array_insert('[0, 1, 2]', '$[0]', 10);

-- 没有变化，因为需要值为数组才行
select json_array_insert('{"a":1, "b":"v1"}', '$.a[0]', 10);
-- {"a": [10, 1], "b": "v1"}
select json_array_insert('{"a":[1], "b":"v1"}', '$.a[0]', 10);

json_storage_size

返回存储的文档的大小

json_storage_size(json_val)

-- 8
select json_storage_size('[1]');
-- 13
select json_storage_size('{"a":1}');
-- 21
select json_storage_size('{"a":1, "b":12}');

json_contains_path

该函数用于返回对应的path是否存在

json_contains_path(json_doc, one_or_all, path[, path] ...)

-- one 表示后面的路径中只要有一个匹配上就算找到
-- 1
select json_contains_path('{"a":1, "b":2}', 'one', '$.a');
-- 1
select json_contains_path('{"a":1, "b":2}', 'one', '$.a', '$.b');
-- 0
select json_contains_path('{"a":1, "b":2}', 'one', '$.c');
-- 1
select json_contains_path('{"a":1, "b":2}', 'one', '$.a', '$.c');

-- all 要求所有的path都能够找到，只要有一个不存在，则返回0
-- 1
select json_contains_path('{"a":1, "b":2}', 'all', '$.a');
-- 1
select json_contains_path('{"a":1, "b":2}', 'all', '$.a', '$.b');
-- 0
select json_contains_path('{"a":1, "b":2}', 'all', '$.c');
-- 0
select json_contains_path('{"a":1, "b":2}', 'all', '$.a', '$.c');

-- 嵌套内部
-- 1
select json_contains_path('{"a":1, "b":2, "c":{"k1":"v1"}}', 'one', '$.c.k1');

json_merge_preserve

该函数跟json_merge_path有点像，但是merge_path是只有为对象才会合并，而当前这个函数没有那么多限制，会将所有的数据都合并为数组

json_merge_preserve(json_doc, json_doc[, json_doc] ...)

-- 非对象的数据都会作为数组进行合并，而对象的相同的key合并，value会合并为数组
-- ["a", "b"]
select json_merge_preserve('"a"', '"b"');
-- [1, "a"]
select json_merge_preserve('1', '"a"');
-- {"a": 1, "b": 2}
select json_merge_preserve('{"a":1}', '{"b":2}');
-- [{"a": 1}, 1]
select json_merge_preserve('{"a":1}', '[1]');
-- {"a": [1, 2], "b": 3}
select json_merge_preserve('{"a":1}', '{"a":2, "b":3}');

参考：

官网json函数文档
https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html