MySQL中json字段的操作方法

2023年 4月 30日数据运维张二河

MySQL5.7.8中引入了json字段，这种类型的字段使用的频率比较低，但是在实际操作中，有些业务仍然在用，我们以此为例，介绍下json字段的操作方法：还是从例子看起： mysql create table

MySQL5.7.8中引入了json字段，这种类型的字段使用的频率比较低，但是在实际操作中，有些业务仍然在用，我们以此为例，介绍下json字段的操作方法：

还是从例子看起：

mysql> create table test1(id int,info json);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test1 values (1,'{“name”:”yeyz”,”age”:26}’),(2,'{“name”:”zhangsan”,”age”:30}’),(3,'{“name”:”lisi”,”age”:35}’);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

首先我们创建了一个表test1，其中id是int字段，info是json字段，插入了三条数据，如上：

mysql> select * from test1 where json_extract(info,”$.age”)>=30;
+——+———————————+
| id | info |
+——+———————————+
| 2 | {“age”: 30, “name”: “zhangsan”} |
| 3 | {“age”: 35, “name”: “lisi”} |
+——+———————————+
2 rows in set (0.00 sec)

我们可以通过json_extract的方法得到json中的内容。其中：

1、$符号代表的是json的根目录，

2、我们使用$.age相当于取出来了json中的age字段，

3、当然，在函数最前面，应该写上字段名字info

下面来看json中常用的函数：

a、json_valid判断是否是json字段，如果是，返回1，如果不是，返回0

mysql> select json_valid(2);
+—————+
| json_valid(2) |
+—————+
| 0 |
+—————+
1 row in set (0.01 sec)
mysql> select json_valid(‘{“num”:2}’);
+————————-+
| json_valid(‘{“num”:2}’) |
+————————-+
| 1 |
+————————-+
1 row in set (0.00 sec)

mysql> select json_valid(‘2’);
+—————–+
| json_valid(‘2’) |
+—————–+
| 1 |
+—————–+
1 row in set (0.00 sec)
mysql> select json_valid(‘name’);
+——————–+
| json_valid(‘name’) |
+——————–+
| 0 |
+——————–+
1 row in set (0.00 sec)

这里需要注意的是，如果传入了字符串2，那么，返回结果是1

b、json_keys传回执行json字段最上一层的key值

mysql> select json_keys(‘{“name”:”yeyz”,”score”:100}’);
+——————————————+
| json_keys(‘{“name”:”yeyz”,”score”:100}’) |
+——————————————+
| [“name”, “score”] |
+——————————————+
1 row in set (0.01 sec)
mysql> select json_keys(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95}}’);
+—————————————————————-+
| json_keys(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95}}’) |
+—————————————————————-+
| [“name”, “score”] |
+—————————————————————-+
1 row in set (0.00 sec)
#如果有多层，可以在最后面使用$的方法，拿到其中的某一层的目录
mysql> select json_keys(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95}}’,’$.score’);
+————————————————————————–+
| json_keys(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95}}’,’$.score’) |
+————————————————————————–+
| [“math”, “English”] |
+————————————————————————–+
1 row in set (0.00 sec)

c、json_length函数，返回最上一层的key个数，如果想取到中间的某一层，则可以使用$的方法，如下：

mysql> select json_length(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95},”age”:26}’);
+—————————————————————————+
| json_length(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95},”age”:26}’) |
+—————————————————————————+
| 3 |
+—————————————————————————+
1 row in set (0.00 sec)

mysql> select json_length(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95},”age”:26}’,’$.score’);
+————————————————————————————-+
| json_length(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95},”age”:26}’,’$.score’) |
+————————————————————————————-+
| 2 |
+————————————————————————————-+
1 row in set (0.00 sec)

d、json_depth函数，json文件的深度，测试例子如下：

mysql> select json_depth(‘{“aaa”:1}’),json_depth(‘{}’);
+————————-+——————+
| json_depth(‘{“aaa”:1}’) | json_depth(‘{}’) |
+————————-+——————+
| 2 | 1 |
+————————-+——————+
1 row in set (0.00 sec)

