如何将MySQL结果集转换为单个JSON OBJECT（MySQL数据类型）

嘿，这个需求我之前在做Oracle Integration Cloud集成项目的时候刚好碰到过！用MySQL自带的JSON函数完全能直接在存储过程里把查询结果转成JSON，这样OIC那边调用完直接拿结果当响应就行，省掉中间的转换步骤，简直是为你的场景量身定做的！

前提条件 #

首先得确保你的MySQL版本是5.7及以上，因为JSON相关的核心函数（比如JSON_OBJECT、JSON_ARRAYAGG）是从5.7版本开始引入的，8.0版本会有更完善的功能支持，但5.7就足够满足你的需求了。

核心实现思路 #

利用MySQL的JSON函数，在存储过程内部直接将查询到的单行/多行数据组装成符合你需求的JSON结构，存储过程执行后直接输出这个JSON字符串，OIC调用时可以直接把这个结果作为REST响应的负载返回，无需额外转换。

示例存储过程代码 #

假设你要查询的是订单主表orders和订单明细表order_items，根据传入的id返回包含订单基本信息+明细的结构化JSON，存储过程可以这么写：

DELIMITER //
CREATE PROCEDURE GetOrderAsJSON(IN p_order_id INT)
BEGIN
    -- 组装包含订单主信息和嵌套明细的JSON对象
    SELECT JSON_OBJECT(
        'order_id', o.id,
        'customer_id', o.customer_id,
        'order_date', o.order_date,
        'total_amount', o.total_amount,
        'items', (
            -- 把订单明细转成JSON数组
            SELECT JSON_ARRAYAGG(
                JSON_OBJECT(
                    'item_id', oi.id,
                    'product_name', oi.product_name,
                    'quantity', oi.quantity,
                    'unit_price', oi.unit_price
                )
            )
            FROM order_items oi
            WHERE oi.order_id = o.id
        )
    ) AS order_json
    FROM orders o
    WHERE o.id = p_order_id;
END //
DELIMITER ;

代码说明 #

• JSON_OBJECT(key, value, ...)：把单个记录的字段键值对组装成一个JSON对象，完美适配单行数据的转换。
• JSON_ARRAYAGG(json_object)：把多行明细数据转换成一个JSON数组，实现嵌套的JSON结构，适合一对多的关联场景。
• 存储过程接收p_order_id参数，和你OIC REST端点的{id}模板参数完全对应，直接传入即可。

特殊场景处理：无匹配记录时的友好返回 #

如果传入的id找不到对应的订单，默认会返回NULL，你可以在存储过程里加个判断，返回更友好的JSON提示：

DELIMITER //
CREATE PROCEDURE GetOrderAsJSON(IN p_order_id INT)
BEGIN
    DECLARE order_count INT;
    -- 先检查是否存在该订单
    SELECT COUNT(*) INTO order_count FROM orders o WHERE o.id = p_order_id;
    
    IF order_count = 0 THEN
        -- 返回错误提示JSON
        SELECT JSON_OBJECT('status', 'error', 'message', '指定ID的订单不存在') AS order_json;
    ELSE
        -- 返回正常订单JSON
        SELECT JSON_OBJECT(
            'order_id', o.id,
            'customer_id', o.customer_id,
            'order_date', o.order_date,
            'total_amount', o.total_amount,
            'items', (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'item_id', oi.id,
                        'product_name', oi.product_name,
                        'quantity', oi.quantity,
                        'unit_price', oi.unit_price
                    )
                )
                FROM order_items oi
                WHERE oi.order_id = o.id
            )
        ) AS order_json
        FROM orders o
        WHERE o.id = p_order_id;
    END IF;
END //
DELIMITER ;

OIC端的适配 #

在OIC里调用这个存储过程时，只需要把返回的order_json字段直接映射到REST响应的负载中即可——因为它已经是标准的JSON格式了，OIC可以直接返回给调用方，完全省去了你之前需要做的转换步骤！

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