如何创建JSON类型?
字符串表示
在MySQL中可以使用字符串表示JSON类型,合法的JSON字符串可以自动解析为JSON对象。可以使用JSON_VALID()来验证是否合法, 用JSON_TYPE来判断对象类型。
SET @j = '["abc", 10, null, true, false]';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1, ARRAY
SET @j = '{"k1": "value", "k2": 10}';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1, OBJECT
select JSON_VALID('"k2": 10')
-- 0
SET @j = '10';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1,INTEGER
SET @j = '"STR"';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1,STRING
select JSON_VALID('STR')
-- 0
注意STRING类型的值必须是在双引号内, 如果字符类型的值里面还包含双引号,可以转义或者使用JSON_QUOTE() 函数:
select '"Hello \\"Tom\\""', JSON_QUOTE('Hello "Tom"')
-- "Hello \"Tom\"", "Hello \"Tom\""
JSON_OBJECT([key, val[, key, val] …])
select JSON_OBJECT('name','test','age',12)
-- {"age": 12, "name": "test"}
SELECT JSON_OBJECT();
-- {}
注意JSON对象的Key必须是字符串,如果为JSON_OBJECT提供了非字符串的key,会转成STRING:
select JSON_OBJECT('name','test',45,12)
-- {"45": 12, "name": "test"}
select JSON_OBJECT("name","test",now(),12);
-- {"name": "test", "2022-04-19 13:24:23": 12}
select JSON_OBJECT("name","test",JSON_OBJECT('address','shanghai'),12);
-- {"name": "test", "{\"address\": \"shanghai\"}": 12}
JSON_ARRAY([val[, val] …])
SELECT JSON_ARRAY(1, 'abc','"ASD"',NULL, TRUE,FALSE, CURTIME());
-- [1, "abc", "\"ASD\"", null, true, false, "13:31:14.000000"]
SELECT JSON_ARRAY();
-- []
JSON_QUOTE(string)
用来获取STRING类型的值
select JSON_QUOTE('[1, 2, 3]'),JSON_QUOTE('hi');
-- "[1, 2, 3]", "hi"
CAST(value AS JSON)
select CAST('12' AS JSON),CAST('"12"' AS JSON),CAST(now() as JSON)
-- 12,"12","2022-04-19 14:00:56.000000"
聚合函数JSON_ARRAYAGG和JSON_OBJECTAGG
select JSON_ARRAYAGG('123'),JSON_OBJECTAGG('name','sdf')
-- ["123"],{"name": "sdf"}
JSON_ARRAYAGG和JSON_OBJECTAGG更强大的功能体现在聚合一组表数据上,比如官网的例子:
mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square|
+------+-----------+-------+
4 rows in set (0.00 sec)
mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value) |
+------+---------------------------------------+
| 2 | {"color": "red", "fabric": "silk"} |
| 3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)
如何访问JSON类型?
查询JSON类型时,会转成utf8mb4字符集(校对规则utf8mb4_bin)的字符串
set @j = JSON_OBJECT('name','test','age',12);
select @j, CHARSET(@j), COLLATION(@j);
-- {"age": 12, "name": "test"},utf8mb4,utf8mb4_bin
JSON_EXTRACT
可以使用-> 操作或者 ->>操作来访问,->>比->多了一步JSON_UNQUOTE
create table t_json(
jdoc json
);
insert into t_json(jdoc) values(JSON_OBJECT('name','hello'));
select jdoc -> '$.name',jdoc->>'$.name' from t_json ;
-- "hello",hello
-> 等价于JSON_EXTRACT而->>等价于JSON_UNQUOTE(JSON_EXTRACT()), 但是函数可以用来获取多个值:
set @j = JSON_OBJECT('name','test','age',12);
select JSON_EXTRACT(@j,'$.name') from t_json;
-- "test"
select JSON_EXTRACT(@j,'$.name','$.age') from t_json;
-- ["test", 12]
set @j = JSON_ARRAY('12',12,'SDF');
select JSON_EXTRACT(@j,'$[0]') from t_json;
-- "12"
select JSON_EXTRACT(@j,'$[1]','$[0]') from t_json;
-- [12, "12"]
对数组来说,有个比较有意思的属性last,最后元素的索引:
set @j = JSON_ARRAY('12',12,'SDF');
select JSON_EXTRACT(@j,'$[last]') from t_json;
-- "SDF"
select JSON_EXTRACT(@j,'$[last-1]','$[last-2]') from t_json;
-- [12, "12"]
JSON_VALUE
与JSON_EXTRACT不同的是,JSON_EXTRACT获取到的是值还是一个JSON类型但是JSON_VALUE还会将值转成相应数据库类型:
set @j = JSON_ARRAY('12',12,'SDF');
select JSON_VALUE(@j,'$[last]'),JSON_EXTRACT(@j,'$[last]') from t_json;
-- SDF,"SDF"
select JSON_VALUE(@j,'$[0]'),JSON_EXTRACT(@j,'$[0]') from t_json;
-- 12,"12"
select JSON_VALUE(@j,'$[1]'),JSON_EXTRACT(@j,'$[1]') from t_json;
-- 12,12
JSON_SEARCH
MySQL提供很多的函数以各种访问JSON类型,JSON_SEARCH在其中也非常重要的,可以用来获取访问路径。他有两种模式one获取匹配的第一个路径,all获取所有匹配到的路径:
SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'one', 'abc');
-- "$[0]"
SELECT JSON_SEARCH(@j, 'all', 'abc');
-- ["$[0]", "$[2].x"]
也可以限定搜索范围:
SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
-- "$[2].x"
还可以模糊匹配:
SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'all', '%bc%');
-- ["$[0]", "$[2].x", "$[3].y"]
SELECT JSON_SEARCH(@j, 'all', '%bc_');
-- "$[3].y"
如何更新JSON类型的值?
JSON_SET()
修改已存在的或者添加新的:
SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
-- ["a", {"b": [1, false]}, [10, 20, 2]]
需要注意一下异常情况,比如不存在c这个数组元素,后面的数组索引4不存在:
SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].c[0]', 1, '$[2][4]', 2);
-- ["a", {"b": [true, false]}, [10, 20, 2]]
-- 可以看到c[0]没有更新,数组元素2加到了最后
但是可以添加新的key,也可以添加数组元素,比如:
SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].c', 'hello', '$[1].d', JSON_ARRAY(3));
-- ["a", {"b": [true, false], "c": "hello", "d": [3]}, [10, 20]]
JSON_INSERT()
只会添加新的值,不会修改现有元素:
SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2, '$[1].c', 3);
-- ["a", {"b": [true, false], "c": 3}, [10, 20, 2]]
JSON_REPLACE()
只会修改现有元素,忽略新增的值或元素:
SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2, '$[0]', JSON_ARRAY(1,2));
-- [[1, 2], {"b": [1, false]}, [10, 20]]
JSON_REMOVE()
SET @j = '["a", {"b": [1, 2,3]}, [10, 20]]';
SELECT JSON_REMOVE(@j, '$[2][3]', '$[1].b[0]', '$[1].b[0]');
-- ["a", {"b": [3]}, [10, 20]]
这里注意b中的元素被移除了两个
关于JSON类型的in-place 更新
JSON类型的部分元素更新,满足以下条件,优化器可以使用in-place更新,而不是删除旧文件并将新文件完整地写入:
- 列是JSON
- UPDATE 语句使用JSON_SET()、 JSON_REPLACE()或 JSON_REMOVE()进行更新
- 修改的目标列和函数的输入列是同一列
- 必须是修改现有值或元素,不能添加新的
- 新值的所占大小不能超过旧的值
如何对JSON类型使用索引?
JSON列,和其他二进制类型的列一样,不能直接建立索引。
但是可以通过Generated Column对JSON中的元素建立索引:
CREATE TABLE t_json (
jdoc JSON,
g varchar(30) GENERATED ALWAYS AS (jdoc->>"$.name"),
INDEX i (g)
);
insert into t_json(jdoc)
values('{"name":"abc","age":1}'),
('{"name":"bcd","age":2}'),
('{"name":"def","age":3}'),
('{"name":"efg","age":4}'),
('{"name":"hij","age":5}');
SELECT * FROM t_json where g = 'abc';
查询可以使用索引。这里需要注意抽取值的操作,字符串最好使用->>, 而不是->。