MySQL 自定义函数——压平

ztwcy6688

已于 2024-04-23 09:19:36 修改

阅读量174

点赞数 6

分类专栏： MYSQL 文章标签： mysql 数据库

于 2024-04-18 14:27:53 首次发布

本文链接：https://blog.csdn.net/ztwqy6688/article/details/137920077

版权

MYSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍如何在MySQL中实现一个函数，接收包含嵌套int数组和null值的JSON，通过循环展开、去重并排序，以便处理复杂数据结构。

摘要由CSDN通过智能技术生成

需求：

如何实现一个函数这个函数接收的是一个任意级数的int数组的json，内部各个元素可能是int 或者任意级数int数组json 或者null值。函数使用循环实现将入参平铺成一阶数组最终将这个一阶数组去掉为null的值再去重再按照从小到大排序。

测试用例：

select FlatternJsonOmitnullDistinctSort('[null,[1,2,3],[null,[1,2,3], [323,[2]]],5,6,7]');

输出：

前言：

笔者测试数据库 mysql 8.0.29
如果遇到新建函数权限报错
请修改my.cnf 增加权限
[mysqld]
log_bin_trust_function_creators = 1

实现：

CREATE FUNCTION `FlatternJsonOmitnullDistinctSort`(input_json JSON) RETURNS json
    DETERMINISTIC
BEGIN
    -- 声明变量
    DECLARE flattened_json JSON DEFAULT JSON_ARRAY();
    DECLARE i INT DEFAULT 0;
    DECLARE array_length INT;
    DECLARE element JSON;
    DECLARE sub_element JSON;
    DECLARE j INT;
    DECLARE sub_array_length INT;
    
    SET array_length = JSON_LENGTH(input_json);
    
    -- 循环遍历每个元素
    WHILE i < array_length DO
        SET element = JSON_UNQUOTE(JSON_EXTRACT(input_json, CONCAT('$[', i, ']')));
        -- 检查元素是否为空
        IF JSON_TYPE(element) != 'NULL' THEN  
            -- 检查元素是否为数组
            IF JSON_TYPE(element) = 'ARRAY' THEN
                SET j = 0;
                SET input_json = JSON_REMOVE(input_json, CONCAT('$[', i, ']'));
                SET sub_array_length = JSON_LENGTH(element);
                -- 把内部元素插入到原数组
                WHILE j < sub_array_length DO
                    SET sub_element = JSON_UNQUOTE(JSON_EXTRACT(element, CONCAT('$[', j, ']')));
                    SET input_json = JSON_ARRAY_INSERT(input_json, CONCAT('$[', j + i, ']'), sub_element);
                    SET j = j + 1;
                END WHILE;
                -- 从第一个重新检查
                SET i = i - 1;
                SET array_length = array_length + sub_array_length;
            ELSE
                -- 如果元素不是null且不是数组，则加入结果数组
               SET flattened_json = JSON_ARRAY_APPEND(flattened_json, '$', CAST(element AS UNSIGNED));
            END IF;	
        END IF;	
        SET i = i + 1;
    END WHILE;	
		-- 将结果数组去重并排序
		SET flattened_json = (SELECT JSON_ARRAYAGG(value) FROM (
        SELECT DISTINCT CAST(value AS SIGNED INTEGER) AS value
        FROM JSON_TABLE(flattened_json, '$[*]' COLUMNS(value VARCHAR(1024) PATH '$')) AS jtbl
        ORDER BY value
    ) AS sorted_values);
		
		
    RETURN flattened_json;
		
		
END