mysql，二维json数组查询，并再次组装新表，存储过程，临时表，内存表，遍历JSON数组，遍历SELECT的结果

1
2
3
[{"index":[{"name":"格式简述"},{"name":"格式简述"}]},
{"index":[{"name":"格式简述"},{"name":"格式简述"}]}]

1
2
3
4
JSON_SEARCH( dimension, 'one', '%度%', NULL, '$[*].index[*].name' ) IS NOT NULL

在dimension 字段中查询 所有 的  维度的  index 元素，的name属性  包含 度 字的数据  没有命中，则返回 null

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
drop table if EXISTS per_package_employee;

CREATE TABLE `per_package_employee` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `site_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '公司id',
  `employee_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `package_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '所属绩效考核包id',
  `dimension` json NOT NULL COMMENT '维度配置数组列表',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `pe_site_package_employee` (`site_id`,`package_id`,`employee_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8268 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='绩效考核个人记录';

DELETE FROM per_package_employee where id >0;
INSERT INTO `per_package_employee`(`id`, `site_id`, `employee_id`, `package_id`, `dimension`) VALUES (1, 569, 8059, 1, '[{"name": "维度A", "index": [{"id": "569_60949a9a863de", "name": "维度A-1", "need": 0, "type": 1, "unit": "V", "custom": 1, "remark": "的风帆股份人头付费", "target": "50", "weight": "20", "schedule": [], "per_remark": "打发打发发放", "score_info": [{"id": 3, "weight": 100, "employees": [{"point": null, "title": "直接主管评分：王五(100%)", "remark": "", "employee_id": 4004}], "multi_executor": 1}], "point_limit": 0, "result_type": "none", "reviewer_id": "", "mamage_record": [], "result_employee_id": 0}], "index_num": 0, "index_type": 1, "weight_type": 0, "target_index": 1, "total_weight": 0, "dimension_weight": 100, "all_dimension_index_weight": 0}, {"name": "维度B", "index": [{"id": 6, "name": "非量化指标1", "need": 0, "type": 2, "unit": "", "custom": 0, "remark": "电放费GV各回各家发给", "target": "0", "weight": 1, "schedule": [], "per_remark": "发给回个话更换", "score_info": [{"id": 3, "weight": 100, "employees": [{"point": null, "title": "直接主管评分：王五(100%)", "remark": "", "employee_id": 4004}], "multi_executor": 1}], "point_limit": 0, "result_type": "none", "reviewer_id": 0, "mamage_record": [], "result_employee_id": 0}], "index_num": 0, "index_type": 2, "weight_type": 0, "target_index": 1, "total_weight": 0, "dimension_weight": 100, "all_dimension_index_weight": 0}, {"name": "维度C", "index": [{"id": 7, "name": "加分项1", "need": 0, "type": 3, "unit": "", "custom": 0, "remark": "", "target": "0", "weight": 0, "schedule": [], "per_remark": "的发广告和和个", "score_info": [{"id": 3, "weight": 100, "employees": [{"point": null, "title": "直接主管评分：王五(100%)", "remark": "", "employee_id": 4004}], "multi_executor": 1}], "point_limit": "", "result_type": "none", "reviewer_id": "", "mamage_record": [], "result_employee_id": 0}], "index_num": 0, "index_type": 3, "weight_type": 0, "target_index": 1, "total_weight": 0, "dimension_weight": 100, "all_dimension_index_weight": 0}, {"name": "维度D", "index": [{"id": 8, "name": "扣分项1", "need": 0, "type": 4, "unit": "", "custom": 0, "remark": "", "target": "0", "weight": 0, "schedule": [], "per_remark": "大大方方要统一", "score_info": [{"id": 3, "weight": 100, "employees": [{"point": null, "title": "直接主管评分：王五(100%)", "remark": "", "employee_id": 4004}], "multi_executor": 1}], "point_limit": "", "result_type": "none", "reviewer_id": 0, "mamage_record": [], "result_employee_id": 0}], "index_num": 0, "index_type": 4, "weight_type": 0, "target_index": 1, "total_weight": 0, "dimension_weight": 100, "all_dimension_index_weight": 0}]');
INSERT INTO `per_package_employee`(`id`, `site_id`, `employee_id`, `package_id`, `dimension`) VALUES (2, 569, 8059, 1, '[{"name": "维度A", "index": [{"id": "569_60949a9a863de", "name": "维度A-1", "need": 0, "type": 1, "unit": "V", "custom": 1, "remark": "的风帆股份人头付费", "target": "50", "weight": "100", "schedule": [], "per_remark": "打发打发发放", "score_info": [{"id": 3, "weight": "20", "employees": [{"point": null, "title": "直接主管评分：5号(20%)", "remark": "", "employee_id": 8064}, {"point": null, "title": "直接主管评分：王五(20%)", "remark": "", "employee_id": 4004}], "multi_executor": 1}, {"id": 4, "weight": "20", "employees": [{"point": null, "title": "二级主管评分：6号(20%)", "remark": "", "employee_id": 8065}, {"point": null, "title": "二级主管评分：5号(20%)", "remark": "", "employee_id": 8064}], "multi_executor": 1}, {"id": 5, "weight": "20", "employees": [{"point": null, "title": "三级主管评分：张三(20%)", "remark": "", "employee_id": 4000}, {"point": null, "title": "三级主管评分：6号(20%)", "remark": "", "employee_id": 8065}], "multi_executor": 1}, {"id": 6, "weight": "20", "employees": [{"point": null, "title": "四级主管评分：赵六(20%)", "remark": "", "employee_id": 4006}, {"point": null, "title": "四级主管评分：张三(20%)", "remark": "", "employee_id": 4000}], "multi_executor": 1}, {"id": 7, "weight": "10", "employees": [{"point": null, "title": "五级主管评分：李四(10%)", "remark": "", "employee_id": 4002}, {"point": null, "title": "五级主管评分：赵六(10%)", "remark": "", "employee_id": 4006}], "multi_executor": 1}, {"id": 8, "weight": "10", "employees": [{"point": null, "title": "六级主管评分：8号(10%)", "remark": "", "employee_id": 8067}, {"point": null, "title": "六级主管评分：李四(10%)", "remark": "", "employee_id": 4002}], "multi_executor": 1}], "point_limit": 0, "result_type": "none", "reviewer_id": "", "mamage_record": [], "result_employee_id": 0}], "index_num": 0, "index_type": 1, "weight_type": 0, "target_index": 1, "total_weight": 0, "dimension_weight": 100, "all_dimension_index_weight": 0}, {"name": "维度B", "index": [{"id": 6, "name": "非量化指标1", "need": 0, "type": 2, "unit": "", "custom": 0, "remark": "电放费GV各回各家发给", "target": "0", "weight": "100", "schedule": [], "per_remark": "发给回个话更换", "score_info": [{"id": 3, "weight": "20", "employees": [{"point": null, "title": "直接主管评分：5号(20%)", "remark": "", "employee_id": 8064}, {"point": null, "title": "直接主管评分：王五(20%)", "remark": "", "employee_id": 4004}], "multi_executor": 1}, {"id": 4, "weight": "20", "employees": [{"point": null, "title": "二级主管评分：6号(20%)", "remark": "", "employee_id": 8065}, {"point": null, "title": "二级主管评分：5号(20%)", "remark": "", "employee_id": 8064}], "multi_executor": 1}, {"id": 5, "weight": "20", "employees": [{"point": null, "title": "三级主管评分：张三(20%)", "remark": "", "employee_id": 4000}, {"point": null, "title": "三级主管评分：6号(20%)", "remark": "", "employee_id": 8065}], "multi_executor": 1}, {"id": 6, "weight": "20", "employees": [{"point": null, "title": "四级主管评分：赵六(20%)", "remark": "", "employee_id": 4006}, {"point": null, "title": "四级主管评分：张三(20%)", "remark": "", "employee_id": 4000}], "multi_executor": 1}, {"id": 7, "weight": "10", "employees": [{"point": null, "title": "五级主管评分：李四(10%)", "remark": "", "employee_id": 4002}, {"point": null, "title": "五级主管评分：赵六(10%)", "remark": "", "employee_id": 4006}], "multi_executor": 1}, {"id": 8, "weight": "10", "employees": [{"point": null, "title": "六级主管评分：8号(10%)", "remark": "", "employee_id": 8067}, {"point": null, "title": "六级主管评分：李四(10%)", "remark": "", "employee_id": 4002}], "multi_executor": 1}], "point_limit": 0, "result_type": "none", "reviewer_id": "", "mamage_record": [], "result_employee_id": 0}], "index_num": 0, "index_type": 2, "weight_type": 0, "target_index": 1, "total_weight": 0, "dimension_weight": 100, "all_dimension_index_weight": 0}, {"name": "维度C", "index": [{"id": 7, "name": "加分项1", "need": 0, "type": 3, "unit": "", "custom": 0, "remark": "", "target": "0", "weight": 0, "schedule": [], "per_remark": "的发广告和和个", "score_info": [{"id": 3, "weight": "20", "employees": [{"point": null, "title": "直接主管评分：5号(20%)", "remark": "", "employee_id": 8064}, {"point": null, "title": "直接主管评分：王五(20%)", "remark": "", "employee_id": 4004}], "multi_executor": 1}, {"id": 4, "weight": "20", "employees": [{"point": null, "title": "二级主管评分：6号(20%)", "remark": "", "employee_id": 8065}, {"point": null, "title": "二级主管评分：5号(20%)", "remark": "", "employee_id": 8064}], "multi_executor": 1}, {"id": 5, "weight": "20", "employees": [{"point": null, "title": "三级主管评分：张三(20%)", "remark": "", "employee_id": 4000}, {"point": null, "title": "三级主管评分：6号(20%)", "remark": "", "employee_id": 8065}], "multi_executor": 1}, {"id": 6, "weight": "20", "employees": [{"point": null, "title": "四级主管评分：赵六(20%)", "remark": "", "employee_id": 4006}, {"point": null, "title": "四级主管评分：张三(20%)", "remark": "", "employee_id": 4000}], "multi_executor": 1}, {"id": 7, "weight": "10", "employees": [{"point": null, "title": "五级主管评分：李四(10%)", "remark": "", "employee_id": 4002}, {"point": null, "title": "五级主管评分：赵六(10%)", "remark": "", "employee_id": 4006}], "multi_executor": 1}, {"id": 8, "weight": "10", "employees": [{"point": null, "title": "六级主管评分：8号(10%)", "remark": "", "employee_id": 8067}, {"point": null, "title": "六级主管评分：李四(10%)", "remark": "", "employee_id": 4002}], "multi_executor": 1}], "point_limit": "", "result_type": "none", "reviewer_id": "", "mamage_record": [], "result_employee_id": 0}], "index_num": 0, "index_type": 3, "weight_type": 0, "target_index": 1, "total_weight": 0, "dimension_weight": 100, "all_dimension_index_weight": 0}, {"name": "维度D", "index": [{"id": 8, "name": "扣分项1", "need": 0, "type": 4, "unit": "", "custom": 0, "remark": "", "target": "0", "weight": 0, "schedule": [], "per_remark": "大大方方要统一", "score_info": [{"id": 3, "weight": "20", "employees": [{"point": null, "title": "直接主管评分：5号(20%)", "remark": "", "employee_id": 8064}, {"point": null, "title": "直接主管评分：王五(20%)", "remark": "", "employee_id": 4004}], "multi_executor": 1}, {"id": 4, "weight": "20", "employees": [{"point": null, "title": "二级主管评分：6号(20%)", "remark": "", "employee_id": 8065}, {"point": null, "title": "二级主管评分：5号(20%)", "remark": "", "employee_id": 8064}], "multi_executor": 1}, {"id": 5, "weight": "20", "employees": [{"point": null, "title": "三级主管评分：张三(20%)", "remark": "", "employee_id": 4000}, {"point": null, "title": "三级主管评分：6号(20%)", "remark": "", "employee_id": 8065}], "multi_executor": 1}, {"id": 6, "weight": "20", "employees": [{"point": null, "title": "四级主管评分：赵六(20%)", "remark": "", "employee_id": 4006}, {"point": null, "title": "四级主管评分：张三(20%)", "remark": "", "employee_id": 4000}], "multi_executor": 1}, {"id": 7, "weight": "10", "employees": [{"point": null, "title": "五级主管评分：李四(10%)", "remark": "", "employee_id": 4002}, {"point": null, "title": "五级主管评分：赵六(10%)", "remark": "", "employee_id": 4006}], "multi_executor": 1}, {"id": 8, "weight": "10", "employees": [{"point": null, "title": "六级主管评分：8号(10%)", "remark": "", "employee_id": 8067}, {"point": null, "title": "六级主管评分：李四(10%)", "remark": "", "employee_id": 4002}], "multi_executor": 1}], "point_limit": "", "result_type": "none", "reviewer_id": 0, "mamage_record": [], "result_employee_id": 0}], "index_num": 0, "index_type": 4, "weight_type": 0, "target_index": 1, "total_weight": 0, "dimension_weight": 100, "all_dimension_index_weight": 0}]');


