如何创建接收JSON输入并返回JSON的MySQL自定义函数

测试函数报错原因 #

你编写的测试函数执行报错的核心原因是：MySQL不存在JSON()这个直接构造JSON值的内置函数，构造JSON对象需要使用JSON_OBJECT()，构造JSON数组需要使用JSON_ARRAY()，语法错误导致了执行异常。
如果只是要验证入参出参逻辑，测试函数可以改成如下写法：

DELIMITER //

DROP FUNCTION IF EXISTS json_extract_object_by_value;
CREATE FUNCTION json_extract_object_by_value(x JSON) RETURNS JSON
DETERMINISTIC
BEGIN
    -- 返回测试JSON对象
    RETURN JSON_OBJECT("code",200,"data",x);
END//

DELIMITER ;

业务场景完整实现 #

你需要的按值查找对应JSON对象的函数完整实现如下：

DELIMITER //

DROP FUNCTION IF EXISTS json_extract_object_by_value;
CREATE FUNCTION json_extract_object_by_value(jsonComponents JSON) RETURNS JSON
DETERMINISTIC
BEGIN
    -- 查找目标值对应的路径
    SET @target_path = JSON_SEARCH(jsonComponents, 'one', 'com.parallelorigin.code.ecs.components.Identity');
    -- 无匹配结果直接返回NULL，避免后续逻辑报错
    IF @target_path IS NULL THEN
        RETURN NULL;
    END IF;
    -- 路径去引号后截取第一段，得到对象所在路径
    SET @obj_path = SUBSTRING_INDEX(JSON_UNQUOTE(@target_path), '.', 1);
    -- 提取对应JSON对象返回
    RETURN JSON_EXTRACT(jsonComponents, @obj_path);
END//

DELIMITER ;

函数调用示例 #

-- 构造测试JSON数据
SET @test_input = JSON_OBJECT(
    "identity", JSON_OBJECT(
        "type", "com.parallelorigin.code.ecs.components.Identity",
        "id", 1001,
        "name", "test_entity"
    ),
    "transform", JSON_OBJECT(
        "type", "com.parallelorigin.code.ecs.components.Transform",
        "x", 10,
        "y", 20
    )
);

-- 调用函数查询结果
SELECT json_extract_object_by_value(@test_input);

调用后返回结果为：
{"id": 1001, "name": "test_entity", "type": "com.parallelorigin.code.ecs.components.Identity"}

注意事项 #

• 函数入参声明为JSON类型即可正常接收JSON格式输入，返回值声明RETURNS JSON即可返回JSON类型结果，不需要额外转换
• 建议增加空值判断逻辑，避免JSON_SEARCH未匹配到值时返回NULL导致后续执行异常
• 仅MySQL 5.7及以上版本支持JSON相关内置函数，低于该版本无法使用该实现

内容的提问来源于stack exchange，提问作者genaray