如何用Oracle SQL生成JSON？版本12.1.0.2.0无法使用JSON

如何用Oracle SQL生成JSON？版本12.1.0.2.0无法使用JSON_ARRAYAGG

阿华AIGC实验室

2026-5-25

在Oracle 12.1.0.2中生成JSON数组的替代方案

嘿，我完全理解你的困境——12.1.0.2确实没赶上Oracle的JSON函数大升级，JSON_ARRAYAGG是12cR2（12.2）才引入的。不过别担心，咱们有几种经过实践验证的替代方法，能在不升级版本的情况下生成合法的JSON数组，我给你详细拆解：

方案一：用`LISTAGG`手动拼接JSON（简单直接）

这是最容易上手的方法，核心思路是用LISTAGG把每行数据拼接成JSON对象字符串，再包裹上数组的[]符号。需要注意处理特殊字符（比如双引号）和NULL值，避免生成无效JSON。

示例代码（针对员工表）

假设你有一张EMPLOYEES表，包含EMP_ID（数字型）和EMP_NAME（字符型）字段，要生成包含员工信息的JSON数组：

SELECT 
    '[' || 
    LISTAGG(
        -- 拼接单个JSON对象，处理NULL和双引号转义
        '{"emp_id":' || emp_id || 
        ',"emp_name":' || CASE 
                            WHEN emp_name IS NULL THEN 'null' 
                            ELSE '"' || REPLACE(emp_name, '"', '\"') || '"' 
                         END || 
        '}',
        ',' -- 对象之间的分隔符
    ) WITHIN GROUP (ORDER BY emp_id) || 
    ']' AS employee_json_array
FROM EMPLOYEES;

注意事项

LISTAGG默认返回VARCHAR2(4000)，如果聚合后的JSON长度超过这个限制，会抛出“字符串过长”的错误。这种情况下建议用方案二。
除了双引号，如果字段里有反斜杠、换行符等特殊字符，需要额外添加REPLACE处理，比如REPLACE(REPLACE(emp_name, '\', '\\'), '"', '\"')。

方案二：用XML函数生成（更可靠，支持大文本）

如果你的数据量较大，或者字段包含大量特殊字符，用XML系列函数（XMLAGG+XMLELEMENT）会更稳妥——XML会自动处理部分转义逻辑，而且支持生成CLOB类型，避免长度限制。

示例代码（CLOB版本，支持大数组）

SELECT 
    '[' || 
    -- 去掉末尾多余的逗号，再包裹数组符号
    RTRIM(
        REPLACE(
            REPLACE(
                -- 将XML聚合结果序列化为CLOB
                XMLSERIALIZE(
                    CONTENT XMLAGG(
                        XMLELEMENT(
                            "dummy", -- 临时标签，后续会被替换掉
                            '{"emp_id":' || emp_id || 
                            ',"emp_name":' || CASE 
                                                WHEN emp_name IS NULL THEN 'null' 
                                                ELSE '"' || REPLACE(emp_name, '"', '\"') || '"' 
                                             END || 
                            '}'
                        ) ORDER BY emp_id
                    ) AS CLOB
                ),
                '<dummy>', '' -- 移除开始标签
            ),
            '</dummy>', ',' -- 移除结束标签，替换为对象分隔符
        ),
        ',' -- 移除最后一个对象后的多余逗号
    ) || 
    ']' AS employee_json_array
FROM EMPLOYEES;