【MySQL】MySQL的JSON 数据类型_mysql jsonobject

一、简单测试

1. 创建测试数据库

MySQL数据类型详解：https://dev.mysql.com/doc/refman/8.0/en/json.html

MySQLJSON函数：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html

数据库DDL语句：

-- auto-generated definition
create table log
(
    id   int auto_increment
        primary key,
    data text null
);
1
2
3
4
5
6
7

2. JSON增删改查语句

1. 新增

JSON_ARRAY

Json数组：JSON_ARRAY([val[, val] …])

# 插入json数组
insert into log (data) value (JSON_ARRAY('hot'));
insert into log (data) value (JSON_ARRAY('hot','new'));
1
2
3

JSON_OBJECT

Json对象：JSON_OBJECT([key, val[, key, val] …])

# 插入JSON对象
insert into log (data) value (JSON_OBJECT('id', 87, 'name', 'carrot'));
1
2

Json字符串：JSON_QUOTE(string)

# 插入JSON字符串
insert into log (data) value (JSON_QUOTE('null'));
insert into  log(data) value (JSON_QUOTE('"null"'));
insert into  log(data) value (JSON_QUOTE('[1, 2, 3]'));
1
2
3
4

此时，测试数据有：（还临时添加了几条数据）

2. 查询

JSON_CONTAINS

JSON_CONTAINS(target, candidate[, path])

---

Json数组：

# 查询 data 是否包含 数值1
select  * from log a where JSON_CONTAINS(a.data,'1');
select  * from log a where JSON_CONTAINS(a.data,CONCAT(1));
1
2
3

# 查询 data 是否包含 字符串hot
select  * from log a where JSON_CONTAINS(a.data,'hot'); # 报错。因为在MySQL中，去除单引号后，hot非法
select  * from log a where JSON_CONTAINS(a.data,'"hot"');
select  * from log a where JSON_CONTAINS(a.data,CONCAT('"','hot','"'));
1
2
3
4

---

Json对象：

# 查询 data里面 key为a 的 value 为1 的列
select  * from log a where JSON_CONTAINS(a.data,'1','$.a');
# 查询 data里面 key为a 的 value 为{"d": 4} 的列
select  * from log a where JSON_CONTAINS(a.data,'{"d": 4}','$.a');
# 查询 data里面 key为c的 value 为 {"d": 4} 的列
select  * from log a where JSON_CONTAINS(a.data,'{"d": 4}','$.c');
# 查询 data里面 key为d 的 value 为 字符串5 的列
select  * from log a where JSON_CONTAINS(a.data,'"5"','$.d');
select  * from log a where JSON_CONTAINS(a.data,CONCAT('"','5','"'),'$.d');
1
2
3
4
5
6
7
8
9

JSON_EXTRACT、column->path

JSON_EXTRACT(json_doc, path[, path] …)

-> 运算符只是简单地提取一个值。

查询 data里面 key为id 的列

# 查询 data里面 key为id 的列
select  * from log a where a.data->'$.id';
select  * from log a where JSON_EXTRACT(a.data,'$.id') ;
1
2
3

查询 data里面 key为id 的 value为87

# 查询 data里面 key为id 的 value为87
select  * from log a where a.data->'$.id' = 87;
select  * from log a where JSON_EXTRACT(a.data,'$.id') = 87;
# 等价于：select  * from log a where JSON_CONTAINS(a.data,'87','$.id');
1
2
3
4

查看date里面key为id，value为87的行，取出Json对象里面的值

# 查看date里面key为id，value为87的行，取出Json对象里面的值
select JSON_EXTRACT(data,'$.id') id
     ,JSON_EXTRACT(data,'$.name') name
from log
where JSON_EXTRACT(data,'$.id' )= 87;

select data->'$.id' id
     ,data->'$.name' name
from log
where data->'$.id' = 87;
1
2
3
4
5
6
7
8
9
10

column->>path

这是一个改进的、不带引号的提取操作符。

select  a.data  -> '$.coverImage' as '->'
       ,(a.data  ->> '$.coverImage')  as '->>'
       , concat('http://localhost:15672/',a.data  -> '$.coverImage') as '->Concat'
       , concat('http://localhost:15672/',a.data  ->> '$.coverImage')  as '->>Concat'
from log a
where a.id = 98
1
2
3
4
5
6

JSON_SEARCH()

返回JSON文档中给定字符串的路径。

mysql json数组对象，单独提取某一个对象并提取某一个属性数据

MySQL - json_search 小结

搜索JSON对象数组里面字符串值

create table project_approve_info
(
    id          int          not null
        primary key,
    detail_data json         null,
    name        varchar(200) null
);

INSERT INTO test.project_approve_info (id, detail_data, name) VALUES (1, '[{"name": "技术活动名称", "value": "210"}, {"name": "计划评审时间", "value": "2021-03-20"}, {"name": "项目名称", "value": "修改编辑时的选人问题"}, {"name": "项目类别", "value": "地震勘探"}, {"name": "成果名称", "value": "成果名称"}, {"name": "评审主持人", "value": "张三"}, {"name": "工作类别", "value": "建设"}, {"name": "内容", "value": "内容"}, {"name": "表格", "value": "1"}, {"name": "费用合计", "value": "102917.1"}, {"name": "会议通知", "value": "通知"}]', '10');

