mysql(一) json数据类型

一、MYSQL中json类型的数据处理

​ MYSQL 5.7.8中引入了json字段类型

json字段的操作方法

初始化数据

-- 创建表 含有字段id、aley， 其中aley为json类型
mysql> show create table test;
+-------+----------------------------------+
| Table | Create Table  
+-------+----------------------------------+
| test  | CREATE TABLE `test` (
  `aley` json DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+---------------------------------+
1 row in set (0.00 sec)

-- 插入数据
mysql> insert into test values ('{"name":"aley","age":18}', 1),('{"name":"szx","age":30}',2),('{"name":"wwj","age":35}', 3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+-----------------------------+----+
| aley                        | id |
+-----------------------------+----+
| {"age": 18, "name": "aley"} |  1 |
| {"age": 30, "name": "szx"}  |  2 |
| {"age": 35, "name": "wwj"}  |  3 |
+-----------------------------+----+
3 rows in set (0.00 sec)

json_valid()

json_valid可以判断字段是否是json类型，如果是则返回1 不是返回0

mysql> select json_valid(aley) from test;
+------------------+
| json_valid(aley) |
+------------------+
|                1 |
|                1 |
|                1 |
+------------------+
3 rows in set (0.00 sec)

mysql> select json_valid(9);
+---------------+
| json_valid(9) |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

json_extract()

使用json_extract获取json内的数据，json_extract需要两个参数， 第一个参数是字段名，第二个参数是需要取的json值，$表示根节点，$.age就是根节点下的age值，如果是多层json可以一直接着后面.key

mysql> select json_extract(aley, "$.age") as age from test;
+------+
| age  |
+------+
| 18   |
| 30   |
| 35   |
+------+
3 rows in set (0.00 sec)

json_keys()

使用json_keys可以查看最上层的所有key, 如果是多层的json只会返回传入的最上层的key

mysql> select json_keys(aley) from test;
+-----------------+
| json_keys(aley) |
+-----------------+
| ["age", "name"] |
| ["age", "name"] |
| ["age", "name"] |
+-----------------+
3 rows in set (0.01 sec)

mysql> select json_keys('{"a":{"b":1}}');
+----------------------------+
| json_keys('{"a":{"b":1}}') |
+----------------------------+
| ["a"]                      |
+----------------------------+
1 row in set (0.00 sec)

mysql> select json_keys(json_extract('{"a":{"b":1}}', '$.a'));
+-------------------------------------------------+
| json_keys(json_extract('{"a":{"b":1}}', '$.a')) |
+-------------------------------------------------+
| ["b"]                                           |
+-------------------------------------------------+
1 row in set (0.02 sec)

json_type()

使用json_type可以查看类型

mysql> select json_type('[1,2,3]');
+----------------------+
| json_type('[1,2,3]') |
+----------------------+
| ARRAY                |
+----------------------+
1 row in set (0.00 sec)

mysql> select json_type('{"a":1}');
+----------------------+
| json_type('{"a":1}') |
+----------------------+
| OBJECT               |
+----------------------+
1 row in set (0.00 sec)

mysql> select json_type('"a"');
+------------------+
| json_type('"a"') |
+------------------+
| STRING           |
+------------------+
1 row in set (0.00 sec)

json_array()

使用json_array可以获得一个json数组,传入一个空的或者多个值，返回这些值的json数组

mysql> select json_array("a", "b", now());
+------------------------------------------+
| json_array("a", "b", now())              |
+------------------------------------------+
| ["a", "b", "2021-06-08 10:36:50.000000"] |
+------------------------------------------+
1 row in set (0.00 sec)

json_extract

使用json_extract可以获取json里面的值

mysql> select json_extract(aley, '$.name') from test;
+------------------------------+
| json_extract(aley, '$.name') |
+------------------------------+
| "aley"                       |
| "szx"                        |
| "wwj"                        |
+------------------------------+
3 rows in set (0.00 sec)

注: json_extract 第一个参数是json数据， 第二个参数是取json值的路径， $为根节点。后面跟json的键(例：json为 {"a":1}, 要取a的值 $.a)

​ 如果是多层嵌套的json可以接着点，如果是json的值是数组可以在键后面跟索引取对应的值

mysql> select json_extract('{"a": [1,2,3]}', '$.a');
+---------------------------------------+
| json_extract('{"a": [1,2,3]}', '$.a') |
+---------------------------------------+
| [1, 2, 3]                             |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract('{"a": [1,2,3]}', '$.a[*]');
+------------------------------------------+
| json_extract('{"a": [1,2,3]}', '$.a[*]') |
+------------------------------------------+
| [1, 2, 3]                                |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract('{"a": [1,2,3]}', '$.a[0]');
+------------------------------------------+
| json_extract('{"a": [1,2,3]}', '$.a[0]') |
+------------------------------------------+
| 1                                        |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract('{"a": [1,2,3]}', '$.a[1]');
+------------------------------------------+
| json_extract('{"a": [1,2,3]}', '$.a[1]') |
+------------------------------------------+
| 2                                        |
+------------------------------------------+
1 row in set (0.00 sec)

​