mysql> select json_depth(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95},”age”:26}’);
+————————————————————————–+
| json_depth(‘{“name”:”yeyz”,”score”:{“math”:100,”English”:95},”age”:26}’) |
+————————————————————————–+
| 3 |
+————————————————————————–+
1 row in set (0.00 sec)

这里需要注意的是，形如{‘aa’:1}这种形式的json，其深度是2

e、json_contains_path函数检索json中是否有一个或者多个成员。

mysql> set @j='{“a”:1,”b”:2,”c”:{“d”:4}}’;
Query OK, 0 rows affected (0.00 sec)
#one的意思是只要包含一个成员，就返回1
mysql> select json_contains_path(@j,’one’,’$.a’,’$.e’);
+——————————————+
| json_contains_path(@j,’one’,’$.a’,’$.e’) |
+——————————————+
| 1 |
+——————————————+
1 row in set (0.00 sec)
#all的意思是所有的成员都包含，才返回1
mysql> select json_contains_path(@j,’all’,’$.a’,’$.e’);
+——————————————+
| json_contains_path(@j,’all’,’$.a’,’$.e’) |
+——————————————+
| 0 |
+——————————————+
1 row in set (0.01 sec)

mysql> select json_contains_path(@j,’one’,’$.c.d’);
+————————————–+
| json_contains_path(@j,’one’,’$.c.d’) |
+————————————–+
| 1 |
+————————————–+
1 row in set (0.00 sec)

mysql> select json_contains_path(@j,’one’,’$.a.d’);
+————————————–+
| json_contains_path(@j,’one’,’$.a.d’) |
+————————————–+
| 0 |
+————————————–+
1 row in set (0.00 sec)

f、json_type函数，判断json中的成员的类型，需要和json_extract结合起来使用。

mysql> select * from test1;
+——+———————————+
| id | info |
+——+———————————+
| 1 | {“age”: 26, “name”: “yeyz”} |
| 2 | {“age”: 30, “name”: “zhangsan”} |
| 3 | {“age”: 35, “name”: “lisi”} |
+——+———————————+
3 rows in set (0.00 sec)
#判断name的类型
mysql> select json_type(json_extract(info,”$.name”)) from test1;
+—————————————-+
| json_type(json_extract(info,”$.name”)) |
+—————————————-+
| STRING |
| STRING |
| STRING |
+—————————————-+
3 rows in set (0.00 sec)
#判断age的类型
mysql> select json_type(json_extract(info,”$.age”)) from test1;
+—————————————+
| json_type(json_extract(info,”$.age”)) |
+—————————————+
| INTEGER |
| INTEGER |
| INTEGER |
+—————————————+
3 rows in set (0.00 sec)
#判断name和age组合起来的类型，可以看到是array
mysql> select json_type(json_extract(info,”$.name”,”$.age”)) from test1;
+————————————————+
| json_type(json_extract(info,”$.name”,”$.age”)) |
+————————————————+
| ARRAY |
| ARRAY |
| ARRAY |
+————————————————+
3 rows in set (0.00 sec)

g、*的作用，所有的值,看下面的例子。

{
“a”:1,
“b”:2,
“c”:
{
“d”:4
}
“e”:
{
“d”:
{
“ddd”:
“5”
}
}
}
mysql> set @j='{“a”:1,”b”:2,”c”:{“d”:4},”e”:{“d”:{“ddd”:”5″}}}’;
Query OK, 0 rows affected (0.00 sec)
#所有成员
mysql> select json_extract(@j,’$.*’);
+—————————————+
| json_extract(@j,’$.*’) |
+—————————————+
| [1, 2, {“d”: 4}, {“d”: {“ddd”: “5”}}] |
+—————————————+
1 row in set (0.00 sec)
#所有成员中的d成员
mysql> select json_extract(@j,’$.*.d’);
+————————–+
| json_extract(@j,’$.*.d’) |
+————————–+
| [4, {“ddd”: “5”}] |
+————————–+
1 row in set (0.00 sec)