走进存储过程-json合并&嵌套循环

飞熊 • 2024年1月3日下午3:25 • Java • 阅读 246

背景

有两个json类型的字段，现在要将这两个字段合并成一个。

表格&数据准备

-- 创建表
CREATE TABLE `json_merge_demo` (
  `id` int NOT NULL,
  `arr1` json DEFAULT NULL,
  `arr2` json DEFAULT NULL,
  `obj` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 加入几条数据
INSERT INTO json_merge_demo (id, arr1, arr2) VALUES
(1, '[1,2,3]', '[{"a":"a","b":"b"}]'),
(2, '[4,5,6]', '[{"c":"c","b":"d"}]'),
(3, '[7,8,9]', '[{"e":"e","f":"f"}]');

🪢执行完后看起来像这样

💡
我们的需求是将 arr1 和 arr2 合并到 obj 中
arr1 中的每一个子项都将做为一个键，值是 arr2
看起来像这样：{ key1: arr2, key2: arr2 }

分析

由于两个都是 json 数组，我们又需要合并且需要遍历 arr1。难免会循环组装普通的查询无法解决
创建一个存储过程来解决
我们是批量处理数据，势必会用到游标。而每一条数据又需遍历 arr1 。到此确定了大体架构为两层循环。

代码实现

-- 删除存储过程 json_merge_fn，如果有的话
drop procedure if exists json_merge_fn;
-- 创建存储过程 json_merge_fn
-- 括号内可加参数。格式：json_merge_fn(filedName type...)
create procedure json_merge_fn()
-- 实际的逻辑，需要用 begin end 包起来
begin
    -- 声明局部变量，必须是在 begin 后开始，例如不能在循环体中再去声明
    -- 声明局部变量格式：declare varName[,varName2] type [default defaultVal]
    -- 可在一行声明多个同类型的局部变量
    declare i,j,n,pId int; -- i,j,n 分别是游标结束标记、内层循环下标、内层循环次数
    declare jsonArr,jsonArr2,jsonObj json;
		-- 定义游标。格式 declare varName cursor for 查询语句
    declare list cursor for select id, arr1, arr2 from json_merge_demo;
    -- 游标遍历完成后进行的操作，这里是将 i 设置为 1
    declare continue handler for not found set i = 1;
    -- 设置 i = 0
    set i = 0;
    -- 打开游标
    open list;
    -- 取一行数据，注意变量顺序对应查询语句的顺序。并且字段名不能和变量名相同
    fetch list into pId,jsonArr,jsonArr2;
    -- while 循环，i <> 1就一直循环
    -- 格式：while 条件表达式 do 循环体 end while;
    while i <> 1
        do
            -- 第一层循环体，初始化第二层循环所需变量
            set j = 0, jsonObj = json_object(), n = json_length(jsonArr);
            while j < n
                do
                    -- json字段路径：对象 $.xxx，数组 $[x]，注意对象键不能是数字但可以加双引号
                    -- json_extract：从 jsonArr 中取出下标j处的元素
                    -- json_insert：插入一个字段
                    select json_insert(jsonObj, concat('$."', json_extract(jsonArr, concat('$[', j, ']')), '"'), jsonArr2)
                    into jsonObj;
                    -- j + 1
                    set j = j + 1;
                end while;
            -- 执行业务操作
            update json_merge_demo set obj=jsonObj where id = pId;
            -- 取下一行数据
            fetch list into pId,jsonArr,jsonArr2;
        end while;
    -- 关闭游标
    close list;
end;
-- 调用
CALL json_merge_fn();