select * from per_package_employee

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
DROP PROCEDURE IF EXISTS test4;

DELIMITER $$

CREATE PROCEDURE test4()
BEGIN

-- pe_id 个人考核记录ID  pe_eid  个人考核记录用户ID temp_length  指标数组的元素个数  i  指标数组的第N个元素 pei_type 遍历过程中的指标类型

declare pe_id,pe_eid,temp_length,i,pei_type int default 0;

-- 个人考核记录 指标json数组
declare pe_index_info text;
-- pei_name, pei_target,pei_unit  遍历过程中，保存指标ID 名称，目标值，单位
declare pei_id,pei_name,pei_target,pei_unit VARCHAR(255);
-- 遍历标识
declare flag int default 0;

-- 声明游标 (游标中的select 查的是dimension 中 的二维数组 index   [{"index":[{"name":"格式简述"},{"name":"格式简述"}]},{"index":[{"name":"格式简述"},{"name":"格式简述"}]}] )
declare mc cursor for (SELECT
		id,employee_id,JSON_EXTRACT(dimension, '$[*].index[*]') as index_info
	FROM
		per_package_employee 
	WHERE
		site_id = 569 
		AND package_id = 1
		AND JSON_SEARCH( dimension, 'one', '%度%', NULL, '$[*].index[*].name' ) IS NOT NULL);