INSERT INTO test.project_approve_info (id, detail_data, name) VALUES (2, '[{"name": "技术活动名称", "value": "活动1"}, {"name": "计划评审时间", "value": "2021-03-20"}, {"name": "项目名称", "value": "修改编辑时的选人问题"}, {"name": "项目类别", "value": "地震勘探"}, {"name": "成果名称", "value": "成果名称"}, {"name": "评审主持人", "value": "张三"}, {"name": "工作类别", "value": "建设"}, {"name": "内容", "value": "内容"}, {"name": "表格", "value": "1"}, {"name": "费用合计", "value": "200.1"}, {"name": "会议通知", "value": "通知"}]', '12');


 SELECT
  *
FROM
  project_approve_info
WHERE JSON_EXTRACT( detail_data,
  JSON_UNQUOTE(
  REPLACE ( JSON_SEARCH( detail_data, 'all', '费用合计', NULL, '$[*].name' ), 'name', 'value' ) ) ) LIKE '%20%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

搜索json数组对象的landId = 1004

INSERT INTO plant_message_record (id,land_info) VALUES (8905, '[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (8906, '[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (8908, '[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (8909, '[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (89120, '[{"landId": "1175", "landName": "99999999999"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (89130, '[{"landId": "1175", "landName": "99999999999"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (8917, '[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (8919, '[{"landId": "1005", "landName": "05"}, {"landId": "1004", "landName": "04"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (8920, '[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (8922, '[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]');
INSERT INTO plant_message_record (id,land_info) VALUES (8923, '[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]');

# 包含landId，[1].landId
SET @j0 = '[{"landId": "1018", "landName": "18"}, {"landId": "1004", "landName": "19"}, {"landId": "1020", "landName": "20"}]';
# 不包含landId, null
SET @j1 = '[{"landId": "1018", "landName": "18"}, {"landId": "1019", "landName": "19"}, {"landId": "1020", "landName": "20"}]';
# 包含landId，[0].landId
SET @j2 = '[{"landId": "1004", "landName": "18"}, {"landId": "1019", "landName": "19"}, {"landId": "1020", "landName": "20"}]';
# 包含landId，[0].landId;[1].landId
SET @j3 = '[{"landId": "1004", "landName": "18"}, {"landId": "1004", "landName": "19"}, {"landId": "1020", "landName": "20"}]';


select JSON_SEARCH(@j0, 'all', '1004', NULL, '$[*].landId'),
       JSON_SEARCH(@j1, 'all', '1004', NULL, '$[*].landId'),
       JSON_SEARCH(@j2, 'all', '1004', NULL, '$[*].landId'),
       JSON_SEARCH(@j3, 'all', '1004', NULL, '$[*].landId');
       
# 输出的结果为       
+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
|JSON_SEARCH(@j0, 'all', '1004', NULL, '$[*].landId')|JSON_SEARCH(@j1, 'all', '1004', NULL, '$[*].landId')|JSON_SEARCH(@j2, 'all', '1004', NULL, '$[*].landId')|JSON_SEARCH(@j3, 'all', '1004', NULL, '$[*].landId')|
+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
|"$[1].landId"                                       |NULL                                                |"$[0].landId"                                       |["$[0].landId", "$[1].landId"]                      |
+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+

# 从这里就可以得到，只要找得到 landId = 1004 返回结果不为null的，也就是说，需要where JSON_SEARCH() is not null 就是需要我们的数据
SELECT  pmr.id,pmr.land_info
FROM plant_message_record pmr
WHERE  JSON_SEARCH(pmr.land_info, 'all', '1004', NULL, '$[*].landId') is not null
order by pmr.create_time desc
LIMIT 10;

# 结果如下：
+----+----------------------------------------------------------------------------+
|id  |land_info                                                                   |
+----+----------------------------------------------------------------------------+
|8919|[{"landId": "1005", "landName": "05"}, {"landId": "1004", "landName": "04"}]|
|8920|[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]|
|8922|[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]|
|8923|[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]|
|8916|[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]|
|8917|[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]|
|8905|[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]|
|8906|[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]|
|8908|[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]|
|8909|[{"landId": "1004", "landName": "04"}, {"landId": "1005", "landName": "05"}]|
+----+----------------------------------------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

3. 修改

JSON_ARRAY_APPEND

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)

将值附加到 JSON 文档中指定数组的末尾并返回结果。

# 查看当前 99 的 data 信息
select a.data from log a where a.id = 99;
1
2

# 更改99的信息
update log a
set data = JSON_ARRAY_APPEND(a.data, '$[1]', 1)
where a.id = 99;
# 查看更改后的 99 的 data 信息
select a.data from log a where a.id = 99;
1
2
3
4
5
6