如何用Oracle 19c生成规范JSON文件？结合MS Report Builder场景

你在MS Report Builder中基于Oracle 19c环境，当前SQL通过json_object生成单条JSON对象但输出为每行一个对象的表格格式，需要转换为逗号分隔的JSON对象序列或标准JSON数组格式，以下是具体实现方法：

现有SQL代码 #

SELECT json_object(
'id'        VALUE   REFVAL,
'recdate'   VALUE   DATEAPRECV,
'apptype'   VALUE   APPTYP,
'status'    VALUE   STAT,
'person'    VALUE   NAME
)
FROM APPLICATIONS
WHERE APPTYP = 'S10'

当前输出（表格形式） #

每行一个独立JSON对象：

{"id" : "1", "recdate" : "01-01-01", "apptype" : "S10", "status" : "COMP", "person" : "John"}
{"id" : "2", "recdate" : "02-02-02", "apptype" : "S10", "status" : "REG", "person" : "Mary"}

期望输出格式 #

格式1：逗号分隔的JSON对象序列 #

{"id" : "1", "recdate" : "01-01-01", "apptype" : "S10", "status" : "COMP", "person" : "John"},{"id" : "2", "recdate" : "02-02-02", "apptype" : "S10", "status" : "REG", "person" : "Mary"}

格式2：标准JSON数组 #

[
   {
    "id" : "1",
    "recdate" : "01-01-01",
    "apptype" : "S10",
    "status" : "COMP",
    "person" : "John"
   },
   {
    "id" : "2",
    "recdate" : "02-02-02",
    "apptype" : "S10",
    "status" : "REG",
    "person" : "Mary"
   }
]

解决方案 #

1. 生成标准JSON数组（推荐） #

使用Oracle 12cR2及以上版本支持的JSON_ARRAYAGG函数，将所有json_object生成的对象聚合为一个JSON数组：

SELECT json_arrayagg(
    json_object(
        'id'        VALUE   REFVAL,
        'recdate'   VALUE   DATEAPRECV,
        'apptype'   VALUE   APPTYP,
        'status'    VALUE   STAT,
        'person'    VALUE   NAME
    )
    ORDER BY REFVAL -- 可选：按指定字段排序数组内元素
    RETURNING CLOB -- 可选：返回CLOB类型避免长度限制
) AS json_result
FROM APPLICATIONS
WHERE APPTYP = 'S10';

• ORDER BY子句：可指定数组内JSON对象的排序顺序，按需添加
• RETURNING CLOB：当结果集较大时，用CLOB类型避免VARCHAR2的长度限制

2. 生成逗号分隔的JSON对象序列 #

使用LISTAGG函数将单个JSON对象拼接为逗号分隔的字符串：

SELECT listagg(
    json_object(
        'id'        VALUE   REFVAL,
        'recdate'   VALUE   DATEAPRECV,
        'apptype'   VALUE   APPTYP,
        'status'    VALUE   STAT,
        'person'    VALUE   NAME
    ), ','
    WITHIN GROUP (ORDER BY REFVAL) -- 可选：排序拼接顺序
) AS json_sequence
FROM APPLICATIONS
WHERE APPTYP = 'S10';

注意：LISTAGG默认返回VARCHAR2类型，最大长度为4000字符（Oracle 12c及以上可扩展到32767），如果结果超过长度限制，可改用XMLAGG结合XMLSERIALIZE实现：

SELECT rtrim(xmlserialize(content xmlagg(xmlelement(e, json_object(
        'id'        VALUE   REFVAL,
        'recdate'   VALUE   DATEAPRECV,
        'apptype'   VALUE   APPTYP,
        'status'    VALUE   STAT,
        'person'    VALUE   NAME
    ) || ',').extract('//text()') ORDER BY REFVAL) AS CLOB), ',') AS json_sequence
FROM APPLICATIONS
WHERE APPTYP = 'S10';

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