declare continue handler for not found set flag = 1;



-- 创建零时表 MEMORY  速度快
Drop table if exists t_table_index;
CREATE TEMPORARY TABLE t_table_index
(id int not null,
employee_id int not null,
index_id varchar(255),
index_name varchar(255),
index_type int,
index_target varchar(255),
index_unit varchar(200)) ENGINE = MEMORY;


-- 打开游标
open mc;
-- 获取结果
    l:loop  -- 循环，将表的内容都转移到class2中
    fetch mc into pe_id,pe_eid,pe_index_info;
    if flag=1 THEN
       leave l;
    end if;
    
		set temp_length = JSON_LENGTH(pe_index_info);
		set i =0;
		while i<temp_length
		do
			set pei_name = JSON_EXTRACT(pe_index_info, concat("$[",i,"].name"));
			-- 二次正则匹配
			if pei_name REGEXP '度' then
			
			set pei_id = JSON_EXTRACT(pe_index_info, concat("$[",i,"].id"));
			
			set pei_type = JSON_EXTRACT(pe_index_info, concat("$[",i,"].type"));
			set pei_target = JSON_EXTRACT(pe_index_info, concat("$[",i,"].target"));
			set pei_unit = JSON_EXTRACT(pe_index_info, concat("$[",i,"].unit"));
			
			insert into t_table_index (id,employee_id,index_id,index_type,index_name,index_target,index_unit) VALUES (pe_id,pe_eid,pei_id,pei_type,pei_name,pei_target,pei_unit);
			
			end if;
			set i = i+1;
		end while;
    
-- 关闭游标
end loop;
close mc;
select * from t_table_index;

END $$

DELIMITER;


CALL